February 22, 2010 at 10:44 pm
I have two tables TASK_MANAGER and TASK_ACCESS_USERS
I am geting data from this table for desboard purpose using union all.
CREATE TABLE [TASK_MANAGER](
[RID] [bigint] IDENTITY(1,1) NOT NULL,
[TaskType] [smallint] NOT NULL CONSTRAINT [DF_HC_TASK_MANAGER_TaskType] DEFAULT ((0)),
[TargetDate] [datetime] NOT NULL CONSTRAINT [DF_HC_TASK_MANAGER_TargetDate] DEFAULT (getutcdate()),
[Subject] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_HC_TASK_MANAGER_Subject] DEFAULT (''),
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_HC_TASK_MANAGER_Description] DEFAULT (''),
[Status] [smallint] NULL,
CONSTRAINT [PK_TASK_MANAGER] PRIMARY KEY CLUSTERED
(
[RID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
insert into TASK_MANAGER VALUES ( 1,'20-Feb-2010 12:30 PM','Test1','',0)
insert into TASK_MANAGER VALUES ( 1,'23-Feb-2010 1:30 PM','Test2','',0)
insert into TASK_MANAGER VALUES ( 1,'24-Feb-2010 3:00 PM','Test3','',0)
insert into TASK_MANAGER VALUES ( 1,'24-Feb-2010 12:30 PM','Test4','',0)
insert into TASK_MANAGER VALUES ( 1,'25-Feb-2010 11:30 AM','Test5','',0)
CREATE TABLE [TASK_ACCESS_USERS](
[RID] [bigint] IDENTITY(1,1) NOT NULL,
[TaskID] [bigint] NOT NULL CONSTRAINT [DF_HC_TASK_ACCESS_USERS_TaskID] DEFAULT ((0)),
[AccessUserID] [bigint] NOT NULL CONSTRAINT [DF_HC_TASK_ACCESS_USERS_AccessUserID] DEFAULT ((0)),
[AccessRight] [smallint] NOT NULL CONSTRAINT [DF_HC_TASK_ACCESS_USERS_AccessRight] DEFAULT ((0)),
CONSTRAINT [PK_HC_TASK_ACCESS_USERS] PRIMARY KEY CLUSTERED
(
[RID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
insert into TASK_ACCESS_USERS values (1,12,1)
insert into TASK_ACCESS_USERS values (1,14,0)
insert into TASK_ACCESS_USERS values (2,12,0)
insert into TASK_ACCESS_USERS values (2,13,1)
insert into TASK_ACCESS_USERS values (4,12,1)
insert into TASK_ACCESS_USERS values (5,12,1)
insert into TASK_ACCESS_USERS values (3,12,1)
select * from TASK_MANAGER
select * From TASK_ACCESS_USERS
/* get user open task for last 2 days and all */
Select 'Last2days' as [Type], Count(TASK_MANAGER.RID) as Task
From TASK_MANAGER inner join TASK_ACCESS_USERS on
TASK_ACCESS_USERS.TaskID=TASK_MANAGER.RID
where TASK_ACCESS_USERS.AccessUserID =12 and TASK_MANAGER.Status=0
and Targetdate between dateadd(day,-2,getdate()) and getdate()
Union all
Select 'All Days' as [Type], Count(TASK_MANAGER.RID) as Task
From TASK_MANAGER inner join TASK_ACCESS_USERS on
TASK_ACCESS_USERS.TaskID=TASK_MANAGER.RID
where TASK_ACCESS_USERS.AccessUserID =12 and TASK_MANAGER.Status=0
Can any one suggest beter method then this one
February 23, 2010 at 4:44 am
Hi,
how about: SELECT SUM(CASE WHEN Targetdate BETWEEN DATEADD(day,-2,GETDATE()) and GETDATE() THEN 1 ELSE 0 END) Last2days,
COUNT(m.RID) [All Days]
FROM TASK_MANAGER m
JOIN TASK_ACCESS_USERS u ON m.RID = u.TaskID
WHERE u.AccessUserID =12 and m.[Status]=0
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply