Home Spliting a week of data in to days of the Week SQL
Reply: 1

Spliting a week of data in to days of the Week SQL

Justin Greenwood
1#
Justin Greenwood Published in 2017-11-14 11:00:25Z

Good Morning All So I was asked to look where things are getting ordered from on a weekly bases so simple.

DECLARE @vYearWeek INT
SET @vYearWeek = '201745';

SELECT
  LEFT(Orders.ProductWhsLocation,1) AS 'Area',
  COUNT(Orders.HostOrderNo) AS 'Picked'
FROM CHDS_Common.dbo.OMOrder Orders
  LEFT JOIN CHDS_Management.dbo.Calendar Cal ON CONVERT(date, Orders.EarliestPickDate) = Cal.DT 
  WHERE Orders.PicksetNo <> 0 AND cal.YWK = @vYearWeek
  GROUP BY LEFT(ProductWhsLocation,1)
  ORDER BY LEFT(ProductWhsLocation,1);

And this works fine ... how ever I need to got deeper now and spilt it in to days. Now logic would say this will be simple as:

DECLARE @vYearWeek INT
SET @vYearWeek = '201745';

SELECT
  LEFT(Orders.ProductWhsLocation,1) AS 'Area',
  CASE WHEN Cal.DW = 1 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Sunday',
  CASE WHEN Cal.DW = 2 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Monday',
  CASE WHEN Cal.DW = 3 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Tuesday',
  CASE WHEN Cal.DW = 4 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Wednesday',
  CASE WHEN Cal.DW = 5 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Thuresday',
  CASE WHEN Cal.DW = 6 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Friday',
  CASE WHEN Cal.DW = 7 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Saturday'
FROM CHDS_Common.dbo.OMOrder Orders
  LEFT JOIN CHDS_Management.dbo.Calendar Cal ON CONVERT(date, Orders.EarliestPickDate) = Cal.DT 
WHERE Orders.PicksetNo <> 0 AND cal.YWK = @vYearWeek
GROUP BY LEFT(ProductWhsLocation,1)
ORDER BY LEFT(ProductWhsLocation,1);

But as you all know ERROR :( Column 'CHDS_Management.dbo.Calendar.DW' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any Help on sorting this out would be great many thanks all.

B House
2#
B House Reply to 2017-11-14 12:30:56Z

Pivot Query would work in this scenario

--Improved Answer

step1: Get day of week either by date in calendar table or day number

step 2: use pivot table to get count of rows by day

SELECT *

FROM
(
    SELECT
  LEFT(Orders.ProductWhsLocation,1) AS 'Area',
  COUNT(Orders.HostOrderNo) OrderCount,
  DATENAME(weekday,cal.dt) 'DayName'
  FROM CHDS_Common.dbo.OMOrder Orders
  LEFT JOIN CHDS_Management.dbo.Calendar Cal ON CONVERT(date, Orders.EarliestPickDate) = Cal.DT 
WHERE Orders.PicksetNo <> 0 AND cal.YWK = @vYearWeek
GROUP BY LEFT(ProductWhsLocation,1),cal.dw
) AS SourceTable PIVOT(SUM(OrderCount) FOR [DayName] IN([SUNDAY]
                                                        ,[MONDAY]
                                                        ,[TUESDAY]
                                                        ,[WEDNESDAY]
                                                        ,[THURSDAY]
                                                        ,[FRIDAY]
                                                        ,[SATURDAY] )

) AS PivotTable;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.313456 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO