May 18, 2015 at 3:52 am
I have table as below:
create table #team
(
date datetime,
team_id int,
expense_amt float
)
and it has data (like below) for last 2 years.
dateteam_idexpense_amt
5/14/2015510000
5/13/201552000
5/13/20154524
5/13/20153156
5/12/201552455
5/8/2015 3474
5/7/2015 2545
5/5/2015 134534
5/5/2015 32215
4/28/2015445676
I have to take the report for last one year from the current date, conditions are:
1. report should be taken of given team's expense
2. current expense is calculated as sum of expense occured from the report running date (current date) to last three months dateadd( mm, -3, current date)
E-x: for current date's run the output is:
05/18/2015 , for team id: 5 ; expense is sum of (05/18 to 02/18)
05/15/2015 , for team id: 5 ; expense is sum of (05/15 to 02/15)
05/14/2015 , for team id: 5 ; expense is sum of (05/14 to 02/14)
.
.
.
report need to generate until 05/18/2014 . it could be sum of (05/18/2014 to 02/18/2014)
Currently this achieved by using cursor and the performance is very bad
declare date cursor for
select date from bus_dt
where date between '05/18/2015' and '05/18/2014'
open date
fetch date into @begin_dt
select @start_dt = dateadd( mm, -3, @begin_dt)
select date, team_id, SUM(expense_amt) FROM #team
WHERE date >= @start_dt
and date < @begin_dt
and team_id = @team_id
group by date, team_id
fetch date into @begin_dt
Experts, please share your thought on avoiding this cursor to get my expected result.
May 18, 2015 at 4:47 am
Like this?
SELECT
t.*,
x.RollingSum
FROM #team t
OUTER APPLY (
SELECT RollingSum = SUM(expense_amt)
FROM #team ti
WHERE ti.team_id = t.team_id
AND ti.date <= t.date
AND ti.date >= DATEADD(month,-3,t.date)
) x
WHERE t.date >= DATEADD(year, -1, GETDATE())
ORDER BY t.team_id, t.date
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 18, 2015 at 5:57 pm
ChrisM@Work (5/18/2015)
Like this?
SELECTt.*,
x.RollingSum
FROM #team t
OUTER APPLY (
SELECT RollingSum = SUM(expense_amt)
FROM #team ti
WHERE ti.team_id = t.team_id
AND ti.date <= t.date
AND ti.date >= DATEADD(month,-3,t.date)
) x
WHERE t.date >= DATEADD(year, -1, GETDATE())
ORDER BY t.team_id, t.date
I think the OP wants the team totals for 4 quarters, not the 3 month trailing running total at each transaction time for the team. The original post seems fairly clear on that. So maybe something like
WITH quarters(qid, startq, endq) AS (
SELECT dateadd(MM,-12, getdate()), dateadd(MM,-9,getdate()) UNION ALL
SELECT dateadd(MM,-9, getdate()), dateadd(MM,-6,getdate()) UNION ALL
SELECT dateadd(MM,-6, getdate()), dateadd(MM,-3,getdate()) UNION ALL
SELECT dateadd(MM,-3, getdate()) AS startq, getdate() AS endq
),
rev_team(team_id, date, expense_amt) AS (
SELECT t.team_id, q.endq, t.expense_amt
FROM #team t INNER JOIN quarter q ON t.date <= q.endq and t.date > q.startq
SELECT
r.team_id,
x.RollingSum
FROM rev_team r
OUTER APPLY (
SELECT RollingSum = SUM(expense_amt)
FROM rev_team ti
WHERE ti.team_id = r.team_id
AND ti.date = r.date
) x
ORDER BY r.team_id, r.date
(I've been lazy and not tested this, so not guaranteed to be correct.)
Tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply