SQL Query to get and update today and yesterday's data?
Alright, I have to create a similar table structure to mine more simplified -
Test_Table: EmployeeId,Poi开发者_开发技巧nts,Date
Test_Table1:Score,EmployeeId,Date
Leaderboards_Table: Points,Score,EmployeeId,Day,Month,Year
Now I need to write a single query to update or insert into LeaderBoards_Table something like -
UPDATE Leaderboards_Table
SET Points=pts,Score=total_score
FROM
(
(SELECT
(SELECT SCORE from Test_Table1 WHERE Employee = @EmployeeId AND DAY(DATE)=10 AND MONTH(DATE)=12 AND YEAR(DATE)=2010) as pts
)
Points as pts
from Test_Table where Employee = @EmployeeId AND DAY(DATE)=10 AND MONTH(DATE)=12 AND YEAR(DATE)=2010
)
WHERE EmployeeId=@EmployeeId and DAY=10 AND MONTH=12 AND YEAR=2010
Now the above query only updates for today...what I want to do is also update yesterday also in a single query I dont want to write another query....so is there anyway to do a single query to update yesterday and today's points.
UPDATE: Also this query will be called lot of times..so it would be great to have the most performance effective query for this.
Its a lot easier if you change to joins instead of subqueries.
UPDATE Leaderboards_Table
SET
Points=t1.score,
Score=total_score
FROM
Leaderboards_Table lt
INNER JOIN Test_Table1 t1
ON lt.employee_ID = t1.employee_id
INNER JOIN Test_Table t
ON lt.employee_ID = t1.employee_id
and lt.date = t1.date
WHERE
EmployeeId=@EmployeeId and DAY IN (9,10) AND MONTH=12 AND YEAR=2010
Here's a solution that uses the BETWEEEN clause.
UPDATE Leaderboards_Table
SET Points=tt.Points, Score = tt1.Score
FROM LeaderBoards_Table
JOIN Test_Table1 tt1 ON LeaderBoards_Table.EmployeeId = tt1.EmployeeId
JOIN Test_Table tt ON tt1.EmployeeId = tt.EmployeeId
WHERE EmployeeId = @EmployeeId
AND CONVERT(datetime CAST(YEAR AS varchar) + CAST(MONTH AS varchar) + CAST(DAY AS varchar))
BETWEEN '20101209' AND '20101210'
精彩评论