I have a two datasets coming from 2 sources and i have to compare and find the mismatches. One from excel and other from Datawarehouse.
From excel Source_Excel
+-----+-------+------------+----------+
| id | name | City_Scope | flag |
+-----+-------+------------+----------+
| 101 | Plate | NY|TN | Ready |
| 102 | Nut | NY|TN | Sold |
| 103 | Ring | TN|MC | Planning |
| 104 | Glass | NY|TN|MC | Ready |
| 105 | Bolt | MC | Expired |
+-----+-------+------------+----------+
From DW Source_DW
+-----+-------+------+----------+
| id | name | City | flag |
+-----+-------+------+----------+
| 101 | Plate | NY | Ready |
| 101 | Plate | TN | Ready |
| 102 | Nut | TN | Expired |
| 103 | Ring | MC | Planning |
| 104 | Glass | MC | Ready |
| 104 | Glass | NY | Ready |
| 105 | Bolt | MC | Expired |
+-----+-------+------+----------+
Unfortunately Data from excel comes with separator for one column. So i have to use DelimitedSplit8K function to split that into individual rows. so i got the below output after splitting the excel source data.
+-----+-------+------+----------+
| id | name | item | flag |
+-----+-------+------+----------+
| 101 | Plate | NY | Ready |
| 101 | Plate | TN | Ready |
| 102 | Nut | NY | Sold |
| 102 | Nut | TN | Sold |
| 103 | Ring | TN | Planning |
| 103 | Ring | MC | Planning |
| 104 | Glass | NY | Ready |
| 104 | Glass | TN | Ready |
| 104 | Glass | MC | Ready |
| 105 | Bolt | MC | Expired |
+-----+-------+------+----------+
Now my expected output is something like this.
+-----+----------+---------------+--------------+
| ID | Result | Flag_mismatch | City_Missing |
+-----+----------+---------------+--------------+
| 101 | No_Error | | |
| 102 | Error | Yes | Yes |
| 103 | Error | No | Yes |
| 104 | Error | Yes | No |
| 105 | No_Error | | |
+-----+----------+---------------+--------------+
Logic:
- I have to find if there are any mismatches in flag values.
- After splitting if there are any city missing, then that should be reported.
Assume that there wont be any Name and city mismatches.
As a intial step, I'm trying to get the Mismatch rows and I have tried below query. It is not giving me any output. Please suggest where am going wrong.Check Fiddle Here
select a.id,a.name,split.item,a.flag from source_excel a
CROSS APPLY dbo.DelimitedSplit8k(a.city_scope,'|') split
where not exists (
select a.id,split.item
from source_excel a
join source_dw b
on a.id=b.id and a.name=b.name and a.flag=b.flag and split.item=b.city
)
Update
I have tried and got close to the answers with the help of temporary tables. Updated Fiddle . But not sure how to do without temp tables