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
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 and not in a declarative
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