March 4, 2015 at 10:26 am
Hi,
Can somebody help with the query below, it works but not very efficient - its little slow. Is there any other way to re-write the query:
------------------------------------------------------------------------------------------------------------------------------
SELECT PERSID, COALESCE(OVD.Overdue, DUE.Due, UNK.Unknown) AS DUESTATUS, COALESCE(INC.Incomplete, NS.NotStarted, UN.Unknown) AS COMPSTATUS
FROM PERSON PERS
LEFT OUTER JOIN
(
SELECT PERSID, DUESTATUS AS Overdue
FROM PERSON
WHERE DUESTATUS = 'Overdue'
GROUP BY PERSID, DUESTATUS
) AS OVD
ON PERS.PERSID = OVD.PERSID
LEFT OUTER JOIN
(
SELECT PERSID, DUESTATUS AS Due
FROM PERSON
WHERE DUESTATUS = 'Due'
GROUP BY PERSID, DUESTATUS
) AS DUE
ON PERS.PERSID = DUE.PERSID
LEFT OUTER JOIN
(
SELECT PERSID, DUESTATUS AS Unknown
FROM PERSON
WHERE DUESTATUS = 'Unknown'
GROUP BY PERSID, DUESTATUS
) AS UNK
ON PERS.PERSID = UNK.PERSID
LEFT OUTER JOIN
(
SELECT PERSID, COMPSTATUS AS Incomplete
FROM PERSON
WHERE COMPSTATUS = 'Incomplete'
GROUP BY PERSID, COMPSTATUS
) AS INC
ON PERS.PERSID = INC.PERSID
LEFT OUTER JOIN
(
SELECT PERSID, COMPSTATUS AS NotStarted
FROM PERSON
WHERE COMPSTATUS = 'Not Started'
GROUP BY PERSID, COMPSTATUS
) AS NS
ON PERS.PERSID = NS.PERSID
LEFT OUTER JOIN
(
SELECT PERSID, COMPSTATUS AS Unknown
FROM PERSON
WHERE COMPSTATUS = 'Unknown'
GROUP BY PERSID, COMPSTATUS
) AS UN
ON PERS.PERSID = UN.PERSID
---------------------------------------------------------------------------------------------------
Please see attachment for sample data and output. Thanks.
March 4, 2015 at 11:12 am
For starters:
SELECT PERSID,
CASE MAX(CASE DUESTATUS
WHEN 'Overdue' THEN 2
WHEN 'DUE' THEN 1
ELSE 0
END)
WHEN 2 THEN 'Overdue'
WHEN 1 THEN 'Due'
ELSE 'Unknown'
END DUESTATUS,
CASE MAX(CASE COMPSTATUS
WHEN 'Incomplete' THEN 2
WHEN 'Not Started' THEN 1
ELSE 0
END)
WHEN 2 THEN 'Incomplete'
WHEN 1 THEN 'Not Started'
ELSE 'Unknown'
END COMPSTATUS
FROM PERSON
GROUP BY PERSID
Since you are only dealing with one table and you are scanning it it will be tough to get it much more efficient than that.
March 4, 2015 at 11:36 am
Looking at the SQL, it appears that PERSON doesn't contain one row per person but rather multiple rows per person for each status change. I think you can leverage row_number() function and rank the result the way you need.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 4, 2015 at 12:37 pm
Nevyn (3/4/2015)
For starters:
SELECT PERSID,
CASE MAX(CASE DUESTATUS
WHEN 'Overdue' THEN 2
WHEN 'DUE' THEN 1
ELSE 0
END)
WHEN 2 THEN 'Overdue'
WHEN 1 THEN 'Due'
ELSE 'Unknown'
END DUESTATUS,
CASE MAX(CASE COMPSTATUS
WHEN 'Incomplete' THEN 2
WHEN 'Not Started' THEN 1
ELSE 0
END)
WHEN 2 THEN 'Incomplete'
WHEN 1 THEN 'Not Started'
ELSE 'Unknown'
END COMPSTATUS
FROM PERSON
GROUP BY PERSID
Since you are only dealing with one table and you are scanning it it will be tough to get it much more efficient than that.
Nice query Nevyn!! Doubly-nested CASE with MAX - I love it! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 4, 2015 at 1:58 pm
TheSQLGuru (3/4/2015)
Nevyn (3/4/2015)
For starters:
SELECT PERSID,
CASE MAX(CASE DUESTATUS
WHEN 'Overdue' THEN 2
WHEN 'DUE' THEN 1
ELSE 0
END)
WHEN 2 THEN 'Overdue'
WHEN 1 THEN 'Due'
ELSE 'Unknown'
END DUESTATUS,
CASE MAX(CASE COMPSTATUS
WHEN 'Incomplete' THEN 2
WHEN 'Not Started' THEN 1
ELSE 0
END)
WHEN 2 THEN 'Incomplete'
WHEN 1 THEN 'Not Started'
ELSE 'Unknown'
END COMPSTATUS
FROM PERSON
GROUP BY PERSID
Since you are only dealing with one table and you are scanning it it will be tough to get it much more efficient than that.
Nice query Nevyn!! Doubly-nested CASE with MAX - I love it! 😎
Ditto that. Very slick. Well done Nevyn!
-- Itzik Ben-Gan 2001
March 4, 2015 at 2:17 pm
Thanks!
Started doing it with a CTE for the max(case) and then it just occurred to me that I really didn't need it to be in a CTE just to apply the outer case.
March 4, 2015 at 2:28 pm
Nevyn (3/4/2015)
Thanks!Started doing it with a CTE for the max(case) and then it just occurred to me that I really didn't need it to be in a CTE just to apply the outer case.
CASE is BY FAR my favorite TSQL word!! SOOOO powerful!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 5, 2015 at 2:54 am
Thanks Nevyn, that's much much better 🙂
March 5, 2015 at 8:13 am
I'm assuming the goal here is the retreive the latest DUESTATUS and COMPSTATUS for each PERSID, and what we have to work with is a table containing one record for each person status update.
The problem with using GROUP BY for this given scenario is that it may not be as logically consistent and reliable as ranking, when the final result should really be derived from a single record, specifically the last inserted. For example, there may be some cirsumstances where the logical processing status reverts back from 'Overdue' to 'Due' and this is performed by the application as an update but rather a new insert.
We don't have the DDL of the actual table, but given something like the following, I'd be more inclined to use DENSE_RANK() or ROW_NUMBER() and then apply a filter to get columns from the latest record for each PERSID.
create table PERSON
(
primary key ( PERSID, STATUSUPDATE ),
PERSID int not null,
STATUSUPDATE datetime not null
DUESTATUS varchar(30) not null,
COMPSTATUS varchar(30) not null
);
select PERSID, DUESTATUS, COMPSTATUS
from (
select PERSID, DUESTATUS, COMPSTATUS
, dense_rank() over
(partition by PERSID
order by STATUSUPDATE desc) as STATUSRANK
from PERSON ) R
where R.STATUSRANK = 1;
I alternate between GROUP BY and DENSE_RANK() methods depending on the circumstances. Depending on how the records are related, sometimes the final result can't be derived from a single record and must be an aggregated using grouping. Sometimes it just comes down to performance; both may give the same result but one method better leverages the existing primary key and indexes on the table.
Try both.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply