Tag: tsql

SQL, from , seperated rows to colums

Create table test123 ( SeqId int, string1 varchar(100), string2 varchar(100) ) insert into test123(SeqId,string1,string2)values (10,’a,b,c’,’x,y,z’), (20,’d,e,f’,’p,q,r’) output SeqId RowNum String1 String2 10 1 a x 10 2 b y 10 3 c z 20 1 d p 20 2 e q 20 3 f r

How to get no of days from MON-YYYY data

Please let me know how can i get number of days in a month when I give date in MMM-YYYY format. Eg: JAN-2017 = 31 Any helps appreciated.. Thanks

How do I subtract inside of an IN clause in SQL?

I inherited a ASP application that I’m moving to a PHP codebase. One query that I’m struggling with is as follows select * from ( select TERM_REPORTING, LEVEL_GROUPING_DESC, New_primary_college_Desc, ID_count from Enrollment ) a pivot(sum(ID_count) for TERM_REPORTING in ( [” & 201609 – 400 & “], [” & 201609 – 300 & “], [” & […]

How to avoid UPDATE statement locking out the entire table when updating large number of records

I am fairly new to locks and hints. I have a table with very frequent SELECT and INSERT operations. The table has 11 million records. I have added a new column to it and I need to copy over the data from an existing column in the same table to the new column. I am […]

Merge script between 2 tables and not working

I have a code that i was still wondering why it should not work, I want to merge these two tables and look at the code below and tables with error generated. Please the tables are just extracted, and have more rows than pasted below. Just sample MERGE INTO [server1].[DATABASE].[dbo].[MD_ToolsMaintDate] WITH (HOLDLOCK) AS TARGET USING […]

Filtering consecutive dates in SQL Server

I would like to filter consecutive dates from my holiday table if any of the consecutive date is yesterday. Below is my code to get consecutive dates with row numbers. SELECT RW = ROW_NUMBER() OVER( PARTITION BY GRP ORDER BY HolidayDate), HolidayDate FROM (SELECT HolidayDate, DATEDIFF(Day, ‘1900-01-01’, HolidayDate) – ROW_NUMBER() OVER (ORDER BY HolidayDate) AS […]

How to create comma delimited list from table with dynamic columns

I want to be able to grab all of the records in a table into a comma delimited list that I can then use to insert into a table on another database. Due to permission restrictions on the customer’s server I cannot access any of the options when right-clicking on the database name, and all […]

Insert dynamic WHERE condition

I would like to dynamically search for results inside table. That is why I’ve created @MEST_CDO_SEARCH variable. This variable looks like part of the query, after it is dynamically filled with results: DECLARE @MEST_CDO_SEARCH NVARCHAR(MAX); SELECT @MEST_CDO_SEARCH = STUFF((SELECT ‘OR MEST.MEST_CDO LIKE ”%’ + _MEST_CDO2 + ‘%” ‘ FROM @tblEAN128 FOR xml path(”)),1,3,”) After this […]

How to get the Employee records in different scenarios

I have a stored procedure in my database to return the employee details from Employee table to my application. SPHR_Employee_Get(@P_PK INT,@P_ACTIVE TINYINT) Employee table structure is EMP_PK INT, EMP_NAME NVARCHAR(200), EMP_ACTIVE TINYINT Values for EMP_ACTIVE: 0 = InActive / 1 = Active My requirement is: I need to get the employee(s) in the following scenarios, […]

TSQL Merge 2 Ids into Temp Table

I’m working on a stored procedure in TSQL on SQL Server 2012. When I call the stored procedure. My problem: I’m inserting the values into a table Projects and want to store the inserted.ProjectId together with oID from the importing table into a temporary Table; I don’t store the oID from the importing table into […]

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