Condition check with cross rows

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)

Condition check with cross rows

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-28 : 08:01:43

Hello everyone,

I am facing a very serious problem.

I will try my best to explain

I have two tables

create table RegMain ( RegID int, SiteID int, ResourceName varchar(10), CreatedOn datetime )

create table RegDPMALiveStatus ( RegID int, RegStatus varchar(10), UninstDate datetime )

Joining these two tables we may get the information like below

RegID ResourceName SiteID CreatedOn UninstDate

Now When any resource is created then it get inserted into
RegMain with the createdOn date
and at the same time in
RegDPMALiveStatus with UninstDate NULL

Now When any resource is uninstalled then Date is being updated in UninstDate.

Now if again same resource will be created then it get inserted into
RegMain with same resourceName and different RegID

Now i have to get all the resources which where Uninstalled and Reinstalled in the same month
which is may.
this time I have to check Uninstdate and CreatedOn or ResourceName insted of RegID as RegID will be different.

I hope i was very clear to make you understand

Please help me out..

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-28 : 10:08:11

If the regids still link the tables, then this should get you started
select main.*
from #regMain main
inner join #RegDPMALiveStatus status
on
main.regid = status.regid

where
main.created >= dateadd(month,datediff(month,0,createdon),0)
and < dateadd(month,datediff(month,0,UninstDate) + 1,0)

Jim

Everyday I learn something that somebody else already knew

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-29 : 02:45:02

I am sorry but i m not able to understand
what you have done in the query.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources