Problem with Join Condition - Kind of Lost..!!

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

SQL Server Development (2000)

Problem with Join Condition - Kind of Lost..!!

Author

Topic

Ravi0435
Starting Member

47 Posts

Posted - 2009-01-16 : 10:54:48

Hi,
Need to pull a report:

Table Date

Subscriber_Id FirstName LastName Worklocation change_Type change_date_time
------------- ---------- -------- ------------- ----------- -----------------
100 -------- Sam ----- des -------- AUS -------- T ------- 2009-01-10
100 -------- Sam ----- des -------- USA -------- A ------- 2009-01-14
101 -------- Dan ----- dap -------- CHI -------- T ------- 2009-01-11
101 -------- Dan ----- dap -------- GBR -------- U ------- 2009-01-14
102 -------- Jack ----- jil -------- CAN -------- A ------- 2009-01-14
099 -------- vik ----- pau -------- BRA -------- T ------- 2008-07-18
077 -------- jes ----- mul -------- IND -------- T ------- 2008-05-20

(7 row(s) affected)

Report needs Active Members (A & U) and
termintated (T) members in the last 30 days (not beyond that - there are 2 records in the above which shudn't be there)
If the members change worklocation then they are terminated and then added with new worklocation but the Id remains the same as old one jus the worklocation changes to new one.

Subscriber_Id FirstName LastName Worklocation change_Type change_date_time Old_Worklocation
------------- ---------- -------- ------------- ----------- ----------------- ---------------
100 -------- Sam ----- des -------- AUS -------- T ------- 2009-01-10 ------ NULL
100 -------- Sam ----- des -------- USA -------- A ------- 2009-01-14 ------ AUS
101 -------- Dan ----- dap -------- CHI -------- T ------- 2009-01-11 ------ NULL
101 -------- Dan ----- dap -------- GBR -------- U ------- 2009-01-14 ------ CHI
102 -------- Jack ----- jil -------- CAN -------- A ------- 2009-01-14 ------ NULL

Query Trying to Execute:

Select A.Subscriber_Id,A.FirstName,A.Worklocation,A.change_date_time,B.Worklocation as Old_work
From dbo.Testing A
LEFT JOIN
(Select DD.Subscriber_Id,
DD.FirstName,
DD.LastName,
DD.Worklocation,
DD.change_Type,
DD.change_date_time
From dbo.Testing DD,
dbo.Testing ZZ
Where DD.Subscriber_Id = ZZ.Subscriber_Id
AND DD.change_type = 'T'
And DD.Worklocation <> ZZ.Worklocation ) B
ON A.Subscriber_Id = B.Subscriber_Id
And A.change_type <> 'T'

--It works untill here the below condition doesnt work, i dont understand at all ..all i want to do is from the above results eliminate the records with below condition....the one below that also doesnt work.

And DATEDIFF(day, A.change_date_time, getdate()) < 30

--This one doesnt work either
And A.Subscriber_Id not in ( Select Z.Subscriber_Id From dbo.Testing Z
Where Z.change_type = 'T'
And DATEDIFF(day, Z.change_date_time, getdate()) > 30)

Any kind of help/suggestion wud be really appreciated.

thanks,
Ravi

Nothing much that i can do..!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 11:22:36

[code]
SELECT Subscriber_Id, FirstName, LastName, Worklocation, change_Type, change_date_time
FROM Table
WHERE change_Type IN ('A','U')
OR change_date_time>DATEADD(dd,DATEDIFF(dd,0,GETDATE())-30,0)
[/code]

Ravi0435
Starting Member

47 Posts

Posted - 2009-01-16 : 12:03:44

Hello all:

Thanks for those who spent time looking at this....anyways i got the solution:

Select A.Subscriber_Id,
A.FirstName,
A.LastName,
A.Worklocation as Current_Worklocation ,
A.change_date_time,
A.change_type,
Old_Worklocation = (Select distinct Z.Worklocation from dbo.Testing Z
Where A.Subscriber_Id = Z.Subscriber_Id
And Z.change_type = 'T')
FROM dbo.Testing A
Where A.change_type <> 'T'

UNION
Select distinct B.Subscriber_Id,
B.FirstName,
B.LastName,
B.Worklocation as Current_Worklocation,
B.change_date_time,
B.change_type,
Old_Worklocation = NULL
FROM dbo.Testing A, dbo.Testing B
Where A.Subscriber_Id = B.Subscriber_Id
And B.change_type = 'T'
And DATEDIFF(day, B.change_date_time, getdate()) < 30

Although the report requirement was little diff and much easier than i expected...which i came to know very late.

thanks though.

Ravi

Nothing much that i can do..!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 12:08:59

what was problem with query i provided? ALso in last query you dont require distinct as union itself takes distinct combination of values returned.

Ravi0435
Starting Member

47 Posts

Posted - 2009-01-16 : 12:23:47

No visakh16 there was no problem....i didnt check that ...before checking the thread for replies i got my solution....yeah thanks for the suggestion i do not need DISTINCT.

thanks,
R

Nothing much that i can do..!!

Ravi0435
Starting Member

47 Posts

Posted - 2009-01-16 : 12:23:48

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 12:57:39

quote:
Originally posted by Ravi0435

No visakh16 there was no problem....i didnt check that ...before checking the thread for replies i got my solution....yeah thanks for the suggestion i do not need DISTINCT.

thanks,
R

Nothing much that i can do..!!

ok.. just check mine too and in case it gives you what you want, compare yours with mine and take the faster solution. also post back comparison result if you tested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources