I am trying to sum monthly hours for w
ly timecards which have multipleline 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?
p.s. There's a duplicate thread in SQL Server Reporting Services. Sorry for
the mis-hit.Untested, and guessing at your schema, but something like this should
do the trick:
SELECT timecard_id, SUM(hours)
FROM timecards_table
WHERE tc_type = 'c'
OR (tc_type = 'r' AND timecard_id NOT IN (SELECT timecard_id FROM
timecards_table WHERE tc_type = 'c'))
GROUP BY timecard_id|||You need to post DDL and some sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
The answer is you have to look at other columns to identify duplicate time
cards in order to exclude the ones that have been corrected. Without
knowing your data structures we can only guess.
I'll assume your table looks like this:
Create table timecard
(
timecardID integer primary key not null
, EmployeeID varchar(10) not null
, PayEndDate datetime not null
, Workdate datetime not null
, EarningCode varchar(3) not null
, HoursWorked decimal (4,2) not null
, timecard_type char(1) not null
)
I will also assume that you can have only one valid timecard per employee
per workdate. If this is not reality, adjust the criteria to match your
level of uniqueness. You can put the following logic into a view and then
report against it.
select a.employeeID
, a.payenddate
, a.workdate
, a.earningscode
, a.hoursworked
from timecard a
where a.timecard_type = 'c'
or
(
a.timecard_type = 'r'
and not exists (
select 1 from timecard b
where b.employeeid = a.employeeid
and b.payenddate = a.payenddate
and b.workdate = a.workdate
and b.timecard_type = 'c'
)
)
"JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
news:FD43BC50-2072-4A2B-BAE2-3B49CA07EF48@.microsoft.com...
> Duplicate Information with Different IDs
> I am trying to sum monthly hours for w
ly 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?
> p.s. There's a duplicate thread in SQL Server Reporting Services. Sorry
for
> the mis-hit.|||there are many ways to skin a cat, for instance:
create table #timecard(emp_id int not null,
timecard_type char(1) not null check(timecard_type in ('r','c')),
hours int not null,
unique(emp_id, timecard_type))
go
insert into #timecard values(1,'r',40)
insert into #timecard values(1,'c',32)
insert into #timecard values(2,'r',40)
go
select r.emp_id, coalesce(c.hours, r.hours)
from
(select emp_id, hours from #timecard where timecard_type='r') r
left outer join
(select emp_id, hours from #timecard where timecard_type='c') c
on r.emp_id = c.emp_id
emp_id
-- --
1 32
2 40
(2 row(s) affected)|||Once I had all the information in a view, I used this piece in a dataset
query & it worked great! Thanks
"Tracy McKibben" wrote:
> Untested, and guessing at your schema, but something like this should
> do the trick:
> SELECT timecard_id, SUM(hours)
> FROM timecards_table
> WHERE tc_type = 'c'
> OR (tc_type = 'r' AND timecard_id NOT IN (SELECT timecard_id FROM
> timecards_table WHERE tc_type = 'c'))
> GROUP BY timecard_id
>|||Thank you fro reminding me of your refered link. I used a derivative of this
& it worked. Thanks
"Jim Underwood" wrote:
> You need to post DDL and some sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
> The answer is you have to look at other columns to identify duplicate time
> cards in order to exclude the ones that have been corrected. Without
> knowing your data structures we can only guess.
> I'll assume your table looks like this:
> Create table timecard
> (
> timecardID integer primary key not null
> , EmployeeID varchar(10) not null
> , PayEndDate datetime not null
> , Workdate datetime not null
> , EarningCode varchar(3) not null
> , HoursWorked decimal (4,2) not null
> , timecard_type char(1) not null
> )
> I will also assume that you can have only one valid timecard per employee
> per workdate. If this is not reality, adjust the criteria to match your
> level of uniqueness. You can put the following logic into a view and then
> report against it.
> select a.employeeID
> , a.payenddate
> , a.workdate
> , a.earningscode
> , a.hoursworked
> from timecard a
> where a.timecard_type = 'c'
> or
> (
> a.timecard_type = 'r'
> and not exists (
> select 1 from timecard b
> where b.employeeid = a.employeeid
> and b.payenddate = a.payenddate
> and b.workdate = a.workdate
> and b.timecard_type = 'c'
> )
> )
> "JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
> news:FD43BC50-2072-4A2B-BAE2-3B49CA07EF48@.microsoft.com...
> different
> corrected.
> for
>
>|||I got it to work using something else, but I have another place where I can
use this. Thanks
"Alexander Kuznetsov" wrote:
> there are many ways to skin a cat, for instance:
> create table #timecard(emp_id int not null,
> timecard_type char(1) not null check(timecard_type in ('r','c')),
> hours int not null,
> unique(emp_id, timecard_type))
> go
> insert into #timecard values(1,'r',40)
> insert into #timecard values(1,'c',32)
> insert into #timecard values(2,'r',40)
> go
> select r.emp_id, coalesce(c.hours, r.hours)
> from
> (select emp_id, hours from #timecard where timecard_type='r') r
> left outer join
> (select emp_id, hours from #timecard where timecard_type='c') c
> on r.emp_id = c.emp_id
> emp_id
> -- --
> 1 32
> 2 40
> (2 row(s) affected)
>
