March 8, 2014 at 9:48 pm
Hi,
Below is my working code for converting rows into columns.
CREATE Procedure [dbo].[GetReports]
(
@Year int = null,
@Month int = null
)
AS
BEGIN
if OBJECT_ID('#DateRange') is not null drop table #DateRange
declare @cols nvarchar(4000);
DECLARE @Query nvarchar(4000);
DECLARE @fromdate DATE,@todate DATE,@DaysCount int;
--set @Month = 8
--set @Year = 2013
IF(@Year is not null and @Year >0 and @Month is not null and @Month >0)
BEGIN
SELECT @fromdate = DATEADD(MONTH, (@year - 1900) * 12 + @month - 1, '1900-01-01')
,@todate = DATEADD(DAY, - 1, DATEADD(MONTH, (@year - 1900) * 12 + @month, '1900-01-01'))
select @DaysCount = datediff(dd,dateadd(dd, 1-day(@fromdate),@fromdate), dateadd(m,1,dateadd(dd, 1-day(@fromdate),@fromdate)))
select @cols = coalesce(@cols +',','') + '[' + [DATE] + ']' from (
SELECT [DATE] = CONVERT(nVARCHAR(100), DATEADD(DD, n, @fromdate), 121)
FROM (
VALUES (0), (1), (2), (3), (4), (5), (6),(7), (8), (9), (10), (11), (12), (13),
(14), (15), (16), (17), (18), (19),(20), (21), (22), (23), (24), (25),
(26), (27), (28), (29), (30),(31)
) num (n) WHERE n < @DaysCount ) d
END
IF(@month > 0 and @Year > 0 )
BEGIN
;WITH DateRange(Date) AS
(
SELECT
@fromdate as Date
UNION ALL
SELECT
DATEADD(day, 1, Date) as Date
FROM
DateRange
WHERE
Date <= @todate
)
SELECT date into #DateRange from DateRange
set @Query = 'SELECT * from
(
select Name as [Name],mydate as [Date_Of_Day] ,COALESCE ( cast(Records as varchar) , cast((cast(newdate as date)) as varchar)) as [Records] from(
SELECT c.Name,Date as mydate,c.GrocessaryStoredelay as delay
,case when b.Records is null then DATEADD(DAY, (nullif(c.GrocessaryStoreDelay,0) + nullif(DataFrequency,0)), a.Date) else convert(datetime,0,101) end as newdate,b.Records
FROM #DateRange a
Cross join
EDEN_Nutrition.dbo.GrocessaryStore c
Left join
dbo.Month_Report b
On c.StoreID = b.StoreID
And a.date = b.Date_Of_Day
) aa where aa.delay is not null)
as p PIVOT ( max([records]) FOR [Date_Of_Day]
IN ('+ @cols+')) AS pvt order by pvt.Name'
END
Print @Query;
EXEC sp_executesql @Query
END
I Love reading Blogs and After being part of this forum i love to read the Jeff Moden's Awesome blogs.
I am trying to learn about Cross Tabs which is alternative to Pivot.
I am keeping my eyes on the concept explained and i am able to understand the concept. But i tried to implement what i learnt on my same and am struggling because on the jeff's Sample on Cross Tabs deals with aggregate function and so for converting rows to columns.
But on my sample i am struggling to implement cross tab. Can any one suggest me is it possible to create Cross Tab on my sample. If yes please post some modified query based on my sample
thanks
March 9, 2014 at 6:12 am
It would help if you also posted the DDL for the tables used by the cross tab query in your stored procedure as well as sample data for those tables and the expected results based on the sample data.
March 9, 2014 at 10:25 am
Hi Lynn,
Thanks for your reply and here is the sample data.
Table: GrocessaryStore
IdGrocessaryStore int identity(1,1) Primary key,
Name varchar(50),GrocessaryStoreDelay int, DataFrequency int, StoreID varchar(20)
With GrocessaryStore as (
select 1 as IdGrocessaryStore,'WallMart' as Name,10 as GrocessaryStoreDelay, 2 as DataFrequency, 100 as StoreID union all
select 2 as IdGrocessaryStore,'Kelly' as Name,13 as GrocessaryStoreDelay, 1 as DataFrequency, 101 as StoreID union all
select 3 as IdGrocessaryStore,'Subway' as Name,12 as GrocessaryStoreDelay, 3 as DataFrequency, 102 as StoreID union all
select 4 as IdGrocessaryStore,'Dominos' as Name,14 as GrocessaryStoreDelay, 5 as DataFrequency, 103 as StoreID union all
select 5 as IdGrocessaryStore,'Pizzahut' as Name,15 as GrocessaryStoreDelay, 3 as DataFrequency, 104 as StoreID union all
select 6 as IdGrocessaryStore,'BigY' as Name,12 as GrocessaryStoreDelay, 8 as DataFrequency, 105 as StoreID union all
select 7 as IdGrocessaryStore,'Target' as Name,19 as GrocessaryStoreDelay, 3 as DataFrequency, 106 as StoreID union all
select 8 as IdGrocessaryStore,'FishingNet' as Name,20 as GrocessaryStoreDelay, 14 as DataFrequency, 107 as StoreID )
Table : Month_Report
IDReport int identity(1,1) primary key,StoreID varchar(20),Date_Of_Day date, Records int
with Month_Report as (
select 1 as IDReport, 100 as StoreID,'2014-02-01' as Date_Of_Day,1500 as Records union all
select 2 as IDReport, 100 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
select 3 as IDReport, 100 as StoreID,'2014-02-03' as Date_Of_Day,1300 as Records union all
select 4 as IDReport, 100 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
select 5 as IDReport, 100 as StoreID,'2014-02-05' as Date_Of_Day,1100 as Records union all
select 6 as IDReport, 100 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
select 7 as IDReport, 100 as StoreID,'2014-02-07' as Date_Of_Day,1200 as Records union all
select 8 as IDReport, 100 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
select 9 as IDReport, 100 as StoreID,'2014-02-09' as Date_Of_Day,1800 as Records union all
select 10 as IDReport, 100 as StoreID,'2014-02-10' as Date_Of_Day,1900 as Records union all
select 11 as IDReport, 100 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
select 12 as IDReport, 100 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
select 13 as IDReport, 100 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
select 14 as IDReport, 100 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
select 15 as IDReport, 100 as StoreID,'2014-02-15' as Date_Of_Day,NULL as Records union all
select 16 as IDReport, 100 as StoreID,'2014-02-16' as Date_Of_Day, 1000 as Records union all
select 17 as IDReport, 101 as StoreID,'2014-02-01' as Date_Of_Day,2500 as Records union all
select 18 as IDReport, 101 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
select 19 as IDReport, 101 as StoreID,'2014-02-03' as Date_Of_Day,NULL as Records union all
select 20 as IDReport, 101 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
select 21 as IDReport, 101 as StoreID,'2014-02-05' as Date_Of_Day,2100 as Records union all
select 22 as IDReport, 101 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
select 23 as IDReport, 101 as StoreID,'2014-02-07' as Date_Of_Day,2200 as Records union all
select 24 as IDReport, 101 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
select 25 as IDReport, 101 as StoreID,'2014-02-09' as Date_Of_Day,3800 as Records union all
select 26 as IDReport, 101 as StoreID,'2014-02-10' as Date_Of_Day,3900 as Records union all
select 27 as IDReport, 101 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
select 28 as IDReport, 101 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
select 29 as IDReport, 101 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
select 30 as IDReport, 101 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
select 31 as IDReport, 101 as StoreID,'2014-02-15' as Date_Of_Day,1600 as Records union all
select 32 as IDReport, 101 as StoreID,'2014-02-16' as Date_Of_Day,1800 as Records union all
select 33 as IDReport, 101 as StoreID,'2014-02-17' as Date_Of_Day,2700 as Records union all
select 34 as IDReport, 101 as StoreID,'2014-02-18' as Date_Of_Day,2600 as Records
)
Please execute the below two select queries and combine them as expected result. The output is two rows. Hope my sample is clear.
Expected Result:
select 'WallMart' as Name,1500 as [2014-02-01],1200 as [2014-02-02],1300 as [2014-02-03],1800 as [2014-02-04],1100 as [2014-02-05],1000 as [2014-02-06],1200 as [2014-02-07],
1400 as [2014-02-08],1800 as [2014-02-09],1900 as [2014-02-10],1700 as [2014-02-11],1000 as [2014-02-12],1200 as [2014-02-13],1700 as [2014-02-14],
'2014-02-27' as [2014-02-15],1000 as [2014-02-16],
'2014-03-01' as [2014-02-17],'2014-03-02' as [2014-02-18],'2014-03-03' as [2014-02-19],'2014-03-04' as [2014-02-20],'2014-03-02' as [2014-02-21],'2014-03-06' as [2014-02-22],'2014-03-07' as [2014-02-23],
'2014-03-08' as [2014-02-24],'2014-03-09' as [2014-02-25],'2014-03-10' as [2014-02-26],'2014-03-11' as [2014-02-27],'2014-03-12' as [2014-02-28]
select 'Kelly' as Name,2500 as [2014-02-01],1200 as [2014-02-02],'2014-02-17' as [2014-02-03],1800 as [2014-02-04],2100 as [2014-02-05],1000 as [2014-02-06],2200 as [2014-02-07],
1400 as [2014-02-08],3800 as [2014-02-09],3900 as [2014-02-10],1700 as [2014-02-11],1000 as [2014-02-12],1200 as [2014-02-13],1700 as [2014-02-14],
1600 as [2014-02-15],1800 as [2014-02-16],
2700 as [2014-02-17],2600 as [2014-02-18],'2014-03-05' as [2014-02-19],'2014-03-06' as [2014-02-20],'2014-03-07' as [2014-02-21],'2014-03-08' as [2014-02-22],'2014-03-09' as [2014-02-23],
'2014-03-10' as [2014-02-24],'2014-03-11' as [2014-02-25],'2014-03-12' as [2014-02-26],'2014-03-13' as [2014-02-27],'2014-03-14' as [2014-02-28]
As i said the The proc i posted on my previous post was working fine. Thought of achieving that through Cross Tabs which will help me to learn about it. Thanks for considering this post and please help me on this
March 10, 2014 at 6:35 pm
CREATE TABLE #GrocessaryStore
(
IdGrocessaryStore int Primary key
,Name varchar(50)
,GrocessaryStoreDelay int
,DataFrequency int
,StoreID varchar(20)
);
With GrocessaryStore as
(
select 1 as IdGrocessaryStore,'WallMart' as Name,10 as GrocessaryStoreDelay, 2 as DataFrequency, 100 as StoreID union all
select 2 as IdGrocessaryStore,'Kelly' as Name,13 as GrocessaryStoreDelay, 1 as DataFrequency, 101 as StoreID union all
select 3 as IdGrocessaryStore,'Subway' as Name,12 as GrocessaryStoreDelay, 3 as DataFrequency, 102 as StoreID union all
select 4 as IdGrocessaryStore,'Dominos' as Name,14 as GrocessaryStoreDelay, 5 as DataFrequency, 103 as StoreID union all
select 5 as IdGrocessaryStore,'Pizzahut' as Name,15 as GrocessaryStoreDelay, 3 as DataFrequency, 104 as StoreID union all
select 6 as IdGrocessaryStore,'BigY' as Name,12 as GrocessaryStoreDelay, 8 as DataFrequency, 105 as StoreID union all
select 7 as IdGrocessaryStore,'Target' as Name,19 as GrocessaryStoreDelay, 3 as DataFrequency, 106 as StoreID union all
select 8 as IdGrocessaryStore,'FishingNet' as Name,20 as GrocessaryStoreDelay, 14 as DataFrequency, 107 as StoreID
)
INSERT INTO #GrocessaryStore
SELECT * FROM GrocessaryStore;
CREATE TABLE #Month_Report
(
IDReport int primary key
,StoreID varchar(20)
,Date_Of_Day date
, Records int
);
WITH Month_Report as
(
select 1 as IDReport, 100 as StoreID,'2014-02-01' as Date_Of_Day,1500 as Records union all
select 2 as IDReport, 100 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
select 3 as IDReport, 100 as StoreID,'2014-02-03' as Date_Of_Day,1300 as Records union all
select 4 as IDReport, 100 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
select 5 as IDReport, 100 as StoreID,'2014-02-05' as Date_Of_Day,1100 as Records union all
select 6 as IDReport, 100 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
select 7 as IDReport, 100 as StoreID,'2014-02-07' as Date_Of_Day,1200 as Records union all
select 8 as IDReport, 100 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
select 9 as IDReport, 100 as StoreID,'2014-02-09' as Date_Of_Day,1800 as Records union all
select 10 as IDReport, 100 as StoreID,'2014-02-10' as Date_Of_Day,1900 as Records union all
select 11 as IDReport, 100 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
select 12 as IDReport, 100 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
select 13 as IDReport, 100 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
select 14 as IDReport, 100 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
select 15 as IDReport, 100 as StoreID,'2014-02-15' as Date_Of_Day,NULL as Records union all
select 16 as IDReport, 100 as StoreID,'2014-02-16' as Date_Of_Day, 1000 as Records union all
select 17 as IDReport, 101 as StoreID,'2014-02-01' as Date_Of_Day,2500 as Records union all
select 18 as IDReport, 101 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
select 19 as IDReport, 101 as StoreID,'2014-02-03' as Date_Of_Day,NULL as Records union all
select 20 as IDReport, 101 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
select 21 as IDReport, 101 as StoreID,'2014-02-05' as Date_Of_Day,2100 as Records union all
select 22 as IDReport, 101 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
select 23 as IDReport, 101 as StoreID,'2014-02-07' as Date_Of_Day,2200 as Records union all
select 24 as IDReport, 101 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
select 25 as IDReport, 101 as StoreID,'2014-02-09' as Date_Of_Day,3800 as Records union all
select 26 as IDReport, 101 as StoreID,'2014-02-10' as Date_Of_Day,3900 as Records union all
select 27 as IDReport, 101 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
select 28 as IDReport, 101 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
select 29 as IDReport, 101 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
select 30 as IDReport, 101 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
select 31 as IDReport, 101 as StoreID,'2014-02-15' as Date_Of_Day,1600 as Records union all
select 32 as IDReport, 101 as StoreID,'2014-02-16' as Date_Of_Day,1800 as Records union all
select 33 as IDReport, 101 as StoreID,'2014-02-17' as Date_Of_Day,2700 as Records union all
select 34 as IDReport, 101 as StoreID,'2014-02-18' as Date_Of_Day,2600 as Records
)
INSERT INTO #Month_Report
SELECT * FROM Month_Report;
-- Basic pattern for the dynamic SQL
SELECT Name
,[2014-02-01]=MAX(CASE WHEN Date_Of_Day = '2014-02-01' THEN Records END)
,CONVERT(CHAR(10), MAX(Date_Of_Day), 120)
FROM #GrocessaryStore a
JOIN #Month_Report b ON a.StoreID = b.StoreID
GROUP BY Name;
DECLARE @SQL NVARCHAR(MAX);
WITH Dates AS
(
SELECT d=CONVERT(CHAR(10), MAX(Date_Of_Day), 120)
FROM #Month_Report
GROUP BY Date_Of_Day
)
SELECT @SQL = N'
SELECT Name' +
(
SELECT ', [' + d + ']=MAX(CASE WHEN Date_Of_Day=''' + d +''' THEN Records END)'
FROM Dates
ORDER BY d
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)') +
N'FROM #GrocessaryStore a
JOIN #Month_Report b ON a.StoreID = b.StoreID
GROUP BY Name;';
PRINT @SQL;
EXEC sp_executesql @SQL;
GO
DROP TABLE #GrocessaryStore;
DROP TABLE #Month_Report;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 10, 2014 at 8:15 pm
Hi Dwain,
Thanks for your reply and i ran your logic. you have built logic just for as per my expected result. But i have gave some test data for executing the query.
the problem is Year and month are dynamic on my requirement. if you could take a look at proc which posted on my first post, i mentioned on the parameters.
Also, if the records is null then i will be adding the date column + delay+ frequency to display against null value. please execute the expected result query. for the null values i am doing the logic to display as date.
Any help please
March 10, 2014 at 9:04 pm
born2achieve (3/10/2014)
Hi Dwain,Thanks for your reply and i ran your logic. you have built logic just for as per my expected result. But i have gave some test data for executing the query.
the problem is Year and month are dynamic on my requirement. if you could take a look at proc which posted on my first post, i mentioned on the parameters.
Also, if the records is null then i will be adding the date column + delay+ frequency to display against null value. please execute the expected result query. for the null values i am doing the logic to display as date.
Any help please
I believe you can simply limit the date range grouped by the Dates CTE and apply any logic you need to NULL values in there to get what you need. Although you may also need to apply the same NULL date logic in the dynamic SQL as well.
Why don't you play with it a bit and see if you can get it to work. Could be a good learning experience.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 11, 2014 at 5:05 am
Hi dwain,
Thank you so much. I will definitely try and and if i struck up and if i really need your assistance i will drop it here.
March 11, 2014 at 7:35 am
Hi Dwain,
I tried to change the date logic as dynamic and he is my try
CREATE TABLE #GrocessaryStore
(
IdGrocessaryStore int Primary key
,Name varchar(50)
,GrocessaryStoreDelay int
,DataFrequency int
,StoreID varchar(20)
);
With GrocessaryStore as
(
select 1 as IdGrocessaryStore,'WallMart' as Name,10 as GrocessaryStoreDelay, 2 as DataFrequency, 100 as StoreID union all
select 2 as IdGrocessaryStore,'Kelly' as Name,13 as GrocessaryStoreDelay, 1 as DataFrequency, 101 as StoreID union all
select 3 as IdGrocessaryStore,'Subway' as Name,12 as GrocessaryStoreDelay, 3 as DataFrequency, 102 as StoreID union all
select 4 as IdGrocessaryStore,'Dominos' as Name,14 as GrocessaryStoreDelay, 5 as DataFrequency, 103 as StoreID union all
select 5 as IdGrocessaryStore,'Pizzahut' as Name,15 as GrocessaryStoreDelay, 3 as DataFrequency, 104 as StoreID union all
select 6 as IdGrocessaryStore,'BigY' as Name,12 as GrocessaryStoreDelay, 8 as DataFrequency, 105 as StoreID union all
select 7 as IdGrocessaryStore,'Target' as Name,19 as GrocessaryStoreDelay, 3 as DataFrequency, 106 as StoreID union all
select 8 as IdGrocessaryStore,'FishingNet' as Name,20 as GrocessaryStoreDelay, 14 as DataFrequency, 107 as StoreID
)
INSERT INTO #GrocessaryStore
SELECT * FROM GrocessaryStore;
CREATE TABLE #Month_Report
(
IDReport int primary key
,StoreID varchar(20)
,Date_Of_Day date
, Records int
);
WITH Month_Report as
(
select 1 as IDReport, 100 as StoreID,'2014-02-01' as Date_Of_Day,1500 as Records union all
select 2 as IDReport, 100 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
select 3 as IDReport, 100 as StoreID,'2014-02-03' as Date_Of_Day,1300 as Records union all
select 4 as IDReport, 100 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
select 5 as IDReport, 100 as StoreID,'2014-02-05' as Date_Of_Day,1100 as Records union all
select 6 as IDReport, 100 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
select 7 as IDReport, 100 as StoreID,'2014-02-07' as Date_Of_Day,1200 as Records union all
select 8 as IDReport, 100 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
select 9 as IDReport, 100 as StoreID,'2014-02-09' as Date_Of_Day,1800 as Records union all
select 10 as IDReport, 100 as StoreID,'2014-02-10' as Date_Of_Day,1900 as Records union all
select 11 as IDReport, 100 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
select 12 as IDReport, 100 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
select 13 as IDReport, 100 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
select 14 as IDReport, 100 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
select 15 as IDReport, 100 as StoreID,'2014-02-15' as Date_Of_Day,NULL as Records union all
select 16 as IDReport, 100 as StoreID,'2014-02-16' as Date_Of_Day, 1000 as Records union all
select 17 as IDReport, 101 as StoreID,'2014-02-01' as Date_Of_Day,2500 as Records union all
select 18 as IDReport, 101 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
select 19 as IDReport, 101 as StoreID,'2014-02-03' as Date_Of_Day,NULL as Records union all
select 20 as IDReport, 101 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
select 21 as IDReport, 101 as StoreID,'2014-02-05' as Date_Of_Day,2100 as Records union all
select 22 as IDReport, 101 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
select 23 as IDReport, 101 as StoreID,'2014-02-07' as Date_Of_Day,2200 as Records union all
select 24 as IDReport, 101 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
select 25 as IDReport, 101 as StoreID,'2014-02-09' as Date_Of_Day,3800 as Records union all
select 26 as IDReport, 101 as StoreID,'2014-02-10' as Date_Of_Day,3900 as Records union all
select 27 as IDReport, 101 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
select 28 as IDReport, 101 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
select 29 as IDReport, 101 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
select 30 as IDReport, 101 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
select 31 as IDReport, 101 as StoreID,'2014-02-15' as Date_Of_Day,1600 as Records union all
select 32 as IDReport, 101 as StoreID,'2014-02-16' as Date_Of_Day,1800 as Records union all
select 33 as IDReport, 101 as StoreID,'2014-02-17' as Date_Of_Day,2700 as Records union all
select 34 as IDReport, 101 as StoreID,'2014-02-18' as Date_Of_Day,2600 as Records
)
INSERT INTO #Month_Report
SELECT * FROM Month_Report;
-- Basic pattern for the dynamic SQL
--SELECT Name
-- ,[2014-02-01]=MAX(CASE WHEN Date_Of_Day = '2014-02-01' THEN Records END)
-- ,CONVERT(CHAR(10), MAX(Date_Of_Day), 120)
--FROM #GrocessaryStore a
--JOIN #Month_Report b ON a.StoreID = b.StoreID
--GROUP BY Name;
DECLARE @SQL NVARCHAR(MAX);
declare @Year int = 2014, @Month int = 02
DECLARE @FromDate DATETIME = DATEADD(MONTH, 12 * @Year - 22801 + @Month, '19000101'),
@ToDate DATETIME = DATEADD(MONTH, 12 * @Year - 22800 + @Month, '18991231');
WITH Dates(date) AS
(
--SELECT d=CONVERT(CHAR(10), MAX(Date_Of_Day), 120)
--FROM #Month_Report
--GROUP BY Date_Of_Day
SELECT
@fromdate
UNION ALL
SELECT
DATEADD(day, 1, date) as date
FROM
Dates
WHERE
date <@todate
)
--select * from dates
SELECT @SQL = N'
SELECT Name' +
(
SELECT ', [' + CONVERT(CHAR(10), date, 120) + ']=MAX(CASE WHEN Date_Of_Day=''' + CONVERT(CHAR(10), date, 120) +''' THEN Records
ELSE 0 END)'
FROM Dates
--ORDER BY date
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)') +
N'FROM #GrocessaryStore a
JOIN #Month_Report b ON a.StoreID = b.StoreID
GROUP BY Name;';
PRINT @SQL;
EXEC sp_executesql @SQL;
GO
DROP TABLE #GrocessaryStore;
DROP TABLE #Month_Report;
On the CASE statement if the records null then instead of 0 i need to calculate date + (delay+frequency). Struggling to achieve this. Any suggestions or code changes please
March 11, 2014 at 6:33 pm
I'm not sure exactly what you want here as I see no definition of Delay or Frequency.
It does look like you're trying to get all columns in Feb (or whatever month). That part is easy.
CREATE TABLE #GrocessaryStore
(
IdGrocessaryStore int Primary key
,Name varchar(50)
,GrocessaryStoreDelay int
,DataFrequency int
,StoreID varchar(20)
);
With GrocessaryStore as
(
select 1 as IdGrocessaryStore,'WallMart' as Name,10 as GrocessaryStoreDelay, 2 as DataFrequency, 100 as StoreID union all
select 2 as IdGrocessaryStore,'Kelly' as Name,13 as GrocessaryStoreDelay, 1 as DataFrequency, 101 as StoreID union all
select 3 as IdGrocessaryStore,'Subway' as Name,12 as GrocessaryStoreDelay, 3 as DataFrequency, 102 as StoreID union all
select 4 as IdGrocessaryStore,'Dominos' as Name,14 as GrocessaryStoreDelay, 5 as DataFrequency, 103 as StoreID union all
select 5 as IdGrocessaryStore,'Pizzahut' as Name,15 as GrocessaryStoreDelay, 3 as DataFrequency, 104 as StoreID union all
select 6 as IdGrocessaryStore,'BigY' as Name,12 as GrocessaryStoreDelay, 8 as DataFrequency, 105 as StoreID union all
select 7 as IdGrocessaryStore,'Target' as Name,19 as GrocessaryStoreDelay, 3 as DataFrequency, 106 as StoreID union all
select 8 as IdGrocessaryStore,'FishingNet' as Name,20 as GrocessaryStoreDelay, 14 as DataFrequency, 107 as StoreID
)
INSERT INTO #GrocessaryStore
SELECT * FROM GrocessaryStore;
CREATE TABLE #Month_Report
(
IDReport int primary key
,StoreID varchar(20)
,Date_Of_Day date
, Records int
);
WITH Month_Report as
(
select 1 as IDReport, 100 as StoreID,'2014-02-01' as Date_Of_Day,1500 as Records union all
select 2 as IDReport, 100 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
select 3 as IDReport, 100 as StoreID,'2014-02-03' as Date_Of_Day,1300 as Records union all
select 4 as IDReport, 100 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
select 5 as IDReport, 100 as StoreID,'2014-02-05' as Date_Of_Day,1100 as Records union all
select 6 as IDReport, 100 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
select 7 as IDReport, 100 as StoreID,'2014-02-07' as Date_Of_Day,1200 as Records union all
select 8 as IDReport, 100 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
select 9 as IDReport, 100 as StoreID,'2014-02-09' as Date_Of_Day,1800 as Records union all
select 10 as IDReport, 100 as StoreID,'2014-02-10' as Date_Of_Day,1900 as Records union all
select 11 as IDReport, 100 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
select 12 as IDReport, 100 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
select 13 as IDReport, 100 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
select 14 as IDReport, 100 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
select 15 as IDReport, 100 as StoreID,'2014-02-15' as Date_Of_Day,NULL as Records union all
select 16 as IDReport, 100 as StoreID,'2014-02-16' as Date_Of_Day, 1000 as Records union all
select 17 as IDReport, 101 as StoreID,'2014-02-01' as Date_Of_Day,2500 as Records union all
select 18 as IDReport, 101 as StoreID,'2014-02-02' as Date_Of_Day,1200 as Records union all
select 19 as IDReport, 101 as StoreID,'2014-02-03' as Date_Of_Day,NULL as Records union all
select 20 as IDReport, 101 as StoreID,'2014-02-04' as Date_Of_Day,1800 as Records union all
select 21 as IDReport, 101 as StoreID,'2014-02-05' as Date_Of_Day,2100 as Records union all
select 22 as IDReport, 101 as StoreID,'2014-02-06' as Date_Of_Day,1000 as Records union all
select 23 as IDReport, 101 as StoreID,'2014-02-07' as Date_Of_Day,2200 as Records union all
select 24 as IDReport, 101 as StoreID,'2014-02-08' as Date_Of_Day,1400 as Records union all
select 25 as IDReport, 101 as StoreID,'2014-02-09' as Date_Of_Day,3800 as Records union all
select 26 as IDReport, 101 as StoreID,'2014-02-10' as Date_Of_Day,3900 as Records union all
select 27 as IDReport, 101 as StoreID,'2014-02-11' as Date_Of_Day,1700 as Records union all
select 28 as IDReport, 101 as StoreID,'2014-02-12' as Date_Of_Day,1000 as Records union all
select 29 as IDReport, 101 as StoreID,'2014-02-13' as Date_Of_Day,1200 as Records union all
select 30 as IDReport, 101 as StoreID,'2014-02-14' as Date_Of_Day,1700 as Records union all
select 31 as IDReport, 101 as StoreID,'2014-02-15' as Date_Of_Day,1600 as Records union all
select 32 as IDReport, 101 as StoreID,'2014-02-16' as Date_Of_Day,1800 as Records union all
select 33 as IDReport, 101 as StoreID,'2014-02-17' as Date_Of_Day,2700 as Records union all
select 34 as IDReport, 101 as StoreID,'2014-02-18' as Date_Of_Day,2600 as Records
)
INSERT INTO #Month_Report
SELECT * FROM Month_Report;
DECLARE @Year INT = 2014
,@Month INT = 02;
DECLARE @FromDate DATETIME = DATEADD(month, @Month-1, DATEADD(year, @year-1900, 0)),
@ToDate DATETIME = DATEADD(month, @Month, DATEADD(year, @year-1900, 0))-1;
DECLARE @SQL NVARCHAR(MAX)
,@SQLParms NVARCHAR(MAX) = '@FromDate DATE, @ToDate DATE';
WITH Dates AS
(
SELECT d=CONVERT(CHAR(10), @FromDate + n, 120)
FROM
(
SELECT n=0 UNION ALL
SELECT TOP (DATEDIFF(day, @FromDate, @ToDate)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES(0),(0),(0),(0)) a (n1)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0)) b(n2)
) a
)
SELECT @SQL = N'
SELECT Name' +
(
SELECT ', [' + d + ']=MAX(CASE WHEN Date_Of_Day=''' + d +''' THEN Records ELSE 0 END)'
FROM Dates
ORDER BY d
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)') +
N'FROM #GrocessaryStore a
JOIN #Month_Report b ON a.StoreID = b.StoreID
WHERE Date_of_Day BETWEEN @FromDate AND @ToDate
GROUP BY Name;';
PRINT @SQL;
EXEC sp_executesql @SQL, @SQLParms, @FromDate=@FromDate, @ToDate=@ToDate;
GO
DROP TABLE #GrocessaryStore;
DROP TABLE #Month_Report;
Do you see the CASE statement in the place where it sets up the Dynamic SQL?
CASE WHEN Date_Of_Day=''' + d +''' THEN Records ELSE 0 END
I've added the ELSE logic there where you can substitute whatever you want for the NULL value of Records (Delay + Frequency instead of 0).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 11, 2014 at 6:53 pm
Hi Dwain,
Thanks for your reply. I am sorry if i didn't explain properly.
if you execute the query you have given, you can see some of the column values are 0. What i wanted to do is wherever the records 0 in that place i need to do some calculation
For ex :
for the first row(kelly) column (2014-02-15 ) the value is 0 which means no Records found for that day .So in that case get the
GrocessaryStoreDelay and DataFrequency add with the date column 2014-02-15
ie : 13 + 1 + convert(2014-02-15) as date => 2014-03-01 has to display instead 0.
Hope am cleared you. Could you please help me
March 11, 2014 at 7:02 pm
My next try
SELECT @SQL = N'
SELECT Name' +
(
SELECT ', [' + d + ']=MAX(CASE WHEN Date_Of_Day=''' + d +''' THEN Records ELSE (a.GrocessaryStoreDelay + a.DataFrequency) END)'
FROM Dates
ORDER BY d
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)') +
N'FROM #GrocessaryStore a
JOIN #Month_Report b ON a.StoreID = b.StoreID
WHERE Date_of_Day BETWEEN @FromDate AND @ToDate
GROUP BY Name;';
In addition to the GrocessaryStoreDelay+DataFrequency i need to add with the column value. Ta try that below is my try
SELECT @SQL = N'
SELECT Name' +
(
SELECT ', [' + d + ']=MAX(CASE WHEN Date_Of_Day=''' + d +''' THEN Records ELSE dateadd(d,'''+d+''',(a.GrocessaryStoreDelay + a.DataFrequency)) END)'
FROM Dates
ORDER BY d
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)') +
N'FROM #GrocessaryStore a
JOIN #Month_Report b ON a.StoreID = b.StoreID
WHERE Date_of_Day BETWEEN @FromDate AND @ToDate
GROUP BY Name;';
but it gives error as
[highlight]Argument data type varchar is invalid for argument 2 of dateadd function.[/highlight]
Any suggestions please
March 11, 2014 at 7:20 pm
Are we getting close yet?
WITH Dates AS
(
SELECT d=CONVERT(CHAR(10), @FromDate + n, 120)
FROM
(
SELECT n=0 UNION ALL
SELECT TOP (DATEDIFF(day, @FromDate, @ToDate)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES(0),(0),(0),(0)) a (n1)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0)) b(n2)
) a
)
SELECT @SQL = N'
SELECT Name' +
(
SELECT ', [' + d + ']=ISNULL(CAST(MAX(CASE WHEN Date_Of_Day=''' + d +''' THEN CAST(Records AS VARCHAR(10)) END) AS VARCHAR(10)), ' +
'CONVERT(VARCHAR(10), DATEADD(day, MAX(GrocessaryStoreDelay) + MAX(DataFrequency), ''' + d + '''),120))'
FROM Dates
ORDER BY d
FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)') +
N'FROM #GrocessaryStore a
JOIN #Month_Report b ON a.StoreID = b.StoreID
WHERE Date_of_Day BETWEEN @FromDate AND @ToDate
GROUP BY Name;';
Note that there's no need to PM me on this as I get an email when you post to this thread. I am not the only one working this board.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 11, 2014 at 7:23 pm
Awesome. We reached the exact output. Thank you so much and appreciate your great effort and help.
Thank you Gentleman.
March 11, 2014 at 7:26 pm
You're welcome.
Note to any of the other masters that take a look at this. The OP wanted a mixture of integers and dates in his output. I cannot understand why he'd want something like this, other than to say I've seen weirder requirements come out of the mouth of a business user.
Just giving the OP what they wanted.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply