how to check one id exits for all the months

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

SQL Server 2005 Forums

Transact-SQL (2005)

how to check one id exits for all the months

Author

Topic

yogeshwari_am
Starting Member

1 Post

Posted - 2012-03-08 : 07:10:08

Hi,
I have a Payment table,in that I am storing StudID,amount and month.
For the range of months like from month 3 to 6,I would like to check that StudID has made the payment for all the months in that range.
if i write
select * from dbo.tbl_MonthlyPayment where month between 6 and 7
it also returns if the Student paid for only 6th month
Can anyone tell me how to do..
thanks in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-08 : 07:38:47

[code]-- sample table
declare @sample table (StudId int, [month] int)

-- sample data
insert @sample
select 1, 6 union all
select 1, 7 union all
select 2, 5 union all
select 2, 6 union all
select 2, 7 union all
select 2, 8 union all
select 3, 6 union all
select 4, 7

-- show sample data
select * from @sample

-- solution1
select distinct * from
(
select
StudId,
count(*) over (partition by StudId) as cnt
from @sample
where [month] in (6,7)
)dt
where cnt=2

-- solution2
select StudId
from @sample
group by StudId
having sum(case when [month] in (6,7) then 1 else 0 end)=2
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:40:48

this will give all students who has done payments on all months in range 3 to 6


select StudId
from @sample
where [month] between 3 and 6
group by StudId
having count(distinct month) = 3

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources