Two Queries that should return similar results, SQL

Question

Running on SQL Server 2005 Having a little trouble with 2 queries. The first returns a table like this:

NAME     PAstDUe  DueTomorr  Due Today  Due MONd   Due Beyond
CLIENT   23       10         8          13         32

And the second returns a list of the same info that when i run in a pivot table in excel does not add up to the same table/data. Im using these for two different data sources/purposes for a project in visual studio so i cant just not use one. I dont see whats going wrong but i am calculation the results in different ways so i dont know if my math is off or something like that. This is really important because i need this data to be accurate on a day to day basis. If you wonder whats dbo.TruncateDate is doing, it refers the ordernumber of an order in our system and when ran with something involving a date this makes sure that portion of the query ignores all weekends and holidays based on a table in our system containing all of these dates. Hope that made sense. Let me know if i can provide more info.

Query1:

with cte AS (SELECT cl.Name,    
     SUM(CASE WHEN CURRENT_TIMESTAMP > oi.RequiredByDate THEN 1 ELSE 0 END) as PastDue
    ,SUM(CASE WHEN DATEADD(dd, DATEDIFF(dd, 0, oi.RequiredByDate), 0) = dateadd(day, datediff(day, '19000101',CURRENT_TIMESTAMP),'19000102') then 1 ELSE 0 END) as DueTomorrow
    ,SUM(CASE WHEN dbo.TruncateDate(CURRENT_TIMESTAMP) = dbo.TruncateDate(oi.RequiredByDate) THEN 1 Else 0 END) as DueToday
    ,SUM(CASE WHEN DateDiff(day, getdate(), RequiredByDate) BETWEEN 2 and 7 AND DateName(weekday, RequiredByDate) = 'Monday' Then 1 ELSE 0 END) as DueMonday
    ,SUM(CASE WHEN DATEADD(DAY, 2,dbo.TruncateDate(CURRENT_TIMESTAMP)) <=       dbo.TruncateDate(oi.RequiredByDate) THEN 1 ELSE 0 END) as DueBeyond  

     FROM OrderItems oi
     JOIN Orders o ON o.OrderID = oi.OrderID
     JOIN Counties c ON c.FIPS = o.FIPS
     JOIN Clients cl ON cl.ClientID = o.ClientID
     JOIN Milestones m ON m.MilestoneID = oi.LastMilestoneID
     JOIN Products p ON p.ProductID = oi.ProductID
     JOIN Vendors v ON v.VendorID = oi.VendorID
     LEFT JOIN ClientBranches clb ON clb.ClientID = o.ClientID
     WHERE QueueID > 0 AND cl.Name NOT LIKE 'TES%'
     AND cl.NAME LIKE 'HLC%'
     GROUP BY cl.Name)

     Select * FROM cte 

Query 2:

SELECT cl.Name as Client, clb.Name as ClientBranch
  ,
CASE 
    WHEN CURRENT_TIMESTAMP > oi.RequiredByDate THEN 'Past Due'
    WHEN dbo.TruncateDate(CURRENT_TIMESTAMP) = dbo.TruncateDate    (oi.RequiredByDate) THEN 'Due Today'
    WHEN DATEADD(dd, DATEDIFF(dd, 0, oi.RequiredByDate), 0) = dateadd(day, datediff(day, '19000101',CURRENT_TIMESTAMP),'19000102') then 'Due Tomorrow'
    WHEN DateDiff(day, getdate(), RequiredByDate) BETWEEN 2 and 7 AND DateName(weekday, RequiredByDate) = 'Monday' Then 'Due Monday'
    WHEN DATEADD(DAY, 2,dbo.TruncateDate(CURRENT_TIMESTAMP)) <= dbo.TruncateDate(oi.RequiredByDate) THEN 'Due Beyond'  
    ELSE 'WRONG' end
    as DeliveryStatus

 FROM OrderItems oi
 JOIN Orders o ON o.OrderID = oi.OrderID
 JOIN Counties c ON c.FIPS = o.FIPS
 JOIN Clients cl ON cl.ClientID = o.ClientID
 JOIN Milestones m ON m.MilestoneID = oi.LastMilestoneID
 JOIN Products p ON p.ProductID = oi.ProductID
 JOIN Vendors v ON v.VendorID = oi.VendorID
 LEFT JOIN ClientBranches clb ON clb.ClientID = o.ClientID


 WHERE QueueID > 0
and cl.Name not like ('Tes%')
and cl.Name Like 'HLC%'

Answer 1

Could you please run this:

with cte AS (SELECT cl.Name,    
     SUM(CASE WHEN CURRENT_TIMESTAMP > oi.RequiredByDate THEN 1 ELSE 0 END) as PastDue
    ,SUM(CASE WHEN DATEADD(dd, DATEDIFF(dd, 0, oi.RequiredByDate), 0) = dateadd(day, datediff(day, '19000101',CURRENT_TIMESTAMP),'19000102') then 1 ELSE 0 END) as DueTomorrow
    ,SUM(CASE WHEN dbo.TruncateDate(CURRENT_TIMESTAMP) = dbo.TruncateDate(oi.RequiredByDate) THEN 1 Else 0 END) as DueToday
    ,SUM(CASE WHEN DateDiff(day, getdate(), RequiredByDate) BETWEEN 2 and 7 AND DateName(weekday, RequiredByDate) = 'Monday' Then 1 ELSE 0 END) as DueMonday
    ,SUM(CASE WHEN DATEADD(DAY, 2,dbo.TruncateDate(CURRENT_TIMESTAMP)) <=       dbo.TruncateDate(oi.RequiredByDate) THEN 1 ELSE 0 END) as DueBeyond,
     COUNT(*) AS cnt

     FROM OrderItems oi
     JOIN Orders o ON o.OrderID = oi.OrderID
     JOIN Counties c ON c.FIPS = o.FIPS
     JOIN Clients cl ON cl.ClientID = o.ClientID
     JOIN Milestones m ON m.MilestoneID = oi.LastMilestoneID
     JOIN Products p ON p.ProductID = oi.ProductID
     JOIN Vendors v ON v.VendorID = oi.VendorID
     LEFT JOIN ClientBranches clb ON clb.ClientID = o.ClientID
     WHERE QueueID > 0 AND cl.Name NOT LIKE 'TES%'
     AND cl.NAME LIKE 'HLC%'
     GROUP BY cl.Name)

and make sure that cnt is equal to the sum of all other fields?

Update:

Please run this

SELECT  *
FROM    (
SELECT cl.Name,    
    CASE WHEN CURRENT_TIMESTAMP > oi.RequiredByDate THEN 1 ELSE 0 END as PastDue,
    CASE WHEN DATEADD(dd, DATEDIFF(dd, 0, oi.RequiredByDate), 0) = dateadd(day, datediff(day, '19000101',CURRENT_TIMESTAMP),'19000102') then 1 ELSE 0 END as DueTomorrow,
    CASE WHEN dbo.TruncateDate(CURRENT_TIMESTAMP) = dbo.TruncateDate(oi.RequiredByDate) THEN 1 Else 0 END as DueToday,
    CASE WHEN DateDiff(day, getdate(), RequiredByDate) BETWEEN 2 and 7 AND DateName(weekday, RequiredByDate) = 'Monday' Then 1 ELSE 0 END as DueMonday,
    CASE WHEN DATEADD(DAY, 2,dbo.TruncateDate(CURRENT_TIMESTAMP)) <=       dbo.TruncateDate(oi.RequiredByDate) THEN 1 ELSE 0 END as DueBeyond,
     FROM OrderItems oi
     JOIN Orders o ON o.OrderID = oi.OrderID
     JOIN Counties c ON c.FIPS = o.FIPS
     JOIN Clients cl ON cl.ClientID = o.ClientID
     JOIN Milestones m ON m.MilestoneID = oi.LastMilestoneID
     JOIN Products p ON p.ProductID = oi.ProductID
     JOIN Vendors v ON v.VendorID = oi.VendorID
     LEFT JOIN ClientBranches clb ON clb.ClientID = o.ClientID
     WHERE QueueID > 0 AND cl.Name NOT LIKE 'TES%'
     AND cl.NAME LIKE 'HLC%'
     ) q
  WHERE PastDue + DueTomorrow + DueToday + DueMonday + DueBeyond > 1

and see the records which are counted more than once.