Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL query

Posted on 2015-02-05
6
Medium Priority
?
253 Views
Last Modified: 2015-02-05
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
0
Comment
Question by:RickCooper
  • 3
  • 2
6 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40590904
The periods are fixed? I mean, it's always 12 periods?
0
 
LVL 1

Author Comment

by:RickCooper
ID: 40590933
Its 12 periods each year
0
 
LVL 36

Expert Comment

by:ste5an
ID: 40590953
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40590984
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
 
LVL 1

Author Comment

by:RickCooper
ID: 40590987
@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
 
LVL 1

Author Closing Comment

by:RickCooper
ID: 40590999
Thanks Vitor,

It worked without any problems.

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

Thanks
Richard
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question