# Finding Last week, Last month, last Quarter amount in SQL server

i have one table that contain Project_number,Project_Name,Year,QTR,Month,Week,Amount colum. T want create another view that contain Project_number, last week amount, last month amount, last qurter amount for each Project number.
Problem is when i find out last month or last week or last Quarter amount then there is last two years data in first view. so because year i can't find out this data my Query gives last month , last week , last Qurter data for last two years.
i just need last month, last week, last quarter of current year in one view for each Project number.
i attached excel file that contains data in first view . From that you can analyzed Query.
ViewDataForAnalysis.xls
###### Who is Participating?

Commented:
oups, missed the FROM keyword
``````select PRJ.PAPROJNUMBER, Last_quarter.QTD, Last_month.QTD, Last_week.QTD FROM
(select distinct PAPROJNUMBER from expenses) PRJ left join
(select PAPROJNUMBER,sum(amount) QTD
from expenses_qtr
where year = case when datepart(quarter,getdate()) = 1 then datepart(year,getdate())-1 else datepart(year,getdate()) end
and QTR = case when datepart(quarter,getdate()) = 1 then 4 else datepart(quarter,getdate())-1 end
group by PROJNUMBER
) Last_quarter on PRJ.PAPROJNUMBER = Last_quarter.PAPROJNUMBER left join
(select PAPROJNUMBER,sum(amount) QTD
from expenses_qtr
where year = case when datepart(month,getdate()) = 1 then datepart(year,getdate())-1 else datepart(year,getdate()) end
and month = case when datepart(month,getdate()) = 1 then 12 else datepart(month,getdate())-1 end
group by PROJNUMBER
) Last_month on PRJ.PAPROJNUMBER = Last_month.PAPROJNUMBER left join
(select PAPROJNUMBER,sum(amount) QTD
from expenses_qtr
where year = case when datepart(month,getdate()) = 1 then datepart(year,getdate())-1 else datepart(year,getdate()) end
and week = case when datepart(week,getdate()) = 1 then datepart(week,getdate()) = 1 then datepart(week,convert(datetime, '12/31/'+cast(datepart(year,getdate())-1 as varchar),101)) else datepart(week,getdate())-1 end
group by PROJNUMBER
) Last_week on PRJ.PAPROJNUMBER = Last_week.PAPROJNUMBER
``````
0

Commented:
Hi,
Can you provide your query ?

With the information you gave, I think adding a clause like
Year = (select max(year) from table)
would do it.
0

Author Commented:
This is for Last Quarter:
select PAPROJNUMBER,sum(amount) 'QTD' from expenses_qtr
where year = datepart(yy,getdate()) and
QTR = datepart(q,dateadd(q,datediff(q,0,getdate())-1,0)) group by PROJNUMBER

This is for Last month:
select PAPROJNUMBER,amount 'MTD' from expenses_qtr where year=datepart(yy,getdate())
and month=datepart(mm,getdate())-1

this is for last week:
select PAPROJNUMBER,amount 'LastWk' from expenses_qtr where year=datepart(yy,getdate())
and Week= datepart(wk,getdate())-1
if we are in first quarter then  last Quarter amount shuold be  4th Quarter of 2008. Like that month and week.but in above query not it possible. and i need only one query for creating view insteas of three separate Query.so is it possible in query or any other ooption?
0

Commented:
let's give it a try
``````select PRJ.PAPROJNUMBER, Last_quarter.QTD, Last_month.QTD, Last_week.QTD
(select distinct PAPROJNUMBER from expenses) PRJ left join
(select PAPROJNUMBER,sum(amount) QTD
from expenses_qtr
where year = case when datepart(quarter,getdate()) = 1 then datepart(year,getdate())-1 else datepart(year,getdate()) end
and QTR = case when datepart(quarter,getdate()) = 1 then 4 else datepart(quarter,getdate())-1 end
group by PROJNUMBER
) Last_quarter on PRJ.PAPROJNUMBER = Last_quarter.PAPROJNUMBER left join
(select PAPROJNUMBER,sum(amount) QTD
from expenses_qtr
where year = case when datepart(month,getdate()) = 1 then datepart(year,getdate())-1 else datepart(year,getdate()) end
and month = case when datepart(month,getdate()) = 1 then 12 else datepart(month,getdate())-1 end
group by PROJNUMBER
) Last_month on PRJ.PAPROJNUMBER = Last_month.PAPROJNUMBER left join
(select PAPROJNUMBER,sum(amount) QTD
from expenses_qtr
where year = case when datepart(month,getdate()) = 1 then datepart(year,getdate())-1 else datepart(year,getdate()) end
and week = case when datepart(week,getdate()) = 1 then datepart(week,getdate()) = 1 then datepart(week,convert(datetime, '12/31/'+cast(datepart(year,getdate())-1 as varchar),101)) else datepart(week,getdate())-1 end
group by PROJNUMBER
) Last_week on PRJ.PAPROJNUMBER = Last_week.PAPROJNUMBER
``````
0

select paprojnumber
,   convert(varchar,datepart(quarter,dateadd(quarter,-1,getdate()))) + '/' + convert(varchar,datepart(yy,dateadd(q,-1,getdate()))) as Last_QTR_Period     -- not needed just showing calc
,   convert(varchar,datepart(month,dateadd(month,-1,getdate()))) + '/' + convert(varchar,datepart(yy,dateadd(m,-1,getdate()))) as Last_MTH_Period  -- not needed just showing calc
,   convert(varchar,datepart(week,dateadd(week,-1,getdate()))) + '/' + convert(varchar,datepart(yy,dateadd(w,-1,getdate()))) as Last_Week_Period  -- not needed just showing calc
,   sum(case when [year] = datepart(yy,dateadd(q,-1,getdate())) and qtr = datepart(q,dateadd(q,-1,getdate())) then amount else 0.00 end) as Last_QTR
,   sum(case when [year] = datepart(yy,dateadd(m,-1,getdate())) and [month] = datepart(m,dateadd(m,-1,getdate())) then amount else 0.00 end) as Last_MTH
,   sum(case when [year] = datepart(yy,dateadd(w,-1,getdate())) and [week] = datepart(w,dateadd(w,-1,getdate())) then amount else 0.00 end) as Last_WK
from expenses
group by paprojnumber

0

Commented:
just as an example, this :

sum(case when [year] = datepart(yy,dateadd(q,-1,getdate())) and qtr = datepart(q,dateadd(q,-1,getdate())) then amount else 0.00 end) as Last_QTR

doesn't work because year must be current year if current quarter > 1
sum(case ...) is a good track, but the case condition is a bit more complicated.
0

Commented:
sorry
0

Not sure I understand that comment... If last quarter is say 2, then we are in (say) July so...

seems to work OK...
0

oops, i missed the oops :)
0

Author Commented:
thanks............
0

@prashant_24patil   curious - did you even try my solution ? And if so, what was wrong with it ?
0

Commented:
@prashant_24patil I believe Mark's solution works and I'm sure it  is much more elegant (and performant) than mine. Maybe we could share points ?
0