Solved

SQL query

Posted on 2015-02-09
11
163 Views
Last Modified: 2015-02-11
is it possible to group the following query ?

SELECT  SorDetail.MStockCode, SorDetail.MStockDes, SorDetail.MLineShipDate, SorDetail.MBackOrderQty,  RIGHT(RTRIM(SorDetail.MStockCode), 4) 
                         AS Customer
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 = '3188HX')

Open in new window


WhichReturns:

MStockCode              MStockDes                     MLineShipDate              MBackOrderQty      Customer
3188HX                       HOOD ASSY                       2015-02-09 00:00:00.000      3.000      C011
3188HX                       HOOD ASSY                       2015-02-10 00:00:00.000      4.000      C011
3188HX                      HOOD ASSY                       2015-02-11 00:00:00.000      4.000      C011
3188HX                       HOOD ASSY                       2015-02-12 00:00:00.000      4.000      C011
3188HX                       HOOD ASSY                       2015-02-13 00:00:00.000      3.000      C011
3188HX                     HOOD ASSY                       2015-02-16 00:00:00.000      3.000      C011
3188HX                       HOOD ASSY                       2015-02-17 00:00:00.000      3.000      C011
3188HX                      HOOD ASSY                       2015-02-18 00:00:00.000      3.000      C011
3188HX                       HOOD ASSY                       2015-02-19 00:00:00.000      3.000      C011
3188HX                     HOOD ASSY                       2015-02-20 00:00:00.000      1.000      C011

To return:

MStockCode      MStockDes      Customer            15/02/2015      22/02/2015      01/03/2015      08/03/2015
3188HX           HOOD ASSY      C011                      18                        13                             0                    0

Where the dates are the Sundays of the next four weeks and the totals are the quantity required between the dates.

I have asked a question which works for our accounting periods which are in a fixed table but not for the next four weeks which will change each week.
Q_28610736

Thanks
0
Comment
Question by:RickCooper
  • 5
  • 4
  • 2
11 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40598112
You can using Dynamic SQL, but it's a lot easier to just have columns saying "This Week", "Next Week", "Week 3" and "Week 4" - is that OK?

Also, what version of SQL Server are you using, as that will impact the answer.
0
 
LVL 1

Author Comment

by:RickCooper
ID: 40598122
Hi Phillip,

 Its 2005 SQL server.
The columns would be fine.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40598174
How about this:

With mySorDetail as
(SELECT  SorDetail.MStockCode, SorDetail.MStockDes, SorDetail.MLineShipDate, SorDetail.MBackOrderQty,  RIGHT(RTRIM(SorDetail.MStockCode), 4) 
                         AS Customer
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 = '3188HX')),
mySorDetail2 as
(select MStockCode, MStockDes, Customer,sum(MBackOrderQty) as MBackOrderQty, dateadd(d,-datepart(dw,dateadd(d,-1,MLineShipDate)),MLineShipDate) as LineShipDate,
dateadd(d,-datepart(dw,dateadd(d,-1,getdate())),getdate()) as MyWeek
from mySorDetail
Group by MStockCode, MStockDes, Customer, MLineShipDate),
mySorDetail3 as 
(Select MStockCode, MStockDes, Customer, Sum(MBackOrderQty) as MBackOrderQty, LineShipDate, MyWeek 
from mySorDetail2
group by MStockCode, MStockDes, Customer, LineShipDate, MyWeek),
mySorDetail4 as 
(Select MStockCode, MStockDes, Customer, LineShipDate,
(Select sum(MBackOrderQty) from mySorDetail3 as N Where M.MStockCode = N.MStockCode and M.MStockDes = N.MStockDes and M.Customer = N.Customer and M.LineShipDate = N.LineShipDate
and datediff(d,N.LineShipDate,N.MyWeek) = 0) as ThisWeek,
(Select sum(MBackOrderQty) from mySorDetail3 as N Where M.MStockCode = N.MStockCode and M.MStockDes = N.MStockDes and M.Customer = N.Customer and M.LineShipDate = N.LineShipDate
and datediff(d,N.LineShipDate,N.MyWeek) = -7) as NextWeek,
(Select sum(MBackOrderQty) from mySorDetail3 as N Where M.MStockCode = N.MStockCode and M.MStockDes = N.MStockDes and M.Customer = N.Customer and M.LineShipDate = N.LineShipDate
and datediff(d,N.LineShipDate,N.MyWeek) = -14) as Week3,
(Select sum(MBackOrderQty) from mySorDetail3 as N Where M.MStockCode = N.MStockCode and M.MStockDes = N.MStockDes and M.Customer = N.Customer and M.LineShipDate = N.LineShipDate
and datediff(d,N.LineShipDate,N.MyWeek) = -21) as Week4

from mySorDetail3 as M)
Select MStockCode, MStockDes, Customer, isnull(sum(ThisWeek),0) as ThisWeek, isnull(Sum(NextWeek),0) as NextWeek, isnull(Sum(Week3),0) as NextWeek3, isnull(Sum(Week4),0) as NextWeek4
from mySorDetail4
Group by MStockCode, MStockDes, Customer

Open in new window

0
 
LVL 1

Author Closing Comment

by:RickCooper
ID: 40598187
Excellent.
Thank you.

I had been trying to create a temp table to hold the date values.
This solution works without the table.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40600168
A more flexible* and potentially better performing approach would be to generate just the 4 rows needed to define each "week" into a CTE  (a "recursive CTE")

Then that CTE will help not only to aggregate and pivot, but also to filter for the source records that are relevant (via an inner join).

;WITH MY_WEEKS (wkstart, wkfinish, wkno)
AS (
      SELECT
            DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())), CAST(GETDATE() AS date)) AS WKSTART
          , DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())) + 7, CAST(GETDATE() AS date)) AS WKFINISH
          , 1 AS WKNO
      UNION ALL
            SELECT
                  DATEADD(DAY, 7, wkstart)
                , DATEADD(DAY, 7, wkfinish)
                , wkno + 1
            FROM my_weeks
            WHERE wkno < 4
   )
SELECT
      SorDetail.MStockCode
    , SorDetail.MStockDes
    , RIGHT(RTRIM(SorDetail.MStockCode), 4) AS CUSTOMER
    , SUM(CASE WHEN MY_WEEKS.wkno = 1 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK1
    , SUM(CASE WHEN MY_WEEKS.wkno = 2 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK2
    , SUM(CASE WHEN MY_WEEKS.wkno = 3 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK3
    , SUM(CASE WHEN MY_WEEKS.wkno = 4 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK4
FROM SorDetail
      INNER JOIN SorMaster ON SorDetail.SalesOrder = SorMaster.SalesOrder
      INNER JOIN MY_WEEKS ON (SorDetail..MLineShipDate >= MY_WEEKS.wkstart
                          AND SorDetail..MLineShipDate < MY_WEEKS.wkfinish)
WHERE (SorDetail.MBackOrderQty > 0)
      AND (SorMaster.OrderStatus NOT IN ('9', '6', '*', '\'))
      AND (SorDetail.LineType <> '6')
      AND (SorDetail.MStockCode = '3188HX')
;

Open in new window


details
**MS SQL Server 2008 Schema Setup**:

    
    
    CREATE TABLE Table1
    	([MStockCode] varchar(6), [MStockDes] varchar(9), [MLineShipDate] datetime, [MBackOrderQty] int, [Customer] varchar(4))
    ;
    	
    INSERT INTO Table1
    	([MStockCode], [MStockDes], [MLineShipDate], [MBackOrderQty], [Customer])
    VALUES
    	('3188HX', 'HOOD ASSY', '2015-02-09 00:00:00', 3.000, 'C011'),
    	('3188HX', 'HOOD ASSY', '2015-02-10 00:00:00', 4.000, 'C011'),
    	('3188HX', 'HOOD ASSY', '2015-02-11 00:00:00', 4.000, 'C011'),
    	('3188HX', 'HOOD ASSY', '2015-02-12 00:00:00', 4.000, 'C011'),
    	('3188HX', 'HOOD ASSY', '2015-02-13 00:00:00', 3.000, 'C011'),
    	('3188HX', 'HOOD ASSY', '2015-02-16 00:00:00', 3.000, 'C011'),
    	('3188HX', 'HOOD ASSY', '2015-02-17 00:00:00', 3.000, 'C011'),
    	('3188HX', 'HOOD ASSY', '2015-02-18 00:00:00', 3.000, 'C011'),
    	('3188HX', 'HOOD ASSY', '2015-02-19 00:00:00', 3.000, 'C011'),
    	('3188HX', 'HOOD ASSY', '2015-02-20 00:00:00', 1.000, 'C011')
    ;

**Query 1**:

    with my_weeks (wkstart, wkfinish, wkno) as (
          select 
                 DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())), cast(GETDATE() as date)) as wkstart
               , DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())) + 7, cast(GETDATE() as date)) as wkfinish
               , 1 as wkno
          union all
          select dateadd(day,7,wkstart), dateadd(day,7,wkfinish), wkno + 1
          from my_weeks
          where wkno < 4
          )
    SELECT
          MStockCode
        , MStockDes
        , Customer
        , SUM( case when my_weeks.wkno = 1 then MBackOrderQty else 0 end ) AS week1
        , SUM( case when my_weeks.wkno = 2 then MBackOrderQty else 0 end ) AS week2
        , SUM( case when my_weeks.wkno = 3 then MBackOrderQty else 0 end ) AS week3
        , SUM( case when my_weeks.wkno = 4 then MBackOrderQty else 0 end ) AS week4
    FROM table1 D
    INNER JOIN my_weeks ON ( D.MLineShipDate >= my_weeks.wkstart and D.MLineShipDate < my_weeks.wkfinish )
    GROUP BY
          MStockCode
        , MStockDes
        , Customer
    

**[Results][2]**:
    
    | MSTOCKCODE | MSTOCKDES | CUSTOMER | WEEK1 | WEEK2 | WEEK3 | WEEK4 |
    |------------|-----------|----------|-------|-------|-------|-------|
    |     3188HX | HOOD ASSY |     C011 |    18 |    13 |     0 |     0 |



  [1]: http://sqlfiddle.com/#!3/a6494/16

Open in new window


* for example, you can simply extend this to 8 or 12 weeks with minimal changes.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:RickCooper
ID: 40601030
@PortletPaul,

 I tried your suggestion but I get these errors,
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'my_weeks'.

We are using SQL 2005 which might be the problem.

Thanks

Richard
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40602012
Yes.

CAST(GETDATE() AS date)

will not work in 2005, sorry.
but this will achieve the equivalent:

dateadd(day, datediff(day,0, getdate() ), 0)

Please try:
;WITH MY_WEEKS (wkstart, wkfinish, wkno)
AS (
      SELECT
            DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())), dateadd(day, datediff(day,0, getdate() ), 0)) AS WKSTART
          , DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())) + 7, dateadd(day, datediff(day,0, getdate() ), 0)) AS WKFINISH
          , 1 AS WKNO
      UNION ALL
            SELECT
                  DATEADD(DAY, 7, wkstart)
                , DATEADD(DAY, 7, wkfinish)
                , wkno + 1
            FROM my_weeks
            WHERE wkno < 4
   )
SELECT
      SorDetail.MStockCode
    , SorDetail.MStockDes
    , RIGHT(RTRIM(SorDetail.MStockCode), 4) AS CUSTOMER
    , SUM(CASE WHEN MY_WEEKS.wkno = 1 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK1
    , SUM(CASE WHEN MY_WEEKS.wkno = 2 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK2
    , SUM(CASE WHEN MY_WEEKS.wkno = 3 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK3
    , SUM(CASE WHEN MY_WEEKS.wkno = 4 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK4
FROM SorDetail
      INNER JOIN SorMaster ON SorDetail.SalesOrder = SorMaster.SalesOrder
      INNER JOIN MY_WEEKS ON (SorDetail..MLineShipDate >= MY_WEEKS.wkstart
                          AND SorDetail..MLineShipDate < MY_WEEKS.wkfinish)
WHERE (SorDetail.MBackOrderQty > 0)
      AND (SorMaster.OrderStatus NOT IN ('9', '6', '*', '\'))
      AND (SorDetail.LineType <> '6')
      AND (SorDetail.MStockCode = '3188HX')
;

Open in new window

0
 
LVL 1

Author Comment

by:RickCooper
ID: 40602601
@PortletPaul

I changed the code to  
;WITH MY_WEEKS (wkstart, wkfinish, wkno)
AS (
      SELECT
            DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())), dateadd(day, datediff(day,0, getdate() ), 0)) AS wkstart
          , DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())) + 7, dateadd(day, datediff(day,0, getdate() ), 0)) AS wkfinish
          , 1 AS wkno
      UNION ALL
            SELECT
                  DATEADD(DAY, 7, wkstart)
                , DATEADD(DAY, 7, wkfinish)
                , wkno + 1
            FROM MY_WEEKS
            WHERE wkno < 4
   )
SELECT
      SorDetail.MStockCode
    , SorDetail.MStockDes
    , RIGHT(RTRIM(SorDetail.MStockCode), 4) AS CUSTOMER
    , SUM(CASE WHEN MY_WEEKS.wkno = 1 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK1
    , SUM(CASE WHEN MY_WEEKS.wkno = 2 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK2
    , SUM(CASE WHEN MY_WEEKS.wkno = 3 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK3
    , SUM(CASE WHEN MY_WEEKS.wkno = 4 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK4
FROM SorDetail
      INNER JOIN SorMaster ON SorDetail.SalesOrder = SorMaster.SalesOrder
      INNER JOIN MY_WEEKS ON (SorDetail..MLineShipDate >= MY_WEEKS.wkstart
                          AND SorDetail..MLineShipDate < MY_WEEKS.wkfinish)
WHERE (SorDetail.MBackOrderQty > 0)
      AND (SorMaster.OrderStatus NOT IN ('9', '6', '*', '\'))
      AND (SorDetail.LineType <> '6')
;

Open in new window


As our DB is case sensitive and the "MY_WEEKS" was in upper and lower case, but I get the following errors:

Msg 207, Level 16, State 1, Line 25
Invalid column name ''.
Msg 207, Level 16, State 1, Line 26
Invalid column name ''.

Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40602616
lines 25 & 26 contain double periods, they should be singles, please change them

(It will pay dividends if you recognize some errors yourself)

;WITH MY_WEEKS (wkstart, wkfinish, wkno)
AS (
      SELECT
            DATEADD(DAY, -DATEPART(DW, DATEADD(DAY, -1, GETDATE())), DATEADD(DAY, DATEDIFF(DAY,0, GETDATE() ), 0)) AS wkstart
          , DATEADD(DAY, -DATEPART(DW, DATEADD(DAY, -1, GETDATE())) + 7, DATEADD(DAY, DATEDIFF(DAY,0, GETDATE() ), 0)) AS wkfinish
          , 1 AS wkno
      UNION ALL
            SELECT
                  DATEADD(DAY, 7, wkstart)
                , DATEADD(DAY, 7, wkfinish)
                , wkno + 1
            FROM MY_WEEKS
            WHERE wkno < 4
   )
SELECT
      SorDetail.MStockCode
    , SorDetail.MStockDes
    , RIGHT(RTRIM(SorDetail.MStockCode), 4) AS CUSTOMER
    , SUM(CASE WHEN MY_WEEKS.wkno = 1 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK1
    , SUM(CASE WHEN MY_WEEKS.wkno = 2 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK2
    , SUM(CASE WHEN MY_WEEKS.wkno = 3 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK3
    , SUM(CASE WHEN MY_WEEKS.wkno = 4 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK4
FROM SorDetail
      INNER JOIN SorMaster ON SorDetail.SalesOrder = SorMaster.SalesOrder
      INNER JOIN MY_WEEKS ON (SorDetail.MLineShipDate >= MY_WEEKS.wkstart
                          AND SorDetail.MLineShipDate < MY_WEEKS.wkfinish)
WHERE (SorDetail.MBackOrderQty > 0)
      AND (SorMaster.OrderStatus NOT IN ('9', '6', '*', '\'))
      AND (SorDetail.LineType <> '6')
;

Open in new window

0
 
LVL 1

Author Comment

by:RickCooper
ID: 40602626
@PortletPaul,

 Yes I should have spotted the double periods sorry.
 I have had to add a group by as it had errors.

The code works fine and as you have pointed out its easier to change.
I do appreciate your efforts but I have already accepted another answer to the question.

Thanks for your help, I will attach the complete code below.

The complete code I used
;WITH MY_WEEKS (wkstart, wkfinish, wkno)
AS (
      SELECT
            DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())), dateadd(day, datediff(day,0, getdate() ), 0)) AS wkstart
          , DATEADD(DAY, -DATEPART(dw, DATEADD(DAY, -1, GETDATE())) + 7, dateadd(day, datediff(day,0, getdate() ), 0)) AS wkfinish
          , 1 AS wkno
      UNION ALL
            SELECT
                  DATEADD(DAY, 7, wkstart)
                , DATEADD(DAY, 7, wkfinish)
                , wkno + 1
            FROM MY_WEEKS
            WHERE wkno < 4
   )
SELECT
      SorDetail.MStockCode
    , SorDetail.MStockDes
    , RIGHT(RTRIM(SorDetail.MStockCode), 4) AS CUSTOMER
    , SUM(CASE WHEN MY_WEEKS.wkno = 1 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK1
    , SUM(CASE WHEN MY_WEEKS.wkno = 2 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK2
    , SUM(CASE WHEN MY_WEEKS.wkno = 3 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK3
    , SUM(CASE WHEN MY_WEEKS.wkno = 4 THEN SorDetail.MBackOrderQty ELSE 0 END) AS WEEK4
FROM SorDetail
      INNER JOIN SorMaster ON SorDetail.SalesOrder = SorMaster.SalesOrder
      INNER JOIN MY_WEEKS ON (SorDetail.MLineShipDate >= MY_WEEKS.wkstart
                          AND SorDetail.MLineShipDate < MY_WEEKS.wkfinish)
WHERE (SorDetail.MBackOrderQty > 0)
      AND (SorMaster.OrderStatus NOT IN ('9', '6', '*', '\'))
      AND (SorDetail.LineType <> '6')
Group by SorDetail.MStockCode,  SorDetail.MStockDes

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40602632
:) apologies for the "to-ing & fro-ing"

I just wanted to point to an alternative approach.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

17 Experts available now in Live!

Get 1:1 Help Now