Need help with stored procedure...

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 stored procedure...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-18 : 10:12:27

How would I write a stored procedure to get this...

select max(juris_mvt_seq_num) as Juris_mvt_seq_num, cos
from t2juristest
where org_id not like 'p%'
group by cos

If any come up then go into the t2dibest2 table and use the max(dib_mvt_seq_num) for that cos

So for this example, 2 is the max(juris_mvt_seq_num) in table t2juristest, but since it's not like 'p%' in the org_id field , I need to go into the t2dibtest table and use the max(dib_mvt_seq_num) of that cos.

Here's the table info I think if you install all you will see what I'm talking about...I hope


CREATE TABLE [dbo].[T2dibtest2](
[T2id] [int] IDENTITY(1,1) NOT NULL,
[DOC] [varchar](3) NULL,
[CLM] [varchar](6) NULL,
[COS] [varchar](6) NULL,
[APP_RCPDT] [datetime] NULL,
[DIB_MVT_SEQ_NUM] [smallint] NULL,
[LOREC4] [char](3) NULL,
[ORG_ID1] [char](3) NULL,
[DIB_MVT_TYP] [char] (1) NULL)
 
Insert into T2dibtest2
Select '593', '001257', '001257', '2011-04-28', '4', 'S86','S86', 'R' union all
Select '593', '001257', '001257', '2011-04-28', '3', 'S86', 'S07', 'T' union all
Select '593', '001257', '001257', '2011-04-28', '2', 'S86', 'S07', 'R' union all
Select '593', '001257', '001257', '2011-04-28', '1', 'S86', '093', 'T'


CREATE TABLE [dbo].[T2Juristest](
[CLM] [char](6) NOT NULL,
[COS] [char](6) NOT NULL,
[JURIS_MVT_SEQ_NUM] [smallint] NOT NULL,
[ORG_ID] [varchar](3) NOT NULL,
[JURIS_MVT_TYP] [char](1) NOT NULL,
[t2jurisid] [int] IDENTITY(1,1) NOT NULL
)

 
Insert into T2Juristest
Select '001257', '001257', '1', '093', 'T' union all
Select '001257', '001257', '2', '539', 'R'

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 12:27:54

Is there a relationship between the tables we can Join on?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-18 : 13:04:33

Yes the cos field is in both tables.

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 13:31:32

holy sheet...how does it know not to insert into the identity column without a column list?

Is this a M$ "Enhancement" for developers who have been doing it wrong all these years?

EDIT: Also, I don't think you would join on COS..ORG_ID maybe?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-18 : 13:51:04

I'm sorry please delete the tables info and use these instead:

Now you can join on COS and Org_id and Org_id1


CREATE TABLE [dbo].[T2dibtest2](
[T2id] [int] IDENTITY(1,1) NOT NULL,
[DOC] [varchar](3) NULL,
[CLM] [varchar](6) NULL,
[COS] [varchar](6) NULL,
[APP_RCPDT] [datetime] NULL,
[DIB_MVT_SEQ_NUM] [smallint] NULL,
[LOREC4] [char](3) NULL,
[ORG_ID1] [char](3) NULL,
[DIB_MVT_TYP] [char] (1) NULL)
 
Insert into T2dibtest2
Select '593', '001257', '001257', '2011-04-28', '4', 'S86','S86', 'R' union all
Select '593', '001257', '001257', '2011-04-28', '3', 'S86', 'S07', 'T' union all
Select '593', '001257', '001257', '2011-04-28', '2', 'S86', 'S07', 'R' union all
Select '593', '001257', '001257', '2011-04-28', '1', 'S86', '093', 'T'


CREATE TABLE [dbo].[T2Juristest](
[CLM] [varchar](6) NOT NULL,
[COS] [varchar](6) NOT NULL,
[JURIS_MVT_SEQ_NUM] [smallint] NOT NULL,
[ORG_ID] [char](3) NOT NULL,
[JURIS_MVT_TYP] [char](1) NOT NULL,
[t2jurisid] [int] IDENTITY(1,1) NOT NULL
)

 
Insert into T2Juristest
Select '001257', '001257', '1', '093', 'T' union all
Select '001257', '001257', '2', '539', 'R'

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 15:47:23

so what is your expected results?

Can you post a result set you expect to see?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-18 : 19:20:31

This is what I should see for the outcome.


Doc    CLM   COS   App_rcpdt  Dib_mvt_seq _num Lorec4   Org_ID1   Dib_mvt_typ Location
593  001257 001257  4/28/11      4            S86         S86          R       DDS

Can I do a case statement?

select max(juris_mvt_typ) as maxTyp
case
when org_id not like 'p%' then
go into the t2dibtest2 table and grab the org_id1 and output that into a field called Location
end as Location, list the other columns in the t2dibtest2 table too.

Basically I want to check the first table t2juristest to get the max(juris_mvt_typ) for that cos if the org_id field doesn't start the letter P then look at the t2dibtest2 table and grab the max(DIB_MVT_SEQ_NUM) for that cos to determine if it's a FO or DDS. If org_id1 like 's%' then it's location is DDS if org_id1 like 't%' it's location is FO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources