chrisfig
asked on
Tracking and counting records as they move through statuses per day
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:
Here is the chart I am trying to produce with a running count of records per day
Thanks!
Chris
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:
Here is the chart I am trying to produce with a running count of records per day
Thanks!
Chris
Nico Bontenbal
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.
ASKER
Of course!
Here is some 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.
First time posting, so probably typing too much. ha.
Here is some 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.
First time posting, so probably typing too much. ha.
How many total rows are you running this against?
ASKER
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
Thanks
Chris
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much, works perfectly!