May 20, 2014 at 5:08 am
Here is my ddl ..
/* Data generation for #LocalTempTable */
DECLARE @Date1 datetime
DECLARE @Date2 datetime
SET @Date2 = '03-18-10'
SET @Date1 = '02-18-10'
DECLARE @p_Str NVARCHAR(max)
;WITH mycte AS
(
SELECT @Date1 DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 <= @Date2
)
SELECT @p_Str = SUBSTRING(
(SELECT ',' + '[' +CONVERT(VARCHAR,s.DateValue,10) +'] INT'
FROM mycte s
ORDER BY s.DateValue
FOR XML PATH('')),2,200000) OPTION (MAXRECURSION 0)
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID('tempdb..' + '#LocalTempTable'))
BEGIN
DROP TABLE #LocalTempTable
END
CREATE TABLE #LocalTempTable
(
ClientID INT
)
EXEC ('alter table #LocalTempTable add ' + @p_Str )
/* Data generation for #LocalTempTable completed */
/* data generation for #temp */
CREATE TABLE #temp (clientID INT,cnt INT,CreateDate DATETIME)
INSERT #temp
SELECT '7','3','02-18-10' UNION ALL
SELECT '158','3','02-18-10' UNION ALL
SELECT '203','1','02-18-10' UNION ALL
SELECT '117','3','02-19-10' UNION ALL
SELECT '158','1','02-19-10' UNION ALL
SELECT '178','2','02-19-10' UNION ALL
SELECT '7','1','02-22-10' UNION ALL
SELECT '31','1','02-22-10' UNION ALL
SELECT '98','1','02-22-10' UNION ALL
SELECT '158','5','02-22-10' UNION ALL
SELECT '178','1','02-22-10' UNION ALL
SELECT '203','1','02-22-10' UNION ALL
SELECT '98','2','02-23-10' UNION ALL
SELECT '108','1','02-23-10' UNION ALL
SELECT '158','1','02-23-10' UNION ALL
SELECT '174','1','02-23-10' UNION ALL
SELECT '178','3','02-23-10' UNION ALL
SELECT '203','3','02-23-10' UNION ALL
SELECT '7','1','02-24-10' UNION ALL
SELECT '98','1','02-24-10' UNION ALL
SELECT '108','4','02-24-10' UNION ALL
SELECT '158','3','02-24-10' UNION ALL
SELECT '178','1','02-24-10' UNION ALL
SELECT '203','1','02-24-10' UNION ALL
SELECT '7','2','02-25-10' UNION ALL
SELECT '98','1','02-25-10' UNION ALL
SELECT '158','2','02-25-10' UNION ALL
SELECT '177','1','02-25-10' UNION ALL
SELECT '178','9','02-25-10' UNION ALL
SELECT '158','1','02-26-10' UNION ALL
SELECT '178','5','02-26-10' UNION ALL
SELECT '203','1','02-26-10' UNION ALL
SELECT '7','1','03-01-10' UNION ALL
SELECT '7','1','03-02-10' UNION ALL
SELECT '98','1','03-02-10' UNION ALL
SELECT '158','9','03-02-10' UNION ALL
SELECT '178','4','03-02-10' UNION ALL
SELECT '7','1','03-03-10' UNION ALL
SELECT '31','1','03-03-10' UNION ALL
SELECT '98','1','03-03-10' UNION ALL
SELECT '157','1','03-03-10' UNION ALL
SELECT '174','1','03-03-10' UNION ALL
SELECT '178','4','03-03-10' UNION ALL
SELECT '7','1','03-04-10' UNION ALL
SELECT '31','1','03-04-10' UNION ALL
SELECT '157','2','03-04-10' UNION ALL
SELECT '158','1','03-04-10' UNION ALL
SELECT '178','2','03-04-10' UNION ALL
SELECT '7','1','03-05-10' UNION ALL
SELECT '108','2','03-05-10' UNION ALL
SELECT '158','1','03-05-10' UNION ALL
SELECT '178','2','03-05-10' UNION ALL
SELECT '7','2','03-08-10' UNION ALL
SELECT '31','2','03-08-10' UNION ALL
SELECT '98','3','03-08-10' UNION ALL
SELECT '174','1','03-08-10' UNION ALL
SELECT '178','1','03-08-10' UNION ALL
SELECT '203','2','03-08-10' UNION ALL
SELECT '108','1','03-09-10' UNION ALL
SELECT '157','1','03-09-10' UNION ALL
SELECT '158','4','03-09-10' UNION ALL
SELECT '174','2','03-09-10' UNION ALL
SELECT '178','6','03-09-10' UNION ALL
SELECT '158','3','03-10-10' UNION ALL
SELECT '178','2','03-10-10' UNION ALL
SELECT '189','1','03-10-10' UNION ALL
SELECT '7','1','03-11-10' UNION ALL
SELECT '31','3','03-11-10' UNION ALL
SELECT '98','1','03-11-10' UNION ALL
SELECT '108','1','03-11-10' UNION ALL
SELECT '157','1','03-11-10' UNION ALL
SELECT '158','3','03-11-10' UNION ALL
SELECT '178','8','03-11-10' UNION ALL
SELECT '203','1','03-11-10' UNION ALL
SELECT '31','1','03-12-10' UNION ALL
SELECT '158','5','03-12-10' UNION ALL
SELECT '174','1','03-12-10' UNION ALL
SELECT '178','4','03-12-10' UNION ALL
SELECT '31','2','03-15-10' UNION ALL
SELECT '178','3','03-15-10' UNION ALL
SELECT '189','1','03-15-10' UNION ALL
SELECT '7','2','03-16-10' UNION ALL
SELECT '108','1','03-16-10' UNION ALL
SELECT '158','2','03-16-10' UNION ALL
SELECT '158','1','03-17-10' UNION ALL
SELECT '174','1','03-17-10' UNION ALL
SELECT '178','1','03-17-10'
SELECT * from #LocalTempTable
SELECT cnt ,clientID,CONVERT(VARCHAR, CreateDate,10)Created_date
FROM #temp
DROP TABLE #LocalTempTable
DROP TABLE #temp
what i want is to have cnt to be inserted from #temp to #LocalTempTable with respect to created date and clientID column,if cnt is not found then it should be 0 .
Thank you
May 20, 2014 at 6:24 am
Good Morning!
The simplest solution to this would be to use PIVOT to flip the data into the format you are looking for. Please try out this SQL and let me know if it works the way you are looking for it to:
-- Create table of distinct dates for use in pivot
SELECT
DISTINCT CONVERT(VARCHAR, CreateDate,10) AS CreateDate
INTO #distinct_dates_to_insert
FROM #temp
-- Create string of unique dates for use in dynamic SQL
DECLARE @date_list_to_insert VARCHAR(MAX) = ''
SELECT
@date_list_to_insert = @date_list_to_insert + '[' + CONVERT(VARCHAR, CreateDate,10) + '], '
FROM #distinct_dates_to_insert
SET @date_list_to_insert = LEFT(@date_list_to_insert, (LEN(@date_list_to_insert) - 1)) -- Remove rightmost comma
-- Create variable to hold dynamic SQL command
DECLARE @CMD VARCHAR(MAX)
-- Build dynamic SQL to PIVOT over the range of dates based on the above distinct dates from #temp
SET @CMD = '
SELECT
clientID,
' + @date_list_to_insert + '
FROM
(SELECT cnt,
clientID,
CONVERT(VARCHAR, CreateDate,10) Created_date
FROM #temp) CLIENTDATA
PIVOT
(
SUM(cnt)
FOR Created_date IN
( ' + @date_list_to_insert + ')
) AS PVT
'
SELECT @CMD
--INSERT #LocalTempTable
EXEC (@CMD)
Note that the PIVOT only includes dates that are actually used in it. If it is acceptable for your temp table to skip dates with no data, then we are done, otherwise you'll want to have the #temp table include all dates in between with some NULLs or such as filler.
May 20, 2014 at 6:48 am
Ed Pollack (5/20/2014)
Good Morning!The simplest solution to this would be to use PIVOT to flip the data into the format you are looking for. Please try out this SQL and let me know if it works the way you are looking for it to:
-- Create table of distinct dates for use in pivot
SELECT
DISTINCT CONVERT(VARCHAR, CreateDate,10) AS CreateDate
INTO #distinct_dates_to_insert
FROM #temp
-- Create string of unique dates for use in dynamic SQL
DECLARE @date_list_to_insert VARCHAR(MAX) = ''
SELECT
@date_list_to_insert = @date_list_to_insert + '[' + CONVERT(VARCHAR, CreateDate,10) + '], '
FROM #distinct_dates_to_insert
SET @date_list_to_insert = LEFT(@date_list_to_insert, (LEN(@date_list_to_insert) - 1)) -- Remove rightmost comma
-- Create variable to hold dynamic SQL command
DECLARE @CMD VARCHAR(MAX)
-- Build dynamic SQL to PIVOT over the range of dates based on the above distinct dates from #temp
SET @CMD = '
SELECT
clientID,
' + @date_list_to_insert + '
FROM
(SELECT cnt,
clientID,
CONVERT(VARCHAR, CreateDate,10) Created_date
FROM #temp) CLIENTDATA
PIVOT
(
SUM(cnt)
FOR Created_date IN
( ' + @date_list_to_insert + ')
) AS PVT
'
SELECT @CMD
--INSERT #LocalTempTable
EXEC (@CMD)
Note that the PIVOT only includes dates that are actually used in it. If it is acceptable for your temp table to skip dates with no data, then we are done, otherwise you'll want to have the #temp table include all dates in between with some NULLs or such as filler.
Thanks..Ed Pollack
It works..but i have to take all dates even if it is null ..and need to show cnt 0 instead of NULL..so working on it..
May 20, 2014 at 7:08 am
No problem---to get all the columns, just create a date string just like you did at the start, but leave out the INT:
DECLARE @Date1 datetime
DECLARE @Date2 datetime
SET @Date2 = '03-18-10'
SET @Date1 = '02-18-10'
DECLARE @p_Str NVARCHAR(max)
;WITH mycte AS
(
SELECT @Date1 DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 <= @Date2
)
SELECT @p_Str = SUBSTRING(
(SELECT ',' + '[' +CONVERT(VARCHAR,s.DateValue,10) +']'
FROM mycte s
ORDER BY s.DateValue
FOR XML PATH('')),2,200000) OPTION (MAXRECURSION 0)
SELECT @p_Str
At this point, @p_Str will contain all dates in your range. Just replace @date_list_to_insert with @p_Str in the PIVOT and you will get all columns.
At the end of the script, change EXEC (@CMD) to
INSERT #LocalTempTable
EXEC (@CMD)
Now your table is populated with the data you were looking for. Last step, get rid of the NULLs, which can be done with a bit more dynamic SQL---we need to take the date info we used above and put it in table format, so something like this will do it:
SET @CMD = ''
SET @Date2 = '03-18-10'
SET @Date1 = '02-18-10'
DECLARE @p_Str NVARCHAR(max)
;WITH mycte AS
(
SELECT @Date1 DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 <= @Date2
)
SELECT @CMD = @CMD + '
UPDATE #LocalTempTable
SET [' + CONVERT(VARCHAR, DateValue,10) + '] = 0
WHERE [' + CONVERT(VARCHAR, DateValue,10) + '] IS NULL
'
FROM mycte
EXEC (@CMD)
May 20, 2014 at 8:12 am
Hi..Ed Pollack
once again thanks..your code works correctly :-).
meanwhile i come up with below code..just to share
/* Data generation for #LocalTempTable */
DECLARE @Date1 datetime
DECLARE @Date2 datetime
SET @Date2 = '03-18-10'
SET @Date1 = '02-18-10'
DECLARE @p_Str NVARCHAR(max)
;WITH mycte AS
(
SELECT @Date1 DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 <= @Date2
)
SELECT CTE.DateValue, T.cnt,T.ClientId
INTO #temp_final
FROM mycte CTE
LEFT JOIN #temp T ON CONVERT(VARCHAR,CTE.DateValue,10) = CreateDate
OPTION (MAXRECURSION 0)
DECLARE @cols AS NVARCHAR(MAX),@cols2 AS NVARCHAR(MAX),
@query AS VARCHAR(MAX)
select @cols = STUFF((SELECT DISTINCT ',' + 'ISNULL('+QUOTENAME( CONVERT(VARCHAR, DateValue,10))+',0) AS ['+CONVERT(VARCHAR, DateValue,10)+']'
from #temp_final
group by DateValue,ClientId
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @cols2 = STUFF((SELECT DISTINCT ',' + QUOTENAME( CONVERT(VARCHAR, DateValue,10))
from #temp_final
group by DateValue,ClientId
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ClientId,' + @cols + ' from
( select cnt, DateValue,ClientId
from #temp_final where ClientId IS NOT NULL) x
pivot
( max(cnt)
for DateValue in (' + @cols2 + ')
) p '
print(@query)
execute(@query)
DROP TABLE #temp_final
May 20, 2014 at 11:56 am
Megha P (5/20/2014)
Hi..Ed Pollackonce again thanks..your code works correctly :-).
meanwhile i come up with below code..just to share
/* Data generation for #LocalTempTable */
DECLARE @Date1 datetime
DECLARE @Date2 datetime
SET @Date2 = '03-18-10'
SET @Date1 = '02-18-10'
DECLARE @p_Str NVARCHAR(max)
;WITH mycte AS
(
SELECT @Date1 DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 <= @Date2
)
SELECT CTE.DateValue, T.cnt,T.ClientId
INTO #temp_final
FROM mycte CTE
LEFT JOIN #temp T ON CONVERT(VARCHAR,CTE.DateValue,10) = CreateDate
OPTION (MAXRECURSION 0)
DECLARE @cols AS NVARCHAR(MAX),@cols2 AS NVARCHAR(MAX),
@query AS VARCHAR(MAX)
select @cols = STUFF((SELECT DISTINCT ',' + 'ISNULL('+QUOTENAME( CONVERT(VARCHAR, DateValue,10))+',0) AS ['+CONVERT(VARCHAR, DateValue,10)+']'
from #temp_final
group by DateValue,ClientId
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @cols2 = STUFF((SELECT DISTINCT ',' + QUOTENAME( CONVERT(VARCHAR, DateValue,10))
from #temp_final
group by DateValue,ClientId
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ClientId,' + @cols + ' from
( select cnt, DateValue,ClientId
from #temp_final where ClientId IS NOT NULL) x
pivot
( max(cnt)
for DateValue in (' + @cols2 + ')
) p '
print(@query)
execute(@query)
DROP TABLE #temp_final
It's worth noting - you don't need to use that recursive cte to generate the dates, you can you a tally table[/url] instead.
In your code you can replace:
;WITH mycte AS
(
SELECT @Date1 DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 <= @Date2
)
SELECT * FROM mycte;
with this:
SELECT TOP (DATEDIFF(D,@Date1,@Date2)+1) DATEADD(DAY,number,@Date1)
FROM master..spt_values
WHERE type = 'P';
-- Itzik Ben-Gan 2001
May 21, 2014 at 12:08 am
Thanks..Alan Burstein
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply