May 11, 2010 at 3:52 am
Hi there.
Here is the data, I think it explains itself:
DECLARE @t TABLE
(custID int,
module tinyint,
visit_date smalldatetime,
found_count tinyint, --larger value means better found_count
quality tinyint, --values in (1,2); lesser value means better quality
pressure tinyint --values in (1,2,3); lesser value means better pressure
)
INSERT INTO @t (custID, module, visit_date, found_count, quality, pressure)
SELECT 1, 1, '12.01.2010', 1, 2, 3 UNION --last date values are all better,
SELECT 1, 1, '13.02.2010', 2, 1, 1 UNION --should not be in the resultset
SELECT 1, 2, '01.01.2010', 1, 2, 1 UNION --last pressure is worse
SELECT 1, 2, '05.03.2010', 1, 2, 3 UNION --both records _should_ be in the resultset
SELECT 1, 3, '01.01.2010', 2, 1, 3 UNION --no values entered in last visit
SELECT 1, 3, '01.04.2010', 0, 0, 0 UNION --both records _should_ be in the resultset
SELECT 1, 4, '01.02.2010', 1, 1, 1 UNION --same values in both
SELECT 1, 4, '01.04.2010', 1, 1, 1 --should not be in the resultset
--SELECT * FROM @t
Although there are only 2 records per cust_id and module in the sample data, this is no limit.
What I need are all records of a cust_id and module, where the values of found_count (sorry, had problems translating this), quality or pressure in the most recent record are worse than in the other ones. (this includes records where all 3 fields are zero) To see what means "better" in my case, have a look at the table definition.
Thanks in advance,
Steffen.
May 11, 2010 at 4:17 am
Try something like:
;WITH cte
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY custID, module ORDER BY visit_date DESC) AS RowNum
FROM @t
)
SELECT C.*
FROM cte C
JOIN
(
SELECT custID, module
,MIN(found_count) AS found_count
,MIN(quality) AS quality
,MIN(pressure) AS pressure
FROM cte
WHERE RowNum > 1
GROUP BY custID, module
) D
ON C.custID = D.custID
AND C.module = D.module
WHERE C.RowNum = 1
AND
(
C.found_count < D.found_count
OR C.quality < D.quality
OR C.pressure < D.pressure
)
May 11, 2010 at 4:21 am
hopefully this should do the trick if you are using SQL 2005 or later.
WITH CTE AS (
SELECT custID
, module
, visit_date
, found_count
, quality
, pressure
, ROW_NUMBER() OVER(PARTITION BY custID, module ORDER BY visit_date ASC) AS ChronologicalOrder
FROM @t)
SELECT a.custID
, a.module
, a.visit_date
, a.found_count
, a.quality AS current_quality
, a.pressure AS current_pressure
, b.quality AS previous_quality
, b.pressure AS previous_pressure
, b.visit_date AS previous_visit_date
FROM CTE AS a --any sample reading
JOIN
CTE AS b --the reading before
ON a.custID = b.custID
AND a.module = b.module
AND a.ChronologicalOrder - 1 = b.ChronologicalOrder --enforces chronological relationship
WHERE a.quality > b.quality --quality has got worse
OR a.pressure > b.pressure; --pressure has got worse
May 11, 2010 at 4:22 am
Or maybe:
;WITH cte
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY custID, module ORDER BY visit_date DESC) AS RowNum
FROM @t
)
SELECT T.*
FROM @t T
JOIN
(
SELECT C.*
FROM cte C
JOIN
(
SELECT custID, module
,MIN(found_count) AS found_count
,MIN(quality) AS quality
,MIN(pressure) AS pressure
FROM cte
WHERE RowNum > 1
GROUP BY custID, module
) D
ON C.custID = D.custID
AND C.module = D.module
WHERE C.RowNum = 1
AND
(
C.found_count < D.found_count
OR C.quality < D.quality
OR C.pressure < D.pressure
)
) D1
ON T.custID = D1.custID
AND T.module = D1.module
May 11, 2010 at 4:52 am
Hi Ken,
your second query is exactly what I was looking for.
In my tries I always sorted by date ascending and ran into the problem not to know which row_number() is the last. 🙂
Can I mark the thread as answered in some way?
Thank you very much,
Steffen.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply