How Can I Compere A Value In Each Row In A Table

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 2000 Forums

Transact-SQL (2000)

How Can I Compere A Value In Each Row In A Table

Author

Topic

azharrahi
Starting Member

44 Posts

Posted - 2007-01-27 : 02:45:38

Hello .. Hope You all will be fine
Suppose a date Value = '1/12/2007'
I have a table 'PremiumReceived' Having A column ReceivingDate(As date)
Now The Table Has Multiple Rows
I want To Compare The Value = '1/12/2007' With Each Row Of 'ReceivingDate' Column of 'PremiumReceived' Table In a stored Procedure. If The Value Exist return True Else False
I am waiting for urgent reply. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-27 : 02:53:52

[code]
if exists (select * from PremiumReceived where ReceivingDate = '20070112')
begin
print 'Exist'
end
else
begin
print 'Not Exist'
end
[/code]

azharrahi
Starting Member

44 Posts

Posted - 2007-01-27 : 03:15:39

Thanks KH.. The above query results fine
Can u help me more ....
In the same table 'PremiumReceived', A person pay an amount on date '2/2/2003', then he comes on date '2/2/2005' and pay, then he comes on date '2/2/2007' and pay.
Now there are 2 years when he does not come for pay ... i.e 2004 and 2006
I want to retrieve these two years when he does not pay
Tell me the query how can I retrieve these two years..

Azhar Rahi
Software Engineer
Eye4tech Pvt Ltd,Lahore
Pakistan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-27 : 03:18:34

can you post the table structure, some sample data and the result that you want ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-27 : 03:25:33

try this...


declare @table table
(
	ReceivingDate	datetime
)

insert into @table
select	'20030220'	union all
select	'20050220'	union all
select	'20070220'	

select [year]
from
(
	select [year] = 2003	union all
	select [year] = 2004	union all
	select [year] = 2005	union all
	select [year] = 2006	union all
	select [year] = 2007
) y left join @table t
on	y.[year]	= year(ReceivingDate)
where	t.ReceivingDate	is null

azharrahi
Starting Member

44 Posts

Posted - 2007-01-27 : 03:51:08

The Table structure is
ID IID PolicyNo ReceivingDate PeriodFrom PeriodTo PremiumPaid
1 4 LI333333333LHA 10/11/2006 1:49:13 PM 10/1/2006 10/8/2006 100
2 1 LI123456789LHA 10/8/2005 7:49:23 PM 9/1/1998 8/1/1999 100
3 2 LI111111111LHA 10/8/2006 7:49:23 PM 9/1/2006 9/1/2006 200
4 1 LI123456789LHA 10/8/2006 7:49:23 PM 9/1/2000 9/1/2001 100
5 1 LI123456789LHA 10/7/2006 7:49:23 PM 9/1/2001 8/1/2002 100
6 1 LI123456789LHA 9/8/2006 7:49:23 PM 9/1/2003 7/1/2005 100
7 1 LI123456789LHA 10/8/2006 7:49:23 PM 6/1/2007 6/1/2008 100

Now Look At PeriodFrom , and PeriodTo Columns against PolicyNo = 'LI123456789LHA'
There are 3 years When The Policy Holder does not come to pay ... i.e 9/1/1999 to 8/1/2000, 9/1/2005 to 9/1/2006 And 9/1/2006 to 8/1/2007 ...You may find some years from 2004 to 2005 Not Included in PeriodFrom Column ... But They are Included In PeriodTo ... because a policy Holder can pay advance amount for next years.... So NonPayment Years will be that Which are not included in period from and periodto Columns.. Thanks

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-27 : 04:51:20

[code]
select [year]
from
(
select [year] = 2003 union all
select [year] = 2004 union all
select [year] = 2005 union all
select [year] = 2006 union all
select [year] = 2007
) y left join yourtable t
on y.[year] = year(ReceivingDate)
where t.ReceivingDate is null
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources