Same query different result? 1

[sRockyHill]

Hi,

I am confused on these query, why I get different result?
883782 vs. 901223

Thanks


-- Total COUNT Return - 883782
SELECT Count( Transaction_ID) FROM (SELECT DISTINCT Transact.Transaction_ID,
convert(varchar(10), Date_RUN, 101) as Date_RUN, City_RUN, Gender_RUN, Age_RUN, Service_RUN
FROM ReferReportsData.dbo.Transact AS Transact, Town_SubTowns AS tblSubTown, Town_Region_LUW AS TownLU,
Rollup AS RollupLU WHERE tblSubTown.Actual_Town = TownLU.Town_Name AND Transact.City_RUN = tblSubTown.SubTown
AND Transact.Service_RUN = RollupLU.Term AND Service_RUN NOT IN('Unknown','Call disconnected','Caller not eligible','Caller refused referrals','Could not reach caller','Information not available')
AND Transact.Date_RUN BETWEEN convert(DATETIME, '1/1/2004 00:00:00 ') AND convert(DATETIME, '12/31/2005 23:59:59 ')
) as Subquery

-- Total COUNT Return - 901223
SELECT Count(Transaction_ID) FROM ReferReportsData.dbo.Transact1 AS Transact,
ReferReports.dbo.Town_SubTowns AS tblSubTown, ReferReports.dbo.Town_Region_LUW AS TownLU,
ReferReports.dbo.Rollup AS RollupLU WHERE tblSubTown.Actual_Town = TownLU.Town_Name
AND Transact.City_RUN = tblSubTown.SubTown AND Transact.Service_RUN = RollupLU.Term
AND Service_RUN NOT IN('Unknown','Call disconnected','Caller not eligible','Caller refused referrals','Could not reach caller','Information not available')
AND Transact.Date_RUN BETWEEN convert(DATETIME, '1/1/2004 00:00:00 ') AND convert(DATETIME, '12/31/2005 23:59:59 ')


-- Insert into Transact1 - Every night
SELECT DISTINCT Transact.Transaction_ID,
convert(varchar(10), Date_RUN, 101) as Date_RUN, City_RUN, Gender_RUN, Age_RUN, Service_RUN
INTO Transact1 FROM ReferReportsData.dbo.Transact AS Transact

 

[SQLDenis]

top one has DISTINCT

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

There are some duplicated Transaction_ID values - either problem in data or side-effect of join on non-unique column.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]