asked on

SQL query

I have the following code:

SELECT        SorDetail.MStockCode, SorDetail.MStockDes, SorDetail.MLineShipDate, SorDetail.MBackOrderQty
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 = 'PD33188HXC011')

Open in new window

Which returns the following:
HXC011       HOOD ASSY       2015-02-06 00:00:00.000      3.000
HXC011       HOOD ASSY       2015-02-09 00:00:00.000      4.000
HXC011       HOOD ASSY       2015-03-10 00:00:00.000      4.000
HXC011       HOOD ASSY       2015-04-11 00:00:00.000      4.000
I have a table of accounting periods:

SELECT        Period, PStart, PEnd
FROM            bsp_AcPeriods

Open in new window

Returning:

1      2014-09-29 00:00:00.000      2014-11-02 00:00:00.000
2      2014-11-03 00:00:00.000      2014-11-30 00:00:00.000
3      2014-12-01 00:00:00.000      2014-12-28 00:00:00.000
4      2014-12-29 00:00:00.000      2015-02-01 00:00:00.000
5      2015-02-02 00:00:00.000      2015-03-01 00:00:00.000
6      2015-03-02 00:00:00.000      2015-03-29 00:00:00.000
7      2015-03-30 00:00:00.000      2015-05-03 00:00:00.000
8      2015-05-04 00:00:00.000      2015-05-31 00:00:00.000
9      2015-06-01 00:00:00.000      2015-06-28 00:00:00.000
10      2015-06-29 00:00:00.000      2015-07-26 00:00:00.000
11      2015-07-27 00:00:00.000      2015-08-30 00:00:00.000
12      2015-08-31 00:00:00.000      2015-10-04 00:00:00.000

Is it possible to link the two tables so I can group the parts by accounting period and sum the SorDetail.MBackOrderQty to get

MStockCode MStockDes 1 2 3 4 5 6 7 8 9 10 11 12
HXC011        HOOD ASSY 0 0 0       0 7.000 4.00 4.00 0 0 0 0 0

Thanks

Vitor Montalvão

The periods are fixed? I mean, it's always 12 periods?

Richard Cooper

ASKER

Its 12 periods each year

ste5an

Well, the problem is as Vitor pointed out: It's a pivot query. This means strictly speak< it will work only with fixed number of periods. Otherwise you need dynamic SQL. The second option means, that you should consider doing the pivot in the consumer.

Static:

CREATE TABLE #QueryResult
    (
      MStockCode NVARCHAR(255) ,
      MStockDes NVARCHAR(255) ,
      MLineShipDate DATE ,
      MBackOrderQty INT
    );

INSERT  INTO #QueryResult
VALUES  ( 'HXC011', 'HOOD ASSY', '20150206', 3 ),
        ( 'HXC011', 'HOOD ASSY', '20150209', 4 ),
        ( 'HXC011', 'HOOD ASSY', '20150310', 4 ),
        ( 'HXC011', 'HOOD ASSY', '20150411', 4 );

CREATE TABLE #Periods
    (
      Period INT ,
      PStart DATE ,
      PEnd DATE
    )

INSERT  INTO #Periods
VALUES  ( 1, '20140929', '20141102' ),
        ( 2, '20141103', '20141130' ),
        ( 3, '20141201', '20141228' ),
        ( 4, '20141229', '20150201' ),
        ( 5, '20150202', '20150301' ),
        ( 6, '20150302', '20150329' ),
        ( 7, '20150330', '20150503' ),
        ( 8, '20150504', '20150531' ),
        ( 9, '20150601', '20150628' ),
        ( 10, '20150629', '20150726' ),
        ( 11, '20150727', '20150830' ),
        ( 12, '20150831', '20151004' );

WITH    Data
          AS ( SELECT   QR.MStockCode ,
                        QR.MStockDes ,
                        QR.MLineShipDate ,
                        QR.MBackOrderQty
               FROM     #QueryResult QR
             ),
        DateBuckets
          AS ( SELECT   P.Period ,
                        D.MStockCode ,
                        D.MStockDes ,
                        D.MLineShipDate ,
                        D.MBackOrderQty
               FROM     Data D
                        INNER JOIN #Periods P ON D.MLineShipDate >= P.PStart
                                                 AND D.MLineShipDate <= P.PEnd
             )
    SELECT  *
    FROM    DateBuckets DB PIVOT ( SUM(DB.MBackOrderQty) FOR DB.Period IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] ) ) P;

DROP TABLE #Periods;
DROP TABLE #QueryResult;

Open in new window

Dynamic:

CREATE TABLE #QueryResult
    (
      MStockCode NVARCHAR(255) ,
      MStockDes NVARCHAR(255) ,
      MLineShipDate DATE ,
      MBackOrderQty INT
    );

INSERT  INTO #QueryResult
VALUES  ( 'HXC011', 'HOOD ASSY', '20150206', 3 ),
        ( 'HXC011', 'HOOD ASSY', '20150209', 4 ),
        ( 'HXC011', 'HOOD ASSY', '20150310', 4 ),
        ( 'HXC011', 'HOOD ASSY', '20150411', 4 );

CREATE TABLE #Periods
    (
      Period INT ,
      PStart DATE ,
      PEnd DATE
    )

INSERT  INTO #Periods
VALUES  ( 1, '20140929', '20141102' ),
        ( 2, '20141103', '20141130' ),
        ( 3, '20141201', '20141228' ),
        ( 4, '20141229', '20150201' ),
        ( 5, '20150202', '20150301' ),
        ( 6, '20150302', '20150329' ),
        ( 7, '20150330', '20150503' ),
        ( 8, '20150504', '20150531' ),
        ( 9, '20150601', '20150628' ),
        ( 10, '20150629', '20150726' ),
        ( 11, '20150727', '20150830' ),
        ( 12, '20150831', '20151004' );

DECLARE @ColumnList NVARCHAR(MAX) = N'';
DECLARE @Sql NVARCHAR(MAX) = N'';
DECLARE @SqlTemplate NVARCHAR(MAX) = N'
WITH    Data
          AS ( SELECT   QR.MStockCode ,
                        QR.MStockDes ,
                        QR.MLineShipDate ,
                        QR.MBackOrderQty
               FROM     #QueryResult QR
             ),
        DateBuckets
          AS ( SELECT   P.Period ,
                        D.MStockCode ,
                        D.MStockDes ,
                        D.MLineShipDate ,
                        D.MBackOrderQty
               FROM     Data D
                        INNER JOIN #Periods P ON D.MLineShipDate >= P.PStart
                                                 AND D.MLineShipDate <= P.PEnd
             )
    SELECT  *
    FROM    DateBuckets DB PIVOT ( SUM(DB.MBackOrderQty) FOR DB.Period IN ( @ColumnList ) ) P;
';

WITH    Data
          AS ( SELECT   QR.MStockCode ,
                        QR.MStockDes ,
                        QR.MLineShipDate ,
                        QR.MBackOrderQty
               FROM     #QueryResult QR
             )
    SELECT  @ColumnList += ', ' + QUOTENAME(CAST(P.Period AS NVARCHAR(255)))
    FROM    Data D
            INNER JOIN #Periods P ON D.MLineShipDate >= P.PStart
                                     AND D.MLineShipDate <= P.PEnd
    GROUP BY P.Period;

SET @ColumnList = STUFF(@ColumnList, 1, 2, '');
SET @Sql = REPLACE(@SqlTemplate, '@ColumnList', @ColumnList);

EXECUTE (@Sql);

DROP TABLE #Periods;
DROP TABLE #QueryResult;

Open in new window

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Cooper

ASKER

@ste5an

I tried the Static query but get these errors:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ','.

And these for the Dynamic one:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ','.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 67
Must declare the scalar variable "@ColumnList".
Msg 137, Level 15, State 2, Line 73
Must declare the scalar variable "@ColumnList".
Msg 137, Level 15, State 2, Line 74
Must declare the scalar variable "@SqlTemplate".
Msg 137, Level 15, State 2, Line 76
Must declare the scalar variable "@Sql".

Richard Cooper

ASKER

Thanks Vitor,

It worked without any problems.

I will expand on this now I have a starting point.

Thanks
Richard