June 8, 2012 at 3:14 am
Hi,
is there a general sql query section in these forums?? Wouldn't say I'm a newbie but wouldn't say I'm that great at sql either...anyhoo 🙂
I have this query
SELECT dbSchCnt as id,dbPatCnt as pid,dbSchTypeCnt as tid,dbSchDate as time,dbSchStatus as status
FROM SchDetail
WHERE dbSchStatus = 0 AND dbSchDate > GETDATE() AND dbSchDate < GETDATE() + 7
This will bring back any appointments for patients within the next seven days. I wish it to bring back only the first appointment for each patient. I tried using DISTINCT and TOP(1) in front of dbPatCnt (this is the patient id) but I get a syntax error response.
Sorry I don't have any test data to throw in at the mo.
June 8, 2012 at 3:21 am
Looks like a case for row_number and a CTE, if I have undersood your requirements
DECLARE @SchDetail TABLE (dbSchCnt int identity(1,1), dbPatCnt int, dbSchTypeCnt int, dbSchDate datetime, dbSchStatus int)
INSERT INTO @SchDetail VALUES
(1,1,'2012-06-09',0), --should return
(1,1,'2012-06-10',0),
(1,1,'2012-06-11',0),
(1,1,'2012-06-12',0),
(1,1,'2012-06-13',0),
(1,1,'2012-06-14',0),
(2,1,'2012-06-10',0), --should return
(2,1,'2012-06-11',0),
(2,1,'2012-06-12',0),
(2,1,'2012-06-13',0),
(2,1,'2012-06-14',0),
(3,1,'2012-06-11',0), --should return
(3,1,'2012-06-12',0),
(3,1,'2012-06-13',0),
(3,1,'2012-06-14',0)
;with cte as
(
SELECT
ROW_NUMBER() OVER(PARTITION BY dbPatCnt ORDER BY dbSchDate) AS RowNum,
dbSchCnt as id,
dbPatCnt as pid,
dbSchTypeCnt as tid,
dbSchDate as time,
dbSchStatus as status
FROM
@SchDetail
WHERE
dbSchStatus = 0
AND
dbSchDate > GETDATE()
AND
dbSchDate < GETDATE() + 7
)
SELECT * FROM cte WHERE RowNum = 1
June 8, 2012 at 3:35 am
Hi there,
I'm getting incorrect syntax on line 3 (the first insert line)
cheers
June 8, 2012 at 3:38 am
mattech06 (6/8/2012)
Hi there,I'm getting incorrect syntax on line 3 (the first insert line)
cheers
I guess you're on SQL Server 2005 then.
Change the INSERT statement into this:
INSERT INTO @SchDetail VALUES (1,1,'2012-06-09',0); --should return
INSERT INTO @SchDetail VALUES (1,1,'2012-06-10',0);
INSERT INTO @SchDetail VALUES (1,1,'2012-06-11',0);
INSERT INTO @SchDetail VALUES (1,1,'2012-06-12',0);
INSERT INTO @SchDetail VALUES (1,1,'2012-06-13',0);
INSERT INTO @SchDetail VALUES (1,1,'2012-06-14',0);
INSERT INTO @SchDetail VALUES (2,1,'2012-06-10',0); --should return
INSERT INTO @SchDetail VALUES (2,1,'2012-06-11',0);
INSERT INTO @SchDetail VALUES (2,1,'2012-06-12',0);
INSERT INTO @SchDetail VALUES (2,1,'2012-06-13',0);
INSERT INTO @SchDetail VALUES (2,1,'2012-06-14',0);
INSERT INTO @SchDetail VALUES (3,1,'2012-06-11',0); --should return
INSERT INTO @SchDetail VALUES (3,1,'2012-06-12',0);
INSERT INTO @SchDetail VALUES (3,1,'2012-06-13',0);
INSERT INTO @SchDetail VALUES (3,1,'2012-06-14',0);
-- Gianluca Sartori
June 8, 2012 at 3:49 am
No I'm using 2008.
Anyway, that works guys, so thank you very much!
Just one more question ...what is a 'cte'??
June 8, 2012 at 3:56 am
A CTE is a Common Table Expressrion, it acts like a temporary result set
Here is a link to MSDN http://msdn.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx as they can describe it better.
June 8, 2012 at 4:03 am
thanks for that...it's good learning new stuff!
Is there no 'mark as answer' option for form posts?
June 8, 2012 at 4:04 am
Nope, there isn't a mark as answered on this site, the posts stay open incase someone else comes along with a better idea.
June 8, 2012 at 12:46 pm
mattech06 (6/8/2012)
Hi,is there a general sql query section in these forums?? Wouldn't say I'm a newbie but wouldn't say I'm that great at sql either...anyhoo 🙂
I have this query
SELECT dbSchCnt as id,dbPatCnt as pid,dbSchTypeCnt as tid,dbSchDate as time,dbSchStatus as status
FROM SchDetail
WHERE dbSchStatus = 0 AND dbSchDate > GETDATE() AND dbSchDate < GETDATE() + 7
This will bring back any appointments for patients within the next seven days. I wish it to bring back only the first appointment for each patient. I tried using DISTINCT and TOP(1) in front of dbPatCnt (this is the patient id) but I get a syntax error response.
Sorry I don't have any test data to throw in at the mo.
Simplest approach in my mind is the following:
SELECT A.dbSchCnt as id,A.dbPatCnt as pid,A.dbSchTypeCnt as tid,A.dbSchDate as time,A.dbSchStatus as status
FROM SchDetail A
INNER JOIN (
--subquery gets the first appt per patient, then join it back to get the rest.
SELECT dbPatCnt, MIN(dbSchDate) AS dbSchDate
FROM SchDetail
WHERE dbSchDate BETWEEN GETDATE() AND DATEADD(DAY,7,GETDATE())
GROUP BY dbPatCnt
) B ON A.dbpatcnt=B.dbPatCnt
AND A.dbSchDate = B.dbSchDate
WHERE dbSchStatus = 0
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply