Sum Datediff Select problem 2

[mrccopeland]

Hello All,
I'm having a terrible time at this and don't quite know how to go about it (so I'll just jump right in). Say you have 2 tables:

TableA
-------
empsys
epssys
paysys
beginningDate
endingdate


TableB
--------
epssys
paysys
BegDate
EndDate


Table A is linked to Table B by epssys and paysys. Table A to Table B is a one-to-many relationship. I need to find the records from Table A where the number of days from Table A is different from the sum of the number of days from Table B. Hope that makes sense. Any help would be greatly appreciated.
Thanks,
Chris

 

[SQLSister]

klet's tackle this in small chunks. First you need the difference in days per event in table b
Then you need a sum of those per Epsys and paysys

Finally you need to compare this amount to the data dif of the related record in table a and select those which do not match.
So you have:
Select Datediff(day, BEgDate, EndDate) from TableB

Which evolves to
Select epsys, paysys, Sum(Datediff(day, BEgDate, EndDate)) from TableB
Group By epsys, paysys

which becomes a derived table and is joined to the tother table to get the result:
Select Sum(Datediff(day, a.BEgDate, a.EndDate)) as TAbleASum, b.TableBSum, a.epssys, a.paysys
from TableA a left outer join
(Select epsys, paysys, Sum(Datediff(day, BEgDate, EndDate)) as TableBSum from TableB
Group By epsys, paysys) b
On a.epsys = b.epsys and a.paysys =b.paysis
Where Sum(Datediff(day, a.BEgDate, a.EndDate)) <> b.TableBSum


This is not tested. Just a first wahck at it. Try it and see what errors if any you get and we can refine it to be correct.

 

[SemperFiDownUnda]

I'm wondering if this can be done like this.

SELECT a.EmpSys, a.EpsSys, a.PaySys,
DATEDIFF(day,a.BeginningDate, a.EndingDate),
SUM(DATEDIFF(dsy,b.BegDate, b.EndDate))
FROM TableA a
INNER JOIN TableB b
ON a.EpsSys = b.EpsSys
AND a.PaySys = b.PaySys
GROUP BY a.EmpSys, a.EpsSys, a.PaySys,
DATEDIFF(day,a.BeginningDate, a.EndingDate)
HAVING DATEDIFF(day,a.BeginningDate, a.EndingDate) <>
SUM(DATEDIFF(dsy,b.BegDate, b.EndDate))

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[mrccopeland]

Thank you both for your replies. They both seemed to work very well. However, for some reason, the first solution seemed to run much faster.

 

[SemperFiDownUnda]

Shows that 1 query isn't always the solution. Really depends on the data if doing a JOIN is the best solution. Depends on the data in the table and the size of the tables.

Glad you got it working.

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[SQLSister]

You could check out the execution plans of both to see why one runs faster.