July 2, 2010 at 8:24 am
Hi,
I have a write a query. where I need, to utilize the Case Status field and see within the time period of the query(month) when a case status goes from Closed to Active for particular caseID
CASE ID DATESTATUS
ABC 2-JunACTIVE
DSA 4-JunACTIVE
EDD4-Jun CLOSED
QDK3-MayACTIVE
ASD2-MayCLOSED
ABC4-Jun CLOSED
DAJ4-Feb ACTIVE
FKD5-Feb CLOSED
ABC10-JunACTIVE
Here in the above table, Case ID ABC has 3 records in the month of june which started with active(2/june) and
was closed on (4/june) and again was made active on (10/june), I have to get the count of the case ID of cases which goes from closed to active just using a SQL Query(No Stored Procs)
Thanks in advance.
July 2, 2010 at 8:52 am
To help you get some responces I formated some table code for you. Couple question for you though. What whould the output you are looking for be? also is the date really stored as in yoru example? what is the data type of that field? In my sample data I changed the date a little to make it a true date time that can be queried properly.
declare @temp (CaseID varchar(3),Dt Datetime,StatusCd varchar(6))
insert into @temp (CaseID,Dt,StatusCd) values('ABC','06/02/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('DSA','06/04/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('EDD','06/04/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('QDK','06/03/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('ASD','06/02/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('ABC','06/04/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('DAJ','06/04/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('FKD','06/05/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('ABC','06/10/10','ACTIVE')
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 2, 2010 at 9:01 am
Dan.Humphries (7/2/2010)
To help you get some responces I formated some table code for you. Couple question for you though. What whould the output you are looking for be? also is the date really stored as in yoru example? what is the data type of that field? In my sample data I changed the date a little to make it a true date time that can be queried properly.
declare @temp (CaseID varchar(3),Dt Datetime,StatusCd varchar(6))insert into @temp (CaseID,Dt,StatusCd) values('ABC','06/02/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('DSA','06/04/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('EDD','06/04/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('QDK','06/03/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('ASD','06/02/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('ABC','06/04/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('DAJ','06/04/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('FKD','06/05/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('ABC','06/10/10','ACTIVE')
Thanks Dan,
I am looking for the count (Case ID) for the cases which have gone from closed to active status status.
Yes, There is a Date field with data type (date(02-JUL-2010)).
July 2, 2010 at 9:43 am
If all you need is a count then the following should work for you. I am not sure this is necessarily the best possible code but I beleive it would do the job.
declare @temp table (CaseID varchar(3),Dt Datetime,StatusCd varchar(6))
insert into @temp (CaseID,Dt,StatusCd) values('ABC','06/02/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('DSA','06/04/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('EDD','06/04/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('QDK','06/03/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('ASD','06/02/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('ABC','06/04/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('DAJ','06/04/10','ACTIVE')
insert into @temp (CaseID,Dt,StatusCd) values('FKD','06/05/10','CLOSED')
insert into @temp (CaseID,Dt,StatusCd) values('ABC','06/10/10','ACTIVE')
Declare @closedtmp table (ordrnum int,CaseID varchar(3),Dt Datetime)
insert into @closedtmp
Select ROW_NUMBER() OVER(Partition BY CaseID ORDER BY Dt desc) as ordrnum
,CaseID,Dt
from @temp
where StatusCd='CLOSED'
Select count(t.CaseID) from @temp t
join @closedtmp c on c.CaseID=t.CaseID and c.Dt>t.Dt
where t.StatusCd='ACTIVE'
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 2, 2010 at 9:54 am
this is a little better code it uses a CTE instead of the table variable.
create table #temp (CaseID varchar(3),Dt Datetime,StatusCd varchar(6))
insert into #temp (CaseID,Dt,StatusCd) values('ABC','06/02/10','ACTIVE')
insert into #temp (CaseID,Dt,StatusCd) values('DSA','06/04/10','ACTIVE')
insert into #temp (CaseID,Dt,StatusCd) values('EDD','06/04/10','CLOSED')
insert into #temp (CaseID,Dt,StatusCd) values('QDK','06/03/10','ACTIVE')
insert into #temp (CaseID,Dt,StatusCd) values('ASD','06/02/10','CLOSED')
insert into #temp (CaseID,Dt,StatusCd) values('ABC','06/04/10','CLOSED')
insert into #temp (CaseID,Dt,StatusCd) values('DAJ','06/04/10','ACTIVE')
insert into #temp (CaseID,Dt,StatusCd) values('FKD','06/05/10','CLOSED')
insert into #temp (CaseID,Dt,StatusCd) values('ABC','06/10/10','ACTIVE');
with closedtmp (ordrnum,CaseID,Dt) As
(
Select ROW_NUMBER() OVER(Partition BY CaseID ORDER BY Dt desc) as ordrnum
,CaseID,Dt
from #temp
where StatusCd='CLOSED'
)
Select count(t.CaseID) from #temp t
join closedtmp c on c.CaseID=t.CaseID and c.Dt>t.Dt
where t.StatusCd='ACTIVE'
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 2, 2010 at 10:03 am
Thanks a lot Dan,
appreciate your help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply