March 24, 2017 at 8:30 am
Difficulty with following query, I just need a total for all patient days in the prior month. Query currently produces one line per patients. Need to summarize but all efforts lead to error messages. Advise? Thanks for looking
set transaction isolation level read uncommitted;
set nocount on;
declare @PriorEOM as date
declare @PriorFOM as date
Set @PriorEOM = dateadd(day,-day(getdate()),getdate())--end of month
Set @PriorFOM = dateadd(day,-day(@PriorEOM) + 1,@PriorEOM)--1st of month
select "patient_days"=
Case
WHEN adm_ts >= @PriorFOM AND dschrg_ts <=@PriorEOM--admit greater than 1st of month and discharg less than last of month
THEN DATEDIFF(DAY,adm_ts,isnull(dschrg_ts,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)))
WHEN adm_ts < @PriorFOM AND dschrg_ts <=@PriorEOM--admit date less than 1st of month and discharg less than last of month
THEN DATEDIFF(DAY,@PriorFOM,isnull(dschrg_ts,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)))
WHEN adm_ts >= @PriorFOM AND dschrg_ts >=@PriorEOM--admit greater than 1st of month and discharg greater than last of month
THEN DATEDIFF(DAY,adm_ts,@PriorEOM)
WHEN adm_ts <= @PriorFOM AND dschrg_ts >=@PriorEOM--admit less than 1st of month and discharg greater than last of month
THEN DATEDIFF(DAY,@PriorFOM,@PriorEOM)
ELSE ''
END
from TPM300_PAT_VISIT
where
((adm_ts >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)AND adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) )or
(dschrg_ts>= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)AND dschrg_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) )or
(adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND dschrg_ts>= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0)) or
(adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND dschrg_ts = NULL))
and pat_ty = 15323
and vst_sta_cd <>4745
and adm_ts <> dschrg_ts -- no shows or cancels
March 24, 2017 at 9:05 am
Reformatted SQl for my own sanity and readiblity:select -- "String name" = is deprecated, I suggest using AS
Case WHEN adm_ts >= @PriorFOM AND dschrg_ts <=@PriorEOM--admit greater than 1st of month and discharg less than last of month
THEN DATEDIFF(DAY,adm_ts,isnull(dschrg_ts,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)))
WHEN adm_ts < @PriorFOM AND dschrg_ts <=@PriorEOM--admit date less than 1st of month and discharg less than last of month
THEN DATEDIFF(DAY,@PriorFOM,isnull(dschrg_ts,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)))
WHEN adm_ts >= @PriorFOM AND dschrg_ts >=@PriorEOM--admit greater than 1st of month and discharg greater than last of month
THEN DATEDIFF(DAY,adm_ts,@PriorEOM)
WHEN adm_ts <= @PriorFOM AND dschrg_ts >=@PriorEOM--admit less than 1st of month and discharg greater than last of month
THEN DATEDIFF(DAY,@PriorFOM,@PriorEOM)
ELSE ''
END AS patient_days
FROM TPM300_PAT_VISIT
WHERE ((adm_ts >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0) AND adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) )
OR (dschrg_ts>= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0) AND dschrg_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) )
OR (adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND dschrg_ts>= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0))
OR (adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND dschrg_ts = NULL))
AND pat_ty = 15323
AND vst_sta_cd <> 4745
AND adm_ts <> dschrg_ts; -- no shows or cancels
I can't see anything wrong with your SQL, what error message(s) are you getting? Consumable sample data and DDL help, along with expected Outputs.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 24, 2017 at 9:20 am
Hi Thom, thanks for your input. I have tried Rollup, Group by, and various other summary things. I am not that experience with SQL, Various different "it doesn't work this way" errors.
Each GROUP BY expression must contain at least one column that is not an outer reference.
Desired result(example)
PatientDays
28
Current Result(example)
| PatientDays |
| 5 |
| 5 |
| 1 |
| 0 |
| 4 |
| 4 |
| 5 |
| 4 |
March 24, 2017 at 9:25 am
dont think is too hard to fix for you.....some sample data and expected results will help
please read https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ and post back accordingly
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 24, 2017 at 9:30 am
I'm not seeing any aggregate function in there, don't you just need a SUM() around the case statement?
March 24, 2017 at 9:31 am
can you please why, although you have four different case statements and calculations, you are returning the number of days as a single column?
I was thinking along the lines of results with four columns that will represent the different scenarios....just a thought?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 24, 2017 at 9:33 am
ZZartin - Friday, March 24, 2017 9:30 AMI'm not seeing any aggregate function in there, don't you just need a SUM() around the case statement?
Yes, that is what I am trying to do.
March 24, 2017 at 9:34 am
Thom A - Friday, March 24, 2017 9:05 AMselect -- "String name" = is deprecated, I suggest using AS
SUM(
Case WHEN adm_ts >= @PriorFOM AND dschrg_ts <=@PriorEOM--admit greater than 1st of month and discharg less than last of month
THEN DATEDIFF(DAY,adm_ts,isnull(dschrg_ts,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)))
WHEN adm_ts < @PriorFOM AND dschrg_ts <=@PriorEOM--admit date less than 1st of month and discharg less than last of month
THEN DATEDIFF(DAY,@PriorFOM,isnull(dschrg_ts,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)))
WHEN adm_ts >= @PriorFOM AND dschrg_ts >=@PriorEOM--admit greater than 1st of month and discharg greater than last of month
THEN DATEDIFF(DAY,adm_ts,@PriorEOM)
WHEN adm_ts <= @PriorFOM AND dschrg_ts >=@PriorEOM--admit less than 1st of month and discharg greater than last of month
THEN DATEDIFF(DAY,@PriorFOM,@PriorEOM)
ELSE 0
END) AS patient_days
FROM TPM300_PAT_VISIT
WHERE ((adm_ts >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0) AND adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) )
OR (dschrg_ts>= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0) AND dschrg_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) )
OR (adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND dschrg_ts>= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0))
OR (adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND dschrg_ts = NULL))
AND pat_ty = 15323
AND vst_sta_cd <> 4745
AND adm_ts <> dschrg_ts; -- no shows or cancels
Guess work, but is it therefore not simple as above?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 24, 2017 at 9:34 am
J Livingston SQL - Friday, March 24, 2017 9:31 AMcan you please why, although you have four different case statements and calculations, you are returning the number of days as a single column?
I was thinking along the lines of results with four columns that will represent the different scenarios....just a thought?
No, user requires only one total # to enter into state for patient days.
March 24, 2017 at 9:36 am
Thom A - Friday, March 24, 2017 9:34 AMThom A - Friday, March 24, 2017 9:05 AMselect -- "String name" = is deprecated, I suggest using AS
SUM(
Case WHEN adm_ts >= @PriorFOM AND dschrg_ts <=@PriorEOM--admit greater than 1st of month and discharg less than last of month
THEN DATEDIFF(DAY,adm_ts,isnull(dschrg_ts,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)))
WHEN adm_ts < @PriorFOM AND dschrg_ts <=@PriorEOM--admit date less than 1st of month and discharg less than last of month
THEN DATEDIFF(DAY,@PriorFOM,isnull(dschrg_ts,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)))
WHEN adm_ts >= @PriorFOM AND dschrg_ts >=@PriorEOM--admit greater than 1st of month and discharg greater than last of month
THEN DATEDIFF(DAY,adm_ts,@PriorEOM)
WHEN adm_ts <= @PriorFOM AND dschrg_ts >=@PriorEOM--admit less than 1st of month and discharg greater than last of month
THEN DATEDIFF(DAY,@PriorFOM,@PriorEOM)
ELSE 0
END) AS patient_days
FROM TPM300_PAT_VISIT
WHERE ((adm_ts >= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0) AND adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) )
OR (dschrg_ts>= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0) AND dschrg_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) )
OR (adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND dschrg_ts>= DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0))
OR (adm_ts < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AND dschrg_ts = NULL))
AND pat_ty = 15323
AND vst_sta_cd <> 4745
AND adm_ts <> dschrg_ts; -- no shows or cancelsGuess work, but is it therefore not simple as above?
YES, I didn't know you had to enclose the whole case statement. Thank you.
This is exactly what I needed.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply