• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

sql Data rotator

Dear all,
I got this table:
StudentName    : InQueue : DisplayStatus
Student Namex : 1               : True
Student Namey : 2               : False
Student Namez : 3               : False
Student Namea : 4               : False
Student Nameb : 5               : False

I need to change the Display field to be True each 1 minute according to InQueue and change the DisplayStatus for previous row to FALSE


I already add scheduled job (SQL)  to run each 1 minute.

My question is, what the sql scrip should I execute on that scheduled job?

thanks
0
ethar1
Asked:
ethar1
  • 7
  • 5
1 Solution
 
tim_csCommented:
So InQueue 1 should always be the one that is true?

UPDATE tableName
   SET DisplayStatus = 1
WHERE
   InQueue = 1

UPDATE tableName
   SET DisplayStatus = 0
WHERE
   InQueue != 1



OR


UPDATE tableName
   SET DisplayStatus = CASE WHEN InQueue = 1 THEN 1 ELSE 0 END
0
 
ethar1Author Commented:
If I run this every minute
i will got the same result.
UPDATE tableName
   SET DisplayStatus = 1
WHERE
   InQueue = 1

I need to rotat throu rows
0
 
ethar1Author Commented:
first excute find the most top ( small value of InQueue  ) then update the next row in queue to be DisplayStatus = 1, and set DisplayStatus = 0 for most finded one.

and if last row in the queue reset the value again to the most top.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
tim_csCommented:
DECLARE @CurrentDisplay INT

SELECT
   @CurrentDisplay = CASE WHEN InQueue = (SELECT MAX(InQueue) FROM YourTable) THEN 1 ELSE InQueue + 1 END
FROM
   YourTable
WHERE
   DisplayStatus = 1

UPDATE tableName
   SET DisplayStatus = CASE WHEN InQueue = @CurrentDisplay THEN 1 ELSE 0 END
0
 
ethar1Author Commented:
ok, bbut wot if the next item more then +1?
also what if  there is no more item , ( it should go for the first one.).
0
 
tim_csCommented:
My script provided will go back to the first value when it hits the last one.  Your example didn't show it was possible for the next item to be more than plus one.  So you're saying the InQueue values could be 1,2,3,4,5, then 7?

DECLARE @CurrentDisplay INT

SELECT
    @CurrentDisplay = CASE WHEN InQueue = (
                                           SELECT
                                            MAX(InQueue)
                                           FROM
                                            YourTable
                                          ) THEN (
                                                  SELECT
                                                    MIN(InQueue)
                                                  FROM
                                                    YourTable
                                                 )
                           ELSE (
                                 SELECT TOP 1
                                    InQueue
                                 FROM
                                    YourTable
                                 WHERE
                                    InQueue > @CurrentDisplay
                                 ORDER BY
                                    InQueue DESC
                                )
                      END
FROM
    YourTable
WHERE
    DisplayStatus = 1



UPDATE
    tableName
SET 
    DisplayStatus = CASE WHEN InQueue = @CurrentDisplay THEN 1
                         ELSE 0
                    END 

Open in new window

0
 
ethar1Author Commented:
yes,
Also I try you code and give worng result
0
 
tim_csCommented:
I need more info than that to help you out any more.  What do you mean it gave wrong result?
0
 
ethar1Author Commented:
ok, first of all,
I use this to get current display queue ID
     @CurrentDisplay = ProcessingID from mytable
    order by InQueueDisplayStatus desc , InQueue asc
0
 
tim_csCommented:
Update my script.  Included a test case for this one.  

Just run the create and populate temp table first then you can run the script over and over to test.  

/*
CREATE TABLE #tempTable(StudentName varchar(20), InQueue int, DisplayStatus bit)
	
INSERT INTO #tempTable
        (
         StudentName
        ,InQueue
        ,DisplayStatus
        )
VALUES
       ('Student Namex',1,1),
('Student Namey',2, 0),
('Student Namez',3,0),
('Student Namea',4,0),
('Student Nameb',5,0),
('Student Namec',8,0)

--DROP TABLE #tempTable

*/


DECLARE @CurrentDisplay INT



SELECT
    @CurrentDisplay = CASE WHEN InQueue = (
                                           SELECT
                                            MAX(InQueue)
                                           FROM
                                            #tempTable
                                          ) THEN (
                                                  SELECT
                                                    MIN(InQueue)
                                                  FROM
                                                    #tempTable
                                                 )
                           ELSE (
                                 SELECT TOP 1
                                    InQueue
                                 FROM
                                    #tempTable
                                 WHERE
                                    InQueue > t.InQueue
                                 ORDER BY
                                    InQueue
                                )
                      END
FROM
    #tempTable t
WHERE
    DisplayStatus = 1

UPDATE
    #tempTable
SET 
    DisplayStatus = CASE WHEN InQueue = @CurrentDisplay THEN 1
                         ELSE 0
                    END 

SELECT
    *
FROM
    #tempTable

Open in new window

0
 
ethar1Author Commented:
I use this to move around
   DECLARE @CurrentDisplay INT
   DECLARE @MaxID INT
   DECLARE @NextID INT
   DECLARE @InQueue INT
   

SELECT TOP 1
     @CurrentDisplay =  ProcessingID , @InQueue = InQueue from mytable  
    where (StatusID = 2 and MonitorID = 1) order by InQueueDisplayStatus desc , InQueue asc
   
SELECT TOP 1
      @NextID = ProcessingID from mytable  
    where (StatusID = 2 and MonitorID = 1 and InQueueDisplayStatus = 0 and InQueue >@InQueue) order by  InQueue asc


update mytable  set InQueueDisplayStatus = 0 where ProcessingID=@CurrentDisplay;
update mytable  set InQueueDisplayStatus = 1 where ProcessingID=@NextID;
0
 
ethar1Author Commented:
wot about this:


   DECLARE @CurrentDisplay INT
   DECLARE @MaxID INT
   DECLARE @NextID INT
   DECLARE @InQueue INT
   DECLARE @FirstID INT
   


SELECT  TOP (1) @FirstID =  ProcessingID FROM   dbo.mytable 
WHERE     (MonitorID = 1) AND (StatusID = 2)GROUP BY ProcessingID


SELECT TOP 1
     @CurrentDisplay =  ProcessingID , @InQueue = InQueue from mytable  
    where (StatusID = 2 and MonitorID = 1) order by InQueueDisplayStatus desc , InQueue asc
    
SELECT TOP 1
      @NextID = ProcessingID from mytable  
    where (StatusID = 2 and MonitorID = 1 and InQueueDisplayStatus = 0 and InQueue >@InQueue) order by  InQueue asc

select @NextID = ISNULL(@NextID, @FirstID);

update mytable  set InQueueDisplayStatus = 0 where ProcessingID=@CurrentDisplay;
update mytable  set InQueueDisplayStatus = 1 where ProcessingID=@NextID;
   
   
Select ProcessingID, InQueue, InQueueDisplayStatus from mytable  order by  InQueue asc

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now