Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Requtls from query displaying on multiple rows instead of one

Posted on 2012-04-12
5
Medium Priority
?
296 Views
Last Modified: 2012-06-21
Hi,

I am trying to create a query to caculate and show a person's pay date, gross pay for that pay date, and year to date gross total.  

I am able to run the query, enter a pay date, and display the results but the results apear on two rows instead of just one row.  

Please help me figure out what I am doing wrong.


EMPID
pay_date
Gross

empid     pay_date                   gross
11111     2012-01-21 00:00     2000.00
11111     2012-02-07 00:00     2000.00
11111     2012-02-22 00:00     2000.00
11111     2012-03-03 00:00     2500.00
22222     2012-01-21 00:00     1500.00
22222     2012-02-07 00:00     1500.00
22222     2012-02-22 00:00     1500.00
22222     2012-03-03 00:00     1000.00


SQL i am using:

SELECT tblEmployeePay.EMPID AS ID, tblEmployeePay.Gross AS [Gross Pay], Sum(tblEmployeePay.Gross) AS YTD
FROM tblEmployeePay
WHERE (((tblEmployeePay.Pay_Date)<=[Enter Pay Date]))
GROUP BY tblEmployeePay.ID, tblEmployeePay.Gross;

------------------------------------------------------------------------------------------------

The results I am currently getting:

Run Query by and Enter a Pay Date: 2012-03-03 00:00 when prompted

Display results after entering pay date and clicking on ok

ID         Gross Pay           YTD
11111   2500.00             2500.00
11111   2000.00             6000.00
22222   1000.00             1000.00
22222   1500.00             4500.00

------------------------------------------------------------------------------------------------

The results i am trying to get:

Run Query by and Enter a Pay Date: 2012-03-03 00:00 when prompted

Display results after entering pay date and clicking on ok

ID         Gross Pay          YTD
11111   2500.00            8500.00
22222   1000.00            5500.00
0
Comment
Question by:cesemj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 37838264
Try this.

SELECT tblEmployeePay.EMPID AS ID,  Sum(tblEmployeePay.Gross) AS YTD
FROM tblEmployeePay
WHERE (((tblEmployeePay.Pay_Date)<=[Enter Pay Date]))
GROUP BY empid
0
 

Author Comment

by:cesemj
ID: 37838427
Hi and thanks,

the results give me senario 1 which is what i received first before I modified the query to try to get the results in senario 2.  

Senario 1
 ID         YTD
11111   8500.00
22222   5500.00

Senario 2
 ID         Gross Pay         YTD
11111   2500.00            8500.00
22222   1000.00            5500.00
0
 

Author Comment

by:cesemj
ID: 37838466
Hi,

I modified the query to the below statement and get the results  i am looking for but is the statement right or am I wsetting myself up for failur down the road because of using poor syntax?

SELECT tblEmployeePay.EMPID AS ID, Last(tblEmployeePay.Gross) AS [Gross Pay], Sum(tblEmployeePay.Gross) AS YTD
FROM tblEmployeePay
WHERE (((tblEmployeePay.Pay_Date)<=[Enter Pay Date]))
GROUP BY tblEmployeePay.ID;
0
 
LVL 34

Accepted Solution

by:
Norie earned 900 total points
ID: 37838484
Can I ask a question?

What gross value do you want to return?

The most recent?

If it is then the logic of your query is right as far as I can see.
0
 

Author Closing Comment

by:cesemj
ID: 37842579
Thanks for you help.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question