Help with Stored procedure please...

[nc297]

How do I write this?

if max(juris_mvt_seq_num) and org_id in the t2juristest table not like 'p%' then use the org_id1 in the t2dibtest2 table where
max(dib_mvt_seq_num) to determine the location.

I got this to get the max(juris_mvt_seq_num) from the t2juristest table:

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

Since it's not like 'P%' I need to grab the org_id1 column in the other table (t2dibtest2) to tell me the location. How do I do that?

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'

 

[Qik3Coder]

Need more clarity on your problem.
Are you trying to return details for a specific user or are you trying to join to tables on this requirement?

If specific user, then use and IF(@myVar like 'P%')

If join do 2 separate queries and UNION the result set.

HTH,
Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[nc297]

For a specific user I don't know how to write the If statement please help.

Delete tables as I had the datatypes messed up use this one instead.

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'

Thanks

 

[nc297]

This is what I want to 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

 

[Qik3Coder]

I didn't have much test data, but try this:

Select Doc, CLM, [COS], App_rcpdt, Dib_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, Location
FROM(
Select ROW_NUMBER() OVER(Partition BY d.DOC, d.CLM, d.[COS], d.App_RCPDT, MAX(DIB_MVT_SEQ_NUM), LOREC4, d.ORG_ID1 ORDER BY Juris_mvt_typ) RowNum,
Doc, d.CLM, d.[COS], App_rcpdt, MAX(Dib_mvt_seq_num) Dib_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, 
CASE WHEN j.ORG_ID not like 'P%' 
	THEN CASE WHEN d.org_id1 like 'S%' 
		THEN 'DDS' 
		ELSE 'FO' END 
	ELSE j.ORG_ID END Location 
from (	SELECT CLM, COS, Max(DIB_MVT_SEQ_NUM) DIB 
		FROM T2dibTest2 d2 
		WHERE CLM = @SpecificUser
		GROUP BY CLM, COS) as d1
JOIN T2dibtest2 d ON
	d1.CLM = d.CLM
	AND d1.COS = d.COS
	AND d1.DIB = d.DIB_MVT_SEQ_NUM
JOIN T2Juristest j ON
	d.CLM = j.CLM
	AND d.COS = j.COS
GROUP BY DOC, d.CLM, d.[COS], d.App_rcpdt, Lorec4, d.Org_ID1, j.org_id, juris_mvt_typ
) as A WHERE RowNum = 1

HTH,
Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[nc297]

THANK YOU!!! This works perfectly.

I added a couple more records try it.

insert into T2dibtest2
Select '593', '004589', '004589', '2011-04-28', '2', 'S86','001', 'R' union all
Select '593', '004589', '004589', '2011-04-28', '1', 'S86', '001', 'T'


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

Thanks so much for this.

 

[Qik3Coder]

The sorting on the letters is a little shakey, the closest you can get is apha ascending/descending. If you wind up needing more than that, you're going to have to put the values in a "code" table with the sort order specified.

Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[nc297]

Okay I will take a look at it in the morning and apply it to the orginial table to see how it works out.

Thanks!

 

[nc297]

Thanks this works perfectly!!!!!!

 

[nc297]

I'm back again...

I want to add the cos together and make that column called pendinpsc. I added this:

Select Doc, CLM, isnull(count([COS]),0) App_rcpdt, Dib_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, Location
FROM(
Select ROW_NUMBER() OVER(Partition BY d.DOC, d.CLM, d.[COS], d.App_RCPDT, MAX(DIB_MVT_SEQ_NUM), LOREC4, d.ORG_ID1 ORDER BY Juris_mvt_typ) RowNum,
Doc, d.CLM, d.[COS], App_rcpdt, MAX(Dib_mvt_seq_num) Dib_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ,
CASE WHEN j.ORG_ID not like 'P%'
THEN CASE WHEN d.org_id1 like 'S%'
THEN 'DDS'
ELSE 'FO' END
ELSE j.ORG_ID END Location
from ( SELECT CLM, COS, Max(DIB_MVT_SEQ_NUM) DIB
FROM T2dibTest2 d2
WHERE CLM = @SpecificUser
GROUP BY CLM, COS) as d1
JOIN T2dibtest2 d ON
d1.CLM = d.CLM
AND d1.COS = d.COS
AND d1.DIB = d.DIB_MVT_SEQ_NUM
JOIN T2Juristest j ON
d.CLM = j.CLM
AND d.COS = j.COS
GROUP BY DOC, d.CLM, d.[COS], d.App_rcpdt, Lorec4, d.Org_ID1, j.org_id, juris_mvt_typ
) as A WHERE RowNum = 1

But I'm getting this error message:

Column 'A.Doc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I tried to place a.doc in the group by but it won't work. What am I missing?

 

[Qik3Coder]

You added a AGGREGATING (count) function to the "a" resultset. this means any columns not in aggregate functions need to be listed in a GROUP BY pertaining to the query.

...
 AND d.COS = j.COSGROUP BY DOC, d.CLM, d.[COS], d.App_rcpdt, Lorec4, d.Org_ID1, j.org_id, juris_mvt_typ
) as A WHERE RowNum = 1
[GREEN][B]GROUP BY A.Doc,[/B][/GREEN] ...

Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this "[red]there was an error[/red]" [black]crap[/black]

 

[nc297]

Thank you again!!!!

 

[Qik3Coder]

I'm gonna learns you some seequals yet.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this "[red]there was an error[/red]" [black]crap[/black]

 

[nc297]

Yes you are I'm learning for sure. Thanks for answering my questions.

 

[nc297]

I'm back with another question. How do I add this

select clm, max(juris_mvt_seq_num) as juris_mvt_seq_num
From t2pendjuris j
Group by clm


To this query below?

Select Doc, clm, isnull(count([Clm]),0) as pendinpsc, App_rcpdt, Dib_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, Location
FROM(
Select ROW_NUMBER() OVER(Partition BY d.DOC, d.CLM, d.App_RCPDT, MAX(DIB_MVT_SEQ_NUM), LOREC4, d.ORG_ID1 ORDER BY Juris_mvt_typ) RowNum,
Doc, d.CLM, App_rcpdt, MAX(Dib_mvt_seq_num) Dib_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ,
CASE WHEN j.ORG_ID not like 'P%'
THEN CASE WHEN d.org_id1 like 'S%' or d.org_id1 like 'R%' or d.org_id1 like 'P%'
THEN 'DDS'
ELSE 'FO' END
ELSE j.ORG_ID END Location
from (SELECT CLM, Max(DIB_MVT_SEQ_NUM) DIB
FROM T2dibPend d2
GROUP BY CLM) as d1
JOIN T2dibPend d ON
d1.CLM = d.CLM
AND d1.DIB = d.DIB_MVT_SEQ_NUM
JOIN T2PendJuris j ON
d.CLM = j.CLM
GROUP BY DOC, d.CLM, d.App_rcpdt, Lorec4, d.Org_ID1, j.org_id, juris_mvt_typ
) as A WHERE RowNum = 1 and location like 'p%'
GROUP BY A.Doc, a.clm, a.App_rcpdt, a.Dib_mvt_seq_num, a.Lorec4, a.Org_ID1, a.juris_mvt_typ, a.Location


I have this listed above:

SELECT CLM, Max(DIB_MVT_SEQ_NUM) DIB
FROM T2dibPend d2
GROUP BY CLM

I don't know where to add this:

select clm, max(juris_mvt_seq_num) as juris_mvt_seq_num
From t2pendjuris j
Group by clm

should I do a union?

 

[nc297]

Okay I noticed I need to add max(juris_mvt_seq_num) to the first and second select statement

I need help adding the max statement to the query please


Select Doc, clmssn, isnull(count([Clmssn]),0) as pendinpsc, App_rcpdt, Dib_mvt_seq_num, juris_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, Location
FROM(
Select ROW_NUMBER() OVER(Partition BY d.DOC, d.CLMSSN, d.App_RCPDT, MAX(DIB_MVT_SEQ_NUM), MAX(JURIS_MVT_SEQ_NUM), LOREC4, d.ORG_ID1 ORDER BY Juris_mvt_typ) RowNum,
Doc, d.CLMSSN, App_rcpdt, MAX(Dib_mvt_seq_num) Dib_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ,
CASE WHEN j.ORG_ID not like 'P%'
THEN CASE WHEN d.org_id1 like 'S%' or d.org_id1 like 'R%' or d.org_id1 like 'P%'
THEN 'DDS'
ELSE 'FO' END
ELSE j.ORG_ID END Location
from (SELECT CLMSSN, Max(DIB_MVT_SEQ_NUM) DIB
FROM T2dibPend d2
-- WHERE CLM = '001257'
GROUP BY CLMSSN) as d1
JOIN T2dibPend d ON
d1.CLMSSN = d.CLMSSN
AND d1.DIB = d.DIB_MVT_SEQ_NUM
JOIN T2PendJuris j ON
d.CLMSSN = j.CLMSSN
GROUP BY DOC, d.CLMSSN, d.App_rcpdt, Lorec4, d.Org_ID1, j.org_id, juris_mvt_typ
) as A WHERE RowNum = 1 and location like 'p%'
GROUP BY A.Doc, a.clmssn, a.App_rcpdt, a.Dib_mvt_seq_num, a.Lorec4, a.Org_ID1, a.juris_mvt_typ, a.Location

 

[nc297]

Is the bold portion correct?


Select Doc, clm, isnull(count([Clm]),0) as pendinpsc, App_rcpdt, Dib_mvt_seq_num, juris_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, Location
FROM(
Select ROW_NUMBER() OVER(Partition BY d.DOC, d.CLM, d.App_RCPDT, MAX(DIB_MVT_SEQ_NUM), LOREC4, d.ORG_ID1 ORDER BY Juris_mvt_typ) RowNum,
Doc, d.CLM, App_rcpdt, MAX(Dib_mvt_seq_num) Dib_mvt_seq_num, MAX(juris_mvt_seq_num) juris_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ,
CASE WHEN j.ORG_ID not like 'P%'
THEN CASE WHEN d.org_id1 like 'S%' or d.org_id1 like 'R%' or d.org_id1 like 'P%'
THEN 'DDS'
ELSE 'FO' END
ELSE j.ORG_ID END Location
from (SELECT CLM, Max(DIB_MVT_SEQ_NUM) DIB
FROM T2dibPend d2
-- WHERE CLM = '001257'
GROUP BY CLMSSN) as d1
JOIN T2dibPend d ON
d1.CLM = d.CLM
AND d1.DIB = d.DIB_MVT_SEQ_NUM
Inner JOIN (select clm, max (juris_mvt_seq_num) as juris_mvt_seq_num
From T2PendJuris
Group by clm) j
ON d.CLM = j.CLM
GROUP BY DOC, d.CLM, d.App_rcpdt, Lorec4, d.Org_ID1, j.org_id, juris_mvt_typ
) as A WHERE RowNum = 1 and location like 'p%'
GROUP BY A.Doc, a.clm, a.App_rcpdt, a.Dib_mvt_seq_num, a.juris_mvt,seq_num, a.Lorec4, a.Org_ID1, a.juris_mvt_typ, a.Location

 

[Qik3Coder]

nc297,

You have the Max already defined in the subquery (j), so you don't need it in the inner query(a).

Doing so will work, and probably give you the same answer.
What you have now is equivalent to:

Raw results = {5|6|7|8|9}
subquery max results = {9|9|9|9|9}
inner max results = {9|9|9|9|9}


Remove the max from the inner and add the column to the group by.

You've got questions and source code. We want both!

 

[nc297]

Thanks Qik3Coder for getting back to me. I'm sorry I don't understand what you are referring too. Get rid of the A below which I highlighted?

Select Doc, clm, isnull(count([Clm]),0) as pendinpsc, App_rcpdt, Dib_mvt_seq_num, juris_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ, Location
FROM(
Select ROW_NUMBER() OVER(Partition BY d.DOC, d.CLM, d.App_RCPDT, MAX(DIB_MVT_SEQ_NUM), LOREC4, d.ORG_ID1 ORDER BY Juris_mvt_typ) RowNum,
Doc, d.CLM, App_rcpdt, MAX(Dib_mvt_seq_num) Dib_mvt_seq_num, MAX(juris_mvt_seq_num) juris_mvt_seq_num, Lorec4, Org_ID1, juris_mvt_typ,
CASE WHEN j.ORG_ID not like 'P%'
THEN CASE WHEN d.org_id1 like 'S%' or d.org_id1 like 'R%' or d.org_id1 like 'P%'
THEN 'DDS'
ELSE 'FO' END
ELSE j.ORG_ID END Location
from (SELECT CLM, Max(DIB_MVT_SEQ_NUM) DIB
FROM T2dibPend d2
-- WHERE CLM = '001257'
GROUP BY CLMSSN) as d1
JOIN T2dibPend d ON
d1.CLM = d.CLM
AND d1.DIB = d.DIB_MVT_SEQ_NUM
Inner JOIN (select clm, max (juris_mvt_seq_num) as juris_mvt_seq_num
From T2PendJuris
Group by clm) j
ON d.CLM = j.CLM
GROUP BY DOC, d.CLM, d.App_rcpdt, Lorec4, d.Org_ID1, j.org_id, juris_mvt_typ
) as A WHERE RowNum = 1 and location like 'p%'
GROUP BY A.Doc, a.clm, a.App_rcpdt, a.Dib_mvt_seq_num, a.juris_mvt,seq_num, a.Lorec4, a.Org_ID1, a.juris_mvt_typ, a.Location

 

[Qik3Coder]

No, you need the A.
Ignore my last comment. You do not have table aliases on all the tables, so i missed that you have a couple dib_mvt_seq_num references. I was suggesting that you didn't need the max on projection list for the from (...) as a query, and you probably don't. If the code works though, you might as well just leave it.

Lod


You've got questions and source code. We want both!