|
|
You're in the
sql server programming
group:how to change query to get today's timespent??
sql server programming:
following query gets me list of assignments and timespent on them so far select fk_assid, 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN '0' ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END from worktime group by fk_assid in the same query as 3rd column i want to find out how much time spent today. worktime table has following fields WTID,fk_assid,wt_starttime,wt_stoptime just to let u know everytime we start work on assignment a new entry will be created in worktime table. many thanks for ur ideas. --
this is worktime table DDL
CREATE TABLE [WorkTime] ( [wtId] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [fk_assid] [int] NOT NULL , [wt_starttime] [datetime], [wt_stoptime] [datetime] NULL ) ON [PRIMARY] GO -- cheers [quoted text, click to view] "Uri Dimant" wrote:
> rajan > Can you post DDL+ sample data+ expected result. > My guess you should use derived table. > SELECT col.... FROM > ( > ................................. > ) AS X > > "rajani" <rajani@discussions.microsoft.com> wrote in message > news:EF1798CA-57D8-4B7A-9B20-E1092CF926C1@microsoft.com... > > Hi friends > > following query gets me list of assignments and timespent on them so far > > > > select fk_assid, > > 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL > THEN '0' > > ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END from > worktime > > group by fk_assid > > > > in the same query as 3rd column i want to find out how much time spent > > today. > > > > worktime table has following fields > > WTID,fk_assid,wt_starttime,wt_stoptime > > just to let u know everytime we start work on assignment a new entry > > will be created in worktime table. > > many thanks for ur ideas. > > > > > > -- > > cheers > >
this is data
INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!65y8',381,'Dec 3 2003 10:53:00:000AM','Dec 3 2003 1:49:00:000PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!6uu6',381,'Dec 3 2003 12:12:00:000PM','Dec 3 2003 1:49:00:000PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!E6BW',381,'Dec 3 2003 10:29:00:000AM','Dec 3 2003 1:49:00:000PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!GzBK',381,'Dec 3 2003 10:44:00:000AM','Dec 3 2003 1:49:00:000PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!huTV',383,'Dec 5 2003 4:51:00:000PM','Dec 12 2003 4:40:23:550PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!l5ve',382,'Dec 3 2003 4:21:00:000PM','Dec 3 2003 4:21:00:000PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!pHuT',381,'Dec 3 2003 10:52:00:000AM','Dec 3 2003 1:49:00:000PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!PUs8',383,'Dec 5 2003 4:50:00:000PM','Dec 5 2003 4:51:00:000PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!qlWS',381,'Dec 3 2003 10:32:00:000AM','Dec 3 2003 1:49:00:000PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!swIw',381,'Dec 3 2003 10:27:00:000AM','Dec 3 2003 1:49:00:000PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!u6AZ',381,'Dec 3 2003 1:49:00:000PM','Dec 3 2003 1:49:42:640PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!wUQg',381,'Dec 3 2003 10:51:00:000AM','Dec 3 2003 1:49:00:000PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!xXsX',381,'Dec 3 2003 1:51:00:000PM','Dec 3 2003 1:51:26:467PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!YDtL',382,'Dec 3 2003 4:20:00:000PM','Dec 3 2003 4:21:00:000PM') INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!Yq9W',381,'Dec 3 2003 10:45:00:000AM','Dec 3 2003 1:49:00:000PM') -- cheers [quoted text, click to view] "Uri Dimant" wrote:
> rajan > Can you post DDL+ sample data+ expected result. > My guess you should use derived table. > SELECT col.... FROM > ( > ................................. > ) AS X > > "rajani" <rajani@discussions.microsoft.com> wrote in message > news:EF1798CA-57D8-4B7A-9B20-E1092CF926C1@microsoft.com... > > Hi friends > > following query gets me list of assignments and timespent on them so far > > > > select fk_assid, > > 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL > THEN '0' > > ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END from > worktime > > group by fk_assid > > > > in the same query as 3rd column i want to find out how much time spent > > today. > > > > worktime table has following fields > > WTID,fk_assid,wt_starttime,wt_stoptime > > just to let u know everytime we start work on assignment a new entry > > will be created in worktime table. > > many thanks for ur ideas. > > > > > > -- > > cheers > >
rajan
Can you post DDL+ sample data+ expected result. My guess you should use derived table. SELECT col.... FROM ( ................................. ) AS X [quoted text, click to view] "rajani" <rajani@discussions.microsoft.com> wrote in message news:EF1798CA-57D8-4B7A-9B20-E1092CF926C1@microsoft.com... > Hi friends > following query gets me list of assignments and timespent on them so far > > select fk_assid, > 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN '0' > ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END from worktime > group by fk_assid > > in the same query as 3rd column i want to find out how much time spent > today. > > worktime table has following fields > WTID,fk_assid,wt_starttime,wt_stoptime > just to let u know everytime we start work on assignment a new entry > will be created in worktime table. > many thanks for ur ideas. > > > -- > cheers
Try this one
select fk_assid, 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN '0' ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END, datediff(n,CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE(),112),101),getdate()) from worktime group by fk_assid [quoted text, click to view] "rajani" <rajani@discussions.microsoft.com> wrote in message ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!65y8',381,'Dec 3news:B7FBD4DC-ED11-46F3-867E-FF21F2B47BF8@microsoft.com... > this is data > INSERT INTO [worktime] 2003 10:53:00:000AM','Dec 3 2003 1:49:00:000PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!6uu6',381,'Dec 32003 12:12:00:000PM','Dec 3 2003 1:49:00:000PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!E6BW',381,'Dec 32003 10:29:00:000AM','Dec 3 2003 1:49:00:000PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!GzBK',381,'Dec 32003 10:44:00:000AM','Dec 3 2003 1:49:00:000PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!huTV',383,'Dec 52003 4:51:00:000PM','Dec 12 2003 4:40:23:550PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!l5ve',382,'Dec 32003 4:21:00:000PM','Dec 3 2003 4:21:00:000PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!pHuT',381,'Dec 32003 10:52:00:000AM','Dec 3 2003 1:49:00:000PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!PUs8',383,'Dec 52003 4:50:00:000PM','Dec 5 2003 4:51:00:000PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!qlWS',381,'Dec 32003 10:32:00:000AM','Dec 3 2003 1:49:00:000PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!swIw',381,'Dec 32003 10:27:00:000AM','Dec 3 2003 1:49:00:000PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!u6AZ',381,'Dec 32003 1:49:00:000PM','Dec 3 2003 1:49:42:640PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!wUQg',381,'Dec 32003 10:51:00:000AM','Dec 3 2003 1:49:00:000PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!xXsX',381,'Dec 32003 1:51:00:000PM','Dec 3 2003 1:51:26:467PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!YDtL',382,'Dec 32003 4:20:00:000PM','Dec 3 2003 4:21:00:000PM') [quoted text, click to view] > INSERT INTO [worktime] ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!Yq9W',381,'Dec 32003 10:45:00:000AM','Dec 3 2003 1:49:00:000PM') [quoted text, click to view] > > -- > cheers > > > "Uri Dimant" wrote: > > > rajan > > Can you post DDL+ sample data+ expected result. > > My guess you should use derived table. > > SELECT col.... FROM > > ( > > ................................. > > ) AS X > > > > "rajani" <rajani@discussions.microsoft.com> wrote in message > > news:EF1798CA-57D8-4B7A-9B20-E1092CF926C1@microsoft.com... > > > Hi friends > > > following query gets me list of assignments and timespent on them so far > > > > > > select fk_assid, > > > 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL > > THEN '0' > > > ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END from > > worktime > > > group by fk_assid > > > > > > in the same query as 3rd column i want to find out how much time spent > > > today. > > > > > > worktime table has following fields > > > WTID,fk_assid,wt_starttime,wt_stoptime > > > just to let u know everytime we start work on assignment a new entry > > > will be created in worktime table. > > > many thanks for ur ideas. > > > > > > > > > -- > > > cheers > > > > > >
Try:
DECLARE @StartTime varchar(50), @EndTime smalldatetime SET @StartTime = CAST(Year(getdate()) as char(4)) + REPLICATE('0',2 - LEN(CAST(month(getdate()) as varchar(2)))) + CAST(month(getdate()) as varchar(2)) + REPLICATE('0',2 - LEN(CAST(day(getdate()) as varchar(2)))) + CAST(day(getdate()) as varchar(2)) + ' 00:00' SET @EndTime = CAST(Year(getdate()) as char(4)) + REPLICATE('0',2 - LEN(CAST(month(getdate()) as varchar(2)))) + CAST(month(getdate()) as varchar(2)) + REPLICATE('0',2 - LEN(CAST(day(getdate()) as varchar(2)))) + CAST(day(getdate()) as varchar(2)) + ' 23:59' select fk_assid, 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN '0' ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END, timespenttoday = ISNULL( ( select sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) from worktime a WHERE wt_starttime between @StartTime and @EndTime AND wt_stoptime between @StartTime and @EndTime AND worktime.fk_assid = a.fk_assid group by fk_assid ) ,0) from worktime group by fk_assid Regards Meir [quoted text, click to view] "rajani" <rajani@discussions.microsoft.com> wrote in message news:EF1798CA-57D8-4B7A-9B20-E1092CF926C1@microsoft.com... > Hi friends > following query gets me list of assignments and timespent on them so far > > select fk_assid, > 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN '0' > ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END from worktime > group by fk_assid > > in the same query as 3rd column i want to find out how much time spent > today. > > worktime table has following fields > WTID,fk_assid,wt_starttime,wt_stoptime > just to let u know everytime we start work on assignment a new entry > will be created in worktime table. > many thanks for ur ideas. > > > -- > cheers
[quoted text, click to view]
On Wed, 16 Jun 2004 20:53:01 -0700, rajani wrote: >Hi friends >following query gets me list of assignments and timespent on them so far > >select fk_assid, >'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN '0' >ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END from worktime >group by fk_assid > >in the same query as 3rd column i want to find out how much time spent >today. > >worktime table has following fields >WTID,fk_assid,wt_starttime,wt_stoptime >just to let u know everytime we start work on assignment a new entry >will be created in worktime table. >many thanks for ur ideas. Hi rajani, Maybe something like this? SELECT fk_assid, COALESCE (SUM (DATEDIFF (n, wt_starttime, wt_stoptime)), 0) AS timespent, (SUM (CASE WHEN wt_starttime > CONVERT(char(8), getdate(), 114) THEN DATEDIFF (n, wt_starttime, wt_stoptime) ELSE 0 END)) AS timetoday FROM worktime GROUP BY fk_assid (untested) Best, Hugo --
Hi URI
Thank u very much for ur post i dont think i understand ur solution.u suggested datediff(n,CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE(),112),101),getdate()) but i dont see worktime table field in it only getdate()?? am i missing something?? -- cheers [quoted text, click to view] "Uri Dimant" wrote:
> Try this one > select fk_assid, > 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN > '0' > ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END, > > datediff(n,CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE(),112),101),getdate()) > from worktime > group by fk_assid > "rajani" <rajani@discussions.microsoft.com> wrote in message > news:B7FBD4DC-ED11-46F3-867E-FF21F2B47BF8@microsoft.com... > > this is data > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!65y8',381,'Dec 3 > 2003 10:53:00:000AM','Dec 3 2003 1:49:00:000PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!6uu6',381,'Dec 3 > 2003 12:12:00:000PM','Dec 3 2003 1:49:00:000PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!E6BW',381,'Dec 3 > 2003 10:29:00:000AM','Dec 3 2003 1:49:00:000PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!GzBK',381,'Dec 3 > 2003 10:44:00:000AM','Dec 3 2003 1:49:00:000PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!huTV',383,'Dec 5 > 2003 4:51:00:000PM','Dec 12 2003 4:40:23:550PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!l5ve',382,'Dec 3 > 2003 4:21:00:000PM','Dec 3 2003 4:21:00:000PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!pHuT',381,'Dec 3 > 2003 10:52:00:000AM','Dec 3 2003 1:49:00:000PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!PUs8',383,'Dec 5 > 2003 4:50:00:000PM','Dec 5 2003 4:51:00:000PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!qlWS',381,'Dec 3 > 2003 10:32:00:000AM','Dec 3 2003 1:49:00:000PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!swIw',381,'Dec 3 > 2003 10:27:00:000AM','Dec 3 2003 1:49:00:000PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!u6AZ',381,'Dec 3 > 2003 1:49:00:000PM','Dec 3 2003 1:49:42:640PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!wUQg',381,'Dec 3 > 2003 10:51:00:000AM','Dec 3 2003 1:49:00:000PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!xXsX',381,'Dec 3 > 2003 1:51:00:000PM','Dec 3 2003 1:51:26:467PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!YDtL',382,'Dec 3 > 2003 4:20:00:000PM','Dec 3 2003 4:21:00:000PM') > > INSERT INTO [worktime] > ([wtId],[fk_assid],[wt_starttime],[wt_stoptime])VALUES('!Yq9W',381,'Dec 3 > 2003 10:45:00:000AM','Dec 3 2003 1:49:00:000PM') > > > > -- > > cheers > > > > > > "Uri Dimant" wrote: > > > > > rajan > > > Can you post DDL+ sample data+ expected result. > > > My guess you should use derived table. > > > SELECT col.... FROM > > > ( > > > ................................. > > > ) AS X > > > > > > "rajani" <rajani@discussions.microsoft.com> wrote in message > > > news:EF1798CA-57D8-4B7A-9B20-E1092CF926C1@microsoft.com... > > > > Hi friends > > > > following query gets me list of assignments and timespent on them so > far > > > > > > > > select fk_assid, > > > > 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS > NULL > > > THEN '0' > > > > ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END > from > > > worktime > > > > group by fk_assid > > > > > > > > in the same query as 3rd column i want to find out how much time spent > > > > today. > > > > > > > > worktime table has following fields > > > > WTID,fk_assid,wt_starttime,wt_stoptime > > > > just to let u know everytime we start work on assignment a new entry > > > > will be created in worktime table. > > > > many thanks for ur ideas. > > > > > > > > > > > > -- > > > > cheers > > > > > > > > > > >
Hi Hugo
Thanks for the post I tried ur solution but i am getting same values for timespent and today fields -- cheers [quoted text, click to view] "Hugo Kornelis" wrote:
> On Wed, 16 Jun 2004 20:53:01 -0700, rajani wrote: > > >Hi friends > >following query gets me list of assignments and timespent on them so far > > > >select fk_assid, > >'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN '0' > >ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END from worktime > >group by fk_assid > > > >in the same query as 3rd column i want to find out how much time spent > >today. > > > >worktime table has following fields > >WTID,fk_assid,wt_starttime,wt_stoptime > >just to let u know everytime we start work on assignment a new entry > >will be created in worktime table. > >many thanks for ur ideas. > > Hi rajani, > > Maybe something like this? > > SELECT fk_assid, > COALESCE (SUM (DATEDIFF (n, wt_starttime, wt_stoptime)), 0) > AS timespent, > (SUM (CASE WHEN wt_starttime > CONVERT(char(8), getdate(), 114) > THEN DATEDIFF (n, wt_starttime, wt_stoptime) > ELSE 0 > END)) AS timetoday > FROM worktime > GROUP BY fk_assid > (untested) > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
Hi Meir
Ur suggestion worked in my fist test testing and that's great Thank u very much for that :-) -- cheers [quoted text, click to view] "Meir" wrote:
> Try: > > DECLARE @StartTime varchar(50), > @EndTime smalldatetime > > SET @StartTime = CAST(Year(getdate()) as char(4)) + > REPLICATE('0',2 - LEN(CAST(month(getdate()) as varchar(2)))) + > CAST(month(getdate()) as varchar(2)) + > REPLICATE('0',2 - LEN(CAST(day(getdate()) as varchar(2)))) + > CAST(day(getdate()) as varchar(2)) + ' 00:00' > > SET @EndTime = CAST(Year(getdate()) as char(4)) + > REPLICATE('0',2 - LEN(CAST(month(getdate()) as varchar(2)))) + > CAST(month(getdate()) as varchar(2)) + > REPLICATE('0',2 - LEN(CAST(day(getdate()) as varchar(2)))) + > CAST(day(getdate()) as varchar(2)) + ' 23:59' > > select fk_assid, > 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN > '0' > ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END, > timespenttoday = ISNULL( > ( > select sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) from > worktime a > WHERE wt_starttime between @StartTime and @EndTime > AND wt_stoptime between @StartTime and @EndTime > AND worktime.fk_assid = a.fk_assid > group by fk_assid > ) ,0) > from worktime > group by fk_assid > > > Regards > Meir > > "rajani" <rajani@discussions.microsoft.com> wrote in message > news:EF1798CA-57D8-4B7A-9B20-E1092CF926C1@microsoft.com... > > Hi friends > > following query gets me list of assignments and timespent on them so far > > > > select fk_assid, > > 'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL > THEN '0' > > ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END from > worktime > > group by fk_assid > > > > in the same query as 3rd column i want to find out how much time spent > > today. > > > > worktime table has following fields > > WTID,fk_assid,wt_starttime,wt_stoptime > > just to let u know everytime we start work on assignment a new entry > > will be created in worktime table. > > many thanks for ur ideas. > > > > > > -- > > cheers > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |