You are here:

MS SQL Server/sql Max check number

Advertisement


Question
I am trying to pull the max check number from check history for a payperiod, but some people got paid 2 differnt checks within this period. I am using the following query:

Company,
id,
name,
checknumber,
SUM(hrsYTD)

from base, employment, checkhistory

where period = '2014160'
and checknumber = (select max(checknumber) from checkhistory)

Group BY Company, id, name, checknumber


but continue to get the following results
1205
1206

I an trying to only get the hrsytd from 1206

I have even tried the following

Company,
id,
name,
checknumber,
SUM(hrsYTD)

from base, employment, checkhistory

where period = '2014160'
and checknumber = (select max(checknumber B) from checkhistory as B where checkhistory.checknumber = checkhistory B.checknumber
period = '2014160' )

Group BY Company, id, name, checknumber

Answer
I see a couple of things wrong right off the bat, which may or may not be causing your problem. They probably are, but at the very least they're making it difficult to troubleshoot.

First, you have no relationships established among the 3 tables base, employment, and checkhistory. That's going to give you a cartesian product. You should include a JOIN clause to establish how the data in the 3 tables are related.

Second, your subquery "select max(checknumber) from checkhistory" is not qualified at all, so it will return the highest checknumber in the entire checkhistory table, not just the one for the employee/period in question.  


So, you can address those 2 issues and see what you get. If that fixes it, YAY! If not, I suggest you respond to this with the full, complete SELECT statement(s) you are using and the full, complete results. (If you need help addressing those 2 issues, let me know, but be sure to include the schema of the 3 tables).

Additionally, if you can provide 5 or 6 rows of sample data which return the wrong results, that would go a LONG way to finding out the problem.  

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


David Vaughn

Expertise

Performance tuning; T-SQL syntax;

Experience

Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Education/Credentials
Truman State University

©2016 About.com. All rights reserved.