Need help with SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with SP

Author

Topic

Pasi
Posting Yak Master

166 Posts

Posted - 2015-03-04 : 18:58:51

HI All,

I have below SP that populates immunization into a grid. I need to add last_name, first_name from person table.

each record in dB is tied to a patient by their person_id and Person table has this as well.

All I need to tie this grid and add last_name, first_name into this grid. any help appreciated! I added 2 lines but its not working for me?
Thanks.

quote:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ngkbm_unmapped_vaccines]
@design BIT
, @userID INT
AS

BEGIN
SET NOCOUNT ON
SELECT TOP 18000 cpt4_code
, a.vaccine_desc
, brand_name
, CASE WHEN ISNULL(CAST(administer_year AS VARCHAR(4)), '0000') + (RIGHT('0'+ ISNULL(CAST(administer_month AS VARCHAR(2)), '00') ,2 ))+ (RIGHT('0'+ ISNULL(CAST(administer_day AS VARCHAR(2)), '00') ,2 )) = '00000000' THEN ''
ELSE ISNULL(CAST(administer_year AS VARCHAR(4)), '0000') + (RIGHT('0'+ ISNULL(CAST(administer_month AS VARCHAR(2)), '00') ,2 ))+ (RIGHT('0'+ ISNULL(CAST(administer_day AS VARCHAR(2)), '00') ,2 ))  END AS admin_date
, cvx_code
, lot_num
, CASE WHEN dbo.ng_get_date(expiration_date) <> '00000000' THEN dbo.ng_get_date(expiration_date)
ELSE '' END AS expiration_date
, manufacturer_name
, record_source
, strength
, dose
, vaccine_status
, vfc_code
, CASE WHEN dbo.ng_get_date(vfc_date) <> '00000000' THEN dbo.ng_get_date(vfc_date)
ELSE '' END AS VFC_date
, order_vaccine_id
, a.order_num
FROM imm_order_vaccines  a WITH(NOLOCK)
INNER JOIN imm_nor b on b.order_num=a.order_num  --Added this
inner join person p on p.person_id=b.person_id  --Added this
 WHERE ISNULL(cvx_code, 999) = 999 ORDER BY cpt4_code
SET NOCOUNT OFF
END

GO

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-05 : 04:23:20

What's not working? What do you get? What should you get?

Pasi
Posting Yak Master

166 Posts

Posted - 2015-03-05 : 11:29:53

The last_name columns fills out with 999. it looks like the join statement is not working? I added the join statement but apparently is not seeing the join statement? I may need another CASE statement but don't know how to add it or may be its just a simple modification but don't know where I put the join statement?

Thanks.

quote:
Originally posted by gbritton

What's not working? What do you get? What should you get?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-05 : 12:23:02

I do not see last_name in the query so I don't know what you mean.

Pasi
Posting Yak Master

166 Posts

Posted - 2015-03-05 : 12:48:54

Sorry I forgot to put it in the query I added it before "from"
Pasi

quote:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ngkbm_unmapped_vaccines]
@design BIT
, @userID INT
AS

BEGIN
SET NOCOUNT ON
SELECT TOP 18000 cpt4_code
, a.vaccine_desc
, brand_name
, CASE WHEN ISNULL(CAST(administer_year AS VARCHAR(4)), '0000') + (RIGHT('0'+ ISNULL(CAST(administer_month AS VARCHAR(2)), '00') ,2 ))+ (RIGHT('0'+ ISNULL(CAST(administer_day AS VARCHAR(2)), '00') ,2 )) = '00000000' THEN ''
ELSE ISNULL(CAST(administer_year AS VARCHAR(4)), '0000') + (RIGHT('0'+ ISNULL(CAST(administer_month AS VARCHAR(2)), '00') ,2 ))+ (RIGHT('0'+ ISNULL(CAST(administer_day AS VARCHAR(2)), '00') ,2 )) END AS admin_date
, cvx_code
, lot_num
, CASE WHEN dbo.ng_get_date(expiration_date) <> '00000000' THEN dbo.ng_get_date(expiration_date)
ELSE '' END AS expiration_date
, manufacturer_name
, record_source
, strength
, dose
, vaccine_status
, vfc_code
, CASE WHEN dbo.ng_get_date(vfc_date) <> '00000000' THEN dbo.ng_get_date(vfc_date)
ELSE '' END AS VFC_date
, order_vaccine_id
, a.order_num
,last_name
FROM imm_order_vaccines a WITH(NOLOCK)
INNER JOIN imm_nor b on b.order_num=a.order_num --Added this
inner join person p on p.person_id=b.person_id --Added this
WHERE ISNULL(cvx_code, 999) = 999 ORDER BY cpt4_code
SET NOCOUNT OFF
END

GO

Originally posted by gbritton

I do not see last_name in the query so I don't know what you mean.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-05 : 13:02:14

OK since you are not using two part names I can't tell what table last name comes from

Pasi
Posting Yak Master

166 Posts

Posted - 2015-03-05 : 13:14:06

last_name, first_name comes from table "person"
Pasi

quote:
Originally posted by gbritton

OK since you are not using two part names I can't tell what table last name comes from

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-05 : 13:38:16

There's nothing in your query to give you last_name=999. The value must come from the person table

Pasi
Posting Yak Master

166 Posts

Posted - 2015-03-05 : 13:41:32

I added this 2 lines:

INNER JOIN imm_nor b on b.order_num=a.order_num --Added this
inner join person p on p.person_id=b.person_id --Added this

its coming from "Where" clause" I think.

quote:
Originally posted by gbritton

There's nothing in your query to give you last_name=999. The value must come from the person table

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-05 : 15:54:00

No, the where clause filters the results. It cannot add to them

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-05 : 15:57:26

If last_name=999 in your result set, then that's what's in the Person table for the join condition/where clause combo.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Pasi
Posting Yak Master

166 Posts

Posted - 2015-03-05 : 16:13:22

NO its not in persons table and we have no last_name =999. All I am trying to do to add last name column to this grid/results table so each row has last name tied to their immunization. right now if I remove the join, all I get about 13000 records with immunization results but no last name tied to it.

I think my problem is where to put this join statement or select last_name within this SP to pull result per last_name.

quote:
Originally posted by tkizer

If last_name=999 in your result set, then that's what's in the Person table for the join condition/where clause combo.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-05 : 16:26:45

The join and the column are in the correct place.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources