Query - Retreival based on condition

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

Query - Retreival based on condition

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-10-06 : 23:13:05

Need help with the below example

Sample Data

Idenret Sourcesys Customerid revivaldt
1 mbill 123456 01/01/2010
2 cbill 123456 01/01/2010
3 mbill 987654 01/01/2010
4 cbill 987654 01/02/2010
5 cbill 654321 01/01/2010
6 mbill 654321 01/01/2010
7 cbill 975310 12/31/2010
8 cbill 124578 12/31/2010
9 mbill 975310 12/31/2010
10 mbill 124578 12/12/2011
11 cbill 888888 1/1/2011

I have to retreive all the customerids from the above table where a customerid has to be part of mbill and cbill and also have the same
revivaldt

so The query should return

Idenret Sourcesys Customerid revivaldt
1 mbill 123456 01/01/2010
2 cbill 123456 01/01/2010
5 cbill 654321 01/01/2010
6 mbill 654321 01/01/2010
7 cbill 975310 12/31/2010
9 mbill 975310 12/31/2010

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-06 : 23:23:33

[code]
select d.*
from (
select Customerid, revivaldt
from sample_data
group by Customerid, revivaldt
having min (Sourcesys) = 'cbill'
and max (Sourcesys) = 'mbill'
) a
inner join sample_data d on a.Customerid = d.Customerid
and a.revivaldt = d.revivaldt
order by d.Idenret
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 01:28:24

[code]select Idenret, Sourcesys, Customerid, revivaldt
from
(
select Idenret, Sourcesys, Customerid, revivaldt,
count(case when Sourcesys ='mbill' then 1 else null end) over (partition by Customerid, revivaldt) as mbillcnt,
count(case when Sourcesys ='cbill' then 1 else null end) over (partition by Customerid, revivaldt) as cbillcnt
)t
where mbillcnt>0
and cbillcnt>0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources