SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 selecting sum not equal to		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jwigg5pt0
Starting Member

8 Posts
Posted - 04/23/2012 :  18:59:42  Show Profile  Reply with Quote
Table 1 - AppointmentTable with column id(primay) and appointmentTotal(decimal)

Table 2 - TotalsTable with columns appointmentID and total(decimal)

A total is submitted to the TotalsTable via asp.net app that includes the appointmentID. Each time a total is submitted, the app will calculate the sum of all the collected totals in the TotalsTable with the appropriate appointmentID and update the appointmentTotal column in the AppointmentTable with that sum.

What I am trying to accomplish is to find any mismatches where the sum in the TotalsTable for an appointmentID DOES NOT EQUAL the appointmentTotal in the AppointmentTable

A few select queries below that I have tried are not working properly. Any suggestions??

SELECT id
FROM AppointmentTable
WHERE appointmentTotal !=
(SELECT SUM(total)
FROM TotalsTable
WHERE TotalsTable.appointmentID = AppointmentTable.id)

----This will pull up a result only if the SUM is not null and does not equal the appointmentTotal, not vica verca. SO if there weren't any entries in the TotalsTable and the appointmentTotal in the AppointmentsTable ='s any decimal > 0, this does not return. This only will return if there was an entry in the TotalsTable that didnt SUM up to what was in the AppointmentTable

---AND ANOTHER---
SELECT DISTINCT(appointmentID)
, SUM(total) as total
FROM TotalsTable
WHERE total !=
(SELECT appointmentTotal
FROM AppointmentTable
WHERE TotalsTable.appointmentTotal= AppointmentTable.id)
GROUP BY appointmentID
--This one is all over the place. What it looks like is it is showing results if the LAST entry in the TotalsTable does not equal whats in the AppointmentTable although the SUM is correct when running a single select and sum query to verify what it should be.

if there are any misspellings in the tables or names showing above, its not the issue (table and column names were obviously changed on the post)

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 04/23/2012 :  19:10:38  Show Profile  Reply with Quote 

SELECT *
FROM AppointmentTable a 
INNER JOIN (SELECT appointmentID ,SUM(Total) AS Totals
            FROM TotalsTable 
            GROUP BY appointmentID)t
ON t.appointmentID = a.appointmentID 
WHERE Totals <> appointmentTotal


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 04/23/2012 :  19:14:26  Show Profile  Reply with Quote 
SELECT
	COALESCE(a.id,t.AppointmentId) AS Id,
	COALESCE(a.AppointmentTotal,0) AS AppointmentTotal,
	COALESCE(SUM(t.total),0) AS TotalsTotal
FROM
	Appointments a
	FULL JOIN Totals t ON
		t.AppointmentId = a.id
GROUP BY
	COALESCE(a.AppointmentTotal,0),
	COALESCE(a.id,t.AppointmentId)
HAVING
	COALESCE(a.AppointmentTotal,0) <> COALESCE(SUM(t.total),0)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 04/23/2012 :  19:21:37  Show Profile  Reply with Quote
for including any entries not present in totalstable use


SELECT *
FROM AppointmentTable a 
LEFT JOIN (SELECT appointmentID ,SUM(Total) AS Totals
            FROM TotalsTable 
            GROUP BY appointmentID)t
ON t.appointmentID = a.appointmentID 
WHERE COALESCE(Totals,0) <> appointmentTotal


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jwigg5pt0
Starting Member

8 Posts
Posted - 04/23/2012 :  20:38:52  Show Profile  Reply with Quote
quote:
Originally posted by visakh16


SELECT *
FROM AppointmentTable a 
INNER JOIN (SELECT appointmentID ,SUM(Total) AS Totals
            FROM TotalsTable 
            GROUP BY appointmentID)t
ON t.appointmentID = a.appointmentID 
WHERE Totals <> appointmentTotal


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




This here is doing the same as the first one of mine did. As a test there is a value in the AppointmentTable.appointmentTotal but no entries in TotalsTable with the given appointmentID therefore returning a null sum value, which does not pull as a record running this query
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 04/23/2012 :  20:41:00  Show Profile  Reply with Quote
quote:
Originally posted by jwigg5pt0

quote:
Originally posted by visakh16


SELECT *
FROM AppointmentTable a 
INNER JOIN (SELECT appointmentID ,SUM(Total) AS Totals
            FROM TotalsTable 
            GROUP BY appointmentID)t
ON t.appointmentID = a.appointmentID 
WHERE Totals <> appointmentTotal

use the modified suggestion with left join
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




This here is doing the same as the first one of mine did. As a test there is a value in the AppointmentTable.appointmentTotal but no entries in TotalsTable with the given appointmentID therefore returning a null sum value, which does not pull as a record running this query


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jwigg5pt0
Starting Member

8 Posts
Posted - 04/23/2012 :  20:44:00  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

for including any entries not present in totalstable use


SELECT *
FROM AppointmentTable a 
LEFT JOIN (SELECT appointmentID ,SUM(Total) AS Totals
            FROM TotalsTable 
            GROUP BY appointmentID)t
ON t.appointmentID = a.appointmentID 
WHERE COALESCE(Totals,0) <> appointmentTotal


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




This here worked both ways. Thank you.
Although why am I getting an error -- The multi-part identifier "AppointmentTable.id" could not be bound. -- when trying to select an individual column (AppointmentTable.id) instead of all (*) in the query?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 04/23/2012 :  21:25:02  Show Profile  Reply with Quote
which column it tried? it works for all columns in AppointmentTable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.61 seconds. Powered By: Snitz Forums 2000