April 21, 2012 at 10:23 am
i am having the following sales details table with product code, date , time columns
Product CodeDateTime
10001 4/6/201214:43:08.782
10001 4/7/201214:38:42.430
10001 4/6/201211:47:18.240
10002 4/6/201212:54:27.278
10002 4/6/201212:54:53.033
10002 4/12/201211:07:42.222
100024/12/201214:22:32.874
100054/12/201214:43:57.138
100054/16/201215:57:59.228
100064/19/201209:25:13.693
100064/12/201215:42:47.850
I need to find out each hour how many products sold for the day
i want the out put like below
please help me
SQL Out put
Product Date9 to 1010 t0 1111 t0 1212 to 1313 t0 1414 to 1515 to 16
100014/6/201212
100024/6/2012121
100024/12/20121
100054/12/2012
100064/12/20121
100054/16/20121
100064/19/20121
April 21, 2012 at 10:40 am
Heya,
I reckon you need to use a combination of CROSS TAB (or PIVOT, whichever you are more comfortable with), and the DATEPART function to extract the HOUR.
So, combining these both, the query will look something similar to: (I havent tested this, the query is for illustration purposes)
SELECT
Product Code,
Date,
SUM(CASE WHEN DATEPART(HOUR,Time) = 1 THEN 1 ELSE 0 END) as Hour1
...
SUM(CASE WHEN DATEPART(HOUR,Time) = 23 THEN 1 ELSE 0 END) as Hour23
FROM Table
GROUP BY Product Code, Date
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply