July 2, 2008 at 5:28 am
Hi, I need to produce an extract file from a sales table. The extract needs to be in a format of store, till, sales quantity, hour1, hour2, hour3 etc..through to hour 24 for a day.
My table holds the date info in one column as smalldatetime date. How can I construct a query to get a result like :
Store | till | sum sales for hour1 | sum sales for hour2 | sum sales for hour3 | etc.........
for each store
Thanks
July 2, 2008 at 7:15 am
For a solution, please read "How do I create a cross-tab (or "pivot") query?"
at http://sqlserver2000.databases.aspfaq.com/how-do-i-create-a-cross-tab-or-pivot-query.html
SQL = Scarcely Qualifies as a Language
July 2, 2008 at 7:30 am
Sorry, but dont understand that. The link assumes you can split the data across columns like a partition. This table has one date column only, I need to extract data for each hour from it.
July 3, 2008 at 7:42 am
Simon,
Carl is sending you to the correct place to go, though it sounds like you do not understand a pivot-table.
You need to "pivot" your data on hours for each one of 24 hours. In other words, instead of "FOR Q IN (1,2,3,4)" you will need "FOR [Hour] IN (0,1,2,3...23)", with all of the other sections adjusted. Research "Pivot Table" in BOL.
July 3, 2008 at 7:52 am
But I think PIVOT option is introduced in sql2005 only.
SQL Server 2005 introduces the new PIVOT keyword, however I think it will be a disappointment to most. What people have been searching for is a very dynamic way to pivot, so that you don't have to know all of the potential column headers beforehand. Again, I still feel that the best place to rotate this data is on the client. But I know that some people out there are stubborn and/or don't understand the difference.
karthik
July 3, 2008 at 8:01 am
Ah, I think you are right... I shoudl have read the location of the post.
A long but working query should work for you:
SELECT Store, Till
, SUM(CASE [Hour] WHEN 1 THEN Sales ELSE 0 END) AS SalesHr1
, SUM(CASE [Hour] WHEN 1 THEN Qty ELSE 0 END) AS QtyHr1
...
, SUM(CASE [Hour] WHEN 24 THEN Sales ELSE 0 END) AS SalesHr24
, SUM(CASE [Hour] WHEN 24 THEN Qty ELSE 0 END) AS QtyHr24
GROUP BY Store, Till
July 3, 2008 at 8:46 am
Thanks for your input guys. Yes this is SQl2000 🙂
I've also not got a column where I can determine that the hour = 1 or 2 or 3 etc..But have therefore used datepart in a cross tab query
Got a bit of help this end and have come up with a working solution for one of the 'sum' fields, just need to apply that to 2 others!?!? So far have got ...
select store_code,
Business_Date,
sum(hour0) as Hour0,sum(hour1) as Hour1,sum(hour2) as Hour2,sum(hour3) as Hour3,sum(hour4) as Hour4,sum(hour5) as Hour5,sum(hour6) as Hour6,sum(hour7) as Hour7,sum(hour8) as Hour8,sum(hour9) as Hour9,sum(hour10) as Hour10,sum(hour11) as Hour11,sum(hour12) as Hour12,sum(hour13) as Hour13,sum(hour14) as Hour14,sum(hour15) as Hour15,sum(hour16) as Hour16,sum hour17) as Hour17,sum(hour18) as Hour18,sum(hour19) as Hour19,sum(hour20) as Hour20,sum(hour21) as Hour21,sum(hour22) as Hour22,sum(hour23) as Hour23
from
(selectstore_code,
Business_Date,
case hour when 0 then sale_value else 0 end as hour0,
case hour when 1 then sale_value else 0 end as hour1,
case hour when 2 then sale_value else 0 end as hour2,
case hour when 3 then sale_value else 0 end as hour3,
case hour when 4 then sale_value else 0 end as hour4,
case hour when 5 then sale_value else 0 end as hour5,
case hour when 6 then sale_value else 0 end as hour6,
case hour when 7 then sale_value else 0 end as hour7,
case hour when 8 then sale_value else 0 end as hour8,
case hour when 9 then sale_value else 0 end as hour9,
case hour when 10 then sale_value else 0 end as hour10,
case hour when 11 then sale_value else 0 end as hour11,
case hour when 12 then sale_value else 0 end as hour12,
case hour when 13 then sale_value else 0 end as hour13,
case hour when 14 then sale_value else 0 end as hour14,
case hour when 15 then sale_value else 0 end as hour15,
case hour when 16 then sale_value else 0 end as hour16,
case hour when 17 then sale_value else 0 end as hour17,
case hour when 18 then sale_value else 0 end as hour18,
case hour when 19 then sale_value else 0 end as hour19,
case hour when 20 then sale_value else 0 end as hour20,
case hour when 21 then sale_value else 0 end as hour21,
case hour when 22 then sale_value else 0 end as hour22,
case hour when 23 then sale_value else 0 end as hour23
from
(selectstore_code,
convert (char(10),getdate(),103) as Business_Date,
datepart (HH, transaction_date_time) 'Hour',
sale_value
from sale
) a
) b
group by store_code, business_date
July 3, 2008 at 8:50 am
I've read this back and haven't made myself too clear.
I've got the sum of each hour working, but what I meant about getting the other 2 'sums' to work was referrng to the fact that I need to sum 2 other quantities per each hour, not just sales. so the extract will end up as
....|sum sales for hour 1|sum tax for hour 1|sum another int for hour1|...then the asme for hour 2, 3 etc. At the moment my query only gives me the ....|sum sales for hour 1| before then going onto ....|sum sales for hour 2|
Hope that makes sense :hehe:
July 3, 2008 at 10:36 am
Again...
[font="Courier New"]SELECT store_code
, convert (char(10),getdate(),103) as Business_Date
, SUM(CASE Hour(transaction_date_time) WHEN 0 THEN Sales ELSE 0 END) AS SalesHr1
, SUM(CASE Hour(transaction_date_time) WHEN 0 THEN Tax ELSE 0 END) AS TaxHr1
, SUM(CASE Hour(transaction_date_time) WHEN 0 THEN Another_Int ELSE 0 END) AS Another_Int Hr1
... *(THE SECTION REPEATED FOR ALL 24 HOURS!!!)*
, SUM(CASE Hour(transaction_date_time) WHEN 23 THEN Sales ELSE 0 END) AS SalesHr24
, SUM(CASE Hour(transaction_date_time) WHEN 23 THEN Tax ELSE 0 END) AS TaxHr24
, SUM(CASE Hour(transaction_date_time) WHEN 23 THEN Another_Int ELSE 0 END) AS Another_Int Hr24
GROUP BY store_code
, convert (char(10),getdate(),103)[/font]
July 4, 2008 at 6:14 am
Thats cool, thank-you for that. What threw me was your use of 'HOUR'. What it needs (and I'm sure you meant) was...
SUM(CASE when datepart (Hour,transaction_date_time) = 24 THEN Sale_value ELSE 0 END) AS SalesHr24
But anyway it works, so thanks again
July 8, 2008 at 6:13 am
Ah. No, I meant "Hour(..)", I just may have been (okay, so I WAS... :P) mistaken by the edition. I currenty use 2005 on almost all my servers, and "HOUR(DATEFIELDNAME)" is equivelent to "DATEPART(hh, DATEFIELDNAME)", though it was introduced in SQL Server 2005.
You got it now, though!
July 8, 2008 at 6:18 am
So thats the equivilent on 2005 is it. Thats a useful snippet of info to know too !!
Thanks very much for you help
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply