December 21, 2009 at 9:53 pm
Nabha (12/21/2009)
All good answers above,one more way to find your last friday, (and then deduce your other days)
Select dateadd(day, - (datepart(dw, getdate())+1), getdate())
It doesn't appear to work on Saturdays...
DECLARE @Date DATETIME
SET @Date = '20091226'
Select dateadd(day, - (datepart(dw, @Date)+1), @Date),@Date, DATENAME(dw,@Date)
--Jeff Moden
December 21, 2009 at 11:16 pm
I am using the below sql to get the transaction counts for last two weeks(Week ends with most recent friday)
SELECT * FROM ( SELECT ProgramName =
CASE WHEN ProgramName = 'X1' THEN 'Y1'
WHEN ProgramName = 'X2' THEN 'Y2'
END,
ISNULL([2009-12-05],0) 'Saturday December 05 2009',
ISNULL([2009-12-06],0) 'Sunday December 06 2009',
ISNULL([2009-12-07],0) 'Monday December 07 2009',
ISNULL([2009-12-08],0) 'Tuesday December 08 2009',
ISNULL([2009-12-09],0) 'Wednesday December 09 2009',
ISNULL([2009-12-10],0) 'Thursday December 10 2009',
ISNULL([2009-12-11],0) 'Friday December 11 2009',
ISNULL([2009-12-05],0)+
ISNULL([2009-12-06],0)+
ISNULL([2009-12-07],0)+
ISNULL([2009-12-08],0)+
ISNULL([2009-12-09],0)+
ISNULL([2009-12-10],0)+
ISNULL([2009-12-11],0) 'Week1Total',
ISNULL([2009-12-12],0) 'Saturday December 12 2009',
ISNULL([2009-12-13],0) 'Sunday December 13 2009',
ISNULL([2009-12-14],0) 'Monday December 14 2009',
ISNULL([2009-12-15],0) 'Tuesday December 15 2009',
ISNULL([2009-12-16],0) 'Wednesday December 16 2009',
ISNULL([2009-12-17],0) 'Thursday December 17 2009',
ISNULL([2009-12-18],0) 'Friday December 18 2009',
ISNULL([2009-12-12],0)+
ISNULL([2009-12-13],0)+
ISNULL([2009-12-14],0)+
ISNULL([2009-12-15],0)+
ISNULL([2009-12-16],0)+
ISNULL([2009-12-17],0)+
ISNULL([2009-12-18],0) 'Week2Total'
FROM
(SELECT DATEADD(dd, datepart (dw, LC.Datefilled) - DATEPART(dw, DATE_COLUMN), CAST(CONVERT(VARCHAR(10), DATE_COLUMN , 101)AS SMALLDATETIME)) days
,ProgramName
,count(*) 'TotalTransactions'
FROM CSLoyaltyCard LC where programname in ('Y1','Y2')
GROUP BY ProgramName,DATEADD(dd, datepart (dw, DATE_COLUMN) - DATEPART(dw, DATE_COLUMN), CAST(CONVERT(VARCHAR(10), LC.Datefilled , 101)AS SMALLDATETIME))
) spvt
PIVOT
(
SUM([TotalTransactions]) FOR [days] IN ([2009-12-05],[2009-12-06],[2009-12-07],[2009-12-08],[2009-12-09],[2009-12-10],[2009-12-11],
[2009-12-12],[2009-12-13],[2009-12-14],[2009-12-15],[2009-12-16],[2009-12-17],[2009-12-18])
)PVT
Union All
SELECT ProgramName,
ISNULL([2009-12-05],0) 'Saturday December 05 2009',
ISNULL([2009-12-06],0) 'Sunday December 06 2009',
ISNULL([2009-12-07],0) 'Monday December 07 2009',
ISNULL([2009-12-08],0) 'Tuesday December 08 2009',
ISNULL([2009-12-09],0) 'Wednesday December 09 2009',
ISNULL([2009-12-10],0) 'Thursday December 10 2009',
ISNULL([2009-12-11],0) 'Friday December 11 2009',
ISNULL([2009-12-05],0)+
ISNULL([2009-12-06],0)+
ISNULL([2009-12-07],0)+
ISNULL([2009-12-08],0)+
ISNULL([2009-12-09],0)+
ISNULL([2009-12-10],0)+
ISNULL([2009-12-11],0) 'Total',
ISNULL([2009-12-12],0) 'Saturday December 12 2009',
ISNULL([2009-12-13],0) 'Sunday December 13 2009',
ISNULL([2009-12-14],0) 'Monday December 14 2009',
ISNULL([2009-12-15],0) 'Tuesday December 15 2009',
ISNULL([2009-12-16],0) 'Wednesday December 16 2009',
ISNULL([2009-12-17],0) 'Thursday December 17 2009',
ISNULL([2009-12-18],0) 'Friday December 18 2009',
ISNULL([2009-12-12],0)+
ISNULL([2009-12-13],0)+
ISNULL([2009-12-14],0)+
ISNULL([2009-12-15],0)+
ISNULL([2009-12-16],0)+
ISNULL([2009-12-17],0)+
ISNULL([2009-12-18],0) 'Total'
FROM
(SELECT 'Grand Total' ProgramName
,DATEADD(dd, datepart (dw, DATE_COLUMN) - DATEPART(dw, DATE_COLUMN), CAST(CONVERT(VARCHAR(10), DATE_COLUMN , 101)AS SMALLDATETIME)) days
,count(*) 'TotalTransactions'
FROM CSLoyaltyCard LC where programname in ('X1','X2')
group by ProgramName,DATEADD(dd, datepart (dw, DATE_COLUMN) - DATEPART(dw, DATE_COLUMN), CAST(CONVERT(VARCHAR(10), DATE_COLUMN , 101)AS SMALLDATETIME))
) spvt
PIVOT
(
SUM([TotalTransactions]) FOR [days] IN ([2009-12-05],[2009-12-06],[2009-12-07],[2009-12-08],[2009-12-09],[2009-12-10],[2009-12-11],
[2009-12-12],[2009-12-13],[2009-12-14],[2009-12-15],[2009-12-16],[2009-12-17],[2009-12-18])
)PVT)a1
Here i dont want to hard code the date rage. I want to get them dynamically. this report will update on daily basis, but when i execute on Tuesday i have to get the most recent friday week to two weeks(i.e 12/18-12/12 and 12/11 to 12/05).
Please help me. very urgent
December 22, 2009 at 12:49 am
Using the words "very urgent" on this forum will likely get you black balled.;-) Seriously.
Try this for starters...
SELECT DATEADD(dd,DATEDIFF(dd,6,GETDATE()+1)/7*7,-2)+t.Number
FROM Master.dbo.spt_Values t
WHERE t.Type = 'P'
AND t.Number BETWEEN 0 AND 6
... since it's "urgent", I didn't do all the normal testing I would do to make sure it actually works.
Then, read the following article to solve your problem using the bit of code above as the driver...
http://qa.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
December 22, 2009 at 12:52 am
Sorry... I was still focused on your original post... that code was for only 1 week... this is for 2...
SELECT DATEADD(dd,DATEDIFF(dd,6,GETDATE()+1)/7*7,-9)+t.Number
FROM Master.dbo.spt_Values t
WHERE t.Type = 'P'
AND t.Number BETWEEN 0 AND 13
--Jeff Moden
December 22, 2009 at 1:13 am
Jeff Moden (12/21/2009)
Nabha (12/21/2009)
All good answers above,one more way to find your last friday, (and then deduce your other days)
Select dateadd(day, - (datepart(dw, getdate())+1), getdate())It doesn't appear to work on Saturdays...
DECLARE @Date DATETIMESET @Date = '20091226'
Select dateadd(day, - (datepart(dw, @Date)+1), @Date),@Date, DATENAME(dw,@Date)
What a shame :-(, thanks Jeff . Sorry did not test it properly, just to make it up and i dont think its elegant,
DECLARE @Date DATETIME
SET @Date = '20091226'
SELECT CASE datepart(dw, @Date)
WHEN 6 THEN @date
WHEN 7 THEN @date-1
ELSE dateadd(day, - (datepart(dw, @Date)+1),@date)
END
---------------------------------------------------------------------------------
December 22, 2009 at 1:14 am
What is 'p' and 'Number' here in this code
December 22, 2009 at 1:32 am
The requirement is something like this.
(Should include 2 weeks, where week ends with the most recent Friday, and Starts with Saturday. Spans 2 weeks from the last Friday down.
Also include horizontal and vertical Totals
Include days in column, so it should be much easier.
DATE --------------------program1----------------program2
Fri, Dec 18
Thu, Dec 17
Wed, Dec 16
Tue, Dec 15
Mon, Dec 14
Sun, Dec 13
Sat, Dec 12
--Week1 Total
Fri, Dec 11
Thu, Dec 10
Wed, Dec 09
Tue, Dec 08
Mon, Dec 07
Sun, Dec 06
Sat, Dec 05
--Week2 Total
--Grand Total
December 22, 2009 at 2:36 am
Jeff Moden (12/22/2009)
Sorry... I was still focused on your original post... that code was for only 1 week... this is for 2...
SELECT DATEADD(dd,DATEDIFF(dd,6,GETDATE()+1)/7*7,-9)+t.Number
FROM Master.dbo.spt_Values t
WHERE t.Type = 'P'
AND t.Number BETWEEN 0 AND 13
Nice idea Jeff, thanks 🙂
---------------------------------------------------------------------------------
December 22, 2009 at 2:37 am
purushotham.k9 (12/22/2009)
What is 'p' and 'Number' here in this code
Thats a table in master database and it has numbers from 0 to 2047.
That is a simulation of 'number' table. To get your sequence of dates between those two dates. If you had a 'number' or 'tally' table in your database you can use that instead of this.
See this for more,
http://qa.sqlservercentral.com/articles/T-SQL/62867/
---------------------------------------------------------------------------------
December 22, 2009 at 2:59 am
Not sure how you can include this in your query (if you can provide the test data and table create script, may be someone will help with the complete query) but to answer your initial question, this is how you can 'Pivot' it,
read this on how to post data
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
SELECT
[0],[1],[2],[3],[4],[5],[6]
FROM (SELECT DATEADD(dd,DATEDIFF(dd,6,GETDATE()+1)/7*7,-2)+t.Number as [date],
t.number as num
FROM Master.dbo.spt_Values t
WHERE t.Type = 'P'
AND t.Number BETWEEN 0 AND 6)SRC
PIVOT (max(date) FOR num in ([0],[1],[2],[3],[4],[5],[6])) as pvt
Courtesy: Jeff
EDIT: sorry, you can extend this to 2 weeks by changing -2 to -9 and t.number between 0 to 13. I just did this for my convenience.
---------------------------------------------------------------------------------
December 22, 2009 at 4:17 am
I have a table in the table datefiled is the column my data base. That column has daily date. using that i have to slect the transaction count day wise for last two weeks and weekly wise total count.
December 22, 2009 at 6:33 am
purushotham.k9 (12/22/2009)
I have a table in the table datefiled is the column my data base. That column has daily date. using that i have to slect the transaction count day wise for last two weeks and weekly wise total count.
I've referred you to an article that will allow you to easily do such a thing using dynamic SQL and have provided you with an automatic and self correcting source of two weeks worth of dates. Yes, someone could write this for you but then you would learn nothing and everytime it comes up, it would continue to be "urgent" because you won't know how to do it. Read the article and apply it to your situation. It's easy... take the time...
--Jeff Moden
December 22, 2009 at 8:17 pm
Nabha (12/22/2009)
Jeff Moden (12/22/2009)
Sorry... I was still focused on your original post... that code was for only 1 week... this is for 2...
SELECT DATEADD(dd,DATEDIFF(dd,6,GETDATE()+1)/7*7,-9)+t.Number
FROM Master.dbo.spt_Values t
WHERE t.Type = 'P'
AND t.Number BETWEEN 0 AND 13
Nice idea Jeff, thanks 🙂
Thanks, Nabha... and great explanation of what spt_Values and Tally/Numbers tables are. Well done.
--Jeff Moden
January 11, 2010 at 12:58 am
Thanks for the logic...I have got what i need, but when there is not data on particluar day i need to diplay the '0' rather than blank.
Here is the code
SELECT ProgramName,SUBSTRING(datename(dw,[date]),1,3) +', '+ SUBSTRING(datename(mm,[date]),1,3) +' '+ convert(varchar,datepart(dd,[date]))
+' '+ convert(varchar,datepart(yy,[date])) as [Date], [TotalTransactions]
FROM
(SELECT DATEADD(dd,DATEDIFF(dd,6,getdate()+1)/7*7,-9)+t.Number as [date],
ProgramName =
CASE WHEN ProgramName = 'ABC' THEN 'ABC'
WHEN ProgramName = 'ACB' THEN 'ACB'
WHEN ProgramName = 'A' THEN 'A'
WHEN ProgramName = 'B' THEN 'B'
END,
count(*) TotalTransactions
FROM CSLoyaltycard,
Master.dbo.spt_Values t
WHERE t.Type = 'P'
AND t.Number BETWEEN 7 AND 13
and DateFilled = DATEADD(dd,DATEDIFF(dd,6,getdate()+1)/7*7,-9)+t.Number
and groupnumber in ('1','2','3','4') group by programname,t.number)SRC
In the above code i have transactions from 0-6 date range, 7-13 date range i don't have any txs.
So i need to display the 7-13 date range with 0 txs.
Please help me.
January 19, 2010 at 7:30 am
Try coalesce - this returns the first non null in a list of values. Full syntax
COALESCE ( value_you_have_calculated,0 )
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply