Posted by ZeldorBlat on 09/28/56 11:59

holmm wrote:
> Hi,
>
> First post so apologies if this sounds a bit confusing!!
>
> I'm trying to run the following update. On a weekly basis i want to
> insert all the active users ids from a users table into a timesheets
> table along with the last day of the week and a submitted flag set to
> 0. I plan then on creating a schduled job so the script runs weekly.
> The 3 queries i plan to use are below.
>
> Insert statement:
>
> INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,
> TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)
> VALUES ('user ids', 'week end date', '0')
>
> Get User Ids:
>
> SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'
>
> Get last date of the week
> SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)
>
> I'm having trouble combing them as i'm pretty new to this. Is the best
> approach to use a cursor?
>
> If you need anymore info let me know. Thanks in advance.

You've already got all the parts you need. Just stick 'em together :)

INSERT INTO TBL_TIMESHEETS (USER_ID, WEEK_ENDING, IS_SUBMITTED)
SELECT USER_ID, DATEADD(wk, DATEDIFF(wk, 0, getdate()), 6), 0
FROM TBL_USERS
WHERE IS_ACTIVE = '1'

• Next in forum: Re: insert statement blocked
• Prev in forum: Re: this is very, very, very frustrating!!!!!!
• Thread view: Re: Update Query containg static data and data from another table.

[Reply to this message]