Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-02-19
13
Medium Priority
?
763 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:prashant_24patil
  • 6
  • 5
  • 2
13 Comments
 
LVL 6

Expert Comment

by:FVER
ID: 23680216
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 Comment

by:prashant_24patil
ID: 23680596
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
 
LVL 6

Expert Comment

by:FVER
ID: 23681399
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 6

Accepted Solution

by:
FVER earned 500 total points
ID: 23681419
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23681748
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
 
LVL 6

Expert Comment

by:FVER
ID: 23681998
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
 
LVL 6

Expert Comment

by:FVER
ID: 23682016
oups, I missread your query, it should work.
sorry
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23682090
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23682103
oops, i missed the oops :)
0
 

Author Closing Comment

by:prashant_24patil
ID: 31548696
thanks............
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23745435
@prashant_24patil   curious - did you even try my solution ? And if so, what was wrong with it ?
0
 
LVL 6

Expert Comment

by:FVER
ID: 23746271
@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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23746323
Very noble and gallant of you FEVR, I am more curious as to why it was totally overlooked...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question