Improving Query

[UltraSmooth]

I have written a query, although provides the correct results, I'm wondering if my limited sql skills have made it a bit disorganized. I was wondering if someone can have a quick look and possibly punch some holes in the way I am doing things.

First I'll explain the tables being used,

syloctn - contains a list of locations by id

syinvhdr - contains item classification information (categories, vendors, etc)

syinvdtl - contails item details such as onhand quantity, prices, costs, etc.

poheader - purchase order header information such as delivery dates, date ordered by purchase order id

podetail - detail item information for a purchase order

budgetdata - budgeted sales and margin information by category for a fiscal month

syinvhis - sales history summaries by category by week/month/year ending dates, date stored as 7-digit int
monthly_periods - maps 7-digit int to actual calendar date, fiscalperiod, fiscalyear

I need to return a complete set of records based on a certain category (dept+class), location and range of fiscalperiods. This must also contain the total onhand and onorder for the category. Anywhere where a record does not exist to match a 0 must be returned other than Null.

Here's the query,

[
SELECT budtbl.lc_loctn as location
, budtbl.fiscalyear+1 as budgetyear
, budtbl.fiscalperiod
, COALESCE(budtbl.bud_retail, 0) as retail
, COALESCE(budtbl.bud_gm, 0) as margin
, COALESCE(budtbl.bud_mstf, 0) as mstf
, COALESCE(budtbl.bud_endinv, 0) as endinv
, COALESCE(syinvhis.his_salretl, 0) as his_retail
, COALESCE(syinvhis.his_salcost, 0) as his_cost
, COALESCE(syinvhis.his_salunit, 0) as his_units
, COALESCE(onorder.onorder, 0) as onorder
, COALESCE(onhand.onhand, 0) as onhand
FROM
( SELECT cjtbl.lc_loctn
, cjtbl.fiscalyear
, cjtbl.fiscalperiod
, cjtbl.clarion_date
, budgetdata.bud_dept
, budgetdata.bud_class
, budgetdata.bud_retail
, budgetdata.bud_gm
, budgetdata.bud_mstf
, budgetdata.bud_endinv
FROM
( SELECT syloctn.lc_loctn
, monthly_periods.fiscalyear
, monthly_periods.fiscalperiod
, monthly_periods.clarion_date
FROM syloctn
CROSS JOIN monthly_periods
WHERE syloctn.lc_loctn IN ('01','03','04','05') #SUPPLIED VIA PARAMETER
AND ((monthly_periods.fiscalyear>2007 OR (monthly_periods.fiscalyear=2007 AND monthly_periods.fiscalperiod>=10)) AND
(monthly_periods.fiscalyear<2008 OR (monthly_periods.fiscalyear=2008 AND monthly_periods.fiscalperiod<=3)))) as cjtbl
LEFT JOIN budgetdata
ON budgetdata.bud_year=cjtbl.fiscalyear+1
AND budgetdata.bud_period=cjtbl.fiscalperiod
AND budgetdata.bud_loctn=cjtbl.lc_loctn
AND budgetdata.bud_dept='C1' #SUPPLIED VIA PARAMETER
AND budgetdata.bud_class='490') as budtbl #SUPPLIED VIA PARAMETER
LEFT JOIN syinvhis
ON syinvhis.his_tdate=budtbl.clarion_date
AND syinvhis.his_type='DC' #NEVER CHANGES
AND syinvhis.his_loctn=budtbl.lc_loctn
AND syinvhis.his_period='M' #NEVER CHANGES
AND syinvhis.his_service='R' #NEVER CHANGES
AND syinvhis.his_key='C1490' # #SUPPLIED VIA PARAMETER, STRING CONCATENATION OF DEPT AND CLASS
LEFT JOIN
( SELECT syinvhdr.ih_sbclass
, syinvhdr.ih_dept
, syinvhdr.ih_class
, pod_ploctn
, monthly_periods.fiscalyear
, monthly_periods.fiscalperiod
, SUM(pod_unitcost * (pod_qtyordrd - pod_qtyrecvd - pod_qtycncld)) as onorder
FROM syinvhdr
JOIN podetail
ON podetail.pod_style=syinvhdr.ih_style
AND (pod_unitcost * (pod_qtyordrd - pod_qtyrecvd - pod_qtycncld))>0
JOIN poheader
ON poheader.poh_ponum=podetail.pod_ponum
AND (poh_status='O' OR poh_status='B') #VARIABLES O AND B NEVER CHANGE
JOIN monthly_periods
ON monthly_periods.clarion_date=GET_FMONTH_END(poheader.poh_delbdate)
AND ((monthly_periods.fiscalyear>2008 OR (monthly_periods.fiscalyear=2008 AND monthly_periods.fiscalperiod>=10)) #YEAR AND PERIOD SUPPLIED VIA PARAMETER
AND (monthly_periods.fiscalyear<2009 OR (monthly_periods.fiscalyear=2009 AND monthly_periods.fiscalperiod<=3)))
WHERE syinvhdr.ih_dept='C1' #SUPPLIED VIA PARAMETER
AND syinvhdr.ih_class='490' #SUPPLIED VIA PARAMETER
GROUP BY ih_dept,ih_class,pod_ploctn,fiscalyear,fiscalperiod
) AS onorder
ON onorder.pod_ploctn=lc_loctn
AND onorder.fiscalyear=budtbl.fiscalyear+1
AND onorder.fiscalperiod=budtbl.fiscalperiod
LEFT JOIN
( SELECT syinvdtl.id_loctn
, SUM(syinvdtl.id_curonh*syinvdtl.id_lcost) as onhand
FROM syinvhdr
JOIN syinvdtl
ON syinvdtl.id_style=syinvhdr.ih_style
AND syinvdtl.id_loctn IN ('01','03','04','05') #SUPPLIED VIA PARAMETER
WHERE syinvhdr.ih_dept='C1' #SUPPLIED VIA PARAMETER
AND syinvhdr.ih_class='490' #SUPPLIED VIA PARAMETER
GROUP BY syinvdtl.id_loctn
, syinvhdr.ih_dept
, syinvhdr.ih_class
) AS onhand
ON onhand.id_loctn=budtbl.lc_loctn
ORDER BY budgetyear
, fiscalperiod
, lc_loctn;
]

One of the biggest reasons I feel I've gone overboard on it is because I have to supply the parameters manually to so many places in the query. I couldn't see a way around this because I need to join to tables for the onhand and onorder but these values are stored in the database by item# so a subquery that sums them up by category/loctn was needed. I also used a cross join to get me the full list of records that must be populated and returned.

I know this is long-winded and since the query does work it's no huge concern but I certainly would like to learn better ways of doing things if possible. I would appreciated anyone's feedback. You learn from your mistakes!

Also, how does one display the properly formatted code within their posts?

 

[HarleyQuinn]

In response to your question about displaying code correctly include the code within code tags like this:[ignore]

[/ignore][code]Your code...

[/code]

Note: the

 tags won't be displayed, I've left them in their for explanitory purposes.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

[URL unfurl="true"]http://lessthandot.com[/URL]