December 13, 2007 at 9:01 am
Hi, I'm trying to write some code to run an extract. I have a table where there is more than one row\entry for each id (customer). See the table example below.
I want to extract one value. This should be the value of bbb from the most recent date, unless this is null, if this is the case then take the most recent mob instead. If this value is also null then take the bbb entry for the second most recent entry (in this case 23/10/06). Again, if this is null then take the entry for mob instead and so on. Therefore in this case I would need to go through 6 iterations until i got to the first non null entry which is the mob entry for the third most recent id. However some customers will have more or less than 3 entrys, so I cant limit the query to looping 3 times.
id date bbb mob
1 31/12/07 null null
1 23/10/06 null null
1 14/11/05 null 0774444444
Thanks, Simon
December 13, 2007 at 10:45 am
SET NOCOUNT ON
DECLARE @T Table(id integer,date datetime,bbb varchar(15),mob varchar(15))
DECLARE @CustomerID int
SET @CustomerID=1
INSERT INTO @T SELECT 1,'20071231',null,null
INSERT INTO @T SELECT 1,'20061023',null,null
INSERT INTO @T SELECT 1,'20051114',null,'0774444444'
SELECT TOP 1 *,COALESCE(bbb,mob) Result FROM @T WHERE ID=@CustomerID AND COALESCE(bbb,mob) IS NOT NULL ORDER By Date Desc
Vasc
December 13, 2007 at 10:54 am
select id, coalesce(bbb, mob) as val
from table
where coalesce(bbb, mob) is not null
and date =
(select max(date)
from table t2
where id = table.id
and coalesce(bbb, mob) is not null)
Will get you a set of all ids and their corresponding values based on the rules given. Depending on the size of the table, the indexes, etc., it might be a bit slow (because of the sub-query), but shouldn't be too bad. I've used code like this more than a few times and it's worked quite well.
(The solution given in the first reply will only return one id's data at a time. This will return all of them that have valid data.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 13, 2007 at 10:57 am
As an additional note on my reply, you'll need to add a "distinct" operator to the select statements if an ID can have more than one entry per day, unless the entries include a distinct time in the "date" field (SQL usually does, unless all dates are being stored as if they were midnight).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2007 at 1:31 am
To avoid correlated subqueries and gain better performance, you might try this:
As a basis, you need to have a query, which finds the correct date for each ID.
SELECT id, MAX(date)
FROM yourtable
WHERE bbb IS NOT NULL OR mob IS NOT NULL
GROUP BY id
You can the use result of this query as if it was a table in another query, like this... I work on the assumption that, for each customer, values in "date" column are unique. If dates are not unique, an additional GROUP BY and MAX with the COALESCE should give you correct results (well, depending on what you consider as "correct result" in a situation where there are two rows with same ID, same date, and different values in bbb column... IMHO if this can happen, you need to specify additional criteria)
SELECT yt.id, COALESCE(yt.bbb, yt.mob)
FROM yourtable yt
JOIN
(SELECT id, MAX(date) as thedate
FROM yourtable
WHERE bbb IS NOT NULL OR mob IS NOT NULL
GROUP BY id) AS Q ON Q.id=yt.id AND Q.thedate=yt.date
P.S. Simon, as can be seen in your description of a problem, you are thinking row-based. This isn't the best way in SQL. It helps a lot to find set-based solution, if you learn to describe the problems from the set-based viewpoint. What I mean is, instead of saying "look at first row, if it doesn't satisfy conditions, try another" you should try to think like this: "For each customer, I have to find a row with highest date of those rows that have some value in either bbb or mob, and get the bbb (or mob, if bbb is null) value".
December 14, 2007 at 3:19 am
Thank you very much for your replies guys, all of them have assisted in this issue and my general t-sql development. Ten Centuries, thanks for the additional tip, you are quite right I do tend to think along the lines of row based as opposed to set based, and need to start thinking that set based for issues like these.
thank-you 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply