RE: How to count records w/o counting duplicates by week, month, or ye
- From: Dale Fye <dale.fye@xxxxxxxxxx>
- Date: Fri, 17 Apr 2009 04:44:07 -0700
Another method would be to create a normalizing query, to get your data in a
format that would be far easier to do most of these calculations. It would
look something like:
SELECT WeDte, EmpID, WgRate, "Mon" as DayOfWeek, [Mon] as HoursWorked
FROM yourTable
WHERE [MON] is not NULL
UNION ALL
SELECT WeDte, EmpID, WgRate, "Tue" as DayOfWeek, [Tue] as HoursWorked
FROM yourTable
WHERE [Tue] is not NULL
UNION ALL
SELECT WeDte, EmpID, WgRate, "Wed" as DayOfWeek, [Wed] as HoursWorked
FROM yourTable
WHERE [Wed] is not NULL
UNION ALL
SELECT WeDte, EmpID, WgRate, "Thu" as DayOfWeek, [Thu] as HoursWorked
FROM yourTable
WHERE [Thu] is not NULL
UNION ALL
SELECT WeDte, EmpID, WgRate, "Fri" as DayOfWeek, [Fri] as HoursWorked
FROM yourTable
WHERE [Fri] is not NULL
UNION ALL
SELECT WeDte, EmpID, WgRate, "Sat" as DayOfWeek, [Sat] as HoursWorked
FROM yourTable
WHERE [Sat] is not NULL
Once you have your data in this structure you can do almost anything with
it. You might even want to add a DateWorked column that subtracts the
appropriate number of days from the WeDte field. This would give you that
added ability of summing hours by month, not the month of the WeDte.
----
HTH
Dale
"John" wrote:
I have a table with payroll data. I have an employe ID, a week ending date.
(Sunday), and hours worked on Sun, Mon, Tue, ... Sat. What I need is a query
that counts the number of employees, and gives total hours worked on what
days. The catch is that overtime and double-time hours are shown on a
different record, with the same week ending date. If I do a simle count of
employee ID, I get 2x the number of real employees. Not all employees work
over time or double-time.
Fields:
WeDte: week ending date
EmpID: Employee ID (text)
Mon: Monday Hours
Tue: Tuesay Hours
Etc. through "Sat: Saturday Hours"
WgRate: Employee Wage Rate
What I would like to do is to group the qery by week, month, quarter, or
year to get the total hours, salary cost, and employee count. The hours and
salary cost is not an issue, but counting the first EmpID in the month, and
then not counting it until the next month is killing me!
--
Thanks in advance!
**John**
- References:
- Prev by Date: RE: Ranking swimmers on their time
- Next by Date: RE: protect a query
- Previous by thread: RE: How to count records w/o counting duplicates by week, month, or ye
- Next by thread: RE: How to count records w/o counting duplicates by week, month, or ye
- Index(es):
Relevant Pages
|
