need help..select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need help..select query

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-03-24 : 06:03:07

Hi all....
I have many tables in a module but here i am specifying three tables from which i have to fetch my data..
Those three tables are User_Login,Task_assignTask,Task_StatusUpdate
Structure of tables is as follows..
User_Login
[UserId]----primary key
,[UserName]
,[Password]
,[UserLevel]
,[RegDate]
,[LastLoginDate]
,[LastPasswordChangeDate]
,[isActivated]
,[DisplayName]
Task_assignTask
[AssignId]---primary key
,[AssignSerialNo]
,[AssignDesc]
,[FromDate]
,[ToDate]
,[IsForwarded]
,[AssignTo]
,[IsRecursive]
,[TaskId]
,[SubTaskId]
,[CreatedBy]
,[CompanyId]
,[DeptId]
,[ReAssigned]
,[CreatedOn]
,[Priority]
Task_StatusUpdate
[Sno]
,[UpdationDate]
,[ProgressPercent]
,[Remarks]
,[UserId]--foreign key
,[AssignId]--foreign key
,[StatusId]--foreign key from one other table task_status
,[VarifiedOn]
,[CountReopenTask]

I have to select UserId,DisplayName,TotalAssignTask to any user.

Flow of data is as follows whenever any task is assigned to a user a new assignId is created .One more table ,table status consists of 6 rows having different status id as 1,2,3,4,5,6 representing
Task_Status table:-
statusid statusname .......
1 Open Open Status False False
2 InProgress NULL True True
3 Completed Status Complete True True
4 ReOpen ReOpen Status False True
5 Dismissed dismissed status False False
6 Verified Verified and Completed False False
NULL NULL NULL NULL NULL

Hope i am able to give some detail about the problem...looking forward to some help..

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-26 : 11:50:18

Can you explain TotalAssignTask which you want to return? This isn't a field in your tables. Is this a calculation?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 04:26:34

[code]
SELECT ul.[UserId],ul.[DisplayName],t.TotalAssignTask
FROM User_Login ul
INNER JOIN
(SELECT [UserId],
COUNT(CASE WHEN st.statusname <> 'Dismissed' THEN s.[AssignId] ELSE NULL END) AS TotalAssignTask
FROM [Sno] s
JOIN Task_Status st
ON st.Statusid=s.statusid
GROUP BY [UserId]
)t
ON t.[UserId]=ul.[UserId]
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources