SEO

#1 (**permalink**) 02-29-2008, 08:31 PM

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.

#2 (**permalink**) 02-29-2008, 08:31 PM

On 28 Sep 2006 07:24:36 -0700, 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.

Hi holmm,

Try if this gets you the desired results:

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

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

#3 (**permalink**) 02-29-2008, 08:31 PM

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'

#4 (**permalink**) 02-29-2008, 08:31 PM

holmm wrote:

> Is the best approach to use a cursor?

Almost never.

#5 (**permalink**) 02-29-2008, 08:31 PM

Ed Murphy wrote:

> holmm wrote:
>
> > Is the best approach to use a cursor?
>
> Almost never.

Thanks for the replys. I'll go for the simply insert statement rather
than a cursor.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Update Query containg static data and data from another table.