|
|
Posted by tatata9999 on 10/08/07 16:48
On Oct 7, 9:36 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (tatata9...@gmail.com) writes:
> > Interesting idea, Erland, but it did not seem to work for me, maybe I
> > didn't do it right? Only two variations.
>
> We have only seen fragments and pieces of what you have been doing. It
> would help if you posted a create table statement for your table, insert
> statements with sample data, and the desired result given the sample.
> That makes it easy to develop tested query. Without that, we are mainly
> guessing.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Very fair. Thank you, and Ed Murphy. Now, the problem is, when I
apply either one of the following sum queries, the result is about 30
hours short of 114 hours, how come? Could my data be compromised?
Or?
-- ddl
CREATE TABLE [dbo].[myTBL] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[mydate] [datetime] NOT NULL default getDate(),
[proj] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[start] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stop] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TRIGGER populateStartOrNot4myTBL
ON dbo.myTBL
AFTER INSERT AS
If exists (select *
from thisTEMP -- for business logic / business rule
where autostart = 1)
begin
UPDATE myTBL
SET start = Cast(DatePart(hh,getDate()) as varchar(2)) + ':' +
Cast(DatePart(n,getDate()) as varchar(2))
WHERE id = (SELECT id FROM inserted)
end
-- sample dml
insert into myTBL(proj, start, stop)
values ('test me','17:00','17:55');
insert into myTBL(proj, start, stop)
values ('test me','8:00','14:25');
insert into myTBL(proj, start)
values ('test me','23:25');
-- suppose, the above one insert, create ID, 3
update myTBL
set stop = '1:15'
where ID = 3
-- now for the row 3, it spans two days
-- reg query 1
select ID, mydate as origdate,convert(char,mydate,101) as mydate,
proj, start, stop,
CASE WHEN (datediff(n,start,stop) < 0) THEN
Left((datediff(n,start,'23:59') + datepart(n,'2007-09-19
10:01:00') + datediff(n,'00:00',stop))/60.0,4)
ELSE Left(datediff(n,start,stop)/60.0,4) END as hours_spent
from myTBL
order by origdate DESC
-- comment: looks good
-- reg q 2
select ID, mydate as origdate,convert(char,mydate,101) as mydate,
proj, start, stop,
CASE WHEN (datediff(n,start,stop) < 0) THEN
(datediff(n,start,stop) + 1440)/60.0
ELSE Left(datediff(n,start,stop)/60.0,4) END as hours_spent
from myTBL
order by origdate DESC
-- comment: looks good
-- sum
-- try 1
set ANSI_WARNINGS OFF;
select proj, SUM(CASE WHEN (datediff(n,start,stop)/60 < 0)
THEN datediff(n,start,'23:59') + datepart(n,'2007-09-19
10:01:00') + datediff(n,'00:00',stop)/60
WHEN (datediff(n,start,stop)/60 > 0)
THEN (datediff(n,start,stop)/60)
End) * 1.0 as total_hours
from myTBL
-- where ID < 3
group by proj with rollup
set ANSI_WARNINGS ON;
-- comment: bad
-- try 2
set ANSI_WARNINGS OFF;
select proj, SUM(CASE WHEN (datediff(n,start,stop)/60 < 0)
THEN (datediff(n,start,stop) + 1440)/60.0
WHEN (datediff(n,start,stop)/60 > 0)
THEN (datediff(n,start,stop)/60.0)
End) as total_hours
from myTBL
group by proj with rollup
set ANSI_WARNINGS ON;
-- comment: not bad
-- try 3
set ANSI_WARNINGS OFF;
select proj, SUM(convert(decimal(10,2),CASE WHEN
(datediff(n,start,stop)/60 < 0)
THEN (datediff(n,start,stop) + 1440)/60.0
WHEN (datediff(n,start,stop)/60 > 0)
THEN (datediff(n,start,stop)/60.0)
End)) as total_hours
from myTBL
group by proj with rollup
-- comment: not bad
Navigation:
[Reply to this message]
|