March 8, 2007 at 1:41 pm
Hello all,
I have built the following query which works fine..
SELECT Sb.Creationuserid,
Sb.Creationusername,
Sb.Batchname,
Sb.Creationstationid,
Sb.Batchclass,
Ss.Externalbatchid,
Ss.Documentscreated,
Ss.Documentsdeleted,
Ss.Pagesscanned,
Ss.Pagesdeleted,
Ss.Acreleaseddocs,
Ss.Deleted,
Ss.Batchstatusname,
Ss.Lastsessiondatetime,
Ss.Firstsessiondatetime,
Ss.Mvsreleaseddocs,
Ss.Mvsindexeddocs,
Ss.Mvsstoreddocs
FROM Ascentcapture.Dbo.Statsbatch AS Sb
INNER JOIN
(SELECT Sbm.Externalbatchid,
Max (Sbm.Enddatetime) AS Lastsessiondatetime,
Min (Sbm.Enddatetime) AS Firstsessiondatetime,
Sum (Sbm.Documentscreated) AS Documentscreated,
Sum (Case When (Sbm.ModuleName = 'Scan') Then
(Sbm.Documentsdeleted)
Else
(Null)
End) AS Documentsdeleted,
Sum (Sbm.Pagesscanned) AS Pagesscanned,
Sum (Case When (Sbm.ModuleName = 'Scan') Then
(Sbm.Pagesdeleted)
Else
(Null)
End)AS Pagesdeleted,
Sum (CASE WHEN (Sbm.Modulename = 'Release') THEN
(Sft.Completeddocs)
ELSE
(NULL)
END) AS Acreleaseddocs,
Sum (Case When (Sbm.ModuleName <> 'Scan') Then
Sbm.DocumentsDeleted
Else
(NULL)
End )AS Deleted,
Max (CASE (Cast (Sbm.Batchstatus AS Varchar(15)))
WHEN ('64') THEN ('Completed')
WHEN ('32') THEN ('Error')
WHEN ('4') THEN ('In Progress')
WHEN ('512') THEN ('In Progress')
WHEN ('2') THEN ('Ready')
WHEN ('128') THEN ('Reserved')
WHEN ('8') THEN ('Suspended')
ELSE (Cast (Sbm.Batchstatus AS Varchar(15)))
END) AS Batchstatusname,
Max (Sr.Mvsreleaserecon) AS Mvsreleaseddocs,
Max (Sr.Mvsindexrecon) AS Mvsindexeddocs,
Max (Sr.Mvsstorerecon) AS Mvsstoreddocs
FROM Ascentcapture.Dbo.Statsbatchmodule AS Sbm
INNER JOIN
Ascentcapture.Dbo.Statsformtype AS Sft ON Sbm.Batchmoduleid = Sft.Batchmoduleid
AND Formtypename NOT IN ('None', 'Loose Pages')
LEFT OUTER JOIN
(SELECT X.Batchid,
Max (CASE
WHEN (X.Tbl = 1) THEN (X.Countbatchid)
ELSE (NULL)
END) AS Mvsreleaserecon,
Max (CASE
WHEN (X.Tbl = 2) THEN (X.Countbatchid)
ELSE (NULL)
END) AS Mvsindexrecon,
Max (CASE
WHEN (X.Tbl = 3) THEN (X.Countbatchid)
ELSE (NULL)
END) AS Mvsstorerecon
FROM ( SELECT 1 AS Tbl,
Convert (Bigint,
Rr.Batchid) AS Batchid,
Count (CASE
WHEN (Rr.Statuscode = 0) THEN
(Rr.Batchid)
ELSE
(NULL)
END) AS Countbatchid
FROM Mvsconnect.Dbo.Mvsreleaserecon AS Rr
WHERE Isnumeric (Rr.Batchid) = 1
GROUP BY Convert (Bigint,
Rr.Batchid)
UNION ALL
SELECT 2,
Convert (Bigint,
Ir.Batchid),
Count (Ir.Traceinfo2)
FROM Mvsconnect.Dbo.Mvsindexrecon AS Ir
WHERE Rtrim (Ir.Traceinfo2) = 'MVSINDEX SUCCESSFUL'
AND Isnumeric (Ir.Batchid) = 1
GROUP BY Convert (Bigint,
Ir.Batchid)
UNION ALL
SELECT 3,
Convert (Bigint,
Sr.Batchid),
Count (Sr.Traceinfo2)
FROM Mvsconnect.Dbo.Mvsstorerecon AS Sr
WHERE Rtrim (Sr.Traceinfo2) = 'MVSSTORE SUCCESSFUL'
AND Isnumeric (Sr.Batchid) = 1
GROUP BY Convert (Bigint,
Sr.Batchid)) AS X
GROUP BY X.Batchid) AS Sr ON Sbm.Externalbatchid = Sr.Batchid
GROUP BY Sbm.Externalbatchid) AS Ss ON Sb.Externalbatchid = Ss.Externalbatchid
However, I need to change this section:
Max (CASE (Cast (Sbm.Batchstatus AS Varchar(15)))
WHEN ('64') THEN ('Completed')
WHEN ('32') THEN ('Error')
WHEN ('4') THEN ('In Progress')
WHEN ('512') THEN ('In Progress')
WHEN ('2') THEN ('Ready')
WHEN ('128') THEN ('Reserved')
WHEN ('8') THEN ('Suspended')
ELSE (Cast (Sbm.Batchstatus AS Varchar(15)))
END) AS Batchstatusname,
The issue is that each row has various status's (ExternalBatchID being the unique identifier), but I want to grab the status based on the latest ENDDATETIME. In other words, if at 3/8/2007 8:00:00AM the status was 'In Progress', but at 12:12:00 it was 'Suspended' I want to show 'Suspended' as the Status
I'm already grabbing the last EndDateTime (with Max (Sbm.Enddatetime) AS Lastsessiondatetime) but I'm having a problem with the status as the statement is currently constructed.
Any help would be appreciated....think I'll grab more coffee... 😉
March 8, 2007 at 3:51 pm
There is too much detail to weed out. Let's say I have an incident table with an action.
create table incident (incidentid int)
create table action (actionid int identity(1,1) , incidentid int, status char(1),
createdate datetime default(getdate())
)
I want to find the current status for the incident.
Select a.incidentid, b.MaxCreateDate , b.Status
from incident a
left join
(select b1.incidentid, b2.MaxCreateDate, b1.Status
from action b1
inner join
(select incidentID, max(CreateDate) as MaxCreateDate
from action
group by incidentid
) b2
on b1.incidentid = b2.incidentid
and b1.createdate = b2.maxcreatedate
) b
on a.incidentid = b.incidentid
Russel Loski, MCSE Business Intelligence, Data Platform
March 9, 2007 at 6:46 am
This is really hard to debug without actual data and table structure, and I can't guarantee this will work, but try replacing the Max( Case (Cast(Sbm.Enddatetime).... with the following...
CASE
WHEN Sbm.Enddatetime = MAX( Sbm.Enddatetime ) THEN
CASE (Cast (Sbm.Batchstatus AS Varchar(15)))
WHEN ('64') THEN ('Completed')
WHEN ('32') THEN ('Error')
WHEN ('4') THEN ('In Progress')
WHEN ('512') THEN ('In Progress')
WHEN ('2') THEN ('Ready')
WHEN ('128') THEN ('Reserved')
WHEN ('8') THEN ('Suspended')
ELSE (Cast (Sbm.Batchstatus AS Varchar(15)))
END
ELSE ''
END AS Batchstatusname
March 9, 2007 at 7:24 am
Never mind my previous post. I created some makeshift tables to try to piece together a good solution for you, and I think this will give you what you want. It's not very pretty, but I'm pretty sure this one will work... replace this...
Max( CASE (Cast (Sbm.Batchstatus AS Varchar(15)))
WHEN ('64') THEN ('Completed')
WHEN ('32') THEN ('Error')
WHEN ('4') THEN ('In Progress')
WHEN ('512') THEN ('In Progress')
WHEN ('2') THEN ('Ready')
WHEN ('128') THEN ('Reserved')
WHEN ('8') THEN ('Suspended')
ELSE (Cast (Sbm.Batchstatus AS Varchar(15)))
END
)
with this...
, CASE ( CAST(
( SELECT Sbm2.Batchstatus
FROM Ascentcapture.Dbo.Statsbatchmodule Sbm2
WHERE Sbm2.Id = Sbm.Id
AND Sbm2.Enddatetime =
( SELECT Max( Sbm3.Enddatetime )
FROM Ascentcapture.Dbo.Statsbatchmodule Sbm3
WHERE Sbm3.Id = Sbm.Id
)
) AS VARCHAR( 15 ) )
)
WHEN ('64') THEN ('Completed')
WHEN ('32') THEN ('Error')
WHEN ('4') THEN ('In Progress')
WHEN ('512') THEN ('In Progress')
WHEN ('2') THEN ('Ready')
WHEN ('128') THEN ('Reserved')
WHEN ('8') THEN ('Suspended')
ELSE (Cast(
( SELECT Sbm2.Batchstatus
FROM Ascentcapture.Dbo.Statsbatchmodule Sbm2
WHERE Sbm2.Id = Sbm.Id
AND Sbm2.Enddatetime =
( SELECT Max( Sbm3.Enddatetime )
FROM Ascentcapture.Dbo.Statsbatchmodule Sbm3
WHERE Sbm3.Id = Sbm.Id
)
) AS VARCHAR( 15 ) )
)
END AS Batchstatusname
Hope this helps you. All the subqueries make it somewhat inefficient, but I believe it'll give you what you want.
March 9, 2007 at 9:45 am
Thank you very much, that was a huge help and I have it working as desired.
Thank you all, it is very much appreciated...
Rich
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply