Turning multiple rows into one row

Transact-SQL
1

I am trying to find a way to calculate the date difference between different rows when these rows have the same Med_rec_no. I would be subtracting the sch_stp_ts from the "row" below that is labeled adm_ts

DROP TABLE #TEMP1
SELECT * INTO #TEMP1 FROM (
select DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TPM300_PAT_VISIT.vst_ext_id as 'Visit Ext',
--TPM300_PAT_VISIT.vst_int_id AS 'Visit Int',
TPM300_PAT_VISIT.adm_ts,
--TYPE.cod_dtl_ds AS 'PAT_TYP',
DISCH_SERV.cod_dtl_ds,
TOR101_CASE_PROCEDURE.pro_stp_dt,
TOR101_CASE_PROCEDURE.sch_stp_ts,
TSM910_ICD9_REF.icd9_code,
TSM910_ICD9_REF.icd9_abrv_ds

from
TPM300_PAT_VISIT
INNER JOIN TSM180_MST_COD_DTL TYPE ON TPM300_PAT_VISIT.pat_ty = TYPE.cod_dtl_int_id
INNER JOIN TSM180_MST_COD_DTL DISCH_SERV ON TPM300_PAT_VISIT.dschg_srv_cd = DISCH_SERV.cod_dtl_int_id
left JOIN TPM318_VISIT_DIAGNOSIS ON TPM300_PAT_VISIT.vst_int_id = TPM318_VISIT_DIAGNOSIS.vst_int_id
LEFT JOIN TSM910_ICD9_REF ON TPM318_VISIT_DIAGNOSIS.icd9_int_id = TSM910_ICD9_REF.icd9_int_id
LEFT JOIN TOR100_CASE_HEADER ON TPM300_PAT_VISIT.vst_int_id = TOR100_CASE_HEADER.vst_int_id
LEFT JOIN TOR101_CASE_PROCEDURE ON TOR100_CASE_HEADER.cas_int_id = TOR101_CASE_PROCEDURE.cas_int_id
LEFT JOIN TOR105_CASE_PARTICIPANT ON TOR101_CASE_PROCEDURE.cas_pro_int_id = TOR105_CASE_PARTICIPANT.cas_pro_int_id

where
--TOR105_CASE_PARTICIPANT.ptc_stp_dt BETWEEN '01/01/2014' AND getdate()
--and
--AND
TSM910_ICD9_REF.icd9_code IN ('I468', 'I469', 'I97121', 'I97120', 'I213','I214','J960','J9589','J698','R092')
AND
ICD9_diag_ty = 'P'

--AND
--TSM910_ICD9_REF.icd9_code = 'R092'
--AND
--TSM910_ICD9_REF.icd9_code IN ('J960','J9589','J698')

UNION ALL

SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TPM300_PAT_VISIT.vst_ext_id as 'Visit Ext',
--TPM300_PAT_VISIT.vst_int_id AS 'Visit Int',
TPM300_PAT_VISIT.adm_ts,
DISCH_SERV.cod_dtl_ds,
--TYPE.cod_dtl_ds AS 'PAT_TYP',

TOR101_CASE_PROCEDURE.pro_stp_dt,
TOR101_CASE_PROCEDURE.sch_stp_ts,
TSM910_ICD9_REF.icd9_code,
TSM910_ICD9_REF.icd9_abrv_ds

FROM
TPM300_PAT_VISIT
INNER JOIN TSM180_MST_COD_DTL ON TPM300_PAT_VISIT.pat_ty = TSM180_MST_COD_DTL.cod_dtl_int_id
INNER JOIN TPB105_CHARGE_DETAIL ON TPM300_PAT_VISIT.vst_int_id = TPB105_CHARGE_DETAIL.vst_int_id
INNER JOIN TSM180_MST_COD_DTL DISCH_SERV ON TPM300_PAT_VISIT.dschg_srv_cd = DISCH_SERV.cod_dtl_int_id

INNER JOIN TSM180_MST_COD_DTL TYPE ON TPM300_PAT_VISIT.pat_ty = TYPE.cod_dtl_int_id
left JOIN TPM318_VISIT_DIAGNOSIS ON TPM300_PAT_VISIT.vst_int_id = TPM318_VISIT_DIAGNOSIS.vst_int_id
LEFT JOIN TSM910_ICD9_REF ON TPM318_VISIT_DIAGNOSIS.icd9_int_id = TSM910_ICD9_REF.icd9_int_id
LEFT JOIN TOR100_CASE_HEADER ON TPM300_PAT_VISIT.vst_int_id = TOR100_CASE_HEADER.vst_int_id
LEFT JOIN TOR101_CASE_PROCEDURE ON TOR100_CASE_HEADER.cas_int_id = TOR101_CASE_PROCEDURE.cas_int_id
LEFT JOIN TOR105_CASE_PARTICIPANT ON TOR101_CASE_PROCEDURE.cas_pro_int_id = TOR105_CASE_PARTICIPANT.cas_pro_int_id

WHERE
TPM300_PAT_VISIT.adm_ts BETWEEN '01/01/2015' AND '01/01/2016'
AND
sch_stp_ts IS NOT NULL
AND
pro_stp_dt IS NOT NULL
AND
ICD9_diag_ty = 'P'
AND
DISCH_SERV.cod_dtl_ds LIKE ('%SURGERY%')
) AS TEMP1

2

The easiest way I can think of would be a MAX() within a windowing function where the partition (window) would be the med_rec_no. Do a search on windowing functions and see if you can understand them. Paragon doesn't make the task easy.

3

This isn't the OR, but denials. The code below shows using RowNumber to get the minimum row. If you order by either the admit date or sch_stp_ts , the difference in dates can be done by subtracting values from row number - 1, etc.

SELECT
	T486.er_file_pat_hst_int_id,
	T486.rsn_grp_cd,
	T486.rsn_cod_ext_id,
	T486.invoice_row,
	TPB483.rsn_ds
FROM
	(
		SELECT 
			TPB486.er_file_pat_hst_int_id,
			TPB486.rsn_grp_cd,
			TPB486.rsn_cod_ext_id,
		   ROW_NUMBER() OVER (PARTITION BY TPB486.er_file_pat_hst_int_id ORDER BY TPB486.rsn_hst_int_id DESC) as invoice_row
		FROM
			TPB486_REASON_HISTORY_DETAIL				AS TPB486
	)													AS T486
	JOIN
		TPB483_ER_REJECTION_CODES						AS TPB483
		ON
			TPB483.er_rejection_codes_int_id = T486.er_file_pat_hst_int_id
WHERE invoice_row = 1

	SELECT DISTINCT
		T486.invoice_row								AS invoice_row,
		TPB486.rsn_cod_int_id							AS rsn_cod_int_id,
		TPB486.rsn_cod_ext_id							AS rsn_cod_ext_id,
		TPB486.rsn_grp_cd								AS rsn_grp_cd,
		TPB483.rsn_ds									AS rsn_ds
	FROM
			TPB484_ER_FILE_PATIENT_HISTORY				AS TPB484		-- link to denial date
		JOIN
			TPB486_REASON_HISTORY_DETAIL				AS TPB486		-- denial date
			ON
				TPB486.er_file_pat_hst_int_id = TPB484.er_file_pat_hst_int_id
		JOIN
			(
				SELECT 
					er_file_pat_hst_int_id,
					rsn_cod_int_id,
					rsn_cod_ext_id,
					ROW_NUMBER() OVER (PARTITION BY TPB484.billing_invoice_number ORDER BY TPB486.rsn_cod_ext_id DESC) as invoice_row
				FROM
					TPB486_REASON_HISTORY_DETAIL		AS TPB486
			)											AS T486
		ON
			T486.er_file_pat_hst_int_id = TPB484.er_file_pat_hst_int_id
		JOIN
			TPB483_ER_REJECTION_CODES					AS TPB483
			ON
				TPB483.er_rejection_codes_int_id = T486.rsn_cod_int_id
	WHERE
		T486.invoice_row = 1					AND
		T486.rsn_cod_int_id = TPB486.rsn_cod_int_id
	ORDER BY
		TPB484.er_file_pat_hst_int_id

The code above was written while trying to figure out how to compare data from a vendor who used the ivo_ext_id as their top level to data from a hospital that based their data on vst_int_id. Since each vst_int_id can have many ivo_ext_ids, I had to pick one only.

Since your problem is two dates, this type of code ought to allow you to select specific rows. The only other way I can think of is to unpivot the data so that the dates are in the same record. Maybe our experts can chime in.

4

Take a look at LEAD/LAG windowing functions - they will get you the value from a row before/after the current row (depending on the window).

5

Maybe this article will help you: https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/