Ok, today users finished testing the 3 reports I inserted your code in to get the correct date needed and it worked to perfection. Thank you so much for your fast help. I would have never figured this one out.
Just start having an issue with the code you did for me.
The table I am reading from currently has 456,716 records in it. Not sure when it got that high but that is production records.
If I run this report from UAT site which has only 220,734 records it runs without issue.
It seems like when your code executes now, it take 25 – 35 minutes to process these records and write records to #SC_EarliestDate
Any ideas what could be causing this. Causes report to fail because it times out.
Any help would be greatly appreciated.
CREATE TABLE [#SC_EarliestDate] ( [Group_ID] INT, [Created_On] VARCHAR(10) ) ON [PRIMARY] CREATE TABLE [SC_StatusDaysDateTEST] ( [LogID] INT, [Created_On] VARCHAR(10), [Group_ID] INT, [SalesID] VARCHAR(20), [Status1] VARCHAR(10) ) ON [PRIMARY]*/
--Build temp SC_EarliestDateTEST table also needed to find the last date a group has changed
IS NOT NULL
--Build temp #SC_EarliestDate table CREATE
( [Group_ID] INT, [Created_On] VARCHAR(10) ) ON [PRIMARY]
--It reads thru StatusDaysDateTEST table and finds the last date this group id was changed.
--This date will be used to calculate the Status Days for that group id.
as (select logid
,created_on ,group_id ,salesid ,status1 ,row_number()
by group_id order
by created_on desc,logid desc)
by group_id order
by salesid desc)
from sc_statusdaysdateTEST ) ,cte2
as (select a.group_id
,a.rn ,1 as adjustment from cte1 as a left outer
join cte1 as b
on b.group_id=a.group_id and b.salesid=a.salesid and b.status1=a.status1 and b.rn=a.rn-1 and b.rk=a.rk where b.logid
from (select a.logid
,a.created_on ,a.group_id ,a.salesid ,a.status1 ,sum(b.adjustment)
from cte1 as a left outer
join cte2 as b
on b.group_id=a.group_id and b.rn<=a.rn group by a.logid ,a.created_on ,a.group_id ,a.salesid ,a.status1 ,a.rk ) as a
order by grouprank ,created_on)
My test just finished and it ran for over 28 minutes and produced 43,744 records into table #sc_earliestdate.
I'd look into this:
- is it possible to store created_on as a date field instead of varchar
- create appropriate index on sc_statusdaysdate table (group_id at least)
- experiment on porting cte1 to temp table with appropriate index
please take a look and provide feedback
Alternative Way = SQL
;WITH cte AS (SELECT a.*, 1 [grp] FROM [sc_statusdaysdate] a WHERE rn = 1 UNION ALL SELECT a.*, ( CASE WHEN a.[group_id] = b.[group_id] AND a.salesid = b.salesid AND a.status1 = b.status1 THEN b.grp WHEN a.[group_id] <> b.[group_id] THEN 1 ELSE b.grp + 1 END ) FROM [sc_statusdaysdate] a, cte b WHERE a.rn = b.rn + 1) SELECT * FROM cte go