Solved

SQL query

Posted on 2015-02-05
6
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 51

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 34

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 500 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

617 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