Richard Cooper
asked on
SQL query
is it possible to group the following query ?
WhichReturns:
MStockCode MStockDes MLineShipDate MBackOrderQty Customer
3188HX HOOD ASSY 2015-02-09 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-10 00:00:00.000 4.000 C011
3188HX HOOD ASSY 2015-02-11 00:00:00.000 4.000 C011
3188HX HOOD ASSY 2015-02-12 00:00:00.000 4.000 C011
3188HX HOOD ASSY 2015-02-13 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-16 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-17 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-18 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-19 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-20 00:00:00.000 1.000 C011
To return:
MStockCode MStockDes Customer 15/02/2015 22/02/2015 01/03/2015 08/03/2015
3188HX HOOD ASSY C011 18 13 0 0
Where the dates are the Sundays of the next four weeks and the totals are the quantity required between the dates.
I have asked a question which works for our accounting periods which are in a fixed table but not for the next four weeks which will change each week.
Q_28610736
Thanks
SELECT SorDetail.MStockCode, SorDetail.MStockDes, SorDetail.MLineShipDate, SorDetail.MBackOrderQty, RIGHT(RTRIM(SorDetail.MStockCode), 4)
AS Customer
FROM SorDetail
INNER JOIN SorMaster ON SorDetail.SalesOrder = SorMaster.SalesOrder
WHERE (SorDetail.MBackOrderQty > 0) AND (SorMaster.OrderStatus NOT IN ('9', '6', '*', '\')) AND (SorDetail.LineType <> '6') AND (SorDetail.MStockCode = '3188HX')
WhichReturns:
MStockCode MStockDes MLineShipDate MBackOrderQty Customer
3188HX HOOD ASSY 2015-02-09 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-10 00:00:00.000 4.000 C011
3188HX HOOD ASSY 2015-02-11 00:00:00.000 4.000 C011
3188HX HOOD ASSY 2015-02-12 00:00:00.000 4.000 C011
3188HX HOOD ASSY 2015-02-13 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-16 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-17 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-18 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-19 00:00:00.000 3.000 C011
3188HX HOOD ASSY 2015-02-20 00:00:00.000 1.000 C011
To return:
MStockCode MStockDes Customer 15/02/2015 22/02/2015 01/03/2015 08/03/2015
3188HX HOOD ASSY C011 18 13 0 0
Where the dates are the Sundays of the next four weeks and the totals are the quantity required between the dates.
I have asked a question which works for our accounting periods which are in a fixed table but not for the next four weeks which will change each week.
Q_28610736
Thanks
ASKER
Hi Phillip,
Its 2005 SQL server.
The columns would be fine.
Its 2005 SQL server.
The columns would be fine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent.
Thank you.
I had been trying to create a temp table to hold the date values.
This solution works without the table.
Thank you.
I had been trying to create a temp table to hold the date values.
This solution works without the table.
A more flexible* and potentially better performing approach would be to generate just the 4 rows needed to define each "week" into a CTE (a "recursive CTE")
Then that CTE will help not only to aggregate and pivot, but also to filter for the source records that are relevant (via an inner join).
details
* for example, you can simply extend this to 8 or 12 weeks with minimal changes.
Then that CTE will help not only to aggregate and pivot, but also to filter for the source records that are relevant (via an inner join).
;WITH MY_WEEKS (wkstart, wkfinish, wkno)
AS (
SELECT
DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())), CAST(GETDATE() AS date)) AS WKSTART
, DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())) + 7, CAST(GETDATE() AS date)) AS WKFINISH
, 1 AS WKNO
UNION ALL
SELECT
DATEADD(DAY, 7, wkstart)
, DATEADD(DAY, 7, wkfinish)
, wkno + 1
FROM my_weeks
WHERE wkno < 4
)
SELECT
SorDetail.MStockCode
, SorDetail.MStockDes
, RIGHT(RTRIM(SorDetail.MStockCode), 4) AS CUSTOMER
, SUM(CASE WHEN MY_WEEKS.wkno = 1 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK1
, SUM(CASE WHEN MY_WEEKS.wkno = 2 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK2
, SUM(CASE WHEN MY_WEEKS.wkno = 3 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK3
, SUM(CASE WHEN MY_WEEKS.wkno = 4 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK4
FROM SorDetail
INNER JOIN SorMaster ON SorDetail.SalesOrder = SorMaster.SalesOrder
INNER JOIN MY_WEEKS ON (SorDetail..MLineShipDate >= MY_WEEKS.wkstart
AND SorDetail..MLineShipDate < MY_WEEKS.wkfinish)
WHERE (SorDetail.MBackOrderQty > 0)
AND (SorMaster.OrderStatus NOT IN ('9', '6', '*', '\'))
AND (SorDetail.LineType <> '6')
AND (SorDetail.MStockCode = '3188HX')
;
details
**MS SQL Server 2008 Schema Setup**:
CREATE TABLE Table1
([MStockCode] varchar(6), [MStockDes] varchar(9), [MLineShipDate] datetime, [MBackOrderQty] int, [Customer] varchar(4))
;
INSERT INTO Table1
([MStockCode], [MStockDes], [MLineShipDate], [MBackOrderQty], [Customer])
VALUES
('3188HX', 'HOOD ASSY', '2015-02-09 00:00:00', 3.000, 'C011'),
('3188HX', 'HOOD ASSY', '2015-02-10 00:00:00', 4.000, 'C011'),
('3188HX', 'HOOD ASSY', '2015-02-11 00:00:00', 4.000, 'C011'),
('3188HX', 'HOOD ASSY', '2015-02-12 00:00:00', 4.000, 'C011'),
('3188HX', 'HOOD ASSY', '2015-02-13 00:00:00', 3.000, 'C011'),
('3188HX', 'HOOD ASSY', '2015-02-16 00:00:00', 3.000, 'C011'),
('3188HX', 'HOOD ASSY', '2015-02-17 00:00:00', 3.000, 'C011'),
('3188HX', 'HOOD ASSY', '2015-02-18 00:00:00', 3.000, 'C011'),
('3188HX', 'HOOD ASSY', '2015-02-19 00:00:00', 3.000, 'C011'),
('3188HX', 'HOOD ASSY', '2015-02-20 00:00:00', 1.000, 'C011')
;
**Query 1**:
with my_weeks (wkstart, wkfinish, wkno) as (
select
DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())), cast(GETDATE() as date)) as wkstart
, DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())) + 7, cast(GETDATE() as date)) as wkfinish
, 1 as wkno
union all
select dateadd(day,7,wkstart), dateadd(day,7,wkfinish), wkno + 1
from my_weeks
where wkno < 4
)
SELECT
MStockCode
, MStockDes
, Customer
, SUM( case when my_weeks.wkno = 1 then MBackOrderQty else 0 end ) AS week1
, SUM( case when my_weeks.wkno = 2 then MBackOrderQty else 0 end ) AS week2
, SUM( case when my_weeks.wkno = 3 then MBackOrderQty else 0 end ) AS week3
, SUM( case when my_weeks.wkno = 4 then MBackOrderQty else 0 end ) AS week4
FROM table1 D
INNER JOIN my_weeks ON ( D.MLineShipDate >= my_weeks.wkstart and D.MLineShipDate < my_weeks.wkfinish )
GROUP BY
MStockCode
, MStockDes
, Customer
**[Results][2]**:
| MSTOCKCODE | MSTOCKDES | CUSTOMER | WEEK1 | WEEK2 | WEEK3 | WEEK4 |
|------------|-----------|----------|-------|-------|-------|-------|
| 3188HX | HOOD ASSY | C011 | 18 | 13 | 0 | 0 |
[1]: http://sqlfiddle.com/#!3/a6494/16
* for example, you can simply extend this to 8 or 12 weeks with minimal changes.
ASKER
@PortletPaul,
I tried your suggestion but I get these errors,
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'my_weeks'.
We are using SQL 2005 which might be the problem.
Thanks
Richard
I tried your suggestion but I get these errors,
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'my_weeks'.
We are using SQL 2005 which might be the problem.
Thanks
Richard
Yes.
CAST(GETDATE() AS date)
will not work in 2005, sorry.
but this will achieve the equivalent:
dateadd(day, datediff(day,0, getdate() ), 0)
Please try:
CAST(GETDATE() AS date)
will not work in 2005, sorry.
but this will achieve the equivalent:
dateadd(day, datediff(day,0, getdate() ), 0)
Please try:
;WITH MY_WEEKS (wkstart, wkfinish, wkno)
AS (
SELECT
DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())), dateadd(day, datediff(day,0, getdate() ), 0)) AS WKSTART
, DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())) + 7, dateadd(day, datediff(day,0, getdate() ), 0)) AS WKFINISH
, 1 AS WKNO
UNION ALL
SELECT
DATEADD(DAY, 7, wkstart)
, DATEADD(DAY, 7, wkfinish)
, wkno + 1
FROM my_weeks
WHERE wkno < 4
)
SELECT
SorDetail.MStockCode
, SorDetail.MStockDes
, RIGHT(RTRIM(SorDetail.MStockCode), 4) AS CUSTOMER
, SUM(CASE WHEN MY_WEEKS.wkno = 1 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK1
, SUM(CASE WHEN MY_WEEKS.wkno = 2 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK2
, SUM(CASE WHEN MY_WEEKS.wkno = 3 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK3
, SUM(CASE WHEN MY_WEEKS.wkno = 4 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK4
FROM SorDetail
INNER JOIN SorMaster ON SorDetail.SalesOrder = SorMaster.SalesOrder
INNER JOIN MY_WEEKS ON (SorDetail..MLineShipDate >= MY_WEEKS.wkstart
AND SorDetail..MLineShipDate < MY_WEEKS.wkfinish)
WHERE (SorDetail.MBackOrderQty > 0)
AND (SorMaster.OrderStatus NOT IN ('9', '6', '*', '\'))
AND (SorDetail.LineType <> '6')
AND (SorDetail.MStockCode = '3188HX')
;
ASKER
@PortletPaul
I changed the code to
As our DB is case sensitive and the "MY_WEEKS" was in upper and lower case, but I get the following errors:
Msg 207, Level 16, State 1, Line 25
Invalid column name ''.
Msg 207, Level 16, State 1, Line 26
Invalid column name ''.
Thanks
I changed the code to
;WITH MY_WEEKS (wkstart, wkfinish, wkno)
AS (
SELECT
DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())), dateadd(day, datediff(day,0, getdate() ), 0)) AS wkstart
, DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())) + 7, dateadd(day, datediff(day,0, getdate() ), 0)) AS wkfinish
, 1 AS wkno
UNION ALL
SELECT
DATEADD(DAY, 7, wkstart)
, DATEADD(DAY, 7, wkfinish)
, wkno + 1
FROM MY_WEEKS
WHERE wkno < 4
)
SELECT
SorDetail.MStockCode
, SorDetail.MStockDes
, RIGHT(RTRIM(SorDetail.MStockCode), 4) AS CUSTOMER
, SUM(CASE WHEN MY_WEEKS.wkno = 1 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK1
, SUM(CASE WHEN MY_WEEKS.wkno = 2 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK2
, SUM(CASE WHEN MY_WEEKS.wkno = 3 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK3
, SUM(CASE WHEN MY_WEEKS.wkno = 4 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK4
FROM SorDetail
INNER JOIN SorMaster ON SorDetail.SalesOrder = SorMaster.SalesOrder
INNER JOIN MY_WEEKS ON (SorDetail..MLineShipDate >= MY_WEEKS.wkstart
AND SorDetail..MLineShipDate < MY_WEEKS.wkfinish)
WHERE (SorDetail.MBackOrderQty > 0)
AND (SorMaster.OrderStatus NOT IN ('9', '6', '*', '\'))
AND (SorDetail.LineType <> '6')
;
As our DB is case sensitive and the "MY_WEEKS" was in upper and lower case, but I get the following errors:
Msg 207, Level 16, State 1, Line 25
Invalid column name ''.
Msg 207, Level 16, State 1, Line 26
Invalid column name ''.
Thanks
lines 25 & 26 contain double periods, they should be singles, please change them
(It will pay dividends if you recognize some errors yourself)
(It will pay dividends if you recognize some errors yourself)
;WITH MY_WEEKS (wkstart, wkfinish, wkno)
AS (
SELECT
DATEADD(DAY, -DATEPART(DW, DATEADD(DAY, -1, GETDATE())), DATEADD(DAY, DATEDIFF(DAY,0, GETDATE() ), 0)) AS wkstart
, DATEADD(DAY, -DATEPART(DW, DATEADD(DAY, -1, GETDATE())) + 7, DATEADD(DAY, DATEDIFF(DAY,0, GETDATE() ), 0)) AS wkfinish
, 1 AS wkno
UNION ALL
SELECT
DATEADD(DAY, 7, wkstart)
, DATEADD(DAY, 7, wkfinish)
, wkno + 1
FROM MY_WEEKS
WHERE wkno < 4
)
SELECT
SorDetail.MStockCode
, SorDetail.MStockDes
, RIGHT(RTRIM(SorDetail.MStockCode), 4) AS CUSTOMER
, SUM(CASE WHEN MY_WEEKS.wkno = 1 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK1
, SUM(CASE WHEN MY_WEEKS.wkno = 2 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK2
, SUM(CASE WHEN MY_WEEKS.wkno = 3 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK3
, SUM(CASE WHEN MY_WEEKS.wkno = 4 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK4
FROM SorDetail
INNER JOIN SorMaster ON SorDetail.SalesOrder = SorMaster.SalesOrder
INNER JOIN MY_WEEKS ON (SorDetail.MLineShipDate >= MY_WEEKS.wkstart
AND SorDetail.MLineShipDate < MY_WEEKS.wkfinish)
WHERE (SorDetail.MBackOrderQty > 0)
AND (SorMaster.OrderStatus NOT IN ('9', '6', '*', '\'))
AND (SorDetail.LineType <> '6')
;
ASKER
@PortletPaul,
Yes I should have spotted the double periods sorry.
I have had to add a group by as it had errors.
The code works fine and as you have pointed out its easier to change.
I do appreciate your efforts but I have already accepted another answer to the question.
Thanks for your help, I will attach the complete code below.
The complete code I used
Yes I should have spotted the double periods sorry.
I have had to add a group by as it had errors.
The code works fine and as you have pointed out its easier to change.
I do appreciate your efforts but I have already accepted another answer to the question.
Thanks for your help, I will attach the complete code below.
The complete code I used
;WITH MY_WEEKS (wkstart, wkfinish, wkno)
AS (
SELECT
DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())), dateadd(day, datediff(day,0, getdate() ), 0)) AS wkstart
, DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())) + 7, dateadd(day, datediff(day,0, getdate() ), 0)) AS wkfinish
, 1 AS wkno
UNION ALL
SELECT
DATEADD(DAY, 7, wkstart)
, DATEADD(DAY, 7, wkfinish)
, wkno + 1
FROM MY_WEEKS
WHERE wkno < 4
)
SELECT
SorDetail.MStockCode
, SorDetail.MStockDes
, RIGHT(RTRIM(SorDetail.MStockCode), 4) AS CUSTOMER
, SUM(CASE WHEN MY_WEEKS.wkno = 1 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK1
, SUM(CASE WHEN MY_WEEKS.wkno = 2 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK2
, SUM(CASE WHEN MY_WEEKS.wkno = 3 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK3
, SUM(CASE WHEN MY_WEEKS.wkno = 4 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK4
FROM SorDetail
INNER JOIN SorMaster ON SorDetail.SalesOrder = SorMaster.SalesOrder
INNER JOIN MY_WEEKS ON (SorDetail.MLineShipDate >= MY_WEEKS.wkstart
AND SorDetail.MLineShipDate < MY_WEEKS.wkfinish)
WHERE (SorDetail.MBackOrderQty > 0)
AND (SorMaster.OrderStatus NOT IN ('9', '6', '*', '\'))
AND (SorDetail.LineType <> '6')
Group by SorDetail.MStockCode, SorDetail.MStockDes
:) apologies for the "to-ing & fro-ing"
I just wanted to point to an alternative approach.
I just wanted to point to an alternative approach.
Also, what version of SQL Server are you using, as that will impact the answer.