?
Solved

Tracking and counting records as they move through statuses per day

Posted on 2012-08-13
7
Medium Priority
?
316 Views
Last Modified: 2012-08-21
Hello,

     I need some help creating a report in SSRS that has a running total of records as they change status at the end of each day for a date range.  I am able to create reports with running totals by day if the data is incremental, but i experience an issue when a record changes status (i.e. from In progress to Failed and back to in progress) how do I subtract from the first running total and add to the second. I have 10 status I need to create this report from and there isn't a current logic of if in status A it came from status C.  Is there a way to create a variable per status to check against that stores?  This has been driving me up the wall!

Here is the query to get the data that I need a running total from:

select  convert(date,dateEvent) as date,
(case when a.rsltstate in (28) then idrecord end) as OnHold,
(case when a.rsltstate in (57) then idrecord end) as Blocked,
(case when a.rsltstate in (23) then idrecord end) as NotStarted,
(case when a.rsltstate in (24) then idrecord end) as InProgress,
(case when a.rsltstate in (25) then idrecord end) as Passed,
(case when a.rsltstate in (26) then idrecord end) as Failed,
(case when a.rsltstate in (27) then idrecord end) as UnClear,
(case when a.rsltstate in (29) then idrecord end) as ReRun,
(case when a.rsltstate in (52) then idrecord end) as InvaildTest,
(case when a.rsltstate in (58) then idrecord end) as ConditionalPass

from trevts a where
OrderNum = (select MAX(ordernum) from TREVTS b
where CONVERT(date,dateadd(hh, -7,b.dateevent)) = CONVERT(date,dateadd(hh, -7,a.dateevent)) and RsltState !=0)
and a.parentid in (select idrecord from TESTRUN )

here is the data of the individual parentids per day:  

Data from Query showing parentid per day in each status
Here is the chart I am trying to produce with a running count of records per day
running total chart of parent ids per day




Thanks!

Chris
0
Comment
Question by:chrisfig
  • 3
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 38290957
I don't understand the question. Can you give some sample data from the trevts table, and explain what output you need from that sample data and the logic you used to get that data.
0
 

Author Comment

by:chrisfig
ID: 38292606
Of course!  

Here is some sample data:

 TREVTS sample data
What I am trying to achieve from this table is a running count of parentid's per rsltstate per day.  I am only concerned with what rsltstate the parentid was in at the end of each day so I select the max(ordernum) to get the last entry of that day for the parentid.  My issues comes in when a parentid moves from one rsltstate to another. I don't know how to keep track of that change in the running totals by adding to the new state running total and subtracting from the old states running total.  

For example.  In the sample data  ParentID 11 goes into rsltstate 25 on 12/9/2011, which adds one to the running total of 25.  On 12/12 parent ID 11 goes into rsltstate 29, which I need to add one to the running total of 29 and subtract one from 25.

example of change needed
First time posting, so probably typing too much. ha.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38293105
How many total rows are you running this against?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:chrisfig
ID: 38293134
In my test DB I am running this against 122 rows.  In the production DB I will be I will be running this against 2849 rows, up to 5000, in the TREVTS table.

Thanks

Chris
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 38293284
I think I understand now. You want to show how much orders are in a given state on a particular day. In your sample on 12/12 there are 3 orders, 2 passed, 1 rerun. Because on 12/9 there were 3 passed it's probably because one of those went from Passed to ReRun. So instead of using some kind of running total you could create a query that finds that data for a particular day. You can start with a query that returns all the dates you want, and join that with a query that returns the number of orders in a particular state on that day (even if the last status update was a few days ago). It will be quite a complicated query. I don't have time to create an example today or tomorrow. I'll get back to you on Thursday.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 38299404
If you are not familiar with the ROW_NUMBER function read this article first:
http://msdn.microsoft.com/en-us/library/ms186734.aspx

When you run this code
--create table with sample data
set nocount on
create table #trevts (parentid int, ordernum int, dateEvent date, RsltState int)
insert into #trevts values(3,1,'20111201',24)
insert into #trevts values(3,2,'20111201',25)
insert into #trevts values(3,3,'20111201',0)
insert into #trevts values(5,1,'20111201',0)
insert into #trevts values(5,2,'20111201',24)
insert into #trevts values(5,3,'20111201',25)
insert into #trevts values(5,4,'20111201',0)
insert into #trevts values(11,1,'20111209',24)
insert into #trevts values(11,2,'20111209',25)
insert into #trevts values(11,3,'20111209',0)
insert into #trevts values(11,4,'20111209',24)
insert into #trevts values(11,5,'20111209',25)
insert into #trevts values(11,6,'20111209',24)
insert into #trevts values(11,7,'20111209',24)
insert into #trevts values(11,8,'20111209',24)
insert into #trevts values(11,9,'20111209',24)
insert into #trevts values(11,10,'20111209',25)
insert into #trevts values(11,11,'20111209',25)
insert into #trevts values(11,12,'20111209',25)
insert into #trevts values(11,13,'20111209',25)
insert into #trevts values(11,14,'20111212',29)

--actual query
select * from #trevts

--drop temp table
drop table #trevts

Open in new window

You'll see we have test data that matches your previous post. You need to replace the 'select * from #trevts' part in the '--actual query' section with the queries below to see how I created the final query:
Lets start with finding the max order id's using the row number function. When you run this query:
select
            dateEvent,
            parentid,
            RsltState,
            ordernum,
            ROW_NUMBER() OVER (PARTITION BY dateEvent, parentid ORDER BY ordernum desc) as rownum
        from
            #trevts
        where
            RsltState <> 0

Open in new window

You'll see that the highest order numbers get rownum 1. So this query:
select
    dateEvent,
    parentid,
    RsltState
from
    (
        select
            dateEvent,
            parentid,
            RsltState,
            ROW_NUMBER() OVER (PARTITION BY dateEvent, parentid ORDER BY ordernum desc) as rownum
        from
            #trevts
        where
            RsltState <> 0
    ) lastorder
where
    rownum = 1

Open in new window

Returns the dates, parentids and statuses we need for our input. For a given date we need the last status a parentid is in. So we use the rownum function again to set the rownum to 1 for the last status. like this:
select
    dateEvent,
    parentid,
    RsltState,
    ROW_NUMBER() OVER (PARTITION BY parentid ORDER BY dateEvent desc) as rownum
from
    (
        select
            dateEvent,
            parentid,
            RsltState,
            ROW_NUMBER() OVER (PARTITION BY dateEvent, parentid ORDER BY ordernum desc) as rownum
        from
            #trevts
        where
            RsltState <> 0
    ) lastorder
where
    rownum = 1

Open in new window

Now we can use this to get the last statuses for any given date like this:
select
    dateEvent,
    parentid,
    RsltState
from
    (
    select
        dateEvent,
        parentid,
        RsltState,
        ROW_NUMBER() OVER (PARTITION BY parentid ORDER BY dateEvent desc) as rownum
    from
        (
            select
                dateEvent,
                parentid,
                RsltState,
                ROW_NUMBER() OVER (PARTITION BY dateEvent, parentid ORDER BY ordernum desc) as rownum
            from
                #trevts
            where
                RsltState <> 0
        ) lastorder
    where
        rownum = 1
        and dateEvent<='20111212'
    ) as laststatus
where
    rownum = 1

Open in new window

When you start changing the date value in this query you'll see you'll get the status the various parenids are in on that given date. Now we can use this as a subquery that we join with the unique dates to get the result set like this:
select 
    dates.dateEvent,
    (
    select
        COUNT(*)
    from
        (
        select
            dateEvent,
            parentid,
            RsltState,
            ROW_NUMBER() OVER (PARTITION BY parentid ORDER BY dateEvent desc) as rownum
        from
            (
                select
                    dateEvent,
                    parentid,
                    RsltState,
                    ROW_NUMBER() OVER (PARTITION BY dateEvent, parentid ORDER BY ordernum desc) as rownum
                from
                    #trevts
                where
                    RsltState <> 0
            ) lastorder
        where
            lastorder.rownum = 1
            and dateEvent<=dates.dateEvent
        ) as laststatus
    where
        laststatus.rownum = 1
        and laststatus.RsltState = 25
     ) as Passed,
     (
    select
        COUNT(*)
    from
        (
        select
            dateEvent,
            parentid,
            RsltState,
            ROW_NUMBER() OVER (PARTITION BY parentid ORDER BY dateEvent desc) as rownum
        from
            (
                select
                    dateEvent,
                    parentid,
                    RsltState,
                    ROW_NUMBER() OVER (PARTITION BY dateEvent, parentid ORDER BY ordernum desc) as rownum
                from
                    #trevts
                where
                    RsltState <> 0
            ) lastorder
        where
            lastorder.rownum = 1
            and dateEvent<=dates.dateEvent
        ) as laststatus
    where
        laststatus.rownum = 1
        and laststatus.RsltState = 29
     ) as ReRun    
from 
    (select distinct dateEvent from #trevts where RsltState <> 0) as dates

Open in new window

0
 

Author Closing Comment

by:chrisfig
ID: 38316463
Thanks so much, works perfectly!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question