Creating a balance sheet - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating a balance sheet

Author

Topic

singularity
Posting Yak Master

153 Posts

Posted - 2008-01-16 : 08:59:02

I currently have a table that looks like this:

Case_ID Update_Date Status UserID
123 12/12/2007 2:30:07 PM Pending Denial abc1234
123 12/14/2007 2:31:44 PM Pending Approval def4321
123 12/15/2007 3:15:16 PM Approved def4321
456 12/16/2007 9:45:32 AM Pending Denial ghi5678
456 12/17/2007 1:11:02 PM Denied jkl8765
456 12/18/2007 3:43:23 PM Pending Approval jkl8765
456 12/19/2007 8:00:22 AM Approved mno9999
789 12/20/2007 9:19:09 AM Pending Approval pqr7777
789 12/20/2007 2:12:55 PM Approved pqr7777

I need to create a day-by-day "balance sheet" with the following statuses:

- New: The first day that a case shows up on the table.
- Pending: A case would show as Pending if it's *final* status for that day was either Pending Approval or Pending Denial.
- Completed: A case would show as Completed if it's *final* status for that day was either Approved or Denied.
- Re-Opened: A case would show as Re-Opened if it's status at the end of the previous day was Approved or Denied and then got changed to Pending Approval or Pending Denial on the current day.
- Previously Pended: A case would show as Previously Pended on the current day if it ended the previous day in a Pending Denial or Pending Approval status.

Except for the New status, changes that happen within a day don't count. Only the final status for each day matters.

Thus, the desired result for the data set above would look like this:

Date Type Case_ID User_ID
12/12/07 New 123 abc1234
12/12/07 Pending 123 abc1234
12/13/07 Previously Pended 123 abc1234
12/13/07 Pending 123 abc1234
12/14/07 Previously Pended 123 abc1234
12/14/07 Pending 123 def4321
12/15/07 Previously Pended 123 def4321
12/15/07 Completed 123 def4321
12/16/07 New 456 ghi5678
12/17/07 Previously Pended 456 ghi5678
12/17/07 Completed 456 jkl8765
12/18/07 Re-Opened 456 jkl8765
12/19/07 Previoulsy Pended 456 jkl8765
12/19/07 Completed 456 mno9999
12/20/07 New 789 pqr7777
12/20/07 Completed 789 pqr7777

This should balance the following way:

New + Re-Opened - Completed = Pending

OK, hopefully this wasn't too confusing. Do you guys have any suggestions on the best way to do this?

singularity
Posting Yak Master

153 Posts

Posted - 2008-01-16 : 14:55:39

OK, I was able to work most of it out, but I'm struggling with the pends. Here's what I'm trying to do:

I have data that looks like this:

Case_ID Date Status
123 12/12/07 Pended
123 12/13/07 Completed
123 12/15/07 Pended
123 12/20/07 Completed
456 12/28/07 Pended
456 12/29/07 Completed

I need to create a row for every day a case spent in pending status, like so:

Date Status Case_ID
12/12/07 Pended 123
12/15/07 Pended 123
12/16/07 Pended 123
12/17/07 Pended 123
12/18/07 Pended 123
12/19/07 Pended 123
12/28/07 Pended 456

Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-16 : 22:00:09

[code]DECLARE @sample TABLE
(
Case_ID int,
[Date] datetime,
Status varchar(10)
)
INSERT INTO @sample
SELECT 123, '12/12/07', 'Pended' UNION ALL
SELECT 123, '12/13/07', 'Completed' UNION ALL
SELECT 123, '12/15/07', 'Pended' UNION ALL
SELECT 123, '12/20/07', 'Completed' UNION ALL
SELECT 456, '12/28/07', 'Pended' UNION ALL
SELECT 456, '12/29/07', 'Completed'

SELECT s.Case_ID, d.DATE, s.PStatus
FROM F_TABLE_DATE('2007-12-01', '2007-12-31') d
INNER JOIN
(
SELECT p.Case_ID,
PDate = p.Date, PStatus = p.Status, PRowNo = p.row_no,
CDate = c.Date, CStatus = c.Status, CRowNo = c.row_no
FROM
(
SELECT p.Case_ID, p.Date, p.Status,
row_no = (SELECT COUNT(*) FROM @sample x WHERE x.Case_ID = p.Case_ID AND x.Date <= p.Date AND x.Status = p.Status)
FROM @sample p
WHERE Status = 'Pended'
) p
INNER JOIN
(
SELECT c.Case_ID, c.Date, c.Status,
row_no = (SELECT COUNT(*) FROM @sample x WHERE x.Case_ID = c.Case_ID AND x.Date <= c.Date AND x.Status = c.Status)
FROM @sample c
WHERE Status = 'Completed'
) c ON p.Case_ID = c.Case_ID
AND p.row_no = c.row_no
) s
ON d.DATE >= s.PDate
AND d.DATE < s.CDate

/*
Case_ID DATE PStatus
----------- ------------------------------------------------------ ----------
123 2007-12-12 00:00:00.000 Pended
123 2007-12-15 00:00:00.000 Pended
123 2007-12-16 00:00:00.000 Pended
123 2007-12-17 00:00:00.000 Pended
123 2007-12-18 00:00:00.000 Pended
123 2007-12-19 00:00:00.000 Pended
456 2007-12-28 00:00:00.000 Pended

(7 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

singularity
Posting Yak Master

153 Posts

Posted - 2008-02-15 : 15:30:20

Thanks for your help, but there is an issue we're having.

I took khtan's code and modified it to below:

SELECT distinct DATEADD(d,d.n,pdate) as Date, 'Pending' as Type, s.Case_ID, s.Extension_ID, s.upd_user_id, s.upd_user_name
FROM (select n1+n2+n3+n4+n5+n6+n7+n8+n9 as n
from (select 0 n1 union select 1) n1
cross join (select 0 n2 union select 2) n2
cross join (select 0 n3 union select 4) n3
cross join (select 0 n4 union select 8) n4
cross join (select 0 n5 union select 16) n5
cross join (select 0 n6 union select 32) n6
cross join (select 0 n7 union select 64) n7
cross join (select 0 n8 union select 128) n8
cross join (select 0 n9 union select 256) n9) d
INNER JOIN
(
SELECT p.Case_ID,
PDate = CAST(CONVERT(nvarchar(8),p.update_date,112)as datetime), PStatus = p.Status, PRowNo = p.row_no,
CDate = c.Date, CStatus = c.Type, CRowNo = c.row_no, p.upd_user_id, p.upd_user_name, p.extension_id
FROM
(
SELECT p.Case_ID, p.update_date, p.Status, p.extension_id, p.upd_user_id, p.upd_user_name,
row_no = (SELECT COUNT(*) FROM #t x WHERE x.Case_ID = p.Case_ID AND x.update_date <= p.update_date AND x.Status = p.Status)
FROM #t p
WHERE Status IN ('Pending Approval', 'Pending Denial', 'New')
) p
LEFT JOIN
(
SELECT distinct c.Case_ID, c.Date, c.Type,
row_no = (SELECT COUNT(*) FROM #s x WHERE x.Case_ID = c.Case_ID AND x.Date <= c.Date AND x.Type = c.Type)
FROM #s c
WHERE Type = 'Completed'
) c ON p.Case_ID = c.Case_ID
AND p.row_no = c.row_no where
) s
ON d.n < DATEDIFF(d,pdate,(CASE WHEN s.CDate IS NULL THEN getdate() ELSE s.CDate END))

The problem we're having is with cases that were pended and completed mulitple times in the same day.

Here's an example:

Extension Case_ID update_date Status upd_user_id
790427 25537 2007-11-21 11:45:18.713 Pending Approval EXC6872
790427 25537 2007-11-21 11:47:23.743 Complete EXC6872
790427 25537 2007-11-21 11:54:12.147 Pending Approval EXC6872
790427 25537 2007-11-21 11:54:29.320 Complete EXC6872

The results that we're getting using the above query:

Date Type Case_ID Extension_ID Upd_User_ID
2007-11-21 Pending 790427 25537 EXC6872
2007-11-21 Completed 790427 25537 EXC6872

The Pending line should not show up because Pending was not final status for that day. How can we modify the query above so that it only writes a Pending line for days where Pending was the final status?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources