SQLTeam.com | Weblogs | Forums

Rank Records In The Order They Are Read From The Table

sql2008r2

#21

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.


#22

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


#23

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