locked
How to improve the query? RRS feed

  • Question

  • Guys, Is there any way to simplify the query below?

     

    Thanks in advance,

    Aldo.

     

    Code Snippet

    USE myDb;

    if object_id('AuxTable20080722123030') is not null exec('DROP TABLE ' + 'AuxTable20080722123030');

    DECLARE

    @FirstDate AS datetime,

    @LastDate AS datetime;

     

    SET @FirstDate = convert(datetime, '2008-08-01 00:00:00.000', 121);

    SET @LastDate = convert(datetime, '2008-08-31 00:00:00.000', 121);

     

    SELECT

    1 AS 'ID',

    Accounts.FULLNAME AS 'Accounts.FullName',

    CASE WHEN StockMoves.REFERENCE <> 0 THEN 1 ELSE 0 END AS 'WO_ToManuf',

    CASE WHEN StockMoves.REFERENCE <> 0 AND Cast(Stock.ValueDate - StockMoves2.DUEDATE as int) < 0 THEN 1 ELSE 0 END AS 'Early'

     

    INTO AuxTable20080722123030

     

    FROM

    ITEMS AS Items

    INNER JOIN STOCKMOVES AS StockMoves

    INNER JOIN STOCK AS Stock ON StockMoves.STOCKID = Stock.ID

    INNER JOIN DOCUMENTSDEF AS DocumentsDef ON StockMoves.DOCUMENTID = DocumentsDef.DOCUMENTID

    INNER JOIN ACCOUNTS AS Accounts ON Stock.ACCOUNTKEY = Accounts.ACCOUNTKEY ON Items.ITEMKEY = StockMoves.ITEMKEY

    LEFT JOIN StockMoves AS StockMoves2 ON StockMoves.DETAILS = StockMoves2.DETAILS AND StockMoves2.DOCUMENTID In (6, 36)

     

    WHERE

    Accounts.SORTGROUP Between 3000 And 3999

    AND Stock.VALUEDATE BETWEEN @FirstDate AND @LastDate

    AND StockMoves.DOCUMENTID In (1, 2, 35)

    AND Accounts.ACCOUNTKEY In ('301501002', '301505009', '301503003')

     

    SELECT * FROM

    (

    SELECT

    GROUPING([ID]) AS grp,

    CASE WHEN GROUPING([Accounts.FULLNAME]) = 1 THEN 'Sub Total' ELSE [Accounts.FULLNAME] END AS 'Accounts.FullName',

    Sum([WO_ToManuf]) AS 'WO_ToManuf',

    Sum([Early]) AS 'Early'

     

    FROM AuxTable20080722123030

    GROUP BY

    [ID],

    [Accounts.FULLNAME]

    WITH ROLLUP

    )

    AS Data

    WHERE grp = 0 AND [WO_ToManuf] > 0

     

     

    Tuesday, September 2, 2008 9:13 AM

Answers

  • What do you mean by simplify? Are you looking to reduce the number of lines?

    I dont think you need a temp table to run this query. You can directly use the SELECT part of the INSERT query within your ROLUP query.

     

    Code Snippet

    SELECT * FROM

    (

    SELECT

    GROUPING([ID]) AS grp,

    CASE WHEN GROUPING([Accounts.FULLNAME]) = 1 THEN 'Sub Total' ELSE [Accounts.FULLNAME] END AS 'Accounts.FullName',

    Sum([WO_ToManuf]) AS 'WO_ToManuf',

    Sum([Early]) AS 'Early'

    FROM (

    SELECT

    1 AS 'ID',

    Accounts.FULLNAME AS 'Accounts.FullName',

    CASE WHEN StockMoves.REFERENCE <> 0 THEN 1 ELSE 0 END AS 'WO_ToManuf',

    CASE WHEN StockMoves.REFERENCE <> 0 AND Cast(Stock.ValueDate - StockMoves2.DUEDATE as int) < 0 THEN 1 ELSE 0 END AS 'Early'

    FROM ITEMS AS Items

    INNER JOIN STOCKMOVES AS StockMoves

    INNER JOIN STOCK AS Stock ON StockMoves.STOCKID = Stock.ID

    INNER JOIN DOCUMENTSDEF AS DocumentsDef

    ON StockMoves.DOCUMENTID = DocumentsDef.DOCUMENTID

    INNER JOIN ACCOUNTS AS Accounts

    ON Stock.ACCOUNTKEY = Accounts.ACCOUNTKEY ON Items.ITEMKEY = StockMoves.ITEMKEY

    LEFT JOIN StockMoves AS StockMoves2 ON StockMoves.DETAILS = StockMoves2.DETAILS AND StockMoves2.DOCUMENTID In (6, 36)

    WHERE Accounts.SORTGROUP Between 3000 And 3999

    AND Stock.VALUEDATE BETWEEN @FirstDate AND @LastDate

    AND StockMoves.DOCUMENTID In (1, 2, 35)

    AND Accounts.ACCOUNTKEY In ('301501002', '301505009', '301503003')

    ) a

    GROUP BY

    [ID],

    [Accounts.FULLNAME]

    WITH ROLLUP

    ) AS Data

    WHERE grp = 0 AND [WO_ToManuf] > 0

     

     

    Tuesday, September 2, 2008 9:40 AM

All replies

  • Hi there,

     

    It is a little difficult to determine from reading your source code alone. Are you able to provide your table strucutre and perhaps some sample data so that forum members may replicate your scenario in order to investigate it further?

     

    Tuesday, September 2, 2008 9:30 AM
  • What do you mean by simplify? Are you looking to reduce the number of lines?

    I dont think you need a temp table to run this query. You can directly use the SELECT part of the INSERT query within your ROLUP query.

     

    Code Snippet

    SELECT * FROM

    (

    SELECT

    GROUPING([ID]) AS grp,

    CASE WHEN GROUPING([Accounts.FULLNAME]) = 1 THEN 'Sub Total' ELSE [Accounts.FULLNAME] END AS 'Accounts.FullName',

    Sum([WO_ToManuf]) AS 'WO_ToManuf',

    Sum([Early]) AS 'Early'

    FROM (

    SELECT

    1 AS 'ID',

    Accounts.FULLNAME AS 'Accounts.FullName',

    CASE WHEN StockMoves.REFERENCE <> 0 THEN 1 ELSE 0 END AS 'WO_ToManuf',

    CASE WHEN StockMoves.REFERENCE <> 0 AND Cast(Stock.ValueDate - StockMoves2.DUEDATE as int) < 0 THEN 1 ELSE 0 END AS 'Early'

    FROM ITEMS AS Items

    INNER JOIN STOCKMOVES AS StockMoves

    INNER JOIN STOCK AS Stock ON StockMoves.STOCKID = Stock.ID

    INNER JOIN DOCUMENTSDEF AS DocumentsDef

    ON StockMoves.DOCUMENTID = DocumentsDef.DOCUMENTID

    INNER JOIN ACCOUNTS AS Accounts

    ON Stock.ACCOUNTKEY = Accounts.ACCOUNTKEY ON Items.ITEMKEY = StockMoves.ITEMKEY

    LEFT JOIN StockMoves AS StockMoves2 ON StockMoves.DETAILS = StockMoves2.DETAILS AND StockMoves2.DOCUMENTID In (6, 36)

    WHERE Accounts.SORTGROUP Between 3000 And 3999

    AND Stock.VALUEDATE BETWEEN @FirstDate AND @LastDate

    AND StockMoves.DOCUMENTID In (1, 2, 35)

    AND Accounts.ACCOUNTKEY In ('301501002', '301505009', '301503003')

    ) a

    GROUP BY

    [ID],

    [Accounts.FULLNAME]

    WITH ROLLUP

    ) AS Data

    WHERE grp = 0 AND [WO_ToManuf] > 0

     

     

    Tuesday, September 2, 2008 9:40 AM
  • Note that I did not test this query. Since I do not have the tables scripts, I just re-arranged your query and posted it back.

    Tuesday, September 2, 2008 9:42 AM
  • Great!

    Thanks a lot! (again).

     

    USE myDb;

    if object_id('AuxTable20080722123030') is not null exec('DROP TABLE ' + 'AuxTable20080722123030');

    DECLARE

    @FirstDate AS datetime,

    @LastDate AS datetime

    ;

    SET @FirstDate = convert(datetime, '2008-08-01 00:00:00.000', 121);

    SET @LastDate = convert(datetime, '2008-08-31 00:00:00.000', 121);

    SELECT * FROM

    (

    SELECT

    GROUPING([ID]) AS grp,

    CASE WHEN GROUPING([Accounts.FULLNAME]) = 1 THEN 'Sub Total' ELSE [Accounts.FULLNAME] END AS 'Accounts.FullName',

    Sum([WO_ToManuf]) AS 'WO_ToManuf',

    Sum([Early]) AS 'Early'

    FROM (

    SELECT

    1 AS 'ID',

    Accounts.FULLNAME AS 'Accounts.FullName',

    CASE WHEN StockMoves.REFERENCE <> 0 THEN 1 ELSE 0 END AS 'WO_ToManuf',

    CASE WHEN StockMoves.REFERENCE <> 0 AND Cast(Stock.ValueDate - StockMoves2.DUEDATE as int) < 0 THEN 1 ELSE 0 END AS 'Early'

    FROM ITEMS AS Items

    INNER JOIN STOCKMOVES AS StockMoves

    INNER JOIN STOCK AS Stock ON StockMoves.STOCKID = Stock.ID

    INNER JOIN DOCUMENTSDEF AS DocumentsDef ON StockMoves.DOCUMENTID = DocumentsDef.DOCUMENTID

    INNER JOIN ACCOUNTS AS Accounts ON Stock.ACCOUNTKEY = Accounts.ACCOUNTKEY ON Items.ITEMKEY = StockMoves.ITEMKEY

    LEFT JOIN StockMoves AS StockMoves2 ON StockMoves.DETAILS = StockMoves2.DETAILS AND StockMoves2.DOCUMENTID In (6, 36)

    WHERE

    Accounts.SORTGROUP Between 3000 And 3999

    AND Stock.VALUEDATE BETWEEN @FirstDate AND @LastDate

    AND StockMoves.DOCUMENTID In (1, 2, 35)

    AND Accounts.ACCOUNTKEY In ('302000004', '378000015', '302000055')

    ) a

    GROUP BY

    [ID],

    [Accounts.FULLNAME]

    WITH ROLLUP

    ) AS Data

    WHERE grp = 0 AND [WO_ToManuf] > 0

     

    An example of the output:

     

    grp          Accounts.FullName                        WO_ToManuf         Early

    0             A.C.H. MARKETING INC                        1                        0

    0             ARTI MAKINA                                        3                        0

    0             CAROLINA SPECIALTY TOOLS            3                        0

    0             Sub Total                                               7                        0

    Tuesday, September 2, 2008 9:53 AM