Hey all!
I was hoping to get some pointers to help me figure out how to re-write this SSRS report. We've had this report in place for over 2 years, it was built by my predecessor, and for some reason about 3 weeks ago, "it stopped returning the full result set like it did before" as per the end user that received this report...
They argue that the report would send an e-mail for every result, though the subscription is set to send on weekdays at 9PM...there was only 1 subscription...
The report is essentially pulling the most recently created company ID from the Company table, and returning the very first order entered with that customer in the 24 hours prior. It returns some other info that's present on the Orderheader table, and the relationship between those 2 tables is based on the CompanyID. It also returns the city name from the City table. The relationship there is set with the orderheader based on the ORigin/Destination cities which are codes linked to the same code in the city table.
set transaction isolation level read uncommitted
select top 1 ord_billto 'Bill To ID',
c.cmp_name 'Bill To Name',
c.cmp_creditlimit 'Credit Limit',
c.cmp_revtype2 'Sales Office',
c.cmp_subcompany 'Sales Rep',
(select top 1 o.ord_startdate from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'First Ship Date',
(select top 1 o.ord_cmdvalue from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'First Order Value',
(select top 1 o.ord_hdrnumber from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'First Order Number',
(select top 1 (select cty_nmstct from city where cty_code=o.ord_origincity) from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'Ship City',
(select top 1 (select cty_nmstct from city where cty_code=o.ord_destcity) from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'Delivery City',
(select top 1 o.ord_status from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate) 'Order Status'
from orderheader
inner join company c on c.cmp_id=orderheader.ord_billto
where ord_billto not in (select billto_id from fls_firsttime_billto_flag)
and ord_status in ('cmp','std','avl','pln')
group by ord_billto, c.cmp_name, c.cmp_revtype2, c.cmp_subcompany, c.cmp_creditlimit
order by (select top 1 o.ord_startdate from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate)
insert into fls_firsttime_billto_flag
select top 1 ord_billto ,
(select top 1 o.ord_startdate from orderheader o where o.ord_billto=orderheader.ord_billto
and o.ord_status in ('cmp','std','avl','pln')
order by o.ord_startdate)
from orderheader
inner join company c on c.cmp_id=orderheader.ord_billto
where ord_billto not in (select billto_id from fls_firsttime_billto_flag)
As you can see, this query isn't written very well and the performance isn't any good either...(It takes over 40 seconds to return a single row). At the end you can see it also does an Insert so that it logs all the results so they don't come up next time it runs.
Here's an alternate method i tried but for some reason i can't get it to return any results. I checked the execution plan and i can see that it's select 7 rows at the start, but then it drops them when it gets to the first Join: IF i run the subquery in the WHERE clause, it's returning exactly what i need to be able to run the report...Basically i need to figure out how to store the result from the WHERE clause such that the other columns in the query use that as the "filter argument"(Not sure what term to use here :S)
select o.ord_billto, c.cmp_name, c.cmp_creditavail,c.cmp_revtype2, o.ord_startdate, (select top 1 o.ord_hdrnumber from orderheader o order by o.ord_startdate desc)'Order#',
o.ord_status,o.ord_charge, c1.cty_name, c2.cty_name
from orderheader o
inner join company c on o.ord_billto=c.cmp_billto
join city c1 on o.ord_origincity=c1.cty_code
join city c2 on o.ord_destcity=c2.cty_code
where c.cmp_id in (select cmp_id from company where datediff(hh,cmp_createdate,getdate())<=24 and cmp_billto='Y')
I hope i mentioned all the necessary info. Any help or input is appreciated! Btw, we're running level 80 compatability, so i'm pretty sure i won't be able to use any recent new functions to work around this like using WITH...