Comparing rows/columns - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Comparing rows/columns

Author

Topic

Mhuff
Starting Member

5 Posts

Posted - 2014-10-22 : 11:56:55

I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.

I really have not an idea on how to complete something like this and my google searches have been fruitless.

I attached a spreadsheet with an example of what I am getting.

SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TSM040_PERSON_HDR.lst_nm AS 'Last Name',
TSM040_PERSON_HDR.fst_nm AS 'First Name',
TPM300_PAT_VISIT.vst_ext_id AS 'Visit ID',
TSM180_MST_COD_DTL.cod_dtl_ext_id AS 'Patient Type',
TSM180_MST_COD_DTL.cod_dtl_ds,
TPM300_PAT_VISIT.adm_ts AS 'Admit TS',
TPM300_PAT_VISIT.dschrg_ts,
TRX101_THERAPY_ITEM.dug_ds AS 'Drug Desc',
TRX101_THERAPY_ITEM.bnd_nm AS 'Brand Name'

FROM
TPM300_PAT_VISIT
LEFT JOIN TRX100_THERAPY_ORDER
ON TPM300_PAT_VISIT.vst_int_id = TRX100_THERAPY_ORDER.vst_int_id
RIGHT JOIN TRX101_THERAPY_ITEM
ON TRX101_THERAPY_ITEM.prx_int_id = TRX100_THERAPY_ORDER.prx_int_id
INNER JOIN TCP500_RX_ADS
ON TCP500_RX_ADS.prx_int_id = TRX101_THERAPY_ITEM.prx_int_id
INNER JOIN TSM180_MST_COD_DTL
ON TPM300_PAT_VISIT.pat_ty = TSM180_MST_COD_DTL.cod_dtl_int_id
INNER JOIN TSM040_PERSON_HDR
ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-22 : 13:12:36

You can use LEAD in Sql Server 2012 and up

http://msdn.microsoft.com/en-us/library/hh213125.aspx

Mhuff
Starting Member

5 Posts

Posted - 2014-10-22 : 14:04:52

Sorry should have mentioned SQL 2008R2 is what we are using.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-22 : 14:19:35

then you can use a self join with Row_Number() function. Basic idea is to compare with Row_Number() +/- 1

Basically LEAD/LAG were introduced to reduce need for self-join and increase performance for this sort of thing.

Mhuff
Starting Member

5 Posts

Posted - 2014-10-22 : 15:08:04

If I do the row number () function, what happens if I have 3 rows with the same medical record number throughout the month and not sequential. Will this delete one or two of those rows because the function assumes that one follows directly after the other?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-22 : 15:53:38

The Row_Number() function: http://msdn.microsoft.com/en-us/library/ms186734.aspx

It requires an ORDER BY clause (think about it, it wouldn't make sense otherwise)

So, it produces increasing row numbers within the partition (if specified) as per the ORDER BY clause

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources