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.
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.
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
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
if there's isn't any record matched, @order_date should return as null
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
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?
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?
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
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?
ASKER
I was still hoping to return a blank or text if neither condition are satisfied.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks!!
Mike