Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    44

    Unanswered: Calculating patient days for prior month

    I am very rusty on my SQl and struggling with this one.
    Need my patient day calculation to count number of days in the past month. My current query is calculating total patient days.
    Any help is appreciated.

    select
    adm_ts,dschrg_ts,
    DATEDIFF(DAY,adm_ts,isnull(dschrg_ts,DATEADD(MONTH , DATEDIFF(MONTH, -1, GETDATE())-1, -1))) as 'Total 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

  2. #2
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    DAY(DATEADD(DD, -1, DATEADD(MM, DATEDIFF(MM, 0, @DATE),0)))

    This gives you the the days of the preceding month. Write an outer query and datediff the two.

    HTH

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,860
    Provided Answers: 17
    This may be slightly more intuitive. Only works on SQL 2014 and higher:
    Code:
    select datediff(dd, eomonth(getdate(), -2) , eomonth(getdate(), -1)) as DaysLastMonth

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •