Indicate Status based on Max and Min Values

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)

Indicate Status based on Max and Min Values

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-10-19 : 12:43:05

Need help on Grouping

Group Seq Dest
1 1 31
1 2 111
1 3 121
1 4 41
2 5 141
2 6 12
2 7 61
2 8 51
2 9 41
3 10 143
3 11 13
3 12 42

If In a Group the Max Sequence Number and Min Sequence Numbers have the same last 2 digits, then the result should indicate 1 else 0

Example the abobe list give the below result
Group Status
1 0
2 1
3 0

Because , In Group 2 the max Seq Number is 9 which has 41 and the min Seq Number is 5 which has 141, the last 2 digits of both are same. Where as other groups have diffferent Numbers

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-10-19 : 13:30:00

[code]SELECT GROUPS,
CASE WHEN RIGHT(MAXVALUE,2) = RIGHT(M.MINVALUE,2) THEN 1 ELSE 0 END AS STATUS
FROM
(
Select Groups,
MAX(CASE WHEN MAXID = 1 THEN DEST ELSE NULL END)AS MAXVALUE,
MAX(CASE WHEN MINID = 1 THEN DEST ELSE NULL END)AS MINVALUE
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY GROUPS ORDER BY SEQ DESC) AS MAXID,
ROW_NUMBER()OVER (PARTITION BY GROUPS ORDER BY SEQ)AS MINID,GROUPS,DEST
from TABLE
) T
WHERE T.MAXID = 1 OR T.MINID = 1
GROUP BY GROUPS
)M
ORDER BY GROUPS[/code]

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-10-21 : 06:46:03

Thank You, Can I have the DestID of the last and first seqquences included in the list like
Group Status Start End
1 0 31 41
2 1 141 41
3 0 143 42

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 13:17:59

[code]SELECT [Group],
CASE WHEN MaxSeq%100=MinSeq%100 THEN 1 ELSE 0 END,
MinDest,MaxDest
FROM
(
SELECT [Group],
MAX(Seq) AS MaxSeq,
MIN(Seq) AS MinSeq,
MAX(Dest) AS MaxDest,
MIN(Dest) AS MinDest
FROM Table
GROUP BY [Group]
)t
[/code]

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-10-23 : 05:18:44

Thank you , But there is a small concern I need the below:

Table:List_Jobs
JobID SeqNo GroupID TimeToPick DestID Completed
1 1 1 14:23:00 31 0
1 2 1 14:23:00 61 0
1 3 1 14:23:00 62 0
1 4 1 14:23:00 141 0
1 5 2 14:43:00 41 0
1 6 2 14:43:00 11 0
1 7 2 14:43:00 12 0
1 8 2 14:43:00 141 0
1 9 3 14:53:00 41 0
1 10 3 14:53:00 142 0
1 11 4 14:59:00 42 0
1 12 4 14:59:00 61 0
1 13 4 14:59:00 11 0
1 14 4 14:59:00 143 0
2 1 1 14:27:00 31 0
2 2 1 14:27:00 61 0
2 3 1 14:27:00 62 0
2 4 1 14:27:00 141 0
2 5 2 14:29:00 41 0
2 6 2 14:29:00 11 0
2 7 2 14:29:00 12 0
2 8 2 14:29:00 142 0
2 9 3 15:53:00 42 0
2 10 3 15:53:00 142 0
2 11 4 15:59:00 42 0
2 12 4 15:59:00 61 0
2 13 4 15:59:00 11 0
2 14 4 15:59:00 142 0

Result Needed:
DestID of the Max and Min SeqNo of a GroupID sorted on TimeToPick where Completed is 0

Job_ID FiRST Last GroupID TimeToPick Status
1 31 141 1 14:23:00 0
2 31 141 1 14:27:00 0
2 41 142 2 14:29:00 0
1 41 141 2 14:43:00 1
1 41 142 3 14:53:00 0
1 42 143 4 14:59:00 0
2 42 142 3 15:53:00 1
2 42 142 4 15:59:00 1

roshana
Starting Member

31 Posts

Posted - 2009-10-23 : 06:34:24

select distinct YourTable.[Group] ,case when right(T2.minDest,2) = right(T2.maxDest,2) then 1
else 0
end Status ,minDest,maxDest
from YourTable
inner join
(select YourTable.[Group],Dest minDest,T1.maxDest maxDest from YourTable inner join
(select YourTable.[Group],Dest maxDest,minimum from YourTable inner join
(select [Group], max(Seq) maximum ,min(Seq)minimum from YourTable group by [Group]) T
on YourTable.[Group] = T.[Group] and YourTable.Seq = T.maximum) T1
on YourTable.[Group] = T1.[Group] and YourTable.Seq = T1.minimum ) T2
on
YourTable.[Group] = T2.[Group]

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-10-23 : 07:21:10

How can I get the TimeToPick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 12:48:58

[code]select JobID,
MIN(SeqNo) AS First,
MAX(SeqNo) AS Last,
GroupID,
TimeToPick,
<your Status value> -- not sure how u get it
from yourtable
group by JobID,GroupID,TimeToPick
Order by TimeToPick
[/code]

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2009-10-24 : 01:16:21

;with cte_test
as
(select
JobID,
GroupID,
max(TimeToPick) TimeToPick,
Min(SeqNo) minseq,
Max(SeqNo) maxseq
from <your table>
group by JobID, GroupID
)
select
JobID,
mi.DestID as First,
ma.DestID as Last,
GroupID,
TimeToPick,
case when Right(cast(mi.DestID as varchar), 2) = Right(cast(ma.DestID as varchar), 2)
then 1
else 0 end Status
from cte_test t1
cross apply (select DestID from <your table> m
where m.JobID = t1.JobID and m.GroupID = t1.GroupID and m.SeqNo = t1.minseq) mi
cross apply (select DestID from <your table> m
where m.JobID = t1.JobID and m.GroupID = t1.GroupID and m.SeqNo = t1.maxseq) ma

--------------------------------------------------
S.Ahamed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-24 : 04:55:43

quote:
Originally posted by pbguy

;with cte_test
as
(select
JobID,
GroupID,
max(TimeToPick) TimeToPick,
Min(SeqNo) minseq,
Max(SeqNo) maxseq
from <your table>
group by JobID, GroupID
)
select
JobID,
mi.DestID as First,
ma.DestID as Last,
GroupID,
TimeToPick,
case when Right(cast(mi.DestID as varchar), 2) = Right(cast(ma.DestID as varchar), 2)
then 1
else 0 end Status
from cte_test t1
cross apply (select DestID from <your table> m
where m.JobID = t1.JobID and m.GroupID = t1.GroupID and m.SeqNo = t1.minseq) mi
cross apply (select DestID from <your table> m
where m.JobID = t1.JobID and m.GroupID = t1.GroupID and m.SeqNo = t1.maxseq) ma

--------------------------------------------------
S.Ahamed

is cte necessary here?

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-10-26 : 06:21:35

Thank you so much.
How can I make this Result set Conditional on First (DestID) or Last (DestID) and Sort it by say TimeToPick?

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-10-26 : 06:48:54

Sorry, I still have trouble in using this query.
Can I please get answer to this problem :

JobType SeqNo GID Wait DestID SubPrg Status
1 1 1 0 31 0 False
1 2 1 0 11 1 False
1 3 1 0 21 151 False
1 4 1 0 41 0 False
1 5 2 20 141 0 False
1 6 2 20 12 51 False
1 7 2 20 61 201 False
1 8 2 20 42 0 False
1 9 3 20 142 0 True
1 10 3 20 12 51 False
1 11 3 20 63 301 False
1 12 3 20 42 0 False
1 13 4 20 142 0 True
1 14 4 20 12 51 False
1 15 4 20 63 301 False
1 16 4 20 42 0 False
1 17 5 20 142 0 False
1 18 5 20 12 51 False
1 19 5 20 62 252 False
1 20 5 20 43 0 False
1 21 6 20 143 251 False

I need to update Status based on the same logic I had earlire mentioned. nI think this would solve my problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources