unmatched records from two tables with conditions

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)

unmatched records from two tables with conditions

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2008-12-11 : 16:05:09

I have this unique situation
I have two tables X and Y both with the data elements BillingID,Balance,PeriodEndDate for ex

Table X

BillingID,Balance,PeriodEndDate,pr
123,50,10/31/08,1
123,-150,10/31/08,2
456,100,10/31/08,1
123,100,11/31/08,2
789,100,11/31/08,1

Table Y
BillingID,Balance,PeriodEndDate,pr
123,200,10/31/08,2
432,100,11/31/08,2
498,200,10/31/08,1
498,100,10/31/08,2

The final result i need is distinct BillingID from Table X which are not there in Table Y for the same periodEndDate(for ex 10/31/07 for both table x and table Y) and then group by BillingID
and having sumofthier balances in a given table >0.

I tried alot but not sucessfull.
Please suggest.
Thanks,

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-11 : 17:04:48

select billindID from X
where not exists (select 1 from Y where x.billingID = y.billingID and x.periodEnddate = y.periodenddate)
group by billindID having sum(balance) > 0

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-11 : 18:29:38

quote:
Originally posted by hanbingl

select billindID from X
where not exists (select 1 from Y where x.billingID = y.billingID and x.periodEnddate = y.periodenddate)
group by billindID having sum(balance) > 0

This will be faster than Left join with larger resultsets.

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2008-12-11 : 18:53:32

Thanks Hanbingl & Sodeep

I am confused what does select 1 in where not exists (select 1 from Y.............. do? is it 1 or something else. Please let me know.

THanks,

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-12 : 03:38:20

select 1 from Y where x.billingID = y.billingID and x.periodEnddate = y.periodenddate

If there are any records in your table for the combination of billingid and periodenddate
For ex let us say that there are 2 records in your table for the above combination

Then the o/p is
1
1

It simply returns 1 thats it.

Jai Krishna

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2008-12-12 : 09:47:00

Thanks jaikrishna for the clarification.
i have an other issue now the periodenddate should be same for both the tables like previously and should run the query for a specific date and when added an other where condition X.Periodenddate = '10/31/08' it is not giving a right number of records.

can u suggest.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 09:50:47

[code]select billindID from X
where not exists (select 1 from Y where x.billingID = y.billingID and x.periodEnddate = y.periodenddate and Periodenddate = '10/31/08')
group by billindID having sum(balance) > 0
[/code]

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2008-12-12 : 10:30:56

Thanks Visakh; but still the resultset is huge it is giving only for the date selected but it is doing it for every date in the table. like i have for 10/31/08 in Table X a total of 47,000 records and for the same date table Y has 15,000 records. so the result set should not exceed 47,000 right? but using the above listed query it is resulting 459,832 records.

Please suggest.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-12 : 10:35:10

so you want only those records from your main table which are on 10/31/08 and does not have corresponding record on second table?

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2008-12-12 : 10:40:34

Yes; but the period end date should be 10/31/08 in the table Y too.
to summarize i need the records from table x which are not present in table y where the periodenddate on both the tables is same and for a specific periodenddate (like 10/31/08) then group by billingid on table x with a having sum(balance) > 0.

Thanks for all ur help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 00:57:10

[code]select x.billindID from X
left join Y
ON x.billingID = y.billingID
and x.periodEnddate = y.periodenddate
and y.Periodenddate = '10/31/08'
WHERE y.billingID IS NULL
group by x.billindID
having sum(x.balance) > 0[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources