April 5, 2008 at 12:41 am
Quick summary:
To kick off a job which executed a stored proc at a specific time on the 1st and 15th of each month that adds up the values in a specific column in my table for each defined user in the table for the previous 14 day period and takes action based on the total for each user.
My seudo code
Set Variables:
CurrentDate = GetDate() - 1
TrackDate = GetDate() - 14 Days (not quite sure how to get this date)
For each user in TABLE1, SUM COL(PRICE) AS TotalPrice where COL(DATE) >= TrackDate AND COL(DATE) <= CurrentDate
-- IF TotalPrice > 0 ...sqlCodeHere
-- ELSE do nothing
I've been looking at tons of various examples of loops and date functions and am struggling quite a bit.
Any help with the actual T-SQL would be greatly appreciated.
Thank you!
Bob
April 5, 2008 at 7:51 am
Bob:
What I see in your description of the problems is
1. You identified a process that you can apply to a table to identify source data
2. A Conditions in which source data records can be applied to target data records
3. A loop to process all records
Your thinking process of how to deal with SQL data needs to change. Transact SQL is a language that is far more efficient at dealing with sets of data than it is at processing data record-by-record. A better thinking process for solving this problem would be something more like:
1. Identify or generate a set of data that contains all needed source data
2. Use the set of data to target and apply the data to the intended target
3. Use joins instead of loops
A process such as this completely avoids loops and avoids cursors. This does not meant that there isn't a time and a place for loops and cursors but what I would say is that loops and cursors should not generally be your first choice as a method of solving a problem. For instance in the following:
declare @userTable table
( userId varchar(9),
priceSum numeric(12, 3)
)
insert into @userTable (userId)
select 'User 1' union all select 'User 2' union all select 'User 3'
declare @aTable table
( userId varchar(9) not null,
date datetime,
price numeric(9,3)
)
insert into @aTable
select 'User 1', getdate() - 17, 14.95 union all
select 'User 1', getdate() - 15, 21.97 union all -- Not included; OK?
select 'User 1', getdate() - 14, 9.99 union all
select 'User 1', getdate() - 13, 10.00 union all
select 'User 1', getdate() - 1, 35.59 union all
select 'User 1', getdate(), 26.49 union all
select 'User 2', getdate() - 2, 0.00 union all
select 'User 3', getdate() - 8, 21.75
--select * from @atable
;with aim as
( select
userId,
sum(price) as totalPrice
from @aTable
where date >= convert(datetime, convert(varchar(8), getdate()-14, 112))
and date < convert(datetime, convert(varchar(8), getdate(), 112))
group by userId
having sum(price) > 0.00
)
update @userTable
set priceSum = totalPrice
from @userTable a
join aim b
on a.userId = b.userId
select * from @userTable
This is an example of how to update a "userTable" with price total information based on conditions similar to what you described. The "aim" common table expression (CTE) forms a set that contains (1) the UserIDs that can be used to key the update to the target table and (2) the totalPrice data that is the information that needs to be applied to the user table. The actual UPDATE statement joins the target UserTable to the aim CTE and only process userTable records that match based on the userId supplied by the aim CTE. Matching records are updated by applying the totalPrice from the aim CTE to the targeted UserTable records -- all done with a single update statement and no loops and no cursors.
Cursors can be lethal performance killers; again there is a time and a place for cursors and loops but try to avoid them as the first-choice method of completing SQL tasks. Good luck with process!
Also, it will be helpful if you can better describe the process that you are trying perform.
April 5, 2008 at 11:03 am
As previously stated, Cursor and loops of this nature WILL kill any hopes of performance...
Your pseudo code said...
-- IF TotalPrice > 0 ...sqlCodeHere
What do you want to do for "sqlCodeHere".
I recommend that you take a peek at the URL in my signature line for better, more accurate help on your problem...
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2008 at 12:33 am
Many thanks to the previous replies (much needed)
Here is what I've done so far.
Background: I am creating a DB which will track attendance of employees via a point system. I have 2 tables I'm working with; USERS and ATTENDANCE.
USERS
------
UID INT
TEAM_ID INT
FIRST_N VARCHAR(50)
LAST_N VARCHAR(50)
ENABLED BIT
ATTENDANCE
---------------
UID INT
ENTRY_TYPE TINYINT
DATE SMALLDATETIME
POINTS FLOAT
NOTES VARCHAR(MAX)
My code thus far looks like this;
/*Declare local variables for current date and start date.*/
--
DECLARE @DateNow DATETIME
DECLARE @StartDate DATETIME
SET @DateNow=getdate()
SET @StartDate = DATEADD(Day, -14, @DateNow)
--
/*select ALL records from the attendance table within the above defined date range and group them by UID tallying the score column*/
--
SELECT UID, SUM (POINTS) AS TOTAL_POINTS
FROM attendance
WHERE date >= @StartDate
GROUP BY UID
--
/* At this point, I have a result which contains a single row for each UID
in the attendance table and the total score for the previous 14 day period which is exactly what I want (I think)*/
/*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 things are falling apart for me. I'm not sure how ...
a) I can perform an insert action to add a record based on the result for each UID
b) Don't know if this is where I can use an IF type situation.
Any direction would be greatly appreciated.
April 6, 2008 at 7:48 am
I tend to convert my dates to the Day only portion so that i begin at the beginning of the 14th day as a starting point) for end of day, strip off the time and add a day (you can subtract a millisecond also if you want afterwards)
DECLARE @DateNow DATETIME
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @DateNow=getdate()
SET @StartDate = DATEADD(Day, -14, convert( char(10), getdate(), 101))
SET @EndDate = DATEADD( ms, -1, DATEADD(Day, 1, convert( char(10), getdate(), 101)))
April 6, 2008 at 10:16 am
Thank you...
Not sure what this has to do with my question though...
April 6, 2008 at 4:12 pm
ALMOST there.. 🙂
Q: Is it possible to mix and match in an INSERT INTO statement?
Here's where I'm at.
select all records in the atendance table from the previous 14 days
grouped them by UID
created a new table with each UID total > 0
debit 0.5 points in the attendance table for a specific UID
The Problem:
I can't seem to figure out how I can use the insert into statement to insert spefic values for each UID in the attendance table for each corrasponding UID in the newly created TOTAL_POINTS table.
My current code
------------------------------
/*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*/
INSERT INTO ATTENDANCE (UID, ENTRY_TYPE, DATE, POINTS, NOTES)
VALUES ('3', '3', GETDATE(), '-0.5', 'testing')
SELECT UID FROM POINT_TOTALS
WHERE TOTAL > 0
The code above works GREAT but the issue is that in the values section, I don't want to specifically call out the UID '3'. I would like to replace just this item with each UID that exists in the POINT_TOTALS table.
I have tried puting in UID in this place but get errors.
Hence my original question of ... can you mix and match column names and static values in an insert into statement.
Thank you all for your help.
April 6, 2008 at 7:21 pm
Actually, you need to go back and review Kent's response... it's pretty much spot on.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2008 at 7:58 pm
Jeff,
With all due respect, I have read Ken's post about 200 times over this weekend and unfortunately, due to my lack of experience and knowledge with SQL, I have been unsuccessful in applying what he's provided to what I'm trying to do. Don't get me wrong, I'm sure it's all there but I don't understand what is being done in certain sections of the code and have not been able to decifer it with the research I've done thus far. I'm sure to someone such as yourself, glancing at what Ken provided instantly comes off as "spot on," but to me it does not.
I'm not trying to be difficult, nor do I wish for anyone to do my work for me. As you requested, I read the post from your signature and redid my entire post to show my work and provide the actual tables and code that I've used, which apparently was wrong too. So, I'll take one more attempt and see where things go.
1. It seems like Ken is specifically calling out which userId's he is going to work with and I want to use ALL userId's that exist in one of my tables. Is this accurate or am I miss reading the code?
2. Ken is working with ALL tmp tables and I'm assuming this is done for demo purposes, correct? Most of the information I want to work with are in permenant tables in my DB so the only tmp tables I would need are ones for calculating and joining purposes, correct?
3. Given what I have changed my code to, I feel as though I should be able to use it and just add the final piece modifying it to an insert, could I not? (using the example from Ken's)
update @userTable (change to insert into)
set priceSum = totalPrice
from @userTable a
join aim b
on a.userId = b.userId
select * from @userTable
Thank you,
Bob
April 6, 2008 at 9:02 pm
Bob Boursaw (4/6/2008)
ALMOST there.. 🙂Q: Is it possible to mix and match in an INSERT INTO statement?
If you mean "mixing VALUES clauses with SELECT clauses" in an INSERT statement, then no, use one or the other.
That is the problem with your "0" INSERT:
INSERT INTO ATTENDANCE (UID, ENTRY_TYPE, DATE, POINTS, NOTES)
VALUES ('3', '3', GETDATE(), '-0.5', 'testing')
SELECT UID FROM POINT_TOTALS
WHERE TOTAL > 0
You've actually got two different statements here.
A general rule to follow is: if you are inserting only constants, then use the VALUES clause, if you need to pull values from another table, then use the SELECT clause. You need to pull values from your POINT_TOTALS table, so you should use the SELECT clause.
What you are missing is that SELECT can have constants in it too, just the same as the VALUES clause (no parenthesis though). I believe that this is what Jeff was trying to point you too in Kent's post, because Kent has some examples of SELECT with constants.
Hopefully this will be enough to get you going...
- - -
Once you get that part done, you will want to revisit the INSERT statement that you use to load the POINT_TOTALS with, because it appears that it will not have any rows for users who have no attendance, which will throw your "0" INSERT statement off, of course.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 6, 2008 at 9:59 pm
If I need to insert constants, I use INSERT/SELECT... if I need to insert more than one row of constants, I use INSERT/SELECT/UNION ALL... I can't actually remember the last time I used INSERT/VALUES.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2008 at 10:12 pm
Bob Boursaw (4/6/2008)
Jeff,With all due respect, I have read Ken's post about 200 times over this weekend and unfortunately, due to my lack of experience and knowledge with SQL, I have been unsuccessful in applying what he's provided to what I'm trying to do. Don't get me wrong, I'm sure it's all there but I don't understand what is being done in certain sections of the code and have not been able to decifer it with the research I've done thus far. I'm sure to someone such as yourself, glancing at what Ken provided instantly comes off as "spot on," but to me it does not.
I'm not trying to be difficult, nor do I wish for anyone to do my work for me. As you requested, I read the post from your signature and redid my entire post to show my work and provide the actual tables and code that I've used, which apparently was wrong too. So, I'll take one more attempt and see where things go.
1. It seems like Ken is specifically calling out which userId's he is going to work with and I want to use ALL userId's that exist in one of my tables. Is this accurate or am I miss reading the code?
2. Ken is working with ALL tmp tables and I'm assuming this is done for demo purposes, correct? Most of the information I want to work with are in permenant tables in my DB so the only tmp tables I would need are ones for calculating and joining purposes, correct?
3. Given what I have changed my code to, I feel as though I should be able to use it and just add the final piece modifying it to an insert, could I not? (using the example from Ken's)
update @userTable (change to insert into)
set priceSum = totalPrice
from @userTable a
join aim b
on a.userId = b.userId
select * from @userTable
Thank you,
Bob
Kent is working with all tmp tables because he's just doing an example... many of use will make a bit of sample data and stick it into temporary tables in our example code because we don't want to take the chance of inserting data into your real tables by mistake. Normally folks take the "lessons" (if I can call them that) from the sample code and apply it to their real situation. All of the following in Kent's code is just him setting up a test example and is NOT actually part of the solution...
declare @userTable table
( userId varchar(9),
priceSum numeric(12, 3)
)
insert into @userTable (userId)
select 'User 1' union all select 'User 2' union all select 'User 3'
declare @aTable table
( userId varchar(9) not null,
date datetime,
price numeric(9,3)
)
insert into @aTable
select 'User 1', getdate() - 17, 14.95 union all
select 'User 1', getdate() - 15, 21.97 union all -- Not included; OK?
select 'User 1', getdate() - 14, 9.99 union all
select 'User 1', getdate() - 13, 10.00 union all
select 'User 1', getdate() - 1, 35.59 union all
select 'User 1', getdate(), 26.49 union all
select 'User 2', getdate() - 2, 0.00 union all
select 'User 3', getdate() - 8, 21.75
--select * from @atable
Using the above as test data, Kent then wrote the following code to demo what needs to be done...
;with aim as
( select
userId,
sum(price) as totalPrice
from @aTable
where date >= convert(datetime, convert(varchar(8), getdate()-14, 112))
and date < convert(datetime, convert(varchar(8), getdate(), 112))
group by userId
having sum(price) > 0.00
)
update @userTable
set priceSum = totalPrice
from @userTable a
join aim b
on a.userId = b.userId
Of course, you would need to change the @aTable table name and the @userTable names to your real table names. THE CODE IMMEDIATELY ABOVE IS THE CODE YOU WANT... like I said, all you have to do is change the table names to match your's. Test on something other than your real table....
Last but not least, Kent demoed what the final contents of the @userTable would be after the update using the following code...select * from @userTable
So, just to summarize one more time... about the first 50% of the code Kent set up is just to setup a couple of disposable test tables to prove that his code works. That test data is NOT part of the solution... it's just a test setup.
In the code that IS part of the solution, you're gonna need to repoint the table names to the real tables... but only AFTER you're done testing and you're sure the code is bullet proof...
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2008 at 10:36 pm
Jeff Moden (4/6/2008)
If I need to insert constants, I use INSERT/SELECT... if I need to insert more than one row of constants, I use INSERT/SELECT/UNION ALL... I can't actually remember the last time I used INSERT/VALUES.
Yeah, I know. But I figured "one thing at a time".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 6, 2008 at 10:42 pm
Jeff,
Thank you (and everyone) for all the replies. The big piece I was missing finally just sunk in. My issue was with the insert I wanted to do from one table to another. I could not figure out if it was possible to include both constants and table values within my insert into statement. (which I'm sure you told me..) Anyway, my code does what I would like it to do but I'm not sure if it's "bad" code. If I could get a bit of feedback reagrding that, I would really appreciate it.
My final code which produces the desired output is...
DECLARE @DateNow DATETIME
DECLARE @StartDate DATETIME
SET @DateNow=getdate()
SET @StartDate = DATEADD(Day, -14, @DateNow)
CREATE TABLE POINT_TOTALS
(UID int, TOTAL float)
INSERT INTO POINT_TOTALS
SELECT UID, SUM(POINTS) AS TOTAL_POINTS
FROM attendance
WHERE date >= @StartDate
GROUP BY UID
INSERT INTO ATTENDANCE
SELECT UID, '3', GETDATE(), '-0.5', 'SYSTEM GENERATED DEBIT'
FROM POINT_TOTALS
WHERE TOTAL > 0
DROP TABLE POINT_TOTALS
-----------------------------------------------
I guess I'm just curious if there is anything inherently bad in my code or other ways I should be thinking about things.
If I recall from one of Kent's earlier comments, he said I should be thinking in terms of collections and narrowing those collections down until we arrive at the desired result, which is what I believe I did.
April 7, 2008 at 6:36 am
Bob:
I think you are getting there. 🙂
I see a couple of things to consider. First, in your initial post you talked about creating a stored procedure out of this. To me if you put this into a stored procedure it means that you want your code to be reused. The problem with what you have shown in your last post is that you are using a permanent table as a temp table. This will make your code NOT reusable within the context of a stored procedure. Just proceed your table POINT_TOTALS with a # sign to make the table name #POINT_TOTALS and your code will be ready for a stored procedure.
Another small point has to do with optimization -- specifically the where clause:
WHERE date >= @StartDate
I would suggest changing this to the expression
WHERE date >= DATEADD(Day, -14, getdate())
While the previous code is more readable the version that replaces the variable with the expression will sometimes optimize better because the optimizer might be more likely to select an index based on the DATE column.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply