2

I am using SQL-Server 2008.

I have a query in which I pull in a few columns of information over a date range. I need to pull out the earliest order_number for a specific list of order types. A person can easily get multiple orders for the same thing. I can see the logic in my head but am having difficulty transferring it to the keyboard. First I will give an example result set that I am currently getting back.

Example Result Set:

VISIT ID | MRN | ORDER NUM | ORDER DESC        | ORDER STATUS | ADM TO ORD STS HRS
123456   | 123 | 987654321 | CBC WITH WBC DIFF | ACTIVE       | -4
123456   | 123 | 987654321 | CBC WITH WBC DIFF | IN PROGRESS  | -4
123456   | 123 | 987654321 | CBC WITH WBC DIFF | COMPLETE     | -3
123456   | 123 | 999654321 | CBC WITH WBC DIFF | ACTIVE       | 123
123456   | 123 | 999654321 | CBC WITH WBC DIFF | IN PROGRESS  | 139
123456   | 123 | 999654321 | CBC WITH WBC DIFF | COMPLETE     | 146

I am only concerned with the first order, in this case 987654321 since it occured earliest. So my desired output would be:

Desired Ouput:

VISIT ID | MRN | ORDER NUM | ORDER DESC        | ORDER STATUS | ADM TO ORD STS HRS
123456   | 123 | 987654321 | CBC WITH WBC DIFF | ACTIVE       | -4
123456   | 123 | 987654321 | CBC WITH WBC DIFF | IN PROGRESS  | -4
123456   | 123 | 987654321 | CBC WITH WBC DIFF | COMPLETE     | -3

Here is the SELECT AND FROM clause:

DECLARE @SD DATETIME
DECLARE @ED DATETIME
SET @SD = '2013-01-01';
SET @ED = '2013-07-08';

-- COLUMN SELECTION
SELECT DISTINCT PV.PtNo_Num AS 'VISIT ID'
, PV.Med_Rec_No AS 'MRN'
, PV.vst_start_dtime AS 'ADMIT'
, PV.vst_end_dtime AS 'DISC'
, PV.Days_Stay AS 'LOS'
, PV.pt_type AS 'PT TYPE'
, PV.hosp_svc AS 'HOSP SVC'
, SO.ord_no AS 'ORDER NUMBER'
--, SO.ent_dtime AS 'ORDER ENTRY TIME'
--, DATEDIFF(HOUR,PV.vst_start_dtime,SO.ent_dtime) AS 'ADM TO ENTRY HOURS'
, SO.svc_desc AS 'ORDER DESCRIPTION'
, OSM.ord_sts AS 'ORDER STATUS'
, SOS.prcs_dtime AS 'ORDER STATUS TIME'
, DATEDIFF(HOUR,PV.vst_start_dtime,SOS.prcs_dtime) AS 'ADM TO ORD STS IN HOURS'

FROM smsdss.BMH_PLM_PtAcct_Clasf_Dx_V PDV
JOIN smsdss.BMH_PLM_PtAcct_V PV
ON PDV.PtNo_Num = PV.PtNo_Num
JOIN smsdss.dx_cd_dim_v DX
ON PV.prin_dx_cd = DX.dx_cd
JOIN smsmir.sr_ord SO
ON PV.PtNo_Num = SO.episode_no
JOIN smsmir.sr_ord_sts_hist SOS
ON SO.ord_no = SOS.ord_no
JOIN smsmir.ord_sts_modf_mstr OSM
ON SOS.hist_sts = OSM.ord_sts_modf_cd

This is the WHERE clause from the query:

WHERE PDV.ClasfCd IN (
    LIST OF CLASFCDS
)
AND PV.hosp_svc NOT IN (
    'DIA'
    ,'DMS'
    ,'EME'
    )
AND PV.Adm_Date BETWEEN @SD AND @ED
AND SO.svc_cd IN (
    LIST OF CODES
    )
-- THE FOLLOWING GETS RID OF ORDERS THAT WERE DISCONTINUED
-- ONLY FOR EKG, CHEST XRAY PORTABLE, LACTIC ACID, CBC W/DIFF
AND SO.ord_no NOT IN (
    SELECT SO.ord_no

    FROM smsdss.BMH_PLM_PtAcct_Clasf_Dx_V PDV
    JOIN smsdss.BMH_PLM_PtAcct_V PV
    ON PDV.PtNo_Num = PV.PtNo_Num
    JOIN smsdss.dx_cd_dim_v DX
    ON PV.prin_dx_cd = DX.dx_cd
    JOIN smsmir.sr_ord SO
    ON PV.PtNo_Num = SO.episode_no
    JOIN smsmir.sr_ord_sts_hist SOS
    ON SO.ord_no = SOS.ord_no
    JOIN smsmir.ord_sts_modf_mstr OSM
    ON SOS.hist_sts = OSM.ord_sts_modf_cd

    WHERE OSM.ord_sts IN (
        'DISCONTINUE'
        ,'CANCEL'
        )
    AND PDV.ClasfCd IN (
        LIST OF CLASFCDS
        )
    AND PV.hosp_svc NOT IN (
        'DIA'
        ,'DMS'
        ,'EME'
        )
    AND SO.svc_cd IN (
        '00407304',
        '00507301',
        '00600015',
        '00402347'
        )
)
ORDER BY PV.PtNo_Num, SO.ord_no, SOS.prcs_dtime

So I need an accurate way inside the WHERE clause to, I suppose add another SELECT FROM WHERE statement that will allow me to compare the datetime of the Order_number where the Order_Description and Visit_ID's are equal.

MCP_infiltrator
  • 3,961
  • 10
  • 45
  • 82
  • When you say the "earliest order_number for a specific list of order types" I automatically think, OK, we need a min(datetime). Your data and query are a bit thick and would take some wading through, but my initial thinking would be that you would want to select an order min(datetime) and join to that to filter your result set. – mikeY Jul 12 '13 at 18:15
  • Yeah it's basically like that, the code is thick indeed and I am getting crosseyed. There are certain orders that cannot be returned if they were discontinued or canceled, but others that can be, and I need the first order placed for any type of order, ie, the first ekg or, the first cbc with wbc diff since a person can get many of them. – MCP_infiltrator Jul 12 '13 at 18:21
  • Do you get the "first order placed for any type of order" with "select SO.svc_desc, min(SOS.prcs_dtime) from however those two tables are joined group by SO.svc_desc" ? – mikeY Jul 12 '13 at 18:35
  • I am getting all the times, I am working on trying a `AND ( Select...)` statement to get the `MIN(SOS.prcs_dtime)` time. For now though, I do get all the orders and the first order is at the top due to the `ORDER BY` – MCP_infiltrator Jul 12 '13 at 18:40
  • No I meant with just a little stand alone separate SQL statement like I wrote, can you select a set of distinct svc_desc's with their min(prcs_dtime)? If you can, that give you something to join to and filter with. – mikeY Jul 12 '13 at 18:44
  • @mikeY I know have the MIN(SOS.prcs_dtime) only showing up wiht just a small subset of the select statement, I will now fiddle with it in order to get all the info, as I need it all. Thank you. If it works for it's entirety post that as the answer and I will accept it. – MCP_infiltrator Jul 12 '13 at 18:59
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/33385/discussion-between-mcp-infiltrator-and-mikey) – MCP_infiltrator Jul 12 '13 at 19:53

1 Answers1

1

I would suggest developing a stand alone SQL statement that selects a set of distinct svc_desc's with their min(prcs_dtime)'s. Then I would try to join to that set to filter the to the desired results. HTH.

mikeY
  • 519
  • 4
  • 14