I am looking for a better solution to achieve following task
I have a table as shown below
CouponNo Status Sequence
A101 Completed 1
A102 Completed 2
A103 Completed 3
A104 Serving 4
A105 Pending 5
A106 Pending 6
A107 Pending 7
A108 Pending 8
A109 Pending 9
A110 Pending 10
A111 Pending 11
A112 Pending 12
A113 Pending 13
A114 Pending 14
A115 Pending 15
I have a case where tokens can be put on hold. Policy says when we resume hold tokens it should be placed after 3 tokens from the currently serving token In our example lets say A105 and A106 are put on hold. Then the table becomes
CouponNo Status Sequence
A101 Completed 1
A102 Completed 2
A103 Completed 3
A104 Serving 4
A105 Hold -99
A106 Hold -99
A107 Pending 5
A108 Pending 6
A109 Pending 7
A110 Pending 8
A111 Pending 9
A112 Pending 10
A113 Pending 11
A114 Pending 12
A115 Pending 13
Either I put sequence as -99 for all holded tokens or is it better to move them to HOLD table?
secondly when we resume both tokens, i want the table like below
CouponNo Status Sequence
A101 Completed 1
A102 Completed 2
A103 Completed 3
A104 Serving 4
**A105 Pending 8**
**A106 Pending 9**
A107 Pending 5
A108 Pending 6
A109 Pending 7
A110 Pending 10
A111 Pending 11
A112 Pending 12
A113 Pending 13
A114 Pending 14
A115 Pending 15
How we can achieve this by creating a common procedure.
hi
i tried to do this ...
hope this helps ... 
its not complete !!!!
please click arrow to the left for "drop create data script"
drop table #data
go
create table #data
(
CouponNo varchar(100),
Status varchar(100),
Sequence int
)
go
insert into #data select 'A101','Completed',1
insert into #data select 'A102','Completed',2
insert into #data select 'A103','Completed',3
insert into #data select 'A104','Serving',4
insert into #data select 'A105','Pending',5
insert into #data select 'A106','Pending',6
insert into #data select 'A107','Pending',7
insert into #data select 'A108','Pending',8
insert into #data select 'A109','Pending',9
insert into #data select 'A110','Pending',10
insert into #data select 'A111','Pending',11
insert into #data select 'A112','Pending',12
insert into #data select 'A113','Pending',13
insert into #data select 'A114','Pending',14
insert into #data select 'A115','Pending',15
go
select * from #data
go
drop table #tokens_hold
go
create table #tokens_hold
(
token_hold varchar(10)
)
go
insert into #tokens_hold select 'A105'
insert into #tokens_hold select 'A106'
go
select * from #tokens_hold
go
please click arrow to the left for "SQL"
UPDATE a
SET a.status = 'HOLD',
a.sequence = -99
FROM #data a
JOIN #tokens_hold b
ON a.couponno = b.token_hold;
WITH cte
AS (SELECT Row_number()
OVER(
ORDER BY sequence) AS rn,
*
FROM #data
WHERE sequence <> -99)
UPDATE a
SET a.sequence = b.rn
FROM #data a
JOIN cte b
ON a.couponno = b.couponno
go
@ harishgg1
thank you for your effort. Actually the problem lies down when we resume
hi
i have the final part after RESUMING ..please check if correct -)
please click arrow to the left for "Before Resuming SQL Script"
drop table #data
go
create table #data
(
CouponNo varchar(100),
Status varchar(100),
Sequence int
)
go
insert into #data select 'A101','Completed',1
insert into #data select 'A102','Completed',2
insert into #data select 'A103','Completed',3
insert into #data select 'A104','Serving',4
insert into #data select 'A105','Pending',5
insert into #data select 'A106','Pending',6
insert into #data select 'A107','Pending',7
insert into #data select 'A108','Pending',8
insert into #data select 'A109','Pending',9
insert into #data select 'A110','Pending',10
insert into #data select 'A111','Pending',11
insert into #data select 'A112','Pending',12
insert into #data select 'A113','Pending',13
insert into #data select 'A114','Pending',14
insert into #data select 'A115','Pending',15
go
--select 'data',* from #data
--go
drop table #tokens_hold
go
create table #tokens_hold
(
token_hold varchar(10)
)
go
insert into #tokens_hold select 'A105'
insert into #tokens_hold select 'A106'
go
--select 'tokens put on hold',* from #tokens_hold
--go
UPDATE a
SET a.status = 'HOLD',
a.sequence = -99
FROM #data a
JOIN #tokens_hold b
ON a.couponno = b.token_hold;
WITH cte
AS (SELECT Row_number()
OVER(
ORDER BY sequence) AS rn,
*
FROM #data
WHERE sequence <> -99)
UPDATE a
SET a.sequence = b.rn
FROM #data a
JOIN cte b
ON a.couponno = b.couponno
go
select 'output after hold',* from #data
go
After resuming from hold
please click arrow to the left for AFTER resuming SQL Script
; WITH rncte
AS (SELECT Row_number()
OVER(
ORDER BY (SELECT NULL)) AS rn,
*
FROM #data),
nextcte
AS (SELECT *
FROM rncte a
WHERE rn <= (SELECT Max(rn) + 3
FROM rncte
WHERE status = 'HOLD')),
okcte
AS (SELECT CASE
WHEN status = 'HOLD' THEN rn + 3
ELSE rn
END AS rn1,
*
FROM nextcte),
part1cte
AS (SELECT couponno,
CASE
WHEN sequence = -99 THEN 'Pending'
ELSE status
END AS Status,
CASE
WHEN sequence <> -99 THEN sequence
ELSE rn1
END AS sequence
FROM okcte),
part2cte
AS (SELECT couponno,
status,
sequence
FROM rncte
WHERE rn > (SELECT Max(sequence) + 2
FROM part1cte))
SELECT *
FROM part1cte
UNION ALL
SELECT *
FROM part2cte
go
Thank you for your efforts.
But I think my requirements are not met with this.
I must explain it again. When we hold the token and resume following conditions may happen
-
There is one customer being served. in the example quoted above its A104.
-
Lets say currently served token is A105 and i put him on hold. next token is A106 and i put him on hold too. And immediately i want to resume A105 back to pending without serving any other one. In this case no one is getting "served"
-
Token A101 is the first token and i put him on hold and want to resume back. at this stage no one is getting served and no completed tokens.
-
Now lets say token A114 is on hold (As per our example above) and want to resume. Only one token is left after him. So there is no point in adding 3 to his sequence number.
I have created two procedures
create procedure TokenHoldProc(@TokenNo varchar(10))
as
begin
update tblTokenQueue set TokenSequence='-99',TokenStatus='Hold' where TokenNo=@TokenNo
end
create procedure TokenResumeProc(@TokenNo varchar(10),@TokenPushCount int)
as
begin
declare @ServingPosition int
select @ServingPosition=TokenSequence from tblTokenQueue where TokenStatus='Serving'
if (@ServingPosition is null) --if no one is getting served at present
select @ServingPosition=TokenSequence from tblTokenQueue where TokenStatus='Completed'
if (@ServingPosition is null) --if its the first token
update tblTokenQueue set TokenSequence = 1 where TokenNo=@TokenNo;
else
begin
update tblTokenQueue set TokenSequence = TokenSequence + 1 where TokenSequence >=@ServingPosition+(@TokenPushCount+1);
update tblTokenQueue set TokenSequence = @ServingPosition+(@TokenPushCount+1),TokenStatus='Pending' where TokenNo=@TokenNo;
end
else
begin
update tblTokenQueue set TokenSequence = TokenSequence + 1 where TokenSequence >=@ServingPosition+(@TokenPushCount+1);
update tblTokenQueue set TokenSequence = @ServingPosition+(@TokenPushCount+1),TokenStatus='Pending' where TokenNo=@TokenNo;
end
end
As per these procedures first 3 cases will be met.
there are 2 issues
- case number 4
- if i want to resume 2 hold tokens one after the other
can you help with this
hi
for these points ..
- case number 4
- if i want to resume 2 hold tokens one after the other
you need to add logic ... within SQL .. or code
you can also use TEMP Tables ...
i might not be able to help you .. as i am occupied with other things
any experts on this forum can help you !!!
Okay. Anyways Thank you !
hope this helps
case number 4
Now lets say token A114 is on hold (As per our example above) and want to resume. Only one token is left after him. So there is no point in adding 3 to his sequence number.
Only one token is left after him.
A114 = Now lets say token A114 is on hold (As per our example above) and want to resume
select count(couponno) where couponno Row number > 'A114'
Only one token is left after him. So there is no point in adding 3 to his sequence number.
select case when count(couponno) > 1 then THEN rn + 3 else rn
