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
LVL 1
RickCooperAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
I did use a PIVOT solution and also a CTE so it's more readable query:
WITH Sor (MStockCode, MStockDes, MLineShipDate, MBackOrderQty)
AS (
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')
)

SELECT *
FROM (
		SELECT Sor.MStockCode, Sor.MStockDes, bsp_AcPeriods.Period Period, MBackOrderQty
		FROM Sor, bsp_AcPeriods
		WHERE Sor.MLineShipDate BETWEEN bsp_AcPeriods.PStart and bsp_AcPeriods.PEnd
	) AS MySor
PIVOT
(SUM(MBackOrderQty) FOR Period IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) AS MyPivot

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The periods are fixed? I mean, it's always 12 periods?
0
 
RickCooperAuthor Commented:
Its 12 periods each year
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ste5anSenior DeveloperCommented:
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

0
 
RickCooperAuthor Commented:
@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".
0
 
RickCooperAuthor Commented:
Thanks Vitor,

It worked without any problems.

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

Thanks
Richard
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.