Combining 2 Pass Thru Queries where 1 is Group By 1

[nguenthe]

I am trying to merge two pass thru queries. One is parts and part attributes, the other is calculating a rolling 12 months sum of sales.

Here is the code for the one summing the sales:

SELECT z.itemnum, 

 sum(case when z.yr = year(sysdate) and month(sysdate) >= 1 then z.actualusage_01 else case when z.yr = year(sysdate - 365) and month(sysdate) = 1 then z.actualusage_01 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 2 then z.actualusage_02 else case when z.yr = year(sysdate - 365) and month(sysdate) < 2 then z.actualusage_02 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 3 then z.actualusage_03 else case when z.yr = year(sysdate - 365) and month(sysdate) < 3 then z.actualusage_03 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 4 then z.actualusage_04 else case when z.yr = year(sysdate - 365) and month(sysdate) < 4 then z.actualusage_04 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 5 then z.actualusage_05 else case when z.yr = year(sysdate - 365) and month(sysdate) < 5 then z.actualusage_05 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 6 then z.actualusage_06 else case when z.yr = year(sysdate - 365) and month(sysdate) < 6 then z.actualusage_06 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 7 then z.actualusage_07 else case when z.yr = year(sysdate - 365) and month(sysdate) < 7 then z.actualusage_07 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 8 then z.actualusage_08 else case when z.yr = year(sysdate - 365) and month(sysdate) < 8 then z.actualusage_08 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 9 then z.actualusage_09 else case when z.yr = year(sysdate - 365) and month(sysdate) < 9 then z.actualusage_09 else 0 end end +
case when z.yr = year(sysdate)  and month(sysdate) >= 10 then z.actualusage_10 else case when z.yr = year(sysdate - 365) and month(sysdate) < 10 then z.actualusage_10 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 11 then z.actualusage_11 else case when z.yr = year(sysdate - 365) and month(sysdate) < 11 then z.actualusage_11 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 12 then z.actualusage_12 else case when z.yr = year(sysdate - 365) and month(sysdate) < 12 then z.actualusage_12 else 0 end end)

 as 'Qty Used'
 
FROM   domdata.reports.vw_icwhseitemusage AS z 
WHERE  ( (  z.yr = YEAR(sysdate) 
                OR ( z.yr ) = ( YEAR(sysdate) - 1 ) ) 
         AND ( ( z.whse ) = 'WH1' ) ) 

group by z.itemnum

Here is a sample of the one for the item data I need to merge it with:

SELECT icwhseitem_0.whse, 
       icitem_0.vendnum, 
       icitem_0.itemnum, 
       icitem_0.description1, 
       icitem_0.description2, 
       icwhseitem_0.onhandqty, 
       icwhseitem_0.committedqty, 
       icwhseitem_0.bkordqty, 
       icwhseitem_0.onorderqty, 
       icwhseitem_0.lastsaledate, 
       icwhseitem_0.lastpurchdate, 
       icwhseitem_0.nonstock, 
       icitem_0.udchar1                AS 'Return Flag', 
       LEFT(icwhseitem_0.rcptdate, 10) AS 'Last Rect Date', 
       icwhseitem_0.loc 
FROM   domdata.pub.icitem icitem_0, 
       domdata.pub.icwhseitem icwhseitem_0 
WHERE  icitem_0.itemnum = icwhseitem_0.itemnum 
       AND icitem_0.conum = icwhseitem_0.conum 
       AND ( ( icwhseitem_0.whse = 'wh1' ) 
             AND ( icitem_0.vendnum = '11211' ) 
             AND ( icwhseitem_0.onhandqty > 0 ) 
              OR ( icwhseitem_0.whse = 'wh1' ) 
                 AND ( icitem_0.vendnum = '11212' ) 
                 AND ( icwhseitem_0.onhandqty > 0 ) 
              OR ( icwhseitem_0.whse = 'wh1' ) 
                 AND ( icitem_0.vendnum = '11167' ) 
                 AND ( icwhseitem_0.onhandqty > 0 ) )

I am not looking for the full solution, more advice on how to merge two pass thrus where one is groub-by and the other is not.

 

[dhookom]

Since this is a p-t presumably to MS SQL Server, have you considered using CTE with both queries?

Duane
Hook'D on Access
MS Access MVP

 

[nguenthe]

Thanks! I spent some time looking at this last night and here was what we came up with.

SELECT icwhseitem_0.whse, 
       icitem_0.vendnum, 
       icitem_0.itemnum, 
       icitem_0.description1, 
       icitem_0.description2, 
       icwhseitem_0.onhandqty, 
       icwhseitem_0.committedqty, 
       icwhseitem_0.bkordqty, 
       icwhseitem_0.onorderqty, 
       icwhseitem_0.lastsaledate, 
       icwhseitem_0.lastpurchdate, 
       icwhseitem_0.nonstock, 
       icitem_0.udchar1                AS 'Return Flag', 
       LEFT(icwhseitem_0.rcptdate, 10) AS 'Last Rect Date', 
       icwhseitem_0.loc,
 sum(case when z.yr = year(sysdate) and month(sysdate) >= 1 then z.actualusage_01 else case when z.yr = year(sysdate - 365) and month(sysdate) = 1 then z.actualusage_01 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 2 then z.actualusage_02 else case when z.yr = year(sysdate - 365) and month(sysdate) < 2 then z.actualusage_02 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 3 then z.actualusage_03 else case when z.yr = year(sysdate - 365) and month(sysdate) < 3 then z.actualusage_03 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 4 then z.actualusage_04 else case when z.yr = year(sysdate - 365) and month(sysdate) < 4 then z.actualusage_04 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 5 then z.actualusage_05 else case when z.yr = year(sysdate - 365) and month(sysdate) < 5 then z.actualusage_05 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 6 then z.actualusage_06 else case when z.yr = year(sysdate - 365) and month(sysdate) < 6 then z.actualusage_06 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 7 then z.actualusage_07 else case when z.yr = year(sysdate - 365) and month(sysdate) < 7 then z.actualusage_07 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 8 then z.actualusage_08 else case when z.yr = year(sysdate - 365) and month(sysdate) < 8 then z.actualusage_08 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 9 then z.actualusage_09 else case when z.yr = year(sysdate - 365) and month(sysdate) < 9 then z.actualusage_09 else 0 end end +
case when z.yr = year(sysdate)  and month(sysdate) >= 10 then z.actualusage_10 else case when z.yr = year(sysdate - 365) and month(sysdate) < 10 then z.actualusage_10 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 11 then z.actualusage_11 else case when z.yr = year(sysdate - 365) and month(sysdate) < 11 then z.actualusage_11 else 0 end end +
case when z.yr = year(sysdate) and month(sysdate) >= 12 then z.actualusage_12 else case when z.yr = year(sysdate - 365) and month(sysdate) < 12 then z.actualusage_12 else 0 end end) / 12
 as '12MO_Avg_Usage'
 
FROM   domdata.pub.icitem icitem_0 
   inner join domdata.pub.icwhseitem icwhseitem_0 on
(icitem_0.itemnum = icwhseitem_0.itemnum ) and
(icitem_0.conum = icwhseitem_0.conum )

inner join domdata.reports.vw_icwhseitemusage AS z
         on  z.whse  = icwhseitem_0.whse  
         and z.itemnum = icitem_0.itemnum

where ( ( icwhseitem_0.whse = 'wh1' ) 
             AND ( icitem_0.vendnum = '11211' ) 
             AND ( icwhseitem_0.onhandqty > 0 ) 
              OR ( icwhseitem_0.whse = 'wh1' ) 
                 AND ( icitem_0.vendnum = '11212' ) 
                 AND ( icwhseitem_0.onhandqty > 0 ) 
              OR ( icwhseitem_0.whse = 'wh1' ) 
                 AND ( icitem_0.vendnum = '11167' ) 
                 AND ( icwhseitem_0.onhandqty > 0 ) )  

and (z.yr = YEAR(sysdate)
                OR  z.yr  =  YEAR(sysdate) - 1) 

group by icwhseitem_0.whse, 
       icitem_0.vendnum, 
       icitem_0.itemnum, 
       icitem_0.description1, 
       icitem_0.description2, 
       icwhseitem_0.onhandqty, 
       icwhseitem_0.committedqty, 
       icwhseitem_0.bkordqty, 
       icwhseitem_0.onorderqty, 
       icwhseitem_0.lastsaledate, 
       icwhseitem_0.lastpurchdate, 
       icwhseitem_0.nonstock, 
       icitem_0.udchar1, 
       LEFT(icwhseitem_0.rcptdate, 10), 
       icwhseitem_0.loc