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.
Please advise .
i attached excel file that contains data in first view . From that you can analyzed Query.
ViewDataForAnalysis.xls
prashant_24patilAsked:
Who is Participating?
 
FVERCommented:
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

Open in new window

0
 
FVERCommented:
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
 
prashant_24patilAuthor 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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
FVERCommented:
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

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
What about :


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
 
FVERCommented:
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
 
FVERCommented:
oups, I missread your query, it should work.
sorry
0
 
Mark WillsTopic AdvisorCommented:
Not sure I understand that comment... If last quarter is say 2, then we are in (say) July so...

select convert(varchar,datepart(quarter,dateadd(quarter,-1,'20090323'))) + '/' + convert(varchar,datepart(yy,dateadd(q,-1,'20090323'))) as Last_QTR_Period_as_at_Mar
, convert(varchar,datepart(quarter,dateadd(quarter,-1,'20090523'))) + '/' + convert(varchar,datepart(yy,dateadd(q,-1,'20090523'))) as Last_QTR_Period_as_at_May
, convert(varchar,datepart(quarter,dateadd(quarter,-1,'20090723'))) + '/' + convert(varchar,datepart(yy,dateadd(q,-1,'20090723'))) as Last_QTR_Period_as_at_July


seems to work OK...
0
 
Mark WillsTopic AdvisorCommented:
oops, i missed the oops :)
0
 
prashant_24patilAuthor Commented:
thanks............
0
 
Mark WillsTopic AdvisorCommented:
@prashant_24patil   curious - did you even try my solution ? And if so, what was wrong with it ?
0
 
FVERCommented:
@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
 
Mark WillsTopic AdvisorCommented:
Very noble and gallant of you FEVR, I am more curious as to why it was totally overlooked...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.