December 14, 2006 at 8:39 am
Hi,
I have a SQL statement:
SELECT Response_Code, Name, Count(Alert_Id) as 'Total_Alerts',
CASE Alert.Response_Code
WHEN 'RIR' THEN 1
WHEN 'PRR' THEN 2
WHEN 'FFP' THEN 3
WHEN 'UNK' THEN 4
ELSE 5
END as 'SOrder'
FROM Alert WHERE
(Create_Date Between @StartDate And @EndDate )
Group By Response_Code, Name
ORDER BY SOrder, Name
This is what the output looks like:
RIR Cameron Co. 1
RIR Fairfax County 1
RIR Middleborough 3
RIR MissionC 16
RIR Richardson 1
PRR Baltimore City 4
PRR Cameron Co. 5
PRR Middleborough 12
PRR Fairfax County 1
PRR Frederick County 3
FFP Middleborough 2
FFP MECA - IMC 3
FFP Baltimore City 4
I would like the Response_Code to only show up the first occurrence in the list so it is not listed multiple times. I would also like the Name to only be listed once for the first Response_Code it is under. i.e. Middleborough would only be listed once under RIR and Baltimore City would only be listed once under PRR etc. Any help in the SQL statement to acomplish this would be appreciated.
This is what it would look like as needed:
RIR Cameron Co. 1
Fairfax County 1
Middleborough 3
MissionC 16
Richardson 1
PRR Baltimore City 4
Frederick County 3
FFP MECA - IMC 3
Thanks
DT
December 14, 2006 at 8:44 am
Can we assume it's SQL 2000 ? SQL 2005 provides common table expressions (CTE) which would be very useful for doing this without a temp table, but need to know if we can rule out a CTE as a solution ?
December 14, 2006 at 8:48 am
Sorry, this is using SQL 2000, we have not upgraded to 2005 and probably won't for a while.
Thanks
DT
December 14, 2006 at 9:09 am
Something like this maybe ? Will get pretty ugly if there are more than 5 SOrder buckets.
SELECT Response_Code, Name, Count(Alert_Id) as 'Total_Alerts', 1 As SOrder
From Alert
WHERE (Create_Date Between @StartDate And @EndDate )
And Response_Code = 'RIR'
Group By Response_Code, Name
Union All
SELECT Response_Code, Name, Count(Alert_Id) as 'Total_Alerts', 2 As SOrder
From Alert a1
WHERE (Create_Date Between @StartDate And @EndDate )
And Response_Code = 'PRR'
And Not Exists (
Select *
From Alert As a2
Where a1.Name = a2.Name
And a2.Response_Code = 'RIR'
)
Group By Response_Code, Name
Union All
SELECT Response_Code, Name, Count(Alert_Id) as 'Total_Alerts', 3 As SOrder
From Alert a1
WHERE (Create_Date Between @StartDate And @EndDate )
And Response_Code = 'FFP'
And Not Exists (
Select *
From Alert As a2
Where a1.Name = a2.Name
And a2.Response_Code In ( 'RIR', 'PRR' )
)
Group By Response_Code, Name
etc
etc
Order By SOrder, Name
December 14, 2006 at 11:01 am
I am still working on it. It seems to work but I had to include the criteria in the Not Exixts statement as below:
SELECT Response_Code, Site_Id, Count(Alert_Id) as 'Total_Alerts', 1 As SOrder
From Alert
WHERE (Create_Date Between '8/1/05' AND '8/11/05' )AND Acknowledged = 0
And Response_Code = 'RIR'
Group By Response_Code, Site_Id
Union All
SELECT Response_Code, Site_Id, Count(Alert_Id) as 'Total_Alerts', 2 As SOrder
From Alert a1
WHERE (Create_Date Between '8/1/05' AND '8/11/05' )AND Acknowledged = 0
And Response_Code = 'PRR'
And Not Exists (
Select *
From Alert As a2
Where a1.Site_Id = a2.Site_Id
AND (Create_Date Between '8/1/05' AND '8/11/05' )AND Acknowledged = 0
And a2.Response_Code = 'RIR'
)
Group By Response_Code, Site_Id
This seems to show the results needed. Now I will work on replacing the rest of the SQL statement using all the criteria. Thanks alot for your help, this pointed me in the right direction.
DT
December 15, 2006 at 4:25 am
It shouldn't be that bad... Try this:
/*test data*/
create table #alert (Alert_id int identity, response_code varchar(10), [name] varchar(50), create_date datetime, acknowledged bit)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('RIR','Cameron Co.','20050805',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('RIR','Fairfax Co.','20050806',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('RIR','Middleborough','20050802',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('RIR','Middleborough','20050803',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('RIR','Middleborough','20050810',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('RIR','Richardson','20050805',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('PRR','Baltimore City','20050809',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('PRR','Baltimore City','20050811',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('PRR','Baltimore City','20050804',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('PRR','Baltimore City','20050802',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('PRR','Baltimore City','20050820',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('PRR','Middleborough','20050804',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('PRR','Middleborough','20050808',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('PRR','Fairfax Co.','20050804',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('FFP','Baltimore City','20050805',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('FFP','Baltimore City','20050809',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('FFP','MECA - IMC','20050809',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('FFP','MECA - IMC','20050802',0)
INSERT INTO #alert (response_code, [name], create_date, acknowledged) VALUES ('FFP','MECA - IMC','20050818',0)
declare @startdate datetime, @enddate datetime
set @startdate = '20050801'
set @enddate = '20050811'
/*this is the query you need - just remove the # and it should work on your DB*/
SELECT a.Response_Code, a.[Name], Count(a.Alert_Id) as 'Total_Alerts', Q.SOrder
FROM #alert a
JOIN
(SELECT a1.[Name] as areaname,
MIN(CASE a1.Response_Code
WHEN 'RIR' THEN 1
WHEN 'PRR' THEN 2
WHEN 'FFP' THEN 3
WHEN 'UNK' THEN 4
ELSE 5
END) as SOrder
FROM #Alert a1
WHERE (a1.Create_Date Between @StartDate And @EndDate ) AND a1.Acknowledged = 0
GROUP BY a1.[name]) as Q
ON Q.areaname = a.[Name]
WHERE (CASE a.Response_Code
WHEN 'RIR' THEN 1
WHEN 'PRR' THEN 2
WHEN 'FFP' THEN 3
WHEN 'UNK' THEN 4
ELSE 5
END) = SOrder
AND (a.Create_Date Between @StartDate And @EndDate ) AND a.Acknowledged = 0
GROUP BY a.Response_Code, a.[Name],Q.SOrder
First you find out the highest priority case for each "name" (in the inner part - derived table Q), then disregard all other rows except those that have this priority. This way you get all the data you need, without any UNIONs, in one query.
I may have misunderstood something, so if this does not work, let us know and post some actual data as well - I created some random rows, but maybe this isn't enough to test it.
BTW, it could be even better to store the info about priorities in a table (with columns response_code, priority) instead of writing a CASE each time you need it. Then the query would be even simpler - and absolutely independent on the number of codes and priority levels.
December 15, 2006 at 8:24 am
Vladan,
This works great also. Its nice to see more than one solution to a problem. I usually would have the Response_Code, priority in a table, and may add it and just do a join. This would alow additions without having to re-code. This SQL is used on a dynamic web page so I do both the SQL and web development for the web site. Thanks everyone for all the help!!!
Happy Holidays
DT
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply