April 14, 2011 at 10:00 am
I need to write SQL procedure to gathered the data from 10/01/09 to 09/30/10 daily count for particular case type for 10.00 PM to 6.00 (daily basis) . How do i do that ... ??
Please advice.
SELECT C.ROWUPDATE, C.CASETYPE, T.LONG_DESC, C.CASESUBTYPE, COUNT(C.CASETYPE)
FROM PS_CASE C
LEFT OUTER JOIN PS_RC_CASETYPE T ON C.CASETYPE = T.CASETYPE
WHERE (C.ROWUPDATE BETWEEN CONVERT(VARCHAR(11), '10/01/2009', 120) + ' 22:00:00.000'
AND CONVERT(VARCHAR(11), '09/30/2010', 120) + ' 06:00:00.000') /* this give me everything */
AND T.SETID = 'SHARING'
AND C.CASETYPE IN ('EMWTR', 'EMSWR', 'EMFOS')
GROUP BY C.ROWUPDATE, C.CASETYPE, C.CASESUBTYPE, T.LONG_DESC
ORDER BY C.ROWUPDATE, C.CASETYPE, C.CASESUBTYPE
Is there any way i can select only particular time period for each date with one year time frame ???
Thank you in advance.
April 14, 2011 at 12:20 pm
Add this to your where clause and this should take care of it.
AND DATEPART(hh,C.ROWUPDATE)=>20 AND DATEPART(hh,C.ROWUPDATE)=<6
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 14, 2011 at 12:23 pm
Dan.Humphries (4/14/2011)
Add this to your where clause and this should take care of it.
AND DATEPART(hh,C.ROWUPDATE)=>20 AND DATEPART(hh,C.ROWUPDATE)=<6
Not quite gonna get you there. There are no hours that are >= 20 AND <= 6. :w00t:
but if you change it to OR it should work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2011 at 12:29 pm
picky picky. LOL :hehe:
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 14, 2011 at 1:59 pm
voila. it worked thank you all.
Apperciate your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply