need help to filter down records 2

sandeep0000 · Jul 2, 2007

i am using sql server

select cs_id,net_ben_amt,max(upd_dtm) a
from cis.dbo.ag_elig_rslt ager
where
pay_mnth = '200610'
and cur_elig_ind in ('a','p')
and ager.delt_rcrd_sw = 'N'
and ager.elig_sts_cd = 'PS'
and ager.pgm_typ_cd = 'CW'
and cs_id = 'BA43183'
group by cs_id,net_ben_amt

this is my query, for this record i have a cur_elig_ind of a and p. how can i tell my query if i have 'a' then i dont care about 'p' but if i dont have 'a' then i want 'p'

sandeep0000 · Jul 2, 2007

let me know , if i explained what i needed clearly thanks

sandeep0000 · Jul 2, 2007

a= active and p = pending. so i only want active if 'a' comes up , but if 'a' doesnt come up then i would like to use 'p'.

curently im getting 2 records if a person has a person has an 'a' and 'p'

Thanks

bborissov · Jul 3, 2007

Not tested at all:

Code:

[COLOR=blue]select[/color] [COLOR=#FF00FF]ISNULL[/color](ager1.cs_id, ager.cs_id) [COLOR=blue]AS[/color] cs_id,
       [COLOR=#FF00FF]ISNULL[/color](ager1.net_ben_amt, ager.net_ben_amt) [COLOR=blue]AS[/color] net_ben_amt,
       [COLOR=#FF00FF]max[/color]([COLOR=#FF00FF]ISNULL[/color](ager1.upd_dtm,ager.upd_dtm)) a
[COLOR=blue]from[/color] cis.dbo.ag_elig_rslt ager
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] Cs_Id, net_ben_amt, upd_dtm
                  [COLOR=blue]FROM[/color] cis.dbo.ag_elig_rslt
           [COLOR=blue]where[/color] pay_mnth = [COLOR=red]'200610'[/color]
                 and cur_elig_ind = [COLOR=red]'a'[/color]
                 and ager.delt_rcrd_sw = [COLOR=red]'N'[/color]
                 and ager.elig_sts_cd = [COLOR=red]'PS'[/color]
                 and ager.pgm_typ_cd = [COLOR=red]'CW'[/color]
                 and  cs_id = [COLOR=red]'BA43183'[/color]) ager1
[COLOR=blue]where[/color] ager.pay_mnth = [COLOR=red]'200610'[/color]
      and ager.cur_elig_ind = [COLOR=red]'p'[/color]
      and ager.delt_rcrd_sw = [COLOR=red]'N'[/color]
      and ager.elig_sts_cd = [COLOR=red]'PS'[/color]
      and ager.pgm_typ_cd = [COLOR=red]'CW'[/color]
      and ager.cs_id = [COLOR=red]'BA43183'[/color]
[COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]ISNULL[/color](ager1.cs_id, ager.cs_id),
         [COLOR=#FF00FF]ISNULL[/color](ager1.net_ben_amt, ager.net_ben_amt)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

bborissov · Jul 3, 2007

On second thought I think this should be better

(again not tested)

Code:

[COLOR=blue]select[/color] [COLOR=#FF00FF]ISNULL[/color](ager.cs_id, ager1.cs_id) [COLOR=blue]AS[/color] cs_id,
       [COLOR=#FF00FF]ISNULL[/color](ager.net_ben_amt, ager1.net_ben_amt) [COLOR=blue]AS[/color] net_ben_amt,
       [COLOR=#FF00FF]max[/color]([COLOR=#FF00FF]ISNULL[/color](ager.upd_dtm,ager1.upd_dtm)) a
[COLOR=blue]from[/color] cis.dbo.ag_elig_rslt ager
[COLOR=blue]FULL[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] Cs_Id, net_ben_amt, upd_dtm
                  [COLOR=blue]FROM[/color] cis.dbo.ag_elig_rslt
           [COLOR=blue]where[/color] pay_mnth = [COLOR=red]'200610'[/color]
                 and cur_elig_ind = [COLOR=red]'p'[/color]
                 and ager.delt_rcrd_sw = [COLOR=red]'N'[/color]
                 and ager.elig_sts_cd = [COLOR=red]'PS'[/color]
                 and ager.pgm_typ_cd = [COLOR=red]'CW'[/color]
                 and  cs_id = [COLOR=red]'BA43183'[/color]) ager1
[COLOR=blue]where[/color] ager.pay_mnth = [COLOR=red]'200610'[/color]
      and ager.cur_elig_ind = [COLOR=red]'a'[/color]
      and ager.delt_rcrd_sw = [COLOR=red]'N'[/color]
      and ager.elig_sts_cd = [COLOR=red]'PS'[/color]
      and ager.pgm_typ_cd = [COLOR=red]'CW'[/color]
      and ager.cs_id = [COLOR=red]'BA43183'[/color]
[COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]ISNULL[/color](ager.cs_id, ager1.cs_id),
         [COLOR=#FF00FF]ISNULL[/color](ager.net_ben_amt, ager1.net_ben_amt)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

bborissov · Jul 3, 2007

OOPS!
I forgot ON after JOIN clause,

Add

Code:

ON ager.cs_Id = ager1.cs_id

After the JOIN

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

ss1212 · Jul 3, 2007

isnt there a way to just tell it in the where clause to do that? not as complicated?

ss1212 · Jul 3, 2007

i have a simliar problem

where does the ON ager.cs_Id = ager1.cs_id go?

ss1212 · Jul 3, 2007

so after full join

ON ager.cs_Id = ager1.cs_id ?

ss1212 · Jul 3, 2007

should it be like this ?

select ISNULL(ager.cs_id, ager1.cs_id) AS cs_id,
ISNULL(ager.net_ben_amt, ager1.net_ben_amt) AS net_ben_amt,
max(ISNULL(ager.upd_dtm,ager1.upd_dtm)) a
from cis.dbo.ag_elig_rslt ager
FULL JOIN cis.dbo.ag_elig_rslt ager1 ON ager.cs_Id = ager1.cs_id
(SELECT Cs_Id, net_ben_amt, upd_dtm
FROM cis.dbo.ag_elig_rslt
where pay_mnth = '200610'
and cur_elig_ind = 'p'
and ager.delt_rcrd_sw = 'N'
and ager.elig_sts_cd = 'PS'
and ager.pgm_typ_cd = 'CW'
and cs_id = 'BA43183') ager1
where ager.pay_mnth = '200610'
and ager.cur_elig_ind = 'a'
and ager.delt_rcrd_sw = 'N'
and ager.elig_sts_cd = 'PS'
and ager.pgm_typ_cd = 'CW'
and ager.cs_id = 'BA43183'
group by ISNULL(ager.cs_id, ager1.cs_id),
ISNULL(ager.net_ben_amt, ager1.net_ben_amt)

still gettin an error

gmmastros · Jul 3, 2007

Ahem...

How is it possible that 2 different TT members have the same problem, with the same query, including table names and column names. I find it a bit odd, and am curious for an explanation.

Care to explain?

-George

"the screen with the little boxes in the window." - Moron

Rofeu · Jul 3, 2007

Hi,

instead of

Code:

cur_elig_ind in ('a','p')

you can use

Code:

cur_elig_ind = (select MIN(cur_elig_ind) 
from cis.dbo.ag_elig_rslt ager
where 
 pay_mnth = '200610'
and cur_elig_ind in ('a','p')
and ager.delt_rcrd_sw = 'N'
and ager.elig_sts_cd = 'PS'
and ager.pgm_typ_cd = 'CW'
and  cs_id = 'BA43183'
group by cs_id,net_ben_amt))

Cheers,

Roel

ss1212 · Jul 3, 2007

oh, lol sorry, we work together here in sf county office. we been working on this report together and he took off for the 4th of july week , so they need me to finish it

gmmastros · Jul 3, 2007

This is what Boris was getting at:

Code:

[COLOR=blue]select[/color] [COLOR=#FF00FF]ISNULL[/color](ager.cs_id, ager1.cs_id) [COLOR=blue]AS[/color] cs_id,
       [COLOR=#FF00FF]ISNULL[/color](ager.net_ben_amt, ager1.net_ben_amt) [COLOR=blue]AS[/color] net_ben_amt,
       [COLOR=#FF00FF]max[/color]([COLOR=#FF00FF]ISNULL[/color](ager.upd_dtm,ager1.upd_dtm)) a
[COLOR=blue]from[/color] cis.dbo.ag_elig_rslt ager
[COLOR=blue]FULL[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] Cs_Id, net_ben_amt, upd_dtm
                  [COLOR=blue]FROM[/color] cis.dbo.ag_elig_rslt
           [COLOR=blue]where[/color] pay_mnth = [COLOR=red]'200610'[/color]
                 and cur_elig_ind = [COLOR=red]'p'[/color]
                 and ager.delt_rcrd_sw = [COLOR=red]'N'[/color]
                 and ager.elig_sts_cd = [COLOR=red]'PS'[/color]
                 and ager.pgm_typ_cd = [COLOR=red]'CW'[/color]
                 and  cs_id = [COLOR=red]'BA43183'[/color]) ager1
     [COLOR=blue]ON[/color] ager.cs_Id = ager1.cs_id
[COLOR=blue]where[/color] ager.pay_mnth = [COLOR=red]'200610'[/color]
      and ager.cur_elig_ind = [COLOR=red]'a'[/color]
      and ager.delt_rcrd_sw = [COLOR=red]'N'[/color]
      and ager.elig_sts_cd = [COLOR=red]'PS'[/color]
      and ager.pgm_typ_cd = [COLOR=red]'CW'[/color]
      and ager.cs_id = [COLOR=red]'BA43183'[/color]
[COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]ISNULL[/color](ager.cs_id, ager1.cs_id),
         [COLOR=#FF00FF]ISNULL[/color](ager.net_ben_amt, ager1.net_ben_amt)

-George

"the screen with the little boxes in the window." - Moron

ss1212 · Jul 3, 2007

i got this error with the query above

The column prefix 'ager' does not match with a table name or alias name used in the query.

Rofeu · Jul 3, 2007

Hi,

I think there's a 1 missing at the end of the first FROM clause.

Cheers,

Roel

gmmastros · Jul 3, 2007

Code:

[COLOR=blue]select[/color] [COLOR=#FF00FF]ISNULL[/color](ager.cs_id, ager1.cs_id) [COLOR=blue]AS[/color] cs_id,
       [COLOR=#FF00FF]ISNULL[/color](ager.net_ben_amt, ager1.net_ben_amt) [COLOR=blue]AS[/color] net_ben_amt,
       [COLOR=#FF00FF]max[/color]([COLOR=#FF00FF]ISNULL[/color](ager.upd_dtm,ager1.upd_dtm)) a
[COLOR=blue]from[/color] cis.dbo.ag_elig_rslt ager
[COLOR=blue]FULL[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] Cs_Id, net_ben_amt, upd_dtm
                  [COLOR=blue]FROM[/color] cis.dbo.ag_elig_rslt ager
           [COLOR=blue]where[/color] pay_mnth = [COLOR=red]'200610'[/color]
                 and cur_elig_ind = [COLOR=red]'p'[/color]
                 and ager.delt_rcrd_sw = [COLOR=red]'N'[/color]
                 and ager.elig_sts_cd = [COLOR=red]'PS'[/color]
                 and ager.pgm_typ_cd = [COLOR=red]'CW'[/color]
                 and  cs_id = [COLOR=red]'BA43183'[/color]) ager1
     [COLOR=blue]ON[/color] ager.cs_Id = ager1.cs_id
[COLOR=blue]where[/color] ager.pay_mnth = [COLOR=red]'200610'[/color]
      and ager.cur_elig_ind = [COLOR=red]'a'[/color]
      and ager.delt_rcrd_sw = [COLOR=red]'N'[/color]
      and ager.elig_sts_cd = [COLOR=red]'PS'[/color]
      and ager.pgm_typ_cd = [COLOR=red]'CW'[/color]
      and ager.cs_id = [COLOR=red]'BA43183'[/color]
[COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]ISNULL[/color](ager.cs_id, ager1.cs_id),
         [COLOR=#FF00FF]ISNULL[/color](ager.net_ben_amt, ager1.net_ben_amt)

-George

"the screen with the little boxes in the window." - Moron

bborissov · Jul 3, 2007

Thank you George for correcting that query.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

gmmastros · Jul 3, 2007

Boris,

No problem. I figured you might not be around at the moment, so I decided to pitch in a little. We all recognize how difficult it is to write a query without being able to test it. [smile]

-George

"the screen with the little boxes in the window." - Moron

ss1212 · Jul 3, 2007

thanks you two.

i really appriciate your help.
i was getting a little frustrated (not with you guys)

now i can think about the 4th, peace

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

need help to filter down records 2

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor