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]*/
Your Code:
--Build temp SC_EarliestDateTEST table also needed to find the last date a group has changed
IF
Object_id(N'tempdb..#SC_EarliestDate',
N'U')
IS NOT NULL
DROP
TABLE #SC_EarliestDate
--Build temp #SC_EarliestDate table
CREATE
TABLE [#SC_EarliestDate]
(
[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.
;
with cte1
as (select logid
,created_on
,group_id
,salesid
,status1
,row_number()
over(partition
by group_id order
by created_on desc,logid desc)
as rn
,dense_rank()
over(partition
by group_id order
by salesid desc)
as rk
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
is null
)
insert
into #sc_earliestdate(group_id,created_on)
select
top(1)
with ties
group_id
,created_on
from (select a.logid
,a.created_on
,a.group_id
,a.salesid
,a.status1
,sum(b.adjustment)
as grouprank
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 row_number()
over(partition
by group_id
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.
Group_ID Created_On
2186256 2018-04-25
2186257 2018-03-24
2186261 2018-03-24
2186263 2018-03-24
2186272 2018-03-25
2186281 2018-03-24
2186283 2018-03-24
2186322 2018-03-26
2186332 2018-03-24
2186403 2018-03-24
2186413 2018-03-24
2186415 2018-03-24
2186417 2018-03-24
2186421 2018-03-24
2186447 2018-03-24
2186482 2018-03-24
2186486 2018-03-24
2186490 2018-03-25
2186544 2018-03-24
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
hi
Alternative way
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