Link to home
Start Free TrialLog in
Avatar of Richard Cooper
Richard CooperFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of Phillip Burton
Phillip Burton

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.
Avatar of Richard Cooper

ASKER

Hi Phillip,

 Its 2005 SQL server.
The columns would be fine.
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent.
Thank you.

I had been trying to create a temp table to hold the date values.
This solution works without the table.
Avatar of PortletPaul
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.
@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
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

@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
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

@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

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

I just wanted to point to an alternative approach.