Link to home
Start Free TrialLog in
Avatar of biotec
biotec

asked on

Need help with part of sql query for a condition

If they had a tdap (cpt4_code = '90715') then show the date (order_date) else if not then if they had dtap = to any of these codes cpt4_code in (90696, 90697, 90698, 90700) then show the date (order_date) for that else blank.

Assume cpt4_code and order_date come from same table names vaccines. I need to select the most recent in both cases, except if they had a tdap at all ever then no need to look for dtap however if they did not have a tdap ever then we look for most recent dtap.
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Post sample data (electronic not image, so that we can cut and paste it) and its expected result you want. This will allows us to make @tempTable to test the solution hence saving lots of time for you and for the experts.

Mike
Avatar of biotec
biotec

ASKER

That's the problem, there is only a date that will show in the output. I had others help me here on converting the 3 columns that make up the date so that part is good. Problem now is that I'm only pulling the date for one of the immunizations and I need to some sort of case statement and then pull most recent date if they've had more than one dtap. The  SP is shown below.

ALTER PROCEDURE [dbo].[SVCHC_get_tdap_dtap]
@person_id          Uniqueidentifier,          
@order_date as date = '20170517' OUTPUT-- Date of order
AS
BEGIN
SELECT TOP 1 @order_date = cast(cast(io.administer_year*10000 + io.administer_month*100 + io.administer_day as varchar(255)) as date)
FROM imm_order_vaccines io
inner join imm_nor im on io.order_num = im.order_num
WHERE io.cpt4_code = '90715' and im.person_id = @person_id
ORDER BY io.administer_year desc, io.administer_month desc, io.administer_day desc
set nocount off
END

declare @order_date as date = '20170517'
exec svchc_get_tdap_dtap 'D58EC491-D71B-4C64-8701-36A2C86AF236', @order_date OUTPUT
print @Order_date

Open in new window

Avatar of Ryan Chong
If they had a tdap (cpt4_code = '90715') then show the date (order_date) else if not then if they had dtap = to any of these codes cpt4_code in (90696, 90697, 90698, 90700) then show the date (order_date) for that else blank

it seems that if cpt4_code = 90715 or any of 90696, 90697, 90698, 90700, you want to return the correspondence date (order_date), hence you may try like:

ALTER PROCEDURE [dbo].[SVCHC_get_tdap_dtap]
@person_id          Uniqueidentifier,          
@order_date date   OUTPUT-- Date of order
AS
BEGIN

SELECT TOP 1 @order_date = cast(cast(io.administer_year*10000 + io.administer_month*100 + io.administer_day as varchar(255)) as date)
FROM imm_order_vaccines io
inner join imm_nor im on io.order_num = im.order_num
WHERE io.cpt4_code in ('90715', '90696', '90697', '90698', '90700') and im.person_id = @person_id
ORDER BY io.administer_year desc, io.administer_month desc, io.administer_day desc
set nocount off

END

Open in new window

if there's isn't any record matched, @order_date should return as null
Avatar of biotec

ASKER

Thanks, this is close, however we want to first check if they had 90715 because that "trumps" (no pun intended) all others. If they had this at any time we want to show that date, but if it doesn't exist then we look for the others and try to get that order_date.
ok, so you need to check if 90715 exists in your table first, try use EXISTS in your sp:

ALTER PROCEDURE [dbo].[SVCHC_get_tdap_dtap]
@person_id          Uniqueidentifier,          
@order_date date   OUTPUT-- Date of order
AS
BEGIN

set nocount on

if exists(SELECT * FROM imm_order_vaccines io
	inner join imm_nor im on io.order_num = im.order_num
	WHERE io.cpt4_code = '90715' and im.person_id = @person_id)

	SELECT TOP 1 @order_date = cast(cast(io.administer_year*10000 + io.administer_month*100 + io.administer_day as varchar(255)) as date)
	FROM imm_order_vaccines io
	inner join imm_nor im on io.order_num = im.order_num
	WHERE io.cpt4_code = '90715' and im.person_id = @person_id
	ORDER BY io.administer_year desc, io.administer_month desc, io.administer_day desc

else

	SELECT TOP 1 @order_date = cast(cast(io.administer_year*10000 + io.administer_month*100 + io.administer_day as varchar(255)) as date)
	FROM imm_order_vaccines io
	inner join imm_nor im on io.order_num = im.order_num
	WHERE io.cpt4_code in ('90696', '90697', '90698', '90700') and im.person_id = @person_id
	ORDER BY io.administer_year desc, io.administer_month desc, io.administer_day desc

set nocount off

END

Open in new window

Avatar of biotec

ASKER

I think that's going to do it. The only thing I'd want to add is if neither condition are met then replace the blank with  'no dtap or tdap'. Do I need to go to a CASE statement to make this happen?
Avatar of biotec

ASKER

Other problem is I need to add a description as output to this but it does not let me when top 1 @order_date is set to = something. Not sure how to include these.
The date that is returned is correct but it needs to be in format yyyy/mm/dd, is that possible?
ALTER PROCEDURE [dbo].[SVCHC_get_tdap_dtap_rev4]
@person_id          Uniqueidentifier,          
@order_date date   OUTPUT,-- Date of order
@vaccinedesc varchar(255) OUTPUT
AS
BEGIN

set nocount on

if exists(SELECT * FROM imm_order_vaccines io
	inner join imm_nor im on io.order_num = im.order_num
	WHERE io.cpt4_code = '90715' and im.person_id = @person_id)

	SELECT TOP 1 @order_date = cast(cast(io.administer_year*10000 + io.administer_month*100 + io.administer_day as varchar(255)) as date)
	FROM imm_order_vaccines io
	inner join imm_nor im on io.order_num = im.order_num
	WHERE io.cpt4_code = '90715' and im.person_id = @person_id
	ORDER BY io.administer_year desc, io.administer_month desc, io.administer_day desc

else

	SELECT TOP 1 @order_date = cast(cast(io.administer_year*10000 + io.administer_month*100 + io.administer_day as varchar(255)) as date)
	FROM imm_order_vaccines io
	inner join imm_nor im on io.order_num = im.order_num
	WHERE io.cpt4_code in ('90696', '90697', '90698', '90700') and im.person_id = @person_id
	ORDER BY io.administer_year desc, io.administer_month desc, io.administer_day desc

set nocount off

END

Open in new window

Avatar of biotec

ASKER

Actually everything is working fine now except doing an else for a blank. Basically if the patient had not tdap or dtap then we have to account for that and return a '' or blank.
Actually everything is working fine now except doing an else for a blank. Basically if the patient had not tdap or dtap then we have to account for that and return a '' or blank.
how's your scripts look like now?
Avatar of biotec

ASKER

I was still hoping to return a blank or text if neither condition are satisfied.
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of biotec

ASKER

thanks!!