Help with stored procedure

[nc297]

How do I add this line to the below statement?

and cossn is null from table t16pendmvt

I want to check to make sure the cossn in the table t16pendall is not in the table t16pendmvt



select t.fo, Isnull(COUNT(t.cossn), 0)AS PendinFO1


FROM t16pendall t

left join t16pendmvt m

on t.cossn = m.cossn

where mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y')

GROUP BY t.fo

order by t.fo

 

[markros]

Try

select t.fo, Isnull(COUNT(t.cossn), 0)AS PendinFO1 


FROM t16pendall t 


where (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y')) AND not exists (select 1 from 
t16pendmvt m where m.CossN = t.CossN) 

GROUP BY t.fo

order by t.fo

PluralSight Learning Library

 

[nc297]

Thank you that worked. I want to make sure I have it right... you have select 1...

where you have AND not exists (select 1 from
t16pendmvt m where m.CossN = t.CossN)

but I placed cossn in there is that right I'm getting data back.



select t.fo, Isnull(COUNT(t.cossn), 0)AS PendinFO1


FROM t16pendall t


where (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
AND not exists (select cossn from
t16pendmvt m where m.CossN = t.CossN)

GROUP BY t.fo

order by t.fo

 

[markros]

When you use EXISTS subquery, the fields that come after SELECT clause do not matter, as EXISTS (select ...) only checks the condition, but doesn't return rows. So, you can do
IF EXISTS (select 1 from ....)

IF EXISTS (select * from ....)

IF EXISTS (select ExistingField(s) from ...)

I think I need to check what will happen if you put non-existing fields after SELECT.

I have a habit of writing

IF EXISTS (select 1 ...)

but most people use

IF EXISTS (select * ...) form.

PluralSight Learning Library

 

[markros]

If you put non-existing field in the EXISTS (select wrongField
from Table) the SQL Server complains, I just tested.

PluralSight Learning Library

 

[nc297]

Okay great thanks so much for your help. I will keep as I have it and add the cossn field in the subquery. Thanks for the explanation too!

 

[nc297]

I'm back I tried to add this:

and (( Datediff(DAY, t.flg_cdt, Getdate()) > 300 ))


but I'm getting the same results. Where do I add this to get records that are > 300 from getdate where I have it gives me the same numbers with it in or out. Thanks.


SELECT t.fo,

Isnull(COUNT(t.cossn), 0) AS PendFOover1

from t16pendall t


where mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y')

AND not exists (select cossn from

t16pendmvt m where m.CossN = t.CossN)

and (( Datediff(DAY, t.flg_cdt, Getdate()) > 300 ))


group by t.fo


order by t.fo

 

[markros]

Try

ELECT t.fo, 

Isnull(COUNT(t.cossn), 0) AS PendFOover1 

from t16pendall t 


where mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y') 

AND not exists (select cossn from 

t16pendmvt m where m.CossN = t.CossN) 

and t.flg_cdt <= dateadd(day, -300,getdate())

This is assuming you want records which are 300 and more days old.

PluralSight Learning Library

 

[nc297]

Yes that's exactly what I want so I'm guessing there's none in there. Thanks that one worked as well.

 

[markros]

Actually, I just noticed a possible problem. You have mixed AND and OR conditions in your WHERE clause. I always add () when I have OR as otherwise for me it's hard to figure out what is going to be an outcome. I suggest you to logically group conditions to make sure OR does not stand alone with multiple ANDs.

PluralSight Learning Library

 

[nc297]

So could you show me how to group them and with the ()?

 

[markros]

Most likely you need

WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y')) 

AND not exists (select cossn from 

t16pendmvt m where m.CossN = t.CossN) 

and t.flg_cdt <= dateadd(day, -300,getdate())

PluralSight Learning Library

 

[nc297]

Could you explain why you put more parenthesis around this?

WHERE (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))

Does this make it be read first and together?

 

[markros]

Yes, it means that either of these conditions should be true, but two other conditions (date condition and not exists condition) should always be evaluated in addition.

If you don't put these conditions in (), then there is a possibility that OR condition will be always evaluated and the rest will not, e.g. as long as records satisfy this condition
(MFT_POSN1_CD='a' and aged_alien_rsw='y') they will be added to the final result regardless of date and other check.

That's how I understand it. It may be that it still will work as intended (e.g. still check all conditions), but I don't want to take a chance. So, as long as you have OR in the conditions, I always do a logical grouping.

PluralSight Learning Library

 

[nc297]

Thanks so much for the explanation!!!!

 

[SimonSellick]

To enlarge on markros' explanation, the SSMS help says about AND:

"Combines two Boolean expressions and returns TRUE when both expressions are TRUE. When more than one logical operator is used in a statement, the AND operators are evaluated first. You can change the order of evaluation by using parentheses."

So the extra parentheses are necessary to force evaluation of the OR before the result is combined with the AND. You can see the effect with these two demos:

select 1
where 1 = 1 or 0 = 0 and 1 = 2

and

select 1
where (1 = 1 or 0 = 0) and 1 = 2

 

[nc297]

This is great Simon! I have a new project which entails dealing with complex queries which I have never done before. You guys have helped me a lot to understand it all. I have a few more queries which I will try to tackle first then come to you guys for help later today. ;-)

Thanks again!

 

[nc297]

Okay I have another one. I'm getting results are my parethesis correct where I have them? Am I joining the table the correct way too?

SELECT t.fo, Isnull(COUNT(m.cossn), 0) AS pendinfo2
FROM t16pendmvt m
JOIN t16pendall t
ON m.cossn = t.cossn
where ( mvt_typ = 'R' ) AND not ((mvt_loc LIKE 'R%'
or mvt_loc LIKE 'S%' OR mvt_loc LIKE 'V%' ))
GROUP BY t.fo

 

[markros]

Seems correct to me except that now you're a bit heavy on parenthesis:

SELECT t.fo, Isnull(COUNT(m.cossn), 0) AS pendinfo2 
       FROM t16pendmvt m 
       JOIN t16pendall t 
       ON m.cossn = t.cossn 
       where  mvt_typ = 'R'  AND not (mvt_loc LIKE 'R%'
       or mvt_loc LIKE 'S%' OR mvt_loc LIKE 'V%' )
       GROUP  BY t.fo

You can keep yours, of course, but to me they are redundant.


PluralSight Learning Library

 

[nc297]

Thanks again! I have another question...

My boss said this is incorrect. I shouldn't have to join the natdocfile twice in both queries only one time. If I don't join it at all then I don't get all of the records in the natdocfile.


SELECT a.doc, pendinfo1, pendinfo2

INTO #temppd


FROM

(select n.doc,Isnull(COUNT(t.cossn), 0)AS PendinFO1

FROM t16pendall t

right join natdocfile n on n.doc=t.fo

and (mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y')) and t.cossn not in(select cossn from t16pendmvt)
GROUP BY n.doc)a

join

(SELECT n.doc, Isnull(COUNT(m.cossn), 0) AS pendinfo2
FROM t16pendmvt m
JOIN t16pendall t
ON m.cossn = t.cossn
right join natdocfile n on n.doc=t.fo
and ( mvt_typ = 'R' ) AND not (mvt_loc LIKE 'R%' or mvt_loc LIKE 'S%' OR mvt_loc LIKE 'V%' )
GROUP BY n.doc)b
on a.doc = b.doc

I've joined the natdocfile because I want all records returned in that file even it's a 0 after the count is done. Is there another way to join the natdocfile at the end of the two queries or does it matter that I have it listed twice.

Another thing she doesn't like that I'm not using a where clause and using "and" instead. She said I'm doing everything on the join. Does that matter?