Select on Groups - 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)

Select on Groups

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-10-19 : 08:29:10

Here is the query I use to retrieve data

SELECT
T1.[JobID]
,T1.[CastID]
,T1.[SequenceNo]
,T1.[GroupID]
,Convert(Varchar(8)
,T1.[WaitTime],108)
,T1.[DestID]
,T1.[SubProgram]
,T1.[CompleteStatus]
FROM [TEST].[dbo].[List_Jobs] T1
CROSS APPLY (SELECT COUNT(*) AS CummGroupCount
FROM [TEST].[dbo].[List_Jobs] WHERE [JobID] = T1.[JobID] AND [GroupID] = T1.[GroupID] ) T2
WHERE ([WaitTime] <= GETDATE()AND [CompleteStatus] = 0) AND [GroupID] <> 99 AND [CastID] = @vcCastID AND CummGroupCount <=16
ORDER BY [WaitTime] ASC, [JobID] ASC, [SequenceNo] ASC
END

I would like to have the results modified to include selections that have even one CompleteStatus 0 (Now the query checks for all Complete Status 0)

Can any one help?

Thanks in Advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-19 : 08:32:47

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-10-19 : 08:39:26

Job Csst ID Seq Group Time Path Sub-Prg Com-Status
1 KP35001 1 1 16:12:32 31 0 0
1 KP35002 2 1 16:12:32 11 111 0
1 KP35003 3 1 16:12:32 21 121 0
1 KP35004 4 1 16:12:32 41 0 0

Need results like this also:
Job Csst ID Seq Group Time Path Sub Prg Com Status
1 KP35001 1 1 16:12:32 31 0 1
1 KP35002 2 1 16:12:32 11 111 1
1 KP35003 3 1 16:12:32 21 121 0
1 KP35004 4 1 16:12:32 41 0 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-19 : 09:54:57

What is the logic for assigning 1 for com-status?

Madhivanan

Failing to plan is Planning to fail

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-10-19 : 11:56:36

This belongs to a group (Group 1) and if the com-status is 0 or 1(Bit) , If the complete group is not completed , I want the selection list to show them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-20 : 02:08:57

quote:
Originally posted by coagulance

This belongs to a group (Group 1) and if the com-status is 0 or 1(Bit) , If the complete group is not completed , I want the selection list to show them.

com-status is 0 for all the rows. How do you assign 1 for first two rows?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources