Solved

SQL query

Posted on 2015-02-05
6
233 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 45

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 32

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now