SQL query

is it possible to group the following query ?

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')

Open in new window


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
LVL 1
RickCooperAsked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
How about this:

With mySorDetail as
(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')),
mySorDetail2 as
(select MStockCode, MStockDes, Customer,sum(MBackOrderQty) as MBackOrderQty, dateadd(d,-datepart(dw,dateadd(d,-1,MLineShipDate)),MLineShipDate) as LineShipDate,
dateadd(d,-datepart(dw,dateadd(d,-1,getdate())),getdate()) as MyWeek
from mySorDetail
Group by MStockCode, MStockDes, Customer, MLineShipDate),
mySorDetail3 as 
(Select MStockCode, MStockDes, Customer, Sum(MBackOrderQty) as MBackOrderQty, LineShipDate, MyWeek 
from mySorDetail2
group by MStockCode, MStockDes, Customer, LineShipDate, MyWeek),
mySorDetail4 as 
(Select MStockCode, MStockDes, Customer, LineShipDate,
(Select sum(MBackOrderQty) from mySorDetail3 as N Where M.MStockCode = N.MStockCode and M.MStockDes = N.MStockDes and M.Customer = N.Customer and M.LineShipDate = N.LineShipDate
and datediff(d,N.LineShipDate,N.MyWeek) = 0) as ThisWeek,
(Select sum(MBackOrderQty) from mySorDetail3 as N Where M.MStockCode = N.MStockCode and M.MStockDes = N.MStockDes and M.Customer = N.Customer and M.LineShipDate = N.LineShipDate
and datediff(d,N.LineShipDate,N.MyWeek) = -7) as NextWeek,
(Select sum(MBackOrderQty) from mySorDetail3 as N Where M.MStockCode = N.MStockCode and M.MStockDes = N.MStockDes and M.Customer = N.Customer and M.LineShipDate = N.LineShipDate
and datediff(d,N.LineShipDate,N.MyWeek) = -14) as Week3,
(Select sum(MBackOrderQty) from mySorDetail3 as N Where M.MStockCode = N.MStockCode and M.MStockDes = N.MStockDes and M.Customer = N.Customer and M.LineShipDate = N.LineShipDate
and datediff(d,N.LineShipDate,N.MyWeek) = -21) as Week4

from mySorDetail3 as M)
Select MStockCode, MStockDes, Customer, isnull(sum(ThisWeek),0) as ThisWeek, isnull(Sum(NextWeek),0) as NextWeek, isnull(Sum(Week3),0) as NextWeek3, isnull(Sum(Week4),0) as NextWeek4
from mySorDetail4
Group by MStockCode, MStockDes, Customer

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You can using Dynamic SQL, but it's a lot easier to just have columns saying "This Week", "Next Week", "Week 3" and "Week 4" - is that OK?

Also, what version of SQL Server are you using, as that will impact the answer.
0
 
RickCooperAuthor Commented:
Hi Phillip,

 Its 2005 SQL server.
The columns would be fine.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
RickCooperAuthor Commented:
Excellent.
Thank you.

I had been trying to create a temp table to hold the date values.
This solution works without the table.
0
 
PortletPaulfreelancerCommented:
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).

;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')
;

Open in new window


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

Open in new window


* for example, you can simply extend this to 8 or 12 weeks with minimal changes.
0
 
RickCooperAuthor Commented:
@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
0
 
PortletPaulfreelancerCommented:
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:
;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')
;

Open in new window

0
 
RickCooperAuthor Commented:
@PortletPaul

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')
;

Open in new window


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
0
 
PortletPaulfreelancerCommented:
lines 25 & 26 contain double periods, they should be singles, please change them

(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')
;

Open in new window

0
 
RickCooperAuthor Commented:
@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
;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

Open in new window

0
 
PortletPaulfreelancerCommented:
:) apologies for the "to-ing & fro-ing"

I just wanted to point to an alternative approach.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.