Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 'Select' beyond my experience - SOLVED! Thanks Jim

Author  Topic 

Ogriv
Starting Member

40 Posts
Posted - 2009-07-07 : 20:09:06
Greetings,
I've been teaching myself SQL for the past 2 months, and I've been very successful with the standard Select statements. I've even made a huge leap this week and actually wrote several nested statements. Unfortunately, I have painted myself into a corner.

Here is my current Query:


SELECT b.dep_visitid, 
       b.dep_account, 
       c.dis_discharge1time, 
       b.dep_departtime, 
       b.dep_eventid, 
       c.dis_eventid, 
       Datediff(mi,Dateadd(s,c.dis_discharge1time,'03/01/1980 00:00:00'),Dateadd(s,b.dep_departtime,'03/01/1980 00:00:00')) AS minutes 
FROM   (SELECT dbo.admvisits.accountnumber              AS dep_account, 
               dbo.admvitalsigns.visitid                AS dep_visitid, 
               dbo.admvitalsigns.arrivaldatetime, 
               dbo.edmpatientstatuseventhistory.eventid AS dep_eventid, 
               dbo.edmpatientstatuseventhistory.startrw AS dep_departtime 
        FROM   dbo.admvisits 
               LEFT JOIN dbo.admvitalsigns 
                 ON dbo.admvisits.sourceid = dbo.admvitalsigns.sourceid 
                    AND dbo.admvisits.visitid = dbo.admvitalsigns.visitid 
               LEFT JOIN dbo.edmpatientstatuseventhistory 
                 ON dbo.admvisits.sourceid = dbo.edmpatientstatuseventhistory.sourceid 
                    AND dbo.admvisits.visitid = dbo.edmpatientstatuseventhistory.visitid 
        WHERE  dbo.admvitalsigns.arrivaldatetime >= '6/30/2009 00:00:00' 
               AND dbo.admvitalsigns.arrivaldatetime <= '6/30/2009 23:59:59' 
               AND dbo.edmpatientstatuseventhistory.eventid = 'DEPARTED' 
               AND dbo.admvisits.facilityid = '.') AS b, 
       (SELECT dbo.admvisits.accountnumber, 
               dbo.admvitalsigns.visitid                AS dis_visitid, 
               dbo.edmpatientstatuseventhistory.eventid AS dis_eventid, 
               dbo.edmpatientstatuseventhistory.startrw AS dis_discharge1time 
        FROM   dbo.admvisits 
               LEFT JOIN dbo.admvitalsigns 
                 ON dbo.admvisits.sourceid = dbo.admvitalsigns.sourceid 
                    AND dbo.admvisits.visitid = dbo.admvitalsigns.visitid 
               LEFT JOIN dbo.edmpatientstatuseventhistory 
                 ON dbo.admvisits.sourceid = dbo.edmpatientstatuseventhistory.sourceid 
                    AND dbo.admvisits.visitid = dbo.edmpatientstatuseventhistory.visitid 
        WHERE  dbo.edmpatientstatuseventhistory.eventid = 'DISCHARGE2' 
               AND dbo.admvisits.facilityid = '.') AS c 
WHERE  b.dep_visitid = c.dis_visitid



I am scanning EdmPatientStatusEventHistory.EventID for 'DEPARTED'
then I scan the same table and field for 'DISCHARGE2'. I am then
selecting only those records that have the matching VisitID's.

In the end, I only need the derived column of 'Minutes' but I am including all of the other fields just for troubleshooting. This query works, but what I have found is that a record with the same 'VisitID' can have multiple entries of 'DEPARTED' and 'DISCHARGE2'

7 Columns and 5 Rows Returned from the above query:

DEP_VisitID  DEP_Account  DIS_Discharge1Time  DEP_DepartTime  DEP_EventID  DIS_EventID  Minutes
9872622643    03009392        925608331        925608957	DEPARTED      DISCHARGE2	   10
9872622885    03009579 	      925643431	       925650901	DEPARTED      DISCHARGE2	   125
9872622885    03009579	      925643431          925650918	DEPARTED      DISCHARGE2	   126
9872623060    03009737	      925650824	       925651344	DEPARTED      DISCHARGE2	   9
9872623115    03009778	      925659622	       925660103	DEPARTED      DISCHARGE2	   8


As you can see, Rows 2 and 3 contain a duplicate DEP_VisitID. I need to modify this query to only return rows 1,3,4,5 and skip 2

I've tried using the 'Select max()' statement to choose the record with the highest number in the 'DIS_Discharge1time' and the record with the highest number in the 'DEP_DepartTime' but I can't seem to get it to work.

After previewing this message it appears I have lost all of my formatting and it is hard to read. If I can clarify anything at all, please ask. I will happily accept any comments and suggestions.

Many Thanks,

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-07-08 : 01:56:45
Here comes the formatted code!
Have a click on "reply with quote" to see the tags for posting formatted code.

Fred

SELECT b.dep_visitid, 
       b.dep_account, 
       c.dis_discharge1time, 
       b.dep_departtime, 
       b.dep_eventid, 
       c.dis_eventid, 
       Datediff(mi,Dateadd(s,c.dis_discharge1time,'03/01/1980 00:00:00'), 
                Dateadd(s,b.dep_departtime,'03/01/1980 00:00:00')) AS minutes 
FROM   (SELECT dbo.admvisits.accountnumber              AS dep_account, 
               dbo.admvitalsigns.visitid                AS dep_visitid, 
               dbo.admvitalsigns.arrivaldatetime, 
               dbo.edmpatientstatuseventhistory.eventid AS dep_eventid, 
               dbo.edmpatientstatuseventhistory.startrw AS dep_departtime 
        FROM   dbo.admvisits 
               LEFT JOIN dbo.admvitalsigns 
                 ON dbo.admvisits.sourceid = dbo.admvitalsigns.sourceid 
                    AND dbo.admvisits.visitid = dbo.admvitalsigns.visitid 
               LEFT JOIN dbo.edmpatientstatuseventhistory 
                 ON dbo.admvisits.sourceid = dbo.edmpatientstatuseventhistory.sourceid 
                    AND dbo.admvisits.visitid = dbo.edmpatientstatuseventhistory.visitid 
        WHERE  dbo.admvitalsigns.arrivaldatetime >= '6/30/2009 00:00:00' 
               AND dbo.admvitalsigns.arrivaldatetime <= '6/30/2009 23:59:59' 
               AND dbo.edmpatientstatuseventhistory.eventid = 'DEPARTED' 
               AND dbo.admvisits.facilityid = '.') AS b, 
       (SELECT dbo.admvisits.accountnumber, 
               dbo.admvitalsigns.visitid                AS dis_visitid, 
               dbo.edmpatientstatuseventhistory.eventid AS dis_eventid, 
               dbo.edmpatientstatuseventhistory.startrw AS dis_discharge1time 
        FROM   dbo.admvisits 
               LEFT JOIN dbo.admvitalsigns 
                 ON dbo.admvisits.sourceid = dbo.admvitalsigns.sourceid 
                    AND dbo.admvisits.visitid = dbo.admvitalsigns.visitid 
               LEFT JOIN dbo.edmpatientstatuseventhistory 
                 ON dbo.admvisits.sourceid = dbo.edmpatientstatuseventhistory.sourceid 
                    AND dbo.admvisits.visitid = dbo.edmpatientstatuseventhistory.visitid 
        WHERE  dbo.edmpatientstatuseventhistory.eventid = 'DISCHARGE2' 
               AND dbo.admvisits.facilityid = '.') AS c 
WHERE  b.dep_visitid = c.dis_visitid


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Ogriv
Starting Member

40 Posts
Posted - 2009-07-08 : 06:07:45
Wow, that indeed looks much better! Thanks Webfred, Much appreciated! I'll be sure to use the [code] tag in future postings.
Go to Top of Page

Ogriv
Starting Member

40 Posts
Posted - 2009-07-09 : 07:55:43
Perhaps what I'm trying to do is not possible in one query?
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-07-09 : 08:19:00
This may get you started:

        SELECT visits.accountnumber              AS dep_account, 
               vital.visitid                AS dep_visitid, 
               vital.arrivaldatetime, 
               edm.eventid AS dep_eventid, 
               edm.startrw AS dep_departtime 
        FROM   dbo.admvisits visits
               LEFT JOIN dbo.admvitalsigns vital  ON
                        visits.sourceid = vital.sourceid 
                    AND visits.visitid  = vital.visitid 
               LEFT JOIN dbo.edmpatientstatuseventhistory edm   ON 
                        visits.sourceid = dbo.edm.sourceid 
                    AND visits.visitid  = edm.visitid 
        WHERE  (vital.arrivaldatetime >= '6/30/2009 00:00:00' 
               AND vital.arrivaldatetime <= '6/30/2009 23:59:59' 
               AND edm.eventid = 'DEPARTED' 
               AND visits.facilityid = '.')
              OR
               (
                     dbo.edm.eventid = 'DISCHARGE2' 
                 AND visits.facilityid = '.'
                )


What are you trying to calculate with this:
Datediff(mi,Dateadd(s,c.dis_discharge1time,'03/01/1980 00:00:00'),Dateadd(s,b.dep_departtime,'03/01/1980 00:00:00')) AS minutes


Jim
Go to Top of Page

Ogriv
Starting Member

40 Posts
Posted - 2009-07-09 : 23:06:28
Greetings Jim,

So you eliminated the nested query all together by using an 'OR'. I never would have thought of that.

Our system stores a Date/Time stamp as Seconds from MIDNIGHT on MARCH 1, 1980. I need to know the length of time between any particular VisitID's 'DEPARTED'event and the 'DISCHARGE2' event. Each 'VisitID' should have a matching pair of these two events. My main issue is that sometimes a 'VisitID' will have two pairs of these events. When that happens, I need to grab the 'DIS_Discharge1Time' with the highest value and also grab the 'DEP_DepartTime' with the highest value. I would then perform the DateDiff function on that set to get the desired result.

I will try your code in the morning and let you know the results.

Your assistance is much appreciated!

Thanks,

Mike
Go to Top of Page

Ogriv
Starting Member

40 Posts
Posted - 2009-07-10 : 09:09:45

Greetings,

Thanks to your code sample, I have learned that you can use an Alias to shorten the table names!



        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               edm.StartRW 
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DISCHARGE2' 
               AND visits.FacilityID = '.'
                )


After including the date requirement in the OR statement, this query works and returns all of the required data. 


Row	AccountNumber	VisitID 	ArrivalDateTime	EventID	  StartRW
1	3009944	      9872623313	6/30/09 16:33	DEPARTED	925668607
2	3009948	      9872623319	6/30/09 16:41	DEPARTED	925674835
3	3009949	      9872623320	6/30/09 16:41	DEPARTED	925672556
4	3009955	      9872623326	6/30/09 16:51	DEPARTED	925686841
5	3009962	      9872623337	6/30/09 17:10	DISCHARGE2	925674929
6	3009962	      9872623337	6/30/09 17:10	DEPARTED	925682319
7	3009962	      9872623337	6/30/09 17:10	DEPARTED	925682326
8	3009967	      9872623343	6/30/09 17:15	DISCHARGE2	925694841
9	3009967	      9872623343	6/30/09 17:15	DEPARTED	925697618
10	3009970	      9872623344	6/30/09 17:16	DEPARTED	925677295

Now I need to figure out how to eliminate any 'VisitID' records that have duplicate 'EventID'
So Rows 6 and 7 are duplicates. I need to ignore row 6 and only return Row 7. I only want to return the record that has the higher 'StartRW' value.

Then I need to pair up the Records with matching 'VisitID' and perform the DateDiff function on 'StartRW'
So Rows 8 and 9 make a pair, because their VisitID fields match and one is DICHARGE2 and the other is 'DEPARTED'.
So the difference between their 'StartRW' fields is 2777
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-07-10 : 09:22:40
[code] SELECT visits.AccountNumber,
vital.VisitID,
vital.ArrivalDateTime,
edm.EventID,
[StartRW] = MAX(edm.StartRW )
FROM AdmVisits visits
LEFT JOIN AdmVitalSigns vital ON
visits.SourceID = vital.SourceID
AND visits.VisitID = vital.VisitID
LEFT JOIN EdmPatientStatusEventHistory edm ON
visits.SourceID = edm.SourceID
AND visits.VisitID = edm.VisitID
WHERE (vital.ArrivalDateTime >= '6/30/2009 00:00:00'
AND vital.ArrivalDateTime <= '6/30/2009 23:59:59'
AND edm.EventID = 'DEPARTED'
AND visits.FacilityID = '.')
OR
(vital.ArrivalDateTime >= '6/30/2009 00:00:00'
AND vital.ArrivalDateTime <= '6/30/2009 23:59:59'
AND edm.EventID = 'DISCHARGE2'
AND visits.FacilityID = '.'
)
GROUP BY
visits.AccountNumber,
vital.VisitID,
vital.ArrivalDateTime,
edm.EventID [/code]


Post some sample data returned in the above result set, and also post what final result set you'd like to see.

Jim
Go to Top of Page

Ogriv
Starting Member

40 Posts
Posted - 2009-07-10 : 10:40:33
Jim that is awesome! I now have results that remove the duplicate and returns the higher of the two.

AccountNumber	VisitID  	ArrivalDateTime	EventID	StartRW		AccountNumber
3009391	       9872622642	6/30/09 0:13	      DEPARTED	925612335		3009391
3009392	       9872622643	6/30/09 0:22	      DEPARTED	925608957		3009392
3009392	       9872622643	6/30/09 0:22	      DISCHARGE2	925608331		3009392
3009393	       9872622644	6/30/09 0:42	      DEPARTED	925612956		3009393
3009394	       9872622647	6/30/09 0:51	      DEPARTED	925612318		3009394



So I need to perform my DateDiff function (using the values in 'StartRW') on any records that have a matching VisitID.
In the above data, I would use rows 2 and 3 because their VisitId matches (9872611643).
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-07-10 : 11:33:42
This is a self-join
DECLARE @Table TABLE (AccountNumber int,VisitID bigint,ArrivalDateTime datetime,EventID varchar(50)
,StartRW bigint)

INSERT INTO @Table
SELECT 3009391, 9872622642,'6/30/09 0:13','DEPARTED', 925612335 UNION ALL
SELECT 3009392, 9872622643,'6/30/09 0:22','DEPARTED', 925608957 UNION ALL
SELECT 3009392, 9872622643,'6/30/09 0:22','DISCHARGE2', 925608331 UNION ALL
SELECT 3009393, 9872622644,'6/30/09 0:42','DEPARTED', 925612956 UNION ALL
SELECT 3009394, 9872622647,'6/30/09 0:51','DEPARTED', 925612318

SELECT t1.AccountNumber,t1.visitid
,t2.StartRW - t1.StartRW
FROM
@table t1 inner join
@table t2 ON t1.visitid = t2.visitid and t1.StartRW < t2.StartRW

Jim
Go to Top of Page

Ogriv
Starting Member

40 Posts
Posted - 2009-07-10 : 12:02:42
OK, you just confused the heck out of me.
It appears that your creating a table on the fly, then inserting the data into the new table.
But, wouldn't I have to use the field names instead of the actual returned value?


INSERT INTO @Table
SELECT visits.AccountNumber, vital.VisitID,vital.ArrivalDateTime,edm.EventID,StartRW UNION ALL


This is over my head again, I'm going to have to research 'self join' to determine what is happening in that code.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-07-10 : 12:55:58
I took the data you provided me and put it into a table variable (much like a temp table) so that I could play with it.

INSERT INTO @Table
SELECT 3009391, 9872622642,'6/30/09 0:13','DEPARTED', 925612335 UNION ALL
SELECT 3009392, 9872622643,'6/30/09 0:22','DEPARTED', 925608957 UNION ALL
SELECT 3009392, 9872622643,'6/30/09 0:22','DISCHARGE2', 925608331 UNION ALL
SELECT 3009393, 9872622644,'6/30/09 0:42','DEPARTED', 925612956 UNION ALL
SELECT 3009394, 9872622647,'6/30/09 0:51','DEPARTED', 925612318

SELECT t1.AccountNumber,t1.visitid
,[Diff] = t2.StartRW - t1.StartRW
FROM
@table t1 inner join
@table t2 ON t1.visitid = t2.visitid and t1.StartRW < t2.StartRW

gave this result:
AccountNumber visitid Diff
3009392 9872622643 626

You can add whatever columns you need. A self-join is just that - a table joined to itself.

Jim
Go to Top of Page

Ogriv
Starting Member

40 Posts
Posted - 2009-07-10 : 13:32:14
So I need to modify my working query to place the results into a temporary table, then perform an inner join on VisitID between the two?

So this is what populates the "temp" table:

DECLARE @Table  TABLE (AccountNumber int,VisitID bigint,ArrivalDateTime datetime,EventID varchar(50),StartRW bigint)

How do I wrap this around my existing query?

(BTW, I used your MAX example from above and was able to modify a different query that had the same duplication issue, but did not require a self join.)

Please excuse all my questions, this is still quite new to me. Although I did get approval from my manager to get SQL training - Hopefully on the 21st and 22nd of this month.

Can you recommend any SQL reference books?
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-07-10 : 14:30:30
The DECLARE creates the table variable and INSERT INTO SELECT ... Populates it. Books On Line, which comes with SQL Server is a great reference. I've learned more about SQL on this forum than anywhere else, though. I haven't read any books on SQL.
Not to completely bake your noodle, but you could this:
 
SELECT t1.AccountNumber,t1.visitid
,[Diff] = t2.StartRW - t1.StartRW
FROM
   (
 

        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               edm.StartRW 
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DISCHARGE2' 
               AND visits.FacilityID = '.'
                )
     ) t1
INNER JOIN 
    (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               edm.StartRW 
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DISCHARGE2' 
               AND visits.FacilityID = '.'
                )
     ) t2

ON

  t1.visitid = t2.visitid and t1.StartRW < t2.StartRW



What'll really bake your noodle is when Peso or Visakh or someone shows you how to do the same thing I did only better, faster and with fewer lines of code!

Jim
Go to Top of Page

Ogriv
Starting Member

40 Posts
Posted - 2009-07-10 : 15:02:13
OK, since you over baked my noodle, I didn't figure that there was any harm in trying that last piece of code.

I think you were testing me to see if I had learned anything. I added the missing pieces and ran the query.

It worked Perfectly!


SELECT t1.AccountNumber,
       t1.VisitID,
       [Diff] = t2.StartRW - t1.StartRW,
       Datediff(mi,Dateadd(s,t1.StartRW,'03/01/1980 00:00:00'),Dateadd(s,t2.StartRW,'03/01/1980 00:00:00')) AS minutes

FROM
   (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DISCHARGE2' 
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t1
INNER JOIN 
    (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DISCHARGE2' 
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t2


ON

  t1.VisitID = t2.VisitID and t1.StartRW < t2.StartRW



The results:

Accountnumber	VisitID	Diff	minutes
3009392	     9872622643	626	10
3009579	     9872622885	7487	125
3009737	     9872623060	520	9
3009778	     9872623115	481	8
3009896	     9872623251	2663	44
3009936	     9872623304	1652	28
3009939	     9872623307	5380	90
3009962	     9872623337	7397	123
3009967	     9872623343	2777	46
3009984	     9872623364	2898	48
3010067	     9872623476	806	13
3010073	     9872623482	6241	104
3010075	     9872623485	1253	21


Jim, Your time has been truly appreciated.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-07-10 : 15:13:22
You're Welcome and good luck!

Jim
Go to Top of Page

Ogriv
Starting Member

40 Posts
Posted - 2009-07-14 : 11:48:33
Ok, so it worked so well, that they have thrown me a curve ball.

Is it possible to create another join (I think that's what I need to do)?

Apparently the value of the edm.EventID can also be 'DISCHARGE1'
So I would need to search for pairs and then be able to determine whether they were DISCHARGE1 or DISCHARGE2

So I tried to just duplicate the code and change the conditional but the query never seems to finish (20 minutes).

Can I just create another 'OR'?

By the way I got the approval for training. I go next week, so perhaps I'll get a better grasp on this.

I tried to do this but it just hangs forever:


SELECT AVG(g.minutes),AVG(h.minutes)
FROM   (
   SELECT t1.AccountNumber,
       t1.VisitID,
       [Diff] = t2.StartRW - t1.StartRW,
       Datediff(mi,Dateadd(s,t1.StartRW,'03/01/1980 00:00:00'),Dateadd(s,t2.StartRW,'03/01/1980 00:00:00')) AS minutes
     FROM
        (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
            AND vital.ArrivalDateTime <= '7/12/2009 23:59:59'
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
            AND vital.ArrivalDateTime <= '7/12/2009 23:59:59'
               AND edm.EventID = 'DISCHARGE1' 
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t1
INNER JOIN 
    (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
            AND vital.ArrivalDateTime <= '7/12/2009 23:59:59'
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
            AND vital.ArrivalDateTime <= '7/12/2009 23:59:59'
               AND edm.EventID = 'DISCHARGE1' 
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t2

ON
  t1.VisitID = t2.VisitID and t1.StartRW < t2.StartRW)

as g,


   (
   SELECT t1.AccountNumber,
       t1.VisitID,
       [Diff] = t2.StartRW - t1.StartRW,
       Datediff(mi,Dateadd(s,t1.StartRW,'03/01/1980 00:00:00'),Dateadd(s,t2.StartRW,'03/01/1980 00:00:00')) AS minutes
     FROM
        (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
            AND vital.ArrivalDateTime <= '7/12/2009 23:59:59'
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
            AND vital.ArrivalDateTime <= '7/12/2009 23:59:59'
               AND edm.EventID = 'DISCHARGE1' 
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t1
INNER JOIN 
    (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
            AND vital.ArrivalDateTime <= '7/12/2009 23:59:59'
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
            AND vital.ArrivalDateTime <= '7/12/2009 23:59:59'
               AND edm.EventID = 'DISCHARGE1' 
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t2

ON
  t1.VisitID = t2.VisitID and t1.StartRW < t2.StartRW)

as h
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-07-14 : 14:11:57
I'm not sure how Discharge1 fits in, but could it just be this?

Jim
SELECT t1.AccountNumber,
       t1.VisitID,
       [Diff] = t2.StartRW - t1.StartRW,
       Datediff(mi,Dateadd(s,t1.StartRW,'03/01/1980 00:00:00'),Dateadd(s,t2.StartRW,'03/01/1980 00:00:00')) AS minutes

FROM
   (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID IN ('DISCHARGE1', 'DISCHARGE2' )
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t1
INNER JOIN 
    (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID IN ('DISCHARGE1','DISCHARGE2')
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t2


ON

  t1.VisitID = t2.VisitID and t1.StartRW < t2.StartRW
Go to Top of Page

Ogriv
Starting Member

40 Posts
Posted - 2009-07-15 : 07:40:48
Hi again Jim!

This works and returns good data. But here is the curve ball.
How do I determine which rows are DISCHARGE1, and which are DISCHARGE2 ? That is why I nested them as g and h. 




AccountNumber   VisitID         Diff   minutes
03009392	9872622643	626	10
03009406	9872622660	2064	34
03009407	9872622661	1023	17
03009413	9872622669	459	8
03009555	9872622860	2080	34
03009566	9872622871	3674	62
03009571	9872622876	454	7
03009574	9872622881	1641	27
03009579	9872622885	7487	125



I can run this query in a few seconds:


SELECT AVG(g.minutes)
FROM   (
SELECT t1.AccountNumber,
       t1.VisitID,
       [Diff] = t2.StartRW - t1.StartRW,
       Datediff(mi,Dateadd(s,t1.StartRW,'03/01/1980 00:00:00'),Dateadd(s,t2.StartRW,'03/01/1980 00:00:00')) AS minutes

FROM
   (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID IN ('DISCHARGE1')
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t1
INNER JOIN 
    (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID IN ('DISCHARGE1')
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t2


ON

  t1.VisitID = t2.VisitID and t1.StartRW < t2.StartRW)
AS g




But it takes forever when I run this query:


SELECT AVG(g.minutes),AVG(h.minutes)
FROM   (
SELECT t1.AccountNumber,
       t1.VisitID,
       [Diff] = t2.StartRW - t1.StartRW,
       Datediff(mi,Dateadd(s,t1.StartRW,'03/01/1980 00:00:00'),Dateadd(s,t2.StartRW,'03/01/1980 00:00:00')) AS minutes

FROM
   (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID IN ('DISCHARGE1')
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t1
INNER JOIN 
    (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID IN ('DISCHARGE1')
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t2


ON

  t1.VisitID = t2.VisitID and t1.StartRW < t2.StartRW)
AS g,

(
SELECT t1.AccountNumber,
       t1.VisitID,
       [Diff] = t2.StartRW - t1.StartRW,
       Datediff(mi,Dateadd(s,t1.StartRW,'03/01/1980 00:00:00'),Dateadd(s,t2.StartRW,'03/01/1980 00:00:00')) AS minutes

FROM
   (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID IN ('DISCHARGE2')
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t1
INNER JOIN 
    (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '7/12/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '7/12/2009 23:59:59' 
               AND edm.EventID IN ('DISCHARGE2')
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t2


ON

  t1.VisitID = t2.VisitID and t1.StartRW < t2.StartRW)
AS h
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2009-07-15 : 08:35:46
Just add the t1.EventID to your select list!

SELECT t1.AccountNumber,
t1.VisitID,t1.EventID [Diff] = t2.StartRW - t1.StartRW,
Datediff(mi,Dateadd(s,t1.StartRW,'03/01/1980 00:00:00'),Dateadd(s,t2.StartRW,'03/01/1980 00:00:00')) AS minutes


Jim
Go to Top of Page

Ogriv
Starting Member

40 Posts
Posted - 2009-07-15 : 10:38:42
Ok, now I get a table that looks like this:

AccountNumber    VisitID        EventID         Diff    minutes
03009392	9872622643	DISCHARGE2	626	10
03009406	9872622660	DISCHARGE1	2064	34
03009407	9872622661	DISCHARGE1	1023	17
03009413	9872622669	DISCHARGE1	459	8
03009555	9872622860	DISCHARGE1	2080	34
03009566	9872622871	DISCHARGE1	3674	62
03009571	9872622876	DISCHARGE1	454	7
03009574	9872622881	DISCHARGE1	1641	27
03009579	9872622885	DISCHARGE2	7487	125


I need seperate averages of all DISCHARGE1 minutes and DISCHARGE2 minutes.
So I made the following modification that will return the DISCHARGE1 average,
but I am unsure how to get the DISCHARGe2 average. Is it possible, or do I
need to nest it again?


SELECT AVG(v.minutes)
FROM
(
SELECT t1.AccountNumber,
       t1.VisitID,
       t1.EventID, 
       [Diff] = t2.StartRW - t1.StartRW,
       Datediff(mi,Dateadd(s,t1.StartRW,'03/01/1980 00:00:00'),Dateadd(s,t2.StartRW,'03/01/1980 00:00:00')) AS minutes

FROM
   (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID IN ('DISCHARGE1', 'DISCHARGE2' )
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t1
INNER JOIN 
    (
        SELECT visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID, 
               [StartRW] = MAX(edm.StartRW )
        FROM   AdmVisits visits
               LEFT JOIN AdmVitalSigns vital  ON
                        visits.SourceID = vital.SourceID 
                    AND visits.VisitID  = vital.VisitID 
               LEFT JOIN EdmPatientStatusEventHistory edm   ON 
                        visits.SourceID = edm.SourceID 
                    AND visits.VisitID  = edm.VisitID 
        WHERE  (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID = 'DEPARTED' 
               AND visits.FacilityID = '.')
              OR
               (vital.ArrivalDateTime >= '6/30/2009 00:00:00' 
               AND vital.ArrivalDateTime <= '6/30/2009 23:59:59' 
               AND edm.EventID IN ('DISCHARGE1','DISCHARGE2')
               AND visits.FacilityID = '.'
                )
       GROUP BY
              visits.AccountNumber,
               vital.VisitID,             
               vital.ArrivalDateTime, 
               edm.EventID
     ) t2


ON

  t1.VisitID = t2.VisitID and t1.StartRW < t2.StartRW )
AS v
WHERE EventID = 'DISCHARGE1'


FYI: You have been very helpful and patient with me. I have learned quite a bit from our conversation. Hopefully after next weeks training I will have a better grasp on things!


Mike
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site