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

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

user1914
1#
user1914 Published in June 23, 2018, 9:18 pm

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO