September 19, 2011 at 8:25 am
Hello,
I have worked on the following query to run and generate weekly report for past seven days. It run sunday morning , providing information for past seven days.
WITH Mystupidquery AS
(select C.CASETYPE , C.CASESUBTYPE,T.LONGDESC
, COUNT(case when datediff(day, C.CREATIONDATE, getdate()) = 6 then 0 end) day_1
, COUNT(case when datediff(day, C.CREATIONDATE, getdate()) = 5 then 0 end) day_2
, COUNT(case when datediff(day, C.CREATIONDATE, getdate()) = 4 then 0 end) day_3
, COUNT(case when datediff(day, C.CREATIONDATE, getdate()) = 3 then 0 end) day_4
, COUNT(case when datediff(day, C.CREATIONDATE, getdate()) = 2 then 0 end) day_5
, COUNT(case when datediff(day, C.CREATIONDATE, getdate()) = 1 then 0 end) day_6
, COUNT(case when datediff(day, C.CREATIONDATE, getdate()) = 0 then 0 end) day_7
from
caseinformation table C
LEFT OUTER JOIN subtypetableinformation T ON C.CASESUBTYPE = T.CASESUBTYPE
WHERE (C.CREATIONDATE > dateadd(day, -7, getdate())
)
AND C.CASETYPE in ('WCS', 'WCO')
AND C.addedby = 'User1'
AND (T.CASETYPE = C.CASETYPE AND T.CASESUBTYPE = C.CASESUBTYPE)
GROUP BY C.CREATIONDATE, T.LONGDESC , C.CASETYPE, C.CASESUBTYPE
)
SELECT CASETYPE, CASESUBTYPE ,LONGDESC, SUM(day_1) as 'Day1', SUM(day_2) 'Day2',SUM(day_3)
'Day3',SUM(day_4) AS 'Day4',
SUM(day_5) AS 'Day5',SUM(day_6)AS 'Day6', sum(day_7) AS 'Day7' FROM Mystupidquery
GROUP BY CASETYPE, CASESUBTYPE, LONGDESC
This give me following result to dump in Excel file.
WCSBRKPRBroken sprinkler head(s) 3220000
WCSOTHEROther 11376133
WCSWDSTGStage 1 - Wrong day 7522214710219
WCSGHARDWatering hard surfaces 9241200
WCSGRAINWatering when raining 0004101
WCSGWRONWatering wrong time of day 1316117041
End use requested to update the above excel sheet every day (instead weekly) . for example, if i run report tuesday it will give me only informationon for sunday and monday.
WCSBRKPRBroken sprinkler head(s) 3200000
WCSOTHEROther 11300000
WCSWDSTGStage 1 - Wrong day 752200000
WCSGHARDWatering hard surfaces 9200000
WCSGRAINWatering when raining 0000000
WCSGWRONWatering wrong time of day 131600000
how do I update excel on daily basis ?
thank you
September 19, 2011 at 12:47 pm
Can't remember if I've done this, but I think you can set up a linked server to the Excel spreadsheet and then setup a SQL Agent job to run that updates it on a schedule.
September 19, 2011 at 12:51 pm
Another possibility is using SSRS (Reporting Services) and setup a subscription that emails the Excel file on a daily schedule.
September 19, 2011 at 1:05 pm
Thank you. do you have any example or article that i can follow to setup ??
September 24, 2011 at 3:09 pm
Being that this a newbie forum, I wanted to ask you a few non-assumed questions and see if we can get you on the right track. Is this query in a stored procedure? Or are you just copying it to a New Query Window and executing it each time? Besides just using tools to export the information automatically to Excel (SSIS, SSRS), you will need to clean up your query a bit. For example, I use a function that I created to find the beginning of the week no matter what day it is. For example, if it is Tuesday (Sept. 20) , my function would find Sunday (Sept. 18). I can than group all the counts by date between Sunday and Tuesday.
e.g.
WHERE date BETWEEN @StartDate and GetDate()
@StartDate being the date returned by the function that finds the beginning of the week.
GetDate() is function that returns the current date.
Several examples are on the internet for creating this function and usage. If you want me to get more into this, I will be more than happy to post my function and explain further on how to set this up(using a scalar function and stored procedure).
September 24, 2011 at 11:42 pm
kshatriya24 (9/19/2011)
for example, if i run report tuesday it will give me only informationon for sunday and monday.
So... if you run the report on Thursday, are you expecting information for Sunday, Monday, Tuesday, and Wednesday?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply