Can't be this difficult - INSERT INTO

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

SQL Server 2005 Forums

Transact-SQL (2005)

Can't be this difficult - INSERT INTO

Author

Topic

rboursaw
Starting Member

1 Post

Posted - 2008-04-06 : 19:44:56

I'm pretty new to this so I'll explain as best I can.

I am building a small DB which will track attendance for employees based on a point system. I believe I'm almost there (with this piece) but am stuck.

The basic concept:
1. Collect all records from the attendance table for the previous 14 day period
2. sum the points column in the attendance table and group by UID, storing in a new table called TOTAL_POINTS ONLY for those UID's which have a value > 0
3. Perform a basic insert into statement on the attendance table for each UID matching those found in the previous TOTAL_POINTS table

Number 3 is where I'm failing and could really use some help.

My code thus far...
-------------------------------

/*Declare local variables for current date and start date.*/
--
DECLARE @DateNow DATETIME
DECLARE @StartDate DATETIME
SET @DateNow=getdate()
SET @StartDate = DATEADD(Day, -14, @DateNow)
--
/*Create table to hold totals for future calculations*/
CREATE TABLE POINT_TOTALS
(UID int, TOTAL float)

/*select ALL records from the table within the above defined date range
and group them by UID tallying the score column*/
--
INSERT INTO POINT_TOTALS
SELECT UID, SUM (POINTS) AS TOTAL_POINTS
FROM attendance
WHERE date >= @StartDate
GROUP BY UID
--
/*If the TOTAL_POINTS > 0 for the 14 day period, insert a record in to the
attendance table which deducts .5 points for the UID in question*/

*** This is where I'm failing ***

--This was just to make sure I was returning the correct results to the POINTS_TOTAL table.
SELECT UID FROM POINT_TOTALS
WHERE TOTAL > 0

/*All I want to do now is for each of the UID's in the POINT_TOTALS table,
I want to perform a basic insert on the ATTENDANCE table where the UID's in both
match. I would think this to be fairly simple but I can't seem to figure it out.
*/

DROP TABLE POINT_TOTALS

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-04-06 : 22:51:19

You say you only want to insert for records where the UIDs match, but all the UIDs in POINT_TOTALS came from the attendance table so they all match, therefore you don't have to check for a match? Give some sample data and it will be more clear what you want to do.

bfoster
Starting Member

30 Posts

Posted - 2008-04-07 : 17:49:19

I don't know if this is simplifying it too much, but are you looking for something like

DECLARE @Now DATETIME
SET @Now = GETDATE()

INSERT INTO ATTENDANCE(UID, POINTS, DATE)
SELECT UID, -0.5, @Now
FROM POINT_TOTALS
WHERE TOTAL > 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources