While Loop or For Loop - 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

General SQL Server Forums

New to SQL Server Programming

While Loop or For Loop

Author

Topic

velvet_tiger
Starting Member

12 Posts

Posted - 2013-10-07 : 16:36:01

Hi ,

Is it possible to do a While or For Loop in SQL?

I have the below case statement but this case statement breaks out of the case statement once the criteria has been met. I would like it to continue reading all of the fields in the table before it breaks out of the case statement.

Is that possible.

[Code]

(case when Time1 = '0810-0900' THEN '0810-0900'
when Time2 = '0910-1000' THEN '0910-1000'
when Time3 = '1010-1100' THEN '1010-1100'
when Time4 = '1110-1200' THEN '1110-1200'
when Time5 = '1210-1300' THEN '1210-1300'
when Time6 = '1310-1400' THEN '1310-1400'
when Time7 = '1410-1500' THEN '1410-1500'
when Time8 = '1510-1600' THEN '1510-1600'
when Time9 = '1610-1700' THEN '1610-1700'
when Time10 = '1710-1800' THEN '1710-1800'
when Time11 = '1810-1900' THEN '1810-1900'
when Time12 = '1910-2000' THEN '1910-2000'
when Time13 = '2010-2100' THEN '2010-2100'
ELSE '0'
END)
[/code]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 16:42:42

quote:
Originally posted by velvet_tiger

Hi ,

Is it possible to do a While or For Loop in SQL?

I have the below case statement but this case statement breaks out of the case statement once the criteria has been met. I would like it to continue reading all of the fields in the table before it breaks out of the case statement.

Is that possible.

[Code]

(case when Time1 = '0810-0900' THEN '0810-0900'
when Time2 = '0910-1000' THEN '0910-1000'
when Time3 = '1010-1100' THEN '1010-1100'
when Time4 = '1110-1200' THEN '1110-1200'
when Time5 = '1210-1300' THEN '1210-1300'
when Time6 = '1310-1400' THEN '1310-1400'
when Time7 = '1410-1500' THEN '1410-1500'
when Time8 = '1510-1600' THEN '1510-1600'
when Time9 = '1610-1700' THEN '1610-1700'
when Time10 = '1710-1800' THEN '1710-1800'
when Time11 = '1810-1900' THEN '1810-1900'
when Time12 = '1910-2000' THEN '1910-2000'
when Time13 = '2010-2100' THEN '2010-2100'
ELSE '0'
END)
[/code]

The result of that expression is a single string - anything from '0810-0900' to '0'. When you say it should read all the cases, what is the output you are expecting? Even if it read, the condition would not be satisfied, right? And even if the condition were to be satisfied, the output can be only one of those several choices.

Can you describe what you are trying to do - people on the forum may be able to offer solutions that may not necessarily use a while loop or case expression.

SQL is not very good at handling sequential while loop type of constructs. It likes to be told what it should do and have it do in a set-based manner.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-07 : 16:46:47

I really have no idea what you want to do. As James suggested, perhaps a description of what you want to do along with some sample data and expected output can help us to help you. Here are some links that can help you prepare your question so we can offer better assistance:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

velvet_tiger
Starting Member

12 Posts

Posted - 2013-10-07 : 17:39:34

Hi Guys,

Thank you for your response.

I am trying to output the free space from a rosters. Now the roster information looks like this


ROOM    DAY        0810-0900    0910-1000    1010-1100    1110-1200    1210-1300      1310-1400     1410-1500

A27	Monday	   0810-0900   FOUN1008-L06 FOUN1008-L07  1110-1200   1210-1300      LING6102-L01   HIST1004-T01
A27	Tuesday    0810-0900   HIST1008-L06 HIST1008-L07  1110-1200    LING6102-L01) 1310-1400 	 HIST1004-T01
A27	Wednesday  0810-0900   0910-1000    FOUN1008-L07  1110-1200    1210-1300     1310-1400	 HIST1004-T01
A27	Thursday   0810-0900   FOUN1008-L06 1010-1100     HIST1008-L07  HIST1008-L07 1310-1400      LING6102-L01	
A27	Friday   0810-0900  FOUN1008-L06 FOUN1008-L07  1110-1200     1210-1300    LING6102-L01	1410-1500		
[code] 

Of course there are hundreds of rooms with different courses. I would like to output the times that the rooms are free. A room is free when you see the actual times.

Below is the code that I used to generate the above information:

[code]
select  ssrmeet_room_code, 
        ssrmeet_bldg_code,
           
                
      (case 
          when ssrmeet_SUN_DAY = 'U' then 'Sunday'
          when ssrmeet_MON_DAY = 'M' then 'Monday'
          when ssrmeet_TUE_DAY = 'T' then 'Tuesday'
          when ssrmeet_WED_DAY = 'W' then 'Wednesday'
          when ssrmeet_THU_DAY = 'R' then 'Thursday'
          when ssrmeet_FRI_DAY = 'F' then 'Friday'
          when ssrmeet_SAT_DAY = 'S' then 'Saturday' 
          ELSE '0'
          END)DAYS,
       
         Max(case when (SSRMEET_BEGIN_TIME <= '0810' and SSRMEET_END_TIME >= '0900') OR (SSRMEET_BEGIN_TIME <= '0800' and SSRMEET_END_TIME >= '0810') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb||'-'||s.ssbsect_seq_numb  ELSE '0810-0900' END) "0810-0900",
         Max(case when (SSRMEET_BEGIN_TIME <= '0910' and SSRMEET_END_TIME >= '1000') OR (SSRMEET_BEGIN_TIME <= '0900' and SSRMEET_END_TIME >= '0910') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb||'-'||s.ssbsect_seq_numb ELSE '0910-1000' END) "0910-1000",
         Max(case when (SSRMEET_BEGIN_TIME <= '1010' and SSRMEET_END_TIME >= '1100') OR (SSRMEET_BEGIN_TIME <= '1000' and SSRMEET_END_TIME >= '1010') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb||'-'||s.ssbsect_seq_numb ELSE '1010-1100' END) "1010-1100",
         Max(case when (SSRMEET_BEGIN_TIME <= '1110' and SSRMEET_END_TIME >= '1200') OR (SSRMEET_BEGIN_TIME <= '1100' and SSRMEET_END_TIME >= '1110') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb ||'-'||s.ssbsect_seq_numb ELSE '1110-1200' END) "1110-1200",
         Max(case when (SSRMEET_BEGIN_TIME <= '1210' and SSRMEET_END_TIME >= '1300') OR (SSRMEET_BEGIN_TIME <= '1200' and SSRMEET_END_TIME >= '1210') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb ||'-'||s.ssbsect_seq_numb ELSE '1210-1300' END) "1210-1300",
         Max(case when (SSRMEET_BEGIN_TIME <= '1310' and SSRMEET_END_TIME >= '1400') OR (SSRMEET_BEGIN_TIME <= '1300' and SSRMEET_END_TIME >= '1310') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb ||'-'||s.ssbsect_seq_numb ELSE '1310-1400' END) "1310-1400",
         Max(case when (SSRMEET_BEGIN_TIME <= '1410' and SSRMEET_END_TIME >= '1500') OR (SSRMEET_BEGIN_TIME <= '1400' and SSRMEET_END_TIME >= '1410') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb ||'-'||s.ssbsect_seq_numb ELSE '1410-1500' END) "1410-1500",
         Max(case when (SSRMEET_BEGIN_TIME <= '1510' and SSRMEET_END_TIME >= '1600') OR (SSRMEET_BEGIN_TIME <= '1500' and SSRMEET_END_TIME >= '1510') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb||'-'||s.ssbsect_seq_numb ELSE '1510-1600' END) "1510-1600",
         Max(case when (SSRMEET_BEGIN_TIME <= '1610' and SSRMEET_END_TIME >= '1700') OR (SSRMEET_BEGIN_TIME <= '1600' and SSRMEET_END_TIME >= '1610') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb||'-'||s.ssbsect_seq_numb ELSE '1610-1700' END) "1610-1700",
         Max(case when (SSRMEET_BEGIN_TIME <= '1710' and SSRMEET_END_TIME >= '1800') OR (SSRMEET_BEGIN_TIME <= '1700' and SSRMEET_END_TIME >= '1710') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb||'-'||s.ssbsect_seq_numb ELSE '1710-1800' END) "1710-1800",
         Max(case when (SSRMEET_BEGIN_TIME <= '1810' and SSRMEET_END_TIME >= '1900') OR (SSRMEET_BEGIN_TIME <= '1800' and SSRMEET_END_TIME >= '1810') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb||'-'||s.ssbsect_seq_numb ELSE '1810-1900' END) "1810-1900",
         Max(case when (SSRMEET_BEGIN_TIME <= '1910' and SSRMEET_END_TIME >= '2000') OR (SSRMEET_BEGIN_TIME <= '1900' and SSRMEET_END_TIME >= '1910') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb||'-'||s.ssbsect_seq_numb ELSE '1910-2000' END) "1910-2000",
         Max(case when (SSRMEET_BEGIN_TIME <= '2010' and SSRMEET_END_TIME >= '2100') OR (SSRMEET_BEGIN_TIME <= '2000' and SSRMEET_END_TIME >= '2010') THEN s.ssbsect_subj_code||''||s.ssbsect_crse_numb||'-'||s.ssbsect_seq_numb ELSE '2010-2100' END) "2010-2100"


        
FROM  ssbsect s inner join ssrmeet m on m.ssrmeet_crn=s.ssbsect_crn  and m.ssrmeet_term_code=s.ssbsect_term_code
where s.ssbsect_term_code = '201310' 
and m.SSRMEET_ROOM_CODE  IN ('A27') 

group by ssrmeet_room_code, ssrmeet_bldg_code, (case 
          when ssrmeet_SUN_DAY = 'U' then 'Sunday'
          when ssrmeet_MON_DAY = 'M' then 'Monday'
          when ssrmeet_TUE_DAY = 'T' then 'Tuesday'
          when ssrmeet_WED_DAY = 'W' then 'Wednesday'
          when ssrmeet_THU_DAY = 'R' then 'Thursday'
          when ssrmeet_FRI_DAY = 'F' then 'Friday'
          when ssrmeet_SAT_DAY = 'S' then 'Saturday' 
          ELSE '0'
          END)
      
    
 

order by   ssrmeet_room_code , 
         (case 
          when ssrmeet_SUN_DAY = 'U' then 'Sunday'
          when ssrmeet_MON_DAY = 'M' then 'Monday'
          when ssrmeet_TUE_DAY = 'T' then 'Tuesday'
          when ssrmeet_WED_DAY = 'W' then 'Wednesday'
          when ssrmeet_THU_DAY = 'R' then 'Thursday'
          when ssrmeet_FRI_DAY = 'F' then 'Friday'
          when ssrmeet_SAT_DAY = 'S' then 'Saturday' 
          ELSE '0'
          END)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources