Duplicate Information with Different IDs

I am trying to sum monthly hours for weekly timecards which have multiple
line items as tasks, but I am retrieving duplicate entries.
This is because a timecard can have a timecard_type of "r" (regular), or a
timecard_type of "c" (corrected).
If the timecard is corrected, the original with the timecard_type of "r"
still exists, but both time cards with duplicate information, have different
timecard IDs.
WHERE tc_type = 'r'
OR tc_type = 'c'
doesn't help me, so how can I get a monthly total of regular
timecards(timecard_type = 'r'), & corrected (timecard_type = 'c'),
and the original regular timecard is not returned, if it has been corrected.
I've got to think there's some way to use the timecard IDs.
When trying to use an "IF...ELSE", I get the error "Subquery returned more
than 1 value" because of the multiple line items.
Can someone please help?
Thanks much.ok. this uses a bit of old school sql but i hope it will help you
i use subqueries and join.
first to find out if a task has a corrected hour report for a certain
week
and second to retrieve the correct report for the task
here goes...
if your table , WEEKLY_CARDS , has the following fields :
EMPID , YEAR, WEEK_OF_YEAR , TASKID , HOURS , CARDTYPE
and EMPID , YEAR, WEEK_OF_YEAR , TASKID , CARDTYPE are the business
unique key in the table.
run the following sql
select myTable.*
from
WEEKLY_CARDS as myTable
inner join
(select
regular.empid
,regular.year
,regular.week_of_year
,regular.taskid
,isnull(corrected.cardtype,regular.cardtype) as cardtypeToUse
from
(select empid , year , week_of_year , taskid , cardtype
from WEEKLY_CARDS
where cardtype = 'r'
) as regular
left outer join
(select empid , year , week_of_year , taskid , cardtype
from WEEKLY_CARDS
where cardtype = 'c'
) as corrected
on regular.empid = corrected.empid
,regular.year = corrected.year
,regular.week_of_year = corrected.week_of_year
,regular.taskid = corrected.taskid
) whatToUse
on
on mytable.empid = whatToUse.empid
,mytable.year = whatToUse.year
,mytable.week_of_year = whatToUse.week_of_year
,mytable.taskid = whatToUse.taskid
,cardtype.cardtype= whatToUse.cardtypeToUse
if you use more than 1 table (maybe u have a CARDS table) then adjust
the sql accordingly. also, if you need information about a certain week
add the appropriate WHERE clauses
hope this helps
lior
JDArsenault wrote:
> I am trying to sum monthly hours for weekly timecards which have multiple
> line items as tasks, but I am retrieving duplicate entries.
> This is because a timecard can have a timecard_type of "r" (regular), or a
> timecard_type of "c" (corrected).
> If the timecard is corrected, the original with the timecard_type of "r"
> still exists, but both time cards with duplicate information, have different
> timecard IDs.
> WHERE tc_type = 'r'
> OR tc_type = 'c'
> doesn't help me, so how can I get a monthly total of regular
> timecards(timecard_type = 'r'), & corrected (timecard_type = 'c'),
> and the original regular timecard is not returned, if it has been corrected.
> I've got to think there's some way to use the timecard IDs.
> When trying to use an "IF...ELSE", I get the error "Subquery returned more
> than 1 value" because of the multiple line items.
> Can someone please help?
> Thanks much.