April 21, 2006 at 6:41 pm
I am using sql server 2000
I need some help with the following query
CREATE TABLE TBL
(
col1 SMALLINT,
col2 SMALLINT,
col3 SMALLINT,
DT DATETIME
)
INSERT INTO TBL VALUES (1,2,3, GETDATE())
INSERT INTO TBL VALUES (1,2,3, (GETDATE()-1))
INSERT INTO TBL VALUES (1,2,3, (GETDATE()-2))
INSERT INTO TBL VALUES (1,2,3, (GETDATE()-3))
INSERT INTO TBL VALUES (1,2,5, GETDATE())
INSERT INTO TBL VALUES (1,2,5, (GETDATE()-5))
INSERT INTO TBL VALUES (1,2,6, GETDATE())
SELECT col1, col2, col3, CONVERT(CHAR(10), DT, 101) as RDT FROM TBL
col1 col2 col3 RDT
------ ------- ------ ----------
1 2 3 04/21/2006
1 2 3 04/20/2006
1 2 3 04/19/2006
1 2 3 04/18/2006
1 2 5 04/21/2006
1 2 5 04/16/2006
1 2 6 04/21/2006
I want only those rows whose column i.e col1, col2,3 values are present in 4/21/2006 only if it was not present in the past two days i.e. 4/20 and 4/19
so since 1 2 3 satisfies the above condition it should not be in the result set only
1 2 5 04/16/2006
1 2 6 04/21/2006
Also since there are two rows for
1 2 5 04/21/2006
1 2 5 04/16/2006
I need to get only one row , the one with oldest date
1 2 5 04/16/2006
The final output should be
1 2 5 04/16/2006
1 2 6 04/21/2006
thanks for all your help
April 24, 2006 at 7:04 am
Hello,
I have slightly modified the test data, so that I could ignore the time part and work only with the dates - instead of GETDATE() I entered DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) in DT column of table TBL... that is current date with 00:00:00.000 in time portion. If you need to work with the time, we can modify the query later. What I was trying to achieve now is to write simple query that helps to verify your requirements.
So, my table structure and data is this:
CREATE TABLE TBL (col1 SMALLINT,col2 SMALLINT,col3 SMALLINT,DT DATETIME)
INSERT INTO TBL VALUES (1,2,3, DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))
INSERT INTO TBL VALUES (1,2,3, (DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)-1))
INSERT INTO TBL VALUES (1,2,3, (DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)-2))
INSERT INTO TBL VALUES (1,2,3, (DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)-3))
INSERT INTO TBL VALUES (1,2,5, DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))
INSERT INTO TBL VALUES (1,2,5, (DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)-5))
INSERT INTO TBL VALUES (1,2,6, DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))
INSERT INTO TBL VALUES (1,2,7, DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)-1)
INSERT INTO TBL VALUES (1,2,6, DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)-2)
Here we go - enter whatever date you want to check:
DECLARE @cdate datetime
SET @cdate = DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
SELECT a.col1, a.col2, a.col3, CONVERT(CHAR(10), MIN(a.DT), 101)
FROM TBL a
JOIN TBL b ON a.col1=b.col1 AND a.col2=b.col2 AND a.col3=b.col3 AND b.dt = @cdate /*exists on this date*/
LEFT JOIN TBL c ON a.col1=c.col1 AND a.col2=c.col2 AND a.col3=c.col3 AND c.dt IN (@cdate-1, @cdate-2)
WHERE c.col1 IS NULL /*not exists on any of the 2 previous days*/
GROUP BY a.col1, a.col2, a.col3
Query uses self-joins for elimination of rows that don't match criteria. Table with alias "b" filters out those that don't have any row for the given check-date (@cdate). Table with alias "c" makes sure only those col1-3 combinatÃons that DON'T have any row in the 2 days preceding to @cdate will be returned. I'm not sure whether the condition was "when this column combination does not occur on any of the preceding days" or "when it does not occur on both preceding days". I decided to not display the row if it occurs on ony one of the preceding two days.
GROUP BY and MIN makes sure each combination is only returned once, with the lowest date.
Please test it and post how it worked, along with further explanations if you see that I didn't understand some of the requirements correctly.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply