I am trying to calculate the average length of stay, where someone has not left our service.
I just want the average, not the list (which I have managed to get) as I only want the average of the DISTINCT ppr.SEQNO - not all of them.
> USE [exoMashTrust-test] > declare > @StartDate date > ,@StartDate1 date > ,@StartDate2 date > ,@StartDate3 date > ,@EndDate Date > ,@EndDate1 date > ,@EndDate2 date > ,@EndDate3 date > ,@TeamID int > --Edit the dates depending on the months required in the report > set @startdate = '20150101 00:00:00.000' > set @enddate = '20150331 23:59:59.999' > --These are auto calculated Dates > set @StartDate1=@StartDate; > set @StartDate2=dateadd(mm,1,@StartDate1); > set @StartDate3=dateadd(mm,2,@StartDate1); > set @EndDate1=dateadd(mm,-2,@EndDate); > set @EndDate2=dateadd(mm,-1,@EndDate); > set @EndDate3=@EndDate; > --Section Team ID > set @TeamID = 29 > --Average Length of Stay over quarter > --This just lists the length of stay where they haven't left > SELECT > distinct pd.NHI_NUMBER [Client ID] > ,PPR.SEQNO as [Referral ID] > ,convert(Char(10),ppr.ReferralStartDate,103) as [Referral Start Date] > ,convert(Char(10),ppr.ReferralEndDate,103) as [Referral End Date] > ,datediff(d,ppr.ReferralStartDate, ppr.ReferralEndDate) as [No. Days] > FROM > PATIENT_PRIMHD_REFERRAL as ppr > join PATIENT_PRIMHD_ACTIVITY as ppa > on ppr.PATIENT_ID=ppa.PATIENT_ID > join patient_details as pd > on pd.PATIENT_ID=ppa.PATIENT_ID > full outer join PATIENT_ETH_TYPES as pet > on pd.ETHNICITY=pet.SEQNO > JOIN PATIENT_PRIMHD_MHA as mha > on ppa.ActivityTypeCodeID=mha.SEQNO > WHERE > ppr.ReferralEndDate between @startdate and @EndDate > and ppa.ActivityStartDate between @startdate and @EndDate > and ppa.ReferralTeamID=@TeamID > and mha.localshortcode like '%175' > ORDER BY > [Client ID] > ;
I am pretty sure I need to use an OVER and PARTITION but I can't get the syntax right (I have never created a query with these before).
Now I have calculated the average of these and it is 116 - but I cannot get this number with any of my bodgy-coding
Hopefully someone can help.