#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2017
    Posts
    1
    Rep Power
    0

    SQL summarize results


    I just need to summarize, ideally would need prior month and year with a total. That's it.
    I can not figure out how to do this with the query I am using.
    Purpose to find # of days patients were in a bed in the prior month , results are summed on patient, but regardless how I try to group or sum it fails. Any advise? Thanks.



    PHP Code:
    use paragon;
    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(MONTHDATEDIFF(MONTH, -1GETDATE())-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(MONTHDATEDIFF(MONTH, -1GETDATE())-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(MONTHDATEDIFF(MONTH31CURRENT_TIMESTAMP), 0)AND adm_ts DATEADD(MONTHDATEDIFF(MONTH0CURRENT_TIMESTAMP), 0) )or
    (
    dschrg_ts>= DATEADD(MONTHDATEDIFF(MONTH31CURRENT_TIMESTAMP), 0)AND dschrg_ts DATEADD(MONTHDATEDIFF(MONTH0CURRENT_TIMESTAMP), 0) )or
    (
    adm_ts DATEADD(MONTHDATEDIFF(MONTH0CURRENT_TIMESTAMP), 0) AND dschrg_ts>= DATEADD(MONTHDATEDIFF(MONTH31CURRENT_TIMESTAMP), 0)) or
    (
    adm_ts DATEADD(MONTHDATEDIFF(MONTH0CURRENT_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
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,665
    Rep Power
    4288
    Originally Posted by cpagano
    Purpose to find # of days patients were in a bed in the prior month , results are summed on patient,
    let's start with the basic requirement -- you need to select the patient id, then SUM() something, and GROUP BY patient id

    i have a feeling the SUM() should be applied to your big CASE expression, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo