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. 4 3169
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
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'
holmm wrote:
Is the best approach to use a cursor?
Almost never.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: kalamos |
last post by:
This statement fails
update ded_temp a
set a.balance = (select sum(b.ln_amt)
from ded_temp b
where a.cust_no = b.cust_no
and a.ded_type_cd = b.ded_type_cd
and a.chk_no = b.chk_no
group by cust_no, ded_type_cd, chk_no)
|
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then...
|
by: rrh |
last post by:
I am trying to update a field in one table with data from another
table. The problem I'm running into is I need to base the update on a
range of data in the 2nd table.
Table 1 has:
date field
new field
table 2 has:
key field (autonumber)
|
by: JMCN |
last post by:
hi
i need some advice on whether if it would be better to use an append
query or an update query. here is the situation, i have linked another
database table to my current database. then i created a query for the
linked database but eventually i need to have this query to be
constantly updated and append to another table in the current...
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my predecessor, I hasten to add) so that each day it creates a
copy of the record for each company, changes the date to today's date,
and prompts the user...
| |
by: Maxi |
last post by:
There is a lotto system which picks 21 numbers every day out of 80
numbers.
I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21)
Here is the structure and sample data:
"Date","P1","P2","P3","P4","P5","P6","P7","P8","P9","P10","P11","P12","P13","P14","P15","P16","P17","P18","P19","P20","P21"...
|
by: jjturon |
last post by:
Can anyone help me??
I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.
Ex.
SalesManID SalesManName
AT Alan Time
|
by: eholz1 |
last post by:
Hello PHP programmers.
I had a brilliant idea on one of my pages that selects some data from
my mysql database.
I first set the page up to display some info and an image, just one
item, with a row of data, etc.
then I thought it would be nice to do a select, and perhaps an update
(the title of the image) on the same page.
|
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for
renaming the duplicate records? My thinking was to take the results of the
duplicate query, and somehow have it number each line where there is a
duplicate...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |