Tag: sql

want to get the Email information from XML, But getting error

CREATE TABLE XMLTABLE(id int IDENTITY PRIMARY KEY,XML_DATA XML,DATE DATETIME); go INSERT INTO XMLTABLE(XML_DATA,DATE) SELECT CONVERT(XML,BULKCOLUMN)AS DATA,getdate() FROM OPENROWSET(BULK ‘c:\Demo.xml’,SINGLE_BLOB)AS x go DECLARE @XML AS XML DECLARE @OUPT AS INT DECLARE @SQL NVARCHAR (MAX) SELECT @XML= XML_DATA FROM XMLTABLE EXEC sp_xml_preparedocument @OUPT OUTPUT,@XML,'<root xmlns:d=”http://abc” xmlns:ns2=”http://def” />’ SELECT EMAILR FROM OPENXML(@OUPT,’d:ns2:FORM/ns2:Form1/ns2:Part/ns2:Part1/ns2:Ba’) WITH (EMAILR [VARCHAR](100) ‘ns2:EmailAddress’) EXEC sp_xml_removedocument […]

Calculate duration between login and logout time when some records are incomplete

I want to calculate the total time a user spent on a website. There are 3 scenarios. Records of user’s login time and logout time exist. –>The total time should be the total of time differences between login and logout. There are records of user’s login time, but there is no logout time. –>The total […]

Restricting Delete/Update on a table for a Login

Is there a way to restrict a particular user from making any modification on particular table. I am thinking to create a Instead of trigger and some how get the login info and restrict the user from running delete/update statements. But is there any better way to do this, like Sql server has some login […]

How to convert several rows in one row with different selected fields in SQL Server

This question already has an answer here: Find first non-null values for multiple columns 4 answers

SQL Server Query to compare 2 select statements data

Can you please help me in combining the below 2 queries. The result should be as shown in attached image. I would like to compare the balance between 2 queries and flag the balance when state balance is less than city balance. select CT_ID, sum(balance) as StateBalance from tblApp where StateJurisdiction=’New york’ group by CT_ID […]

Select table and column dynamically based on other table rows

I have following table and values, Tb_name column_name1 column_name2 Citator_KTLO_CC Date_Created Date_Modified Citator_KTLO_QA Date_Created Date_Modified I want to select dynamically column from table, so the result is like this: Select Date_Created,Date_Modified from Citator_KTLO_CC and in next loop it will select for second row, like Select Date_Created,Date_Modified from Citator_KTLO_QA How can i do this by using […]

MS SQL: update only the last record of each group

I have a table in MS SQL Server that has some unique and duplicate records. Let’s say my table is like Name Modified Visibility UserTypeEx x 24.05.2015 1 4096 y 01.01.2014 0 4096 z 01.04.2016 1 4096 x 05.03.2015 1 4096 y 06.08.2015 1 4097 y 05.07.2014 1 4096 As you can see, I have […]

SQL split and merge string

this is my problem: I got a string from a column like this: **0756FJ89045GJD38**.pdf Now i have to generate a path by this string: /home/ars/07/56/FJ/89/04/5G/JD/38/0756FJ89045GJD38.pdf I have to take two characters and build it up to one path level from left to right. Maybe u can help me, thanks!

SQL Server : join all left table and repeat right

I have to join tables in a following way: Table A: 1 2 3 4 5 Table B: A B The result table should be: 1 A 2 B 3 A 4 B 5 A Do you have any ideas how to do this?

How to efficiently group by date with a timezone specified?

I’m currently running an aggregate query, summing amounts sold (say) on a given date. select convert(date, datetimesold), sum(amountsold) from tblSold group by convert(date, datetimesold) where datetimesold is a datetime value. The convert(date,…) gets rid of the time value, so the group by can group by whole day. Already this isn’t efficient, as it requires a […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.