Fun with SQL
We had a little fun with some tricky SQL today, which put me in the mood to share my masterpiece, for the morbidly curious. There’s nothing particularly fancy here, just a whole lot of data coming from a lot of different places. Well anyway, enjoy:
# Days Sales Outstanding
$qry = $db->prepare(”
select
dss02_chan.description as chan,
if(master_sales.soldto like ‘9%’ or
master_sales.soldto=’FSFSFS’,
‘Company1′,’Company2′) as business,
month_abbrev,
master_sales.fcyear,
round((sum(sales)/1000),0) as sales,
a_r,
((a_r / round((sum(sales)/1000),0))*100) as pct,
days,
round(((a_r / round((sum(sales)/1000),0) ) * days),0) as dso
from master_sales
inner join cmf_dss on
master_sales.soldto = cmf_dss.soldto
inner join dss02_chan on
cmf_dss.2 = dss02_chan.id
inner join tbl_monthnames on
master_sales.fcmonth = tbl_monthnames.month_id
inner join
(select fcyear,fcmonth,count(date) as days
from tbl_datetofiscal group by fcyear,fcmonth)
as monthdays
on
monthdays.fcmonth = master_sales.fcmonth and
monthdays.fcyear = master_sales.fcyear
left join
(select
round((sum(claims)+sum(future)+sum(current)
+sum(30day)+sum(60day)+sum(90day)
+sum(120day))/1000,0) as a_r,
fcmonth,
fcyear,
cmf_dss.2 as chan,
if(rpt_ar65.soldto like ‘9%’ or
rpt_ar65.soldto=’FSFSFS’,
‘Company1′,’Company2′) as bsn
from rpt_ar65
inner join
(select max(rptdate) as monthenddate
from rpt_ar65 inner join tbl_datetofiscal
on rptdate=tbl_datetofiscal.date
group by fcmonth,fcyear) as monthends
on rpt_ar65.rptdate = monthends.monthenddate
inner join tbl_datetofiscal
on rpt_ar65.rptdate = tbl_datetofiscal.date
inner join cmf_dss
on rpt_ar65.soldto = cmf_dss.soldto
group by fcmonth,fcyear,chan,bsn
) as ar65plus
on
ar65plus.fcmonth = master_sales.fcmonth and
ar65plus.fcyear = master_sales.fcyear and
ar65plus.chan = cmf_dss.2 and
ar65plus.bsn = if(mp=’8′,’Company1′,’Company2′)
where
master_sales.rec_type = ‘ACT’ and
(cmf_dss.2 = ‘EXPRT’ or cmf_dss.2 = ‘SPEC’) and
(
(master_sales.fcyear = ? and
master_sales.fcmonth >= ?) or
(master_sales.fcyear = ? and
master_sales.fcmonth < = ?)
)
group by chan,business,month_abbrev,master_sales.fcyear
order by chan asc,business asc,
master_sales.fcyear asc,master_sales.fcmonth asc
“);
I’ve been lurking for awhile. I like your blog. That is one nasty SQL, I haven’t written anything that complicated using mainframe SQL, yet.
schweeet.
Nice work.
I don’t get this, though:
(master_sales.fcyear = ? and
master_sales.fcmonth >= ?) or
(master_sales.fcyear = ? and
master_sales.fcmonth Null? Not to pick, just curious. I’m not an expert by any means. I know that anything can slow a query down. Is this why you did this? To speed up the query? If so, cool. I’ll use that.
Weird. The first half of my comment was lost …
Eh, I don’t feel like typing it again. Carry on.
Thanks Huts, I’ve never done any sql on a mainframe, that has to be interesting. We had an S/390 at my old job, it was interesting to see how their system of flat vsam files had organically developed into something resembling a relational database over the years, but of course without the benefit of sql.
Huck — just taking a stab at your question, is it that this part:
(master_sales.fcyear = ? and
master_sales.fcmonth >= ?) or
(master_sales.fcyear = ? and
master_sales.fcmonth < = ?)
looks redundant, like either branch will be true? No, the '?' placeholders would get passed different values, so you would specify a range, e.g. "(=2005 and >= 3) or (=2006 and <=2)” yields the last 12 (completed) months.
I gotcha - parameters.
The damned subselects keep throwing me off. How can you read this without indenting? My dyslexia demands indentations, damn it!
Oh I know, I can hardly read it as is. It’s very well indented in the source, but I wasn’t able to figure out a quick way to keep WordPress from stomping all over it when I posted. I’ll probably try to spruce it up tonight.
Don’t worry about it, really, at least not for me.