Set dates not in range with status code to null

Transact-SQL
1

Hi Everyone,

Its been awhile since I posted
What I am trying to do is to modify the AVM.DATE_PROVIDED aka MSS_DATE to a NULL value if it is not in the specified date range of
2015-11-01 to 2015-11-30

SELECT PPD.CUSTOMER_CODE, PPD.NAME_FULL, PPD.PROSPECT_ADMIT_DATE, CASE WHEN PPD.STATUS_CODE = 'H100' AND AVM.DATE_PROVIDED NOT BETWEEN CONVERT(DATETIME,'2015-11-01',120) AND CONVERT(DATETIME,'2015-11-30',120) AND AVS.DATE_PROVIDED BETWEEN CONVERT(DATETIME,'2015-11-01',120) AND CONVERT(DATETIME,'2015-11-30',120) THEN ISNULL(AVM.DATE_PROVIDED, NULL) ELSE AVM.DATE_PROVIDED END AS MSS_DATE, PPD.STATUS_TIME, PPD.DESCRIPTION, PPD.REFERRAL_SOURCE, COALESCE(PPD.NAME_FIRST, ' ') + ' ' + COALESCE(PPD.NAME_LAST, ' ') AS PHYSICIAN_NAME, PPD.ASSOCIATED_FACILITY_ID, PPD.NOTES, CASE WHEN RB.ORGANIZATION_NAME IS NULL THEN 'Family Referral' ELSE RB.ORGANIZATION_NAME END AS ORGANIZATON_NAME, PPD.ADMIT_DATE, PPD.ASSOCIATED_FACILITY_ID ASSOCIATED_FACILITY_ID_2, AVS.DATE_PROVIDED RN_DATE, AVS.DIRECT_START_TIME RN_TIME, COALESCE(AVS.DATE_PROVIDED, '') + COALESCE(RIGHT(CONVERT(DATETIME,AVS.DIRECT_START_TIME/86399.9, 1),8), '') AS RN_DATE_TIME, AVS.NAME_FULL RN_NAME, COALESCE(AVM.DATE_PROVIDED, '') + COALESCE(RIGHT(CONVERT(DATETIME,AVM.DIRECT_START_TIME/86399.9, 1),8), '') AS MSS_DATE_TIME, --AVM.DATE_PROVIDED MSS_DATE, AVM.DIRECT_START_TIME MSS_TIME, CASE WHEN AVM.NAME_FULL IS NULL THEN 'N/A' ELSE AVM.NAME_FULL END MSS_NAME, PPD.STATUS_CODE STATUS_CODES, PPD.TERMINATION_DATE, datediff(dd,PPD.PROSPECT_ADMIT_DATE,PPD.ADMIT_DATE) DAYS_REF_TO_ADMIT

FROM

WHERE
( PPD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2015-11-01',120) AND CONVERT(DATETIME,'2015-11-30',120) )
AND ( PPD.ADMIT_DATE IS NOT NULL )
AND ( AVS.DATE_PROVIDED BETWEEN CONVERT(DATETIME,'2015-11-01',120) AND CONVERT(DATETIME,'2015-11-30',120) )
AND ( AVM.DATE_PROVIDED IS NULL OR AVM.DATE_PROVIDED BETWEEN CONVERT(DATETIME,'2015-11-01',120) AND CONVERT(DATETIME,'2015-11-30',120) )

ORDER BY
PPD.CUSTOMER_CODE ASC

there is 2 records that date in April 2015 and the other is in Feb I believe
that are being picked up because they have the STATUS_CODE = H100
I just need them to read NULL so that the records can be picked up in the WHERE clause

I know this may sound confusing but Ill try to provide more information if I can

Thanks!,

M

2

Can you provide the table names?

1 Like
3

Hey Stephen

its not working I thought I had it here are the tables

PT_Prospect_Fix PPD
LEFT OUTER JOIN RES_BASIC RB ON
(RB.RESOURCE_ID = PPD.ASSOCIATED_FACILITY_ID)
LEFT OUTER JOIN Admit_visit_by_SN AVS ON
(AVS.CUSTOMER_ID = PPD.CUSTOMER_ID)
AND (AVS.ADMIN_SET_ID = PPD.LATEST_ADMIN_SET_ID)
LEFT OUTER JOIN Admit_visit_by_MSS AVM ON
(AVM.CUSTOMER_ID = PPD.CUSTOMER_ID)
AND (AVM.ADMIN_SET_ID = PPD.LATEST_ADMIN_SET_ID)

4

I am able to get all the records when I add (AVM.SERVICE_CODE = PPD.STATUS_CODE)
but all the fields MSS turn into NULL

FROM PT_Prospect_Fix PPD
LEFT OUTER JOIN RES_BASIC RB ON
(RB.RESOURCE_ID = PPD.ASSOCIATED_FACILITY_ID)
LEFT OUTER JOIN Admit_visit_by_SN AVS ON
(AVS.CUSTOMER_ID = PPD.CUSTOMER_ID)
AND (AVS.ADMIN_SET_ID = PPD.LATEST_ADMIN_SET_ID)
LEFT OUTER JOIN Admit_visit_by_MSS AVM ON
(AVM.CUSTOMER_ID = PPD.CUSTOMER_ID)
AND (AVM.SERVICE_CODE = PPD.STATUS_CODE)
AND (AVM.ADMIN_SET_ID = PPD.LATEST_ADMIN_SET_ID)

5

This will return the date value because it isn't NULL. To return NULL for that value change it to:

THEN NULL ELSE avm.DATE_PROVIDED END

6

Thanks for the reply Jeff
but that still didn't fix it :disappointed_relieved:

7

This is a real mess. The FROM clause is empty. We have no DDL. There is no such thing AS "something_status"; you can have a "_code" (zip_code) or a "<something in particular_status" (employment_status). The old Sybase convert () function is used by COBOL programmers to violate the tiered architecture of SQL. We have temporal data types; we do not use strings.

ISNULL () is another old Sybase function that has been replaced by COALESCE. Please think about what “ ISNULL(AVM.provision_date, NULL)” does when the provision date is NULL.

Then look at your first CASE expression:

CASE WHEN ...
THEN AVM.provision_date
ELSE AVM.provision_date
END AS mss_date

We have a DATETIME2(n), DATE and TIME(n) data types and would never split out a date and a time as separate columns from a timestamp. This is so bad it has a name – attributes splitting.

Since we DDL, I do not know what the customer identifier or key is in your data model. I will guess that it is customer_code, but a code (zip_code, for example) is not a key. You have never had a course in data modeling, and do not know the ISO 11179 naming rules, so this makes it very hard to help you.

You spend most of your time and effort doing string manipulations and not answering your query. This is because that is how you did it in COBOL! In fact, you still put the and operations at the END of the punch card so you can rearrange the deck later.

Om fact, you are so locked into procedural code you wrote:

CASE WHEN RB.organization_name IS NULL THEN 'family referral' ELSE RB.organization_name END AS organization_name,

could be:

COALESCE (RB.organization_name, 'family referral') AS organization_name,

But an SQL programmer would use DEFAULT in the DDL instead of a NULL! This is a declarative language and it does display formatting in a presentation layer.

Would you like to try again, with DDL and a cleaned up version of the query?

8

thanks for the reply
a little harsh since I am new and a great way to lower my confidence
thanks for making me depressed hope that makes you feel better

9

Don't take the response you got from Mr. Celko personal. He does that to everyone. His goal is admirable in that he wants people to think more but his delivery is a little harsh. Read what he says and take the concepts away to think about but not his tone. Not everyone will react to your posts in that manner and it shouldn't make you feel that you need to stop asking questions. We all have to learn and ignorance is not stupidity so don't stress about the reply.

1 Like
10

You would have a real hard time if you ever tried to take a programming class from Ed Dijkstra. His favorite phrase to students was "you are doing it completely wrong" and he meant it. But you know what? People learned! :scream:

Decades ago, I used to post very cushy soft responses like "W ell, perhaps 2+2 = 5 for very large values of 2 so do not feel bad." And nobody learned anything from me. So I use a "keisaku" style, on the assumption that posters are not whiny children looking for a kludge, but actually want to do things right and have a sense of professionalism.