December 2, 2015 at 10:00 am
Still a pup with SQL and having difficulty with this query that I am building off of an old Access query.
SELECT
'0720'AS Department,
'2016' AS FY,
DATEADD(DAY,-4,CONVERT(DATE,GETDATE())) AS PeriodEndDate,
sum(chg_unt_no) as 'Count'
FROM
TPM300_PAT_VISIT
INNER JOIN TSM040_PERSON_HDR ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id
INNER JOIN TPB105_CHARGE_DETAIL ON TPM300_PAT_VISIT.vst_int_id = TPB105_CHARGE_DETAIL.vst_int_id
INNER JOIN TPB900_CHG_CODE_MST ON TPB105_CHARGE_DETAIL.chg_cod_int_id = TPB105_CHARGE_DETAIL.chg_cod_int_id
INNER JOIN TSM180_MST_COD_DTL pat_typ ON TPM300_PAT_VISIT.pat_ty = pat_typ.cod_dtl_int_id
WHERE
TPB105_CHARGE_DETAIL.chg_pst_ts BETWEEN DATEADD(DAY,-17,CONVERT(DATE,GETDATE())) AND DATEADD(DAY,-3,CONVERT(DATE,GETDATE()))
AND
TPB105_CHARGE_DETAIL.dpt_ext_id IN ('0421','0423','0424','0425')
AND
--I know this is incorrect, but it's what I am looking for.
TPB900_CHG_CODE_MST.chg_cod_ext_id NOT IN ('0920%','910%','903%','8606%','11108','60917','60918','60919')
Any help or tips are most welcomed! Here to learn:w00t:
December 2, 2015 at 10:09 am
mhuff 48469 (12/2/2015)
Still a pup with SQL and having difficulty with this query that I am building off of an old Access query.
SELECT
'0720'AS Department,
'2016' AS FY,
DATEADD(DAY,-4,CONVERT(DATE,GETDATE())) AS PeriodEndDate,
sum(chg_unt_no) as 'Count'
FROM
TPM300_PAT_VISIT
INNER JOIN TSM040_PERSON_HDR ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id
INNER JOIN TPB105_CHARGE_DETAIL ON TPM300_PAT_VISIT.vst_int_id = TPB105_CHARGE_DETAIL.vst_int_id
INNER JOIN TPB900_CHG_CODE_MST ON TPB105_CHARGE_DETAIL.chg_cod_int_id = TPB105_CHARGE_DETAIL.chg_cod_int_id
INNER JOIN TSM180_MST_COD_DTL pat_typ ON TPM300_PAT_VISIT.pat_ty = pat_typ.cod_dtl_int_id
WHERE
TPB105_CHARGE_DETAIL.chg_pst_ts BETWEEN DATEADD(DAY,-17,CONVERT(DATE,GETDATE())) AND DATEADD(DAY,-3,CONVERT(DATE,GETDATE()))
AND
TPB105_CHARGE_DETAIL.dpt_ext_id IN ('0421','0423','0424','0425')
AND
--I know this is incorrect, but it's what I am looking for.
TPB900_CHG_CODE_MST.chg_cod_ext_id NOT IN ('0920%','910%','903%','8606%','11108','60917','60918','60919')
Any help or tips are most welcomed! Here to learn:w00t:
Give this a try:
SELECT
'0720'AS Department,
'2016' AS FY,
DATEADD(DAY,-4,CONVERT(DATE,GETDATE())) AS PeriodEndDate,
sum(chg_unt_no) as 'Count'
FROM
TPM300_PAT_VISIT
INNER JOIN TSM040_PERSON_HDR
ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id
INNER JOIN TPB105_CHARGE_DETAIL
ON TPM300_PAT_VISIT.vst_int_id = TPB105_CHARGE_DETAIL.vst_int_id
INNER JOIN TPB900_CHG_CODE_MST
ON TPB105_CHARGE_DETAIL.chg_cod_int_id = TPB105_CHARGE_DETAIL.chg_cod_int_id
INNER JOIN TSM180_MST_COD_DTL pat_typ
ON TPM300_PAT_VISIT.pat_ty = pat_typ.cod_dtl_int_id
WHERE
TPB105_CHARGE_DETAIL.chg_pst_ts BETWEEN DATEADD(DAY,-17,CONVERT(DATE,GETDATE())) AND DATEADD(DAY,-3,CONVERT(DATE,GETDATE()))
AND
TPB105_CHARGE_DETAIL.dpt_ext_id IN ('0421','0423','0424','0425')
AND
--I know this is incorrect, but it's what I am looking for.
--TPB900_CHG_CODE_MST.chg_cod_ext_id NOT IN ('0920%','910%','903%','8606%','11108','60917','60918','60919')
TPB900_CHG_CODE_MST.chg_cod_ext_id not like '0920%' and
TPB900_CHG_CODE_MST.chg_cod_ext_id not like '910%' and
TPB900_CHG_CODE_MST.chg_cod_ext_id not like '903%' and
TPB900_CHG_CODE_MST.chg_cod_ext_id not like '8606%' and
TPB900_CHG_CODE_MST.chg_cod_ext_id NOT IN ('11108','60917','60918','60919');
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply