Calculating within a query - think I need coalesce

Transact-SQL
1

Hi

I am trying to calculate the length of time our clients are in our service. If they are still in our service, I want to use the end date of the report to calculate, if they are no longer in the service, I want to use the referral end date to calculate.

I then need to group these so that I can report by less than one month, 1-3 months, etc. Then I want to display only the grouped data, for average length of stay.

I don't know how to code this. Can someone help? I have done some research and think I need coalesce but I have never used this before and I can't get it to work.

Here is my query.

DECLARE
@StartDate datetime
,@EndDate datetime
--Update this date for each report
set @EndDate = '20151001 00:00:00.000'
set @StartDate=dateadd(mm,-12,@EndDate)

--Average length of time in Service
SELECT
AVG(f.[Time in Service] AS [Average Time in Service]

FROM
(
select distinct
'' AS [TTO Average Length of Time Tab]
,ppr.SEQNO as [Referral ID]
,pd.NHI_NUMBER as [NHI]
,convert(Char(10),ppr.ReferralStartDate,103) as [Referral Start Date]
,case
when ppr.ReferralEndDate = '18991230 00:00:00.000'
then ''
else convert(Char(10),ppr.ReferralEndDate,103)
end as [Referral End Date]
,case
WHEN ppr.ReferralEndDate = '18991230 00:00:00.000'
then DATEDIFF(mm,ppr.ReferralStartDate,@EndDate)
else DATEDIFF(mm,ppr.ReferralStartDate,ppr.ReferralEndDate)
end
AS [Time in Service Months]
,CASE
WHEN [Time in Service Months] < 1 THEN 'Less than 1 month'
WHEN [Time in Service Months] BETWEEN 1 AND 3 THEN '1-3 months'
WHEN ([Time in Service Months] >3 AND [Time in Service Months] <6) THEN '3-6 months'
ELSE '6+ months' AS [Time in Service]
from
patient_primhd_referral as ppr
FULL outer join PATIENT_PRIMHD_ACTIVITY AS ppa
on ppr.SEQNO=ppa.REFERRAL_SEQNO
full outer join patient_details as pd
on ppa.PATIENT_ID=pd.PATIENT_ID
where
--Referral was open during period
(ppr.ReferralStartDate >=@StartDate
or ppr.ReferralStartDate <@EndDate)
--Team Codes PN AOD only
and ppr.ReferralTeamID =29
--TTO only - Referral from JU
AND ppr.ReferralFromID=101
) AS f
GROUP BY [f].[Time in Service Months]
ORDER by
[f].[Time in Service]

;

2

Since you are using Referral End Date more than once, you might consider adding a CROSS APPLY to give you the date to compute from. e;g;

CROSS APPLY
)
    SELECT CASE WHEN still in service
                    THEN getdate()
                    ELSE ppr.ReferralStartDate
                END
) _(DateToComputeFrom)

Then, replace ppr.ReferralStartDate with DateToComputeFrom in your query. I reformatted your original below and fixed a couple of errors.

Reformatted:

DECLARE @StartDate DATETIME
      , @EndDate DATETIME

--Update this date for each report
SET @EndDate = '20151001 00:00:00.000'
SET @StartDate = dateadd(mm, - 12, @EndDate)

--Average length of time in Service
SELECT AVG(f.[Time in Service]) AS [Average Time in Service]
FROM (
      SELECT DISTINCT '' AS [TTO Average Length of Time Tab]
            , ppr.SEQNO AS [Referral ID]
            , pd.NHI_NUMBER AS [NHI]
            , convert(CHAR(10), ppr.ReferralStartDate, 103) AS [Referral Start Date]
            , CASE 
                  WHEN ppr.ReferralEndDate = '18991230 00:00:00.000'
                        THEN ''
                  ELSE convert(CHAR(10), ppr.ReferralEndDate, 103)
                  END AS [Referral End Date]
            , CASE 
                  WHEN ppr.ReferralEndDate = '18991230 00:00:00.000'
                        THEN DATEDIFF(mm, ppr.ReferralStartDate, @EndDate)
                  ELSE DATEDIFF(mm, ppr.ReferralStartDate, ppr.ReferralEndDate)
                  END AS [Time in Service Months]
            , CASE 
                  WHEN [Time in Service Months] < 1
                        THEN 'Less than 1 month'
                  WHEN [Time in Service Months] BETWEEN 1
                              AND 3
                        THEN '1-3 months'
                  WHEN (
                              [Time in Service Months] > 3
                              AND [Time in Service Months] < 6
                              )
                        THEN '3-6 months'
                  ELSE '6+ months'
                  END AS [Time in Service]
      FROM patient_primhd_referral AS ppr
      FULL JOIN PATIENT_PRIMHD_ACTIVITY AS ppa
            ON ppr.SEQNO = ppa.REFERRAL_SEQNO
      FULL JOIN patient_details AS pd
            ON ppa.PATIENT_ID = pd.PATIENT_ID
      WHERE
            --Referral was open during period
            (
                  ppr.ReferralStartDate >= @StartDate
                  OR ppr.ReferralStartDate < @EndDate
                  )
            --Team Codes PN AOD only 
            AND ppr.ReferralTeamID = 29
            --TTO only - Referral from JU
            AND ppr.ReferralFromID = 101
      ) AS f
GROUP BY [f].[Time in Service Months]
ORDER BY [f].[Time in Service];
3

Hi

I am getting this error every line where [Time in service months] is used.

Msg 207, Level 16, State 1, Line 65
Invalid column name 'Time in Service Months'.
Msg 207, Level 16, State 1, Line 67
Invalid column name 'Time in Service Months'.
Msg 207, Level 16, State 1, Line 67
Invalid column name 'Time in Service Months'.
Msg 207, Level 16, State 1, Line 71
Invalid column name 'Time in Service Months'.
Msg 207, Level 16, State 1, Line 72
Invalid column name 'Time in Service Months'.

I think this is because it is based on the line above, and maybe SQL hasn't calculated that yet and therefore cannot use it.

4

correct. You could either compute Time In Service Months in a subquery (or cross apply, that's what I do) or repeat the expression deriving it. I don't like that since it violates DRY

5

I haven't used a Cross Apply before, where do I put that? Before this query, or after?

6

Something like this:

SELECT AVG(f.[Time in Service]) AS [Average Time in Service]
FROM (
      SELECT DISTINCT '' AS [TTO Average Length of Time Tab]
            , ppr.SEQNO AS [Referral ID]
            , pd.NHI_NUMBER AS [NHI]
            , convert(CHAR(10), ppr.ReferralStartDate, 103) AS [Referral Start Date]
            , [Referral End Date]
            , [Time in Service Months]
            , [Time in Service]
      FROM patient_primhd_referral AS ppr
      
      -- CROSS APPLY to derive and alias values
      CROSS APPLY
      (
        SELECT CASE 
                  WHEN ppr.ReferralEndDate = '18991230 00:00:00.000'
                        THEN ''
                  ELSE convert(CHAR(10), ppr.ReferralEndDate, 103)
                  END AS [Referral End Date]
             , CASE 
                  WHEN ppr.ReferralEndDate = '18991230 00:00:00.000'
                        THEN DATEDIFF(mm, ppr.ReferralStartDate, @EndDate)
                  ELSE DATEDIFF(mm, ppr.ReferralStartDate, ppr.ReferralEndDate)
                  END AS [Time in Service Months]
             , CASE 
                  WHEN [Time in Service Months] < 1
                        THEN 'Less than 1 month'
                  WHEN [Time in Service Months] BETWEEN 1
                              AND 3
                        THEN '1-3 months'
                  WHEN (
                              [Time in Service Months] > 3
                              AND [Time in Service Months] < 6
                              )
                        THEN '3-6 months'
                  ELSE '6+ months'
                  END AS [Time in Service]
      ) _ -- Use '_' as subquery alias because we don't refer to it -- just the columns
      -- End of CROSS APPLY
       
      FULL JOIN PATIENT_PRIMHD_ACTIVITY AS ppa
            ON ppr.SEQNO = ppa.REFERRAL_SEQNO
      FULL JOIN patient_details AS pd
            ON ppa.PATIENT_ID = pd.PATIENT_ID
      WHERE
            --Referral was open during period
            (
                  ppr.ReferralStartDate >= @StartDate
                  OR ppr.ReferralStartDate < @EndDate
                  )
            --Team Codes PN AOD only 
            AND ppr.ReferralTeamID = 29
            --TTO only - Referral from JU
            AND ppr.ReferralFromID = 101
      ) AS f
GROUP BY [Time in Service Months]
ORDER BY [Time in Service];
7

Thanks for writing that out, I understand it now.

But I'm having the same issue - as the Case Statement for [Time in Service Months] is being calculated in the query, it's giving me this errors every time I calculate it.

Invalid column name 'Time in Service Months'.

8

OIC, you have a second generation derived expression. I did it this way(I faked your tables):

declare @enddate int = 1;
declare @startdate int = 1;

with patient_primhd_referral(SEQNO, ReferralStartDate, ReferralEndDate, ReferralTeamID, ReferralFromID) as
(
    select 1,1,1,1,1
)
,   PATIENT_PRIMHD_ACTIVITY(REFERRAL_SEQNO, PATIENT_ID) as
(
    select 1,1
)
,    patient_details(NHI_NUMBER, PATIENT_ID) as
(
    select 1,1
)

SELECT AVG(f.[Time in Service]) AS [Average Time in Service]
FROM (
      SELECT DISTINCT '' AS [TTO Average Length of Time Tab]
            , ppr.SEQNO AS [Referral ID]
            , pd.NHI_NUMBER AS [NHI]
            , convert(CHAR(10), ppr.ReferralStartDate, 103) AS [Referral Start Date]
            , [Referral End Date]
            , [Time in Service Months]
            , [Time in Service]
      FROM patient_primhd_referral AS ppr
      
      -- CROSS APPLY to derive and alias values
      CROSS APPLY
      (
        SELECT CASE 
                  WHEN ppr.ReferralEndDate = '18991230 00:00:00.000'
                        THEN ''
                  ELSE convert(CHAR(10), ppr.ReferralEndDate, 103)
                  END AS [Referral End Date]
             , CASE 
                  WHEN ppr.ReferralEndDate = '18991230 00:00:00.000'
                        THEN DATEDIFF(mm, ppr.ReferralStartDate, @EndDate)
                  ELSE DATEDIFF(mm, ppr.ReferralStartDate, ppr.ReferralEndDate)
                  END AS [Time in Service Months]
      ) _1
      -- Second cross apply to derive Time In Service
      CROSS APPLY
      ( 
      
             SELECT CASE 
                  WHEN [Time in Service Months] < 1
                        THEN 'Less than 1 month'
                  WHEN [Time in Service Months] BETWEEN 1
                              AND 3
                        THEN '1-3 months'
                  WHEN (
                              [Time in Service Months] > 3
                              AND [Time in Service Months] < 6
                              )
                        THEN '3-6 months'
                  ELSE '6+ months'
                  END AS [Time in Service]
      ) _2 -- Use '_' as subquery alias because we don't refer to it -- just the columns
      -- End of CROSS APPLY
       
      FULL JOIN PATIENT_PRIMHD_ACTIVITY AS ppa
            ON ppr.SEQNO = ppa.REFERRAL_SEQNO
      FULL JOIN patient_details AS pd
            ON ppa.PATIENT_ID = pd.PATIENT_ID
      WHERE
            --Referral was open during period
            (
                  ppr.ReferralStartDate >= @StartDate
                  OR ppr.ReferralStartDate < @EndDate
                  )
            --Team Codes PN AOD only 
            AND ppr.ReferralTeamID = 29
            --TTO only - Referral from JU
            AND ppr.ReferralFromID = 101
      ) AS f
GROUP BY [Time in Service Months]
ORDER BY [Time in Service];
9

I see another problem though. Your query does an AVG() on Time In Service, but that's a varchar, not a numeric

10

I am trying to calculate the length of time our clients are in our service. If they are still in our service, I want to use the end date of the report to calculate, if they are no longer in the service, I want to use the referral end date to calculate.

I then need to group these so that I can report by less than one month, 1-3 months, etc. then I want to display only the grouped data, for average length of stay.

I don't know how to code this. Can someone help? I have done some research and think I need COALESCE() but I have never used this before and I can't get it to work. <<

No, the problem is deeper. You are still writing COBOL! The idea of SQL and tiered architectures was to split out the display from the data. But you still mix them together. This is like hearing German spoken with Japanese syntax and a heavy accent :open_mouth: :fearful: :frowning:

Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (xkcd: ISO 8601). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

And you need to read and download the PDF for: https:--www.simple-talk.com-books-sql-books-119-sql-code-smells-

I am trying to calculate the length of time our clients are in our service. If they are still in our service, I want to use the end date of the report to calculate, if they are no longer in the service, I want to use the referral end date to calculate. <<

Thanks to your lack of basic Netiquette, now we have to create the tables, design the schema and do the query without help from you! I am not going to do that; you need to grow and become a good SQL citizen.

I then need to group these so that I can report by less than one month, 1-3 months, etc. then I want to display only the grouped data, for average length of stay.<<

Did you see how you framed the question in procedural steps :frowning: and not in a declarative :smiley:

I don't know how to code this. Can someone help? I have done some research and think I need coalesce but I have never used this before and I can't get it to work.

Here is my query.<<

No, make it a procedure! We do not use local variables in declarative languages. We do not put commas in the front of a line of code (old punch card trick!)

SELECT DISTINCT is rare in a correct schema, but we have no DDL (really bad Netiquette!)

Data elements do not ever change names in a schema. If it is a “SEQNO” (far too vague to be a valid ISO-11179 name) it cannot be a “referral_id” somewhere ELSE. Major error! I would fire you for this alone.

The old Sybase CONVERT() was put it for you COBOL programmers so you could do display formatting 25+ years ago. Sql programers use temporal data types and then let the presentation layers add punctuation. Also, the ANSI/ISO Standards and current T-SQL use the “yyyy-mm-dd” format, not the solid string of digits. Yes, I know that COBOL would add them with a PICTURE clause, but this is not COBOL.

A FULL OUTER JOIN is also very rare. It usually means that the schema lacks correct DRI, so we kludge it in the DML.

Using crap like “PPR.referral_end_date = '1899-12-30'” is wrong; the purpose of a NULL in a DATE column is to be an eternity or unknown marker. You use “COALESCE(foobar_end_date, CURRENT_TIMESTAMP)” or whatever makes sense in your code.

CREATE PROCEDURE Service_Time_Report
(@in_report_end_date DATE)
AS
BEGIN
..
END;

In declarative programming, we nest expressions and do not materialize local variables. You do not know how simple CASE works, so you use the fancier computed version. Try this approach if you really need to aggregate durations at the data base level:

CASE DATEDIFF(MM, PPR.referral_start_date,
COALESCE (PPR.referral_end_date, CURRENT_TIMESTAMP))
WHEN 0 THEN 'Less than 1 month'
WHEN 1 THEN '1-3 months'
WHEN 2 THEN '1-3 months'
WHEN 3 THEN '1-3 months'
WHEN 4 THEN '4-6 months'
WHEN 5 THEN '4-6 months'
WHEN 6 THEN '4-6 months'
ELSE '6+ months' END AS service_time_category;

Just remember what Artemis Ward said about “it ain’t what we don't know what kills us, but what we know that ain't so!” Based on 30+ years of SQL, you are about 2-3 years away from having your epiphany, losing the old Japanese-German mix and talking in SQL :slight_smile: