Advice on a large query 1

[johnrg1]

Hi All,

I am about to write a Stored Procedure and thought i would ask if anyone had any advice.

The query is to take values from a default table, and add them to another "live" table.

Every monday an "A" shift is to take place. - From the default table

I want to take the that shift and make an entry in the live table for every Monday between two dates entered into the query and add it with the ID for Shift A.

If they were say 1/1/2000 and 31/1/2000 every Monday within these dates would have a Shift "A" added to it.

I want to do this for every value in the default table.

So, if I made any sense, have you any suggestions?

I am assuming I will need some sort of loop in the query and 7 if then statements to distinguish between the days of the week. But I do not know where to start...

 

[donutman]

An example of your data from the two tables and the result you are looking for would probably help.
-Karl

 

[SemperFiDownUnda]

take a look at DATEPART() function.

Should not be a need to loop if I understand you

You could just use

WHERE DATEPART(dw,yourdate) = 1

to your limit only your query to only days (via yourdate) that fall on a monday

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[johnrg1]

Thanks for your replies guys,

A bit more information as you requested.

Default Table:

ID ShiftID Day
1 1 1
2 3 1
3 5 2
4 1 2

Rota Table

RotaID ShiftID Date
1 1 1/1/2000


I want to take all the default values for the week days, and add them to the rota table. So for every Monday between 1/1/2004 and 31/1/2004 2 entries will be made with the relevant date 5/1/2004 and the shiftID 1 and then another with shiftID 3.

This will then be repeated for each day.

I am now puzzled as to how to implement this, the more i think about it, the more confused i get

 

[VintageWine]

DECLARE @j smalldatetime
SELECT @j='20040101'

-- Find first Monday

WHILE DATEPART(dw,@j)<>2
  SELECT @j=DATEADD(dd,1,@j)

-- Form every Monday in month

WHILE @j<'20040201'
BEGIN

    INSERT INTO Rota
      (ShiftID,
      [Date])
    SELECT 
      ShiftID,
      DATEADD(dd,Day-1,@j)
    FROM
      [Default]

END

Not tested

Have fun
Simon

 

[johnrg1]

Simon,

Please forgive my ignorance, could you please explain how this will work?

WHILE DATEPART(dw,@j)<>2 - Why not equal to 2?


I am thinking of something like this


WHILE @j <= @LastDate

IF DATEPART(dw,@j) = 1

BEGIN
INSERT INTO Rota
(ShiftID,
[Date])
SELECT
ShiftID,
@j
FROM
[Default]

SELECT @j=DATEADD(dd,1,@j)
END


Do you feel this would work? - typically i am out of the office all day. I'll just have to wiat till i get home tonight to test it out

 

[VintageWine]

>WHILE DATEPART(dw,@j)<>2 - Why not equal to 2?
It is incrementing @j to the first Monday and then allowing the script to continue.

>I am thinking of something like this
It depends what you want. Your insert statment will put a full copy of defaut table into Rota for every day in the month. I assumed that Default.Day meant:
1 = This shift on Monday
2 = This shift on Tuesday

>There is a glaring error in my code which you have picked up on: I don't increment the loop counter. As my SQL does a week at a time it should have been
SELECT @j=DATEADD(dd,7,@j)

I think with a bit of trial and error you can now acheive your solution.

Simon

 

[johnrg1]

Thank you for your help.

I'll let you know if I am successful or not. But fingers crossed

John

 

[johnrg1]

It got it to work

My solution is

DECLARE @start smalldatetime
DECLARE @end smalldatetime
SELECT @start ='20040101'
SELECT @end = '20040201'

WHILE @start <= @end
BEGIN
IF DATEPART(dw,@start) = 1
BEGIN
INSERT INTO tbl_Rota
(ShiftID,
[Date])
SELECT
ShiftID, @start
FROM
tbl_DefaultRota
WHERE
day = '1'
END
SELECT @start = DATEADD(dd,1,@start)
END

The biggest problem i had was rembering that day 1 = Sunday in SQL, but day 1 = Monday in VB.NET :/

Thanks for your help guys