May 7, 2014 at 4:55 pm
Hi everyone..
I got patient_table columns [patnt_no],[patnt_refno] ,[admit_date],[discharge_date] ,[hospital_branch]
i just want retrieve patient information from 1st discharge date to 6 months later date .
patnt_nopatnt_refnoadmit_datedischarge_date
234256232010-05-07 12:00:00.0002010-06-19 18:00:00.000
234235622010-07-21 14:25:00.0002010-09-25 07:55:16.000
234278562010-11-15 00:00:00.0002013-01-01 00:00:00.000
426195672011-03-16 00:00:00.0002011-05-26 12:00:00.000
426175892014-04-15 00:00:00.0002014-05-30 15:00:45.000
768912542012-03-16 13:15:00.0002012-04-28 16:00:00.000
768945212012-07-14 15:00:00.0002012-09-11 18:00:00.000
768926422012-09-15 14:00:00.0002014-04-13 16:00:00.000
562123232012-10-12 13:00:15.0002014-04-23 19:00:00.000
562124232014-04-28 19:00:00.0002014-05-05 16:00:00.000
I want format like this
234235622010-07-21 14:25:00.0002010-09-25 07:55:16.000
768945212012-07-14 15:00:00.0002012-09-11 18:00:00.000
562124232014-04-28 19:00:00.0002014-05-05 16:00:00.000
Thanks in advance..
May 7, 2014 at 10:10 pm
Something along these lines could do
😎
DECLARE @PATIENT TABLE
(
patnt_no INT NOT NULL
,patnt_refno INT NOT NULL
,admit_date DATETIME2(0) NOT NULL
,discharge_date DATETIME2(0) NOT NULL
);
INSERT INTO @PATIENT (patnt_no,patnt_refno,admit_date,discharge_date)
VALUES
(2342,5623,'2010-05-07 12:00:00.000','2010-06-19 18:00:00.000')
,(2342,3562,'2010-07-21 14:25:00.000','2010-09-25 07:55:16.000')
,(2342,7856,'2010-11-15 00:00:00.000','2013-01-01 00:00:00.000')
,(4261,9567,'2011-03-16 00:00:00.000','2011-05-26 12:00:00.000')
,(4261,7589,'2014-04-15 00:00:00.000','2014-05-30 15:00:45.000')
,(7689,1254,'2012-03-16 13:15:00.000','2012-04-28 16:00:00.000')
,(7689,4521,'2012-07-14 15:00:00.000','2012-09-11 18:00:00.000')
,(7689,2642,'2012-09-15 14:00:00.000','2014-04-13 16:00:00.000')
,(5621,2323,'2012-10-12 13:00:15.000','2014-04-23 19:00:00.000')
,(5621,2423,'2014-04-28 19:00:00.000','2014-05-05 16:00:00.000');
;WITH EARLIEST_DISCHARGE_DATE AS
(
SELECT
DATEADD(MONTH,6,MIN(discharge_date)) AS EDC_DATE
FROM @PATIENT
)
SELECT
PT.patnt_no
,PT.patnt_refno
,PT.admit_date
,PT.discharge_date
FROM @PATIENT PT
CROSS APPLY EARLIEST_DISCHARGE_DATE EDC
WHERE PT.discharge_date <= EDC.EDC_DATE;
Results
patnt_no patnt_refno admit_date discharge_date
----------- ----------- --------------------------- ---------------------------
2342 5623 2010-05-07 12:00:00 2010-06-19 18:00:00
2342 3562 2010-07-21 14:25:00 2010-09-25 07:55:16
Or do you mean per patient
DECLARE @PATIENT TABLE
(
patnt_no INT NOT NULL
,patnt_refno INT NOT NULL
,admit_date DATETIME2(0) NOT NULL
,discharge_date DATETIME2(0) NOT NULL
);
INSERT INTO @PATIENT (patnt_no,patnt_refno,admit_date,discharge_date)
VALUES
(2342,5623,'2010-05-07 12:00:00.000','2010-06-19 18:00:00.000')
,(2342,3562,'2010-07-21 14:25:00.000','2010-09-25 07:55:16.000')
,(2342,7856,'2010-11-15 00:00:00.000','2013-01-01 00:00:00.000')
,(4261,9567,'2011-03-16 00:00:00.000','2011-05-26 12:00:00.000')
,(4261,7589,'2014-04-15 00:00:00.000','2014-05-30 15:00:45.000')
,(7689,1254,'2012-03-16 13:15:00.000','2012-04-28 16:00:00.000')
,(7689,4521,'2012-07-14 15:00:00.000','2012-09-11 18:00:00.000')
,(7689,2642,'2012-09-15 14:00:00.000','2014-04-13 16:00:00.000')
,(5621,2323,'2012-10-12 13:00:15.000','2014-04-23 19:00:00.000')
,(5621,2423,'2014-04-28 19:00:00.000','2014-05-05 16:00:00.000');
;WITH EARLIEST_DISCHARGE_DATE AS
(
SELECT
patnt_no
,DATEADD(MONTH,6,MIN(discharge_date)) AS EDC_DATE
FROM @PATIENT
GROUP BY patnt_no
)
SELECT
PT.patnt_no
,PT.patnt_refno
,PT.admit_date
,PT.discharge_date
FROM @PATIENT PT
INNER JOIN EARLIEST_DISCHARGE_DATE EDC
ON PT.patnt_no = EDC.patnt_no
WHERE PT.discharge_date <= EDC.EDC_DATE;
Results
patnt_no patnt_refno admit_date discharge_date
----------- ----------- --------------------------- ---------------------------
2342 5623 2010-05-07 12:00:00 2010-06-19 18:00:00
2342 3562 2010-07-21 14:25:00 2010-09-25 07:55:16
4261 9567 2011-03-16 00:00:00 2011-05-26 12:00:00
5621 2323 2012-10-12 13:00:15 2014-04-23 19:00:00
5621 2423 2014-04-28 19:00:00 2014-05-05 16:00:00
7689 1254 2012-03-16 13:15:00 2012-04-28 16:00:00
7689 4521 2012-07-14 15:00:00 2012-09-11 18:00:00
May 7, 2014 at 11:05 pm
vasugunda (5/7/2014)
Hi everyone..i just want retrieve patient information from 1st discharge date to 6 months later date .
Can you please elaborate with more clarity as I am confused with above statement and your resultset.Also please let us know what you have tried so far so that we can easily help you.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
May 7, 2014 at 11:41 pm
rhythmk (5/7/2014)
vasugunda (5/7/2014)
Hi everyone..i just want retrieve patient information from 1st discharge date to 6 months later date .
Can you please elaborate with more clarity as I am confused with above statement and your resultset.Also please let us know what you have tried so far so that we can easily help you.
Hi rhythmk,
sometimes it is simpler to respond with a suggestion which could technically be correct, but isn't necessarily so. The feedback is often closer to an accurate description, this can cut few steps in the requirements trawling:-D
😎
May 7, 2014 at 11:52 pm
Eirikur Eiriksson (5/7/2014)
rhythmk (5/7/2014)
vasugunda (5/7/2014)
Hi everyone..i just want retrieve patient information from 1st discharge date to 6 months later date .
Can you please elaborate with more clarity as I am confused with above statement and your resultset.Also please let us know what you have tried so far so that we can easily help you.
Hi rhythmk,
sometimes it is simpler to respond with a suggestion which could technically be correct, but isn't necessarily so. The feedback is often closer to an accurate description, this can cut few steps in the requirements trawling:-D
😎
Hi Eirikur Eiriksson,
I think it is always better to understand the requirement instead of guessing and hitting in the dark . 😉
Even you have provided two solutions because of that. However if you match none of your resultset matches with OP's resultset 🙂
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
May 8, 2014 at 12:42 am
rhythmk (5/7/2014)
However if you match none of your resultset matches with OP's resultset 🙂
Neither does it match the information contained within the requirements.
😎
May 8, 2014 at 1:02 am
vasugunda (5/7/2014)
i just want retrieve patient information from 1st discharge date to 6 months later date.
Do you mean: retrieve for each patient, identified by the patno, all records which have the discharge date greater than the earliest discharge date, for the period of the next 6 months after the earliest discharge date?
😎
DECLARE @PATIENT TABLE
(
patnt_no INT NOT NULL
,patnt_refno INT NOT NULL
,admit_date DATETIME2(0) NOT NULL
,discharge_date DATETIME2(0) NOT NULL
);
INSERT INTO @PATIENT (patnt_no,patnt_refno,admit_date,discharge_date)
VALUES
(2342,5623,'2010-05-07 12:00:00.000','2010-06-19 18:00:00.000')
,(2342,3562,'2010-07-21 14:25:00.000','2010-09-25 07:55:16.000')
,(2342,7856,'2010-11-15 00:00:00.000','2013-01-01 00:00:00.000')
,(4261,9567,'2011-03-16 00:00:00.000','2011-05-26 12:00:00.000')
,(4261,7589,'2014-04-15 00:00:00.000','2014-05-30 15:00:45.000')
,(7689,1254,'2012-03-16 13:15:00.000','2012-04-28 16:00:00.000')
,(7689,4521,'2012-07-14 15:00:00.000','2012-09-11 18:00:00.000')
,(7689,2642,'2012-09-15 14:00:00.000','2014-04-13 16:00:00.000')
,(5621,2323,'2012-10-12 13:00:15.000','2014-04-23 19:00:00.000')
,(5621,2423,'2014-04-28 19:00:00.000','2014-05-05 16:00:00.000');
;WITH EARLIEST_DISCHARGE_DATE AS
(
SELECT
patnt_no
,MIN(discharge_date) AS EDC_MIN
,DATEADD(MONTH,6,MIN(discharge_date)) AS EDC_DATE
FROM @PATIENT
GROUP BY patnt_no
)
SELECT
PT.patnt_no
,PT.patnt_refno
,PT.admit_date
,PT.discharge_date
FROM @PATIENT PT
INNER JOIN EARLIEST_DISCHARGE_DATE EDC
ON PT.patnt_no = EDC.patnt_no
WHERE PT.discharge_date <= EDC.EDC_DATE
AND PT.discharge_date > EDC.EDC_MIN;
Results
patnt_no patnt_refno admit_date discharge_date
----------- ----------- --------------------------- ---------------------------
2342 3562 2010-07-21 14:25:00 2010-09-25 07:55:16
5621 2423 2014-04-28 19:00:00 2014-05-05 16:00:00
7689 4521 2012-07-14 15:00:00 2012-09-11 18:00:00
May 8, 2014 at 1:54 am
This is similar to another post so I post the same solution here
;WITH cte (patnt_no,patnt_refno,admit_date,discharge_date,min_discharge_date) AS (
SELECT patnt_no,patnt_refno,admit_date,discharge_date,
MIN(discharge_date) OVER (PARTITION BY patnt_no)
FROM patient_table)
SELECT patnt_no,patnt_refno,admit_date,discharge_date
FROM cte
WHERE discharge_date <= DATEADD(month,+6,min_discharge_date)
Far away is close at hand in the images of elsewhere.
Anon.
May 11, 2014 at 2:29 am
Hello sir
first of all Thanks for your reply
The main requirement is :
listing all the patnts who discharged and visited the doctor within the six months
for example
patnt_no patnt_refno admit_date discharge_date
----------- ----------- --------------------------- ---------------------------
2342 5623 2010-05-07 12:00:00 2010-06-19 18:00:00
2342 3562 2010-07-21 14:25:00 2010-09-25 07:55:16
and also the second requirement is :
if the same patnt visits after the two years i need those results to be appear in the table
for example
patnt_no patnt_refno admit_date discharge_date
----------- ----------- --------------------------- ---------------------------
2342 58823 2012-05-07 12:00:00 2012-06-19 18:00:00
2342 355562 2012-07-21 14:25:00 2012-09-25 07:55:16
now the result should be
patnt_no patnt_refno admit_date discharge_date
----------- ----------- --------------------------- ---- -----------------------
2342 5623 2010-05-07 12:00:00 2010-06-19 18:00:00
2342 3562 2010-07-21 14:25:00 2010-09-25 07:55:16
2342 58823 2012-05-07 12:00:00 2012-06-19 18:00:00
2342 355562 2012-07-21 14:25:00 2012-09-25 07:55:16
note :
if the patnt discharge in 2008-05-12 and dont have the subsequent readmit in the first six month
then this not included in the records
only the patnts who readmitted in the first six months should be included
Thanks for any reply in advance
May 11, 2014 at 3:06 am
cte (patnt_no,patnt_refno,admit_date,discharge_date,min_discharge_date) AS (
SELECT patnt_no,patnt_refno,admit_date,discharge_date,
MIN(discharge_date) OVER (PARTITION BY patnt_no)
FROM patient_table),
cte2 (patnt_no,patnt_refno,admit_date,discharge_date) AS (
SELECT patnt_no,patnt_refno,admit_date,discharge_date
FROM cte
WHERE admit_date <= DATEADD(month,6,min_discharge_date))
SELECT patnt_no,patnt_refno,admit_date,discharge_date
FROM cte2
UNION ALL
SELECT patnt_no,patnt_refno,admit_date,discharge_date
FROM cte
WHERE admit_date <= DATEADD(Year,2,min_discharge_date)
AND patnt_no IN (SELECT a.patnt_no FROM cte2 a)
Note that all the rows selected for the 6 month data will be repeated in the 2 year data
Far away is close at hand in the images of elsewhere.
Anon.
May 11, 2014 at 3:13 am
Not sure if that was what you wanted but this might be
;WITH cte (patnt_no,patnt_refno,admit_date,discharge_date,min_discharge_date) AS (
SELECT patnt_no,patnt_refno,admit_date,discharge_date,
MIN(discharge_date) OVER (PARTITION BY patnt_no)
FROM patient_table)
SELECT patnt_no,patnt_refno,admit_date,discharge_date
FROM cte
WHERE admit_date <= DATEADD(month,6,min_discharge_date)
OR (admit_date <= DATEADD(Year,2,min_discharge_date)
AND EXISTS (SELECT a.* FROM cte a WHERE a.admit_date <= DATEADD(month,6,a.min_discharge_date)))
Far away is close at hand in the images of elsewhere.
Anon.
May 11, 2014 at 4:03 am
Thanks for the Reply
it s having the duplicates sir
May 11, 2014 at 7:25 am
Eirikur Eiriksson (5/7/2014)
rhythmk (5/7/2014)
vasugunda (5/7/2014)
Hi everyone..i just want retrieve patient information from 1st discharge date to 6 months later date .
Can you please elaborate with more clarity as I am confused with above statement and your resultset.Also please let us know what you have tried so far so that we can easily help you.
Hi rhythmk,
sometimes it is simpler to respond with a suggestion which could technically be correct, but isn't necessarily so. The feedback is often closer to an accurate description, this can cut few steps in the requirements trawling:-D
😎
I hate to disagree with you, but if I don't understand the requirements I would rather ask questions to clarify what is needed rather than spend my time guessing at what the OP is after. This is especially true if I have to make the effort to setup the test environment to do the work as well. The more effort that an OP puts into their question, provides readily consumable DDL, sample data, and expected results the more effort I can apply to helping solve a problem than just making a shot in the dark that may or may not help.
May 11, 2014 at 7:34 am
Lynn Pettis (5/11/2014)
Eirikur Eiriksson (5/7/2014)
rhythmk (5/7/2014)
vasugunda (5/7/2014)
Hi everyone..i just want retrieve patient information from 1st discharge date to 6 months later date .
Can you please elaborate with more clarity as I am confused with above statement and your resultset.Also please let us know what you have tried so far so that we can easily help you.
Hi rhythmk,
sometimes it is simpler to respond with a suggestion which could technically be correct, but isn't necessarily so. The feedback is often closer to an accurate description, this can cut few steps in the requirements trawling:-D
😎
I hate to disagree with you, but if I don't understand the requirements I would rather ask questions to clarify what is needed rather than spend my time guessing at what the OP is after. This is especially true if I have to make the effort to setup the test environment to do the work as well. The more effort that an OP puts into their question, provides readily consumable DDL, sample data, and expected results the more effort I can apply to helping solve a problem than just making a shot in the dark that may or may not help.
Generally I have to agree with you but in this case (and other elsewhere) you can ask continually for info but just get more and more befuddled. It is OK when OP gives you consistent data and results but sometimes a few queries prods them in the right direction and that can help. How many times have you on a thread asked for (and others) info and get little or no help. Yes, you can say OK I won't bother, that is your right, but it does not help the OP who could be a complete novice and wants to avoid the overbearing criticism that this site sometimes portrays.
Far away is close at hand in the images of elsewhere.
Anon.
May 11, 2014 at 8:20 am
David Burrows (5/11/2014)
Lynn Pettis (5/11/2014)
Eirikur Eiriksson (5/7/2014)
rhythmk (5/7/2014)
vasugunda (5/7/2014)
Hi everyone..i just want retrieve patient information from 1st discharge date to 6 months later date .
Can you please elaborate with more clarity as I am confused with above statement and your resultset.Also please let us know what you have tried so far so that we can easily help you.
Hi rhythmk,
sometimes it is simpler to respond with a suggestion which could technically be correct, but isn't necessarily so. The feedback is often closer to an accurate description, this can cut few steps in the requirements trawling:-D
😎
I hate to disagree with you, but if I don't understand the requirements I would rather ask questions to clarify what is needed rather than spend my time guessing at what the OP is after. This is especially true if I have to make the effort to setup the test environment to do the work as well. The more effort that an OP puts into their question, provides readily consumable DDL, sample data, and expected results the more effort I can apply to helping solve a problem than just making a shot in the dark that may or may not help.
Generally I have to agree with you but in this case (and other elsewhere) you can ask continually for info but just get more and more befuddled. It is OK when OP gives you consistent data and results but sometimes a few queries prods them in the right direction and that can help. How many times have you on a thread asked for (and others) info and get little or no help. Yes, you can say OK I won't bother, that is your right, but it does not help the OP who could be a complete novice and wants to avoid the overbearing criticism that this site sometimes portrays.
And I have been there giving shots in the dark, but usually because I managed to have somewhat of an understanding of the requirements. All I was saying that it isn't always best to throw out code in the hopes that you provide a usable answer. Sometimes you need to ask questions first. It also helps to get the OP to think through their problem a bit more before just giving them code.
Plus, I have also been there when I have given code and all you get back from the OP is "Nope, that didn't work." Real helpful response when effort was made to try and help.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply