Link to home
Start Free TrialLog in
Avatar of Mayank Tripathi
Mayank Tripathi

asked on

Please help with the below query - SQL Server

TableA - Query result pasted below :

Please have a look below select statement where i am using EXCEPT keyword. but something goes wrong... i'm expecting 12 rows but somehow i am getting only 10 rows .

TableB contains no rows.

Select  ClientKey,CarbonLocationKey LK ,c.[Location Size] LSize,c.[Location Size Effective From*] EffFrom ,1 IsCurrent,Getdate()F,Getdate()T,'I' Type
            from TableA
            EXCEPT
            Select   ClientKey,CarbonLocationKey LK ,c.[Location Size] LSize,c.[Location Size Effective From*] EffFrom ,1 IsCurrent,Getdate()F,Getdate()T,'I' Type
            from TableB



Table A
ClientKey      LK      LSize      EffFrom      IsCurrent      F      T      Type
1      419      44352.64995      00:00.0      1      33:22.0      33:22.0      I
1      420      24289.17091      00:00.0      1      33:22.0      33:22.0      I
1      421      1898203      00:00.0      1      33:22.0      33:22.0      I
1      422      398211      00:00.0      1      33:22.0      33:22.0      I
1      423      71886.70615      00:00.0      1      33:22.0      33:22.0      I
1      424      53507.3469      00:00.0      1      33:22.0      33:22.0      I
1      425      55218.807      00:00.0      1      33:22.0      33:22.0      I
1      426      65682.39419      00:00.0      1      33:22.0      33:22.0      I
1      427      24289.17091      00:00.0      1      33:22.0      33:22.0      I
1      428      16736.46519      00:00.0      1      33:22.0      33:22.0      I
1      429      38247.90407      00:00.0      1      33:22.0      33:22.0      I
1      430      16736.46519      00:00.0      1      33:22.0      33:22.0      I

Thanks,
Mayank
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Except will give you duplicates.
Avatar of Mayank Tripathi
Mayank Tripathi

ASKER

No Pawan. all are unique
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vitor Montalvão
Which are the 2 extra rows that you're expecting?

Is the UNION return the same rows?
Select  ClientKey,CarbonLocationKey LK ,c.[Location Size] LSize,c.[Location Size Effective From*] EffFrom ,1 IsCurrent,Getdate()F,Getdate()T,'I' Type
from TableA
UNION
Select   ClientKey,CarbonLocationKey LK ,c.[Location Size] LSize,c.[Location Size Effective From*] EffFrom ,1 IsCurrent,Getdate()F,Getdate()T,'I' Type
from TableB

Open in new window

Vitor,
I'm using EXCEPT not using UNION

Thanks,
MT
Pawan,
Let me verify with the data types. will get back to you soon.

Thanks,
MT
I'm using EXCEPT not using UNION
I'm just asking you to confirm if both are returning the same.
@Vitor
In case of UNION i'm getting 22 rows.
and in case of Except i'm getting 10 rows.
Have you tried mine?
Thanks Pawan,

you are right there is a datatype mismatch between two fields date/Datetime and decimal/Float corresponding to TableA and TableB. Once i cast these column i start getting desired result.

Thanks
Mayank
In case of UNION i'm getting 22 rows.
 and in case of Except i'm getting 10 rows.
Thank you.

So, I just need the answer for the other question:
- Which are the 2 extra rows that you're expecting?