last 6 months data

Question

last 6 months data

born2achieve

Hall of Fame

Points: 3182
More actions
January 29, 2014 at 9:52 am

#282897

Hi,
Below is my table structure,
Table Name : MemberVisit
Columns: Id,Visiteddate,PutchaseAmount
sample data:
select * from(
select 1 as Id,'2014-01-13' as Visiteddate,20 as PutchaseAmount
union all
select 1 as Id,'2014-01-12' as Visiteddate,10 as PutchaseAmount
union all
select 1 as Id,'2014-01-02' as Visiteddate,40 as PutchaseAmount
union all
select 1 as Id,'2013-12-12' as Visiteddate,70 as PutchaseAmount
union all
select 1 as Id,'2013-12-02' as Visiteddate,90 as PutchaseAmount
union all
select 1 as Id,'2013-11-11' as Visiteddate,100 as PutchaseAmount
union all
select 1 as Id,'2013-11-02' as Visiteddate,30 as PutchaseAmount
union all
select 1 as Id,'2013-10-18' as Visiteddate,70 as PutchaseAmount
union all
select 1 as Id,'2013-10-03' as Visiteddate,120 as PutchaseAmount) MemberVisit
Requirement:
have to get the last 6 months data(including current month as one of the month)
my required output:
Month PurchaseAmount(Average)
Jan 24
Dec 80
Nov 65
Oct 85
Sep 0
Aug 0
Any sample query please

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I won't get to the exact data you posted as expected output, but I suppose it's not correct and you just included some numbers.

Here's an option.

select DATEADD(MM, DATEDIFF( MM, 0, Visiteddate), 0), --Will return first day of each month to have one row per month

AVG( PutchaseAmount)

from(

select 1 as Id,CAST('2014-01-13' AS date) as Visiteddate,20 as PutchaseAmount

union all

select 1 as Id,'2014-01-12' as Visiteddate,10 as PutchaseAmount

union all

select 1 as Id,'2014-01-02' as Visiteddate,40 as PutchaseAmount

union all

select 1 as Id,'2013-12-12' as Visiteddate,70 as PutchaseAmount

union all

select 1 as Id,'2013-12-02' as Visiteddate,90 as PutchaseAmount

union all

select 1 as Id,'2013-11-11' as Visiteddate,100 as PutchaseAmount

union all

select 1 as Id,'2013-11-02' as Visiteddate,30 as PutchaseAmount

union all

select 1 as Id,'2013-10-18' as Visiteddate,70 as PutchaseAmount

union all

select 1 as Id,'2013-10-03' as Visiteddate,120 as PutchaseAmount) MemberVisit

WHERE Visiteddate >= DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - 5, 0) -- last 6 months including the current one.

AND Visiteddate <= DATEADD( DD, -1, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) + 1, 0)) --include all current month (might need a change if you use time in Visiteddate)

GROUP BY DATEADD(MM, DATEDIFF( MM, 0, Visiteddate), 0)

This query will only include months with data.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 2

I might be taking this a little too simplistic - but I have the code below which will get you your averages by year and month - then just join that to a table of months/years to get the proper output. I got different values for my averages than your desired out put though..

select

DATEPART(YEAR, Visiteddate) AS yr,

DATEPART(MONTH, Visiteddate) AS mo,

AVG(PutchaseAmount)

from

(

select 1 as Id,'2014-01-13' as Visiteddate,20 as PutchaseAmount

union all

select 1 as Id,'2014-01-12' as Visiteddate,10 as PutchaseAmount

union all

select 1 as Id,'2014-01-02' as Visiteddate,40 as PutchaseAmount

union all

select 1 as Id,'2013-12-12' as Visiteddate,70 as PutchaseAmount

union all

select 1 as Id,'2013-12-02' as Visiteddate,90 as PutchaseAmount

union all

select 1 as Id,'2013-11-11' as Visiteddate,100 as PutchaseAmount

union all

select 1 as Id,'2013-11-02' as Visiteddate,30 as PutchaseAmount

union all

select 1 as Id,'2013-10-18' as Visiteddate,70 as PutchaseAmount

union all

select 1 as Id,'2013-10-03' as Visiteddate,120 as PutchaseAmount) MemberVisit

GROUP BY

DATEPART(YEAR, Visiteddate),

DATEPART(MONTH, Visiteddate)

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 3

[edit: double posted - sorry!]

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

born2achieve Hall of Fame Points: 3182 More actions · Answer 4

hi Torpkev,

thanks for your reply. actually the avg gives in terms of decimal. i just rounded and shown as result.

But on your query where is the condition to get the last 6 months data. i just gave the sample data for 6 months. my table has data for a years.

so if i run the query i need to get last 6 months data. Can you please

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

born2achieve (1/29/2014)
hi Torpkev,
thanks for your reply. actually the avg gives in terms of decimal. i just rounded and shown as result.
But on your query where is the condition to get the last 6 months data. i just gave the sample data for 6 months. my table has data for a years.
so if i run the query i need to get last 6 months data. Can you please

I just love being ignored :Whistling:

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

born2achieve Hall of Fame Points: 3182 More actions · Answer 6

Hi Luis,

Could you please tell me what do you mean by "I just love being ignored". Does it mean that ignoring this thread.........

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 7

Luis posted a full solution a few seconds before I did including the 6 month limitation

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

It means that you ignored the first reply to your post made by me.

While I'm here, do you want to include a validation to include months without data?

You could easily do it with a calendar table or we could create one on the fly for this.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

born2achieve Hall of Fame Points: 3182 More actions · Answer 9

Opps My bad, i din't watch the Luis reply.

Hi Luis, Apologize for my mistake. below is the query which i tweaked from yours.

select convert(varchar(3),datename(month, Visiteddate)) as validdate , --Will return first day of each month to have one row per month

AVG( PutchaseAmount)

from(

select 1 as Id,CAST('2014-01-13' AS date) as Visiteddate,20 as PutchaseAmount

union all

select 1 as Id,'2014-01-12' as Visiteddate,10 as PutchaseAmount

union all

select 1 as Id,'2014-01-02' as Visiteddate,40 as PutchaseAmount

union all

select 1 as Id,'2013-12-12' as Visiteddate,70 as PutchaseAmount

union all

select 1 as Id,'2013-12-02' as Visiteddate,90 as PutchaseAmount

union all

select 1 as Id,'2013-11-11' as Visiteddate,100 as PutchaseAmount

union all

select 1 as Id,'2013-11-02' as Visiteddate,30 as PutchaseAmount

union all

select 1 as Id,'2013-10-18' as Visiteddate,70 as PutchaseAmount

union all

select 1 as Id,'2013-10-03' as Visiteddate,120 as PutchaseAmount) MemberVisit

WHERE Visiteddate >= DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - 5, 0) -- last 6 months including the current one.

AND Visiteddate <= DATEADD( DD, -1, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) + 1, 0)) --include all current month (might need a change if you use time in Visiteddate)

GROUP BY convert(varchar(3),datename(month, Visiteddate))

i would like to include 0 if no purchase amount found for the month.

August and September there is no data. so i would like to show 0 purchase amount for those months. Any clue please . The query has to produce 6 rows as like below

Dec80

Jan23

Nov65

Oct95

sep 0

Aug 0

also how to make this order by month because the output has to be

Jan23

Dec80

Nov65

Oct95

sep 0

Aug 0

Any help please

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

As I told you, you can build a "calendar table" on the fly. This will work for 6 months, but you may need to increase it if the requirement changes. Depending on the date range you might want to use a complete tally table (or cte).

Note that we have the information to group by month in the CTE and we just use a LEFT JOIN (you could use a RIGHT JOIN if you prefer) to get all the months even if there's no data.

WITH

--From here to the next comment there's just sample data

MemberVisit AS(

select 1 as Id,CAST('2014-01-13' AS date) as Visiteddate,20 as PutchaseAmount

union all

select 1 as Id,'2014-01-12' as Visiteddate,10.0 as PutchaseAmount

union all

select 1 as Id,'2014-01-02' as Visiteddate,40 as PutchaseAmount

union all

select 1 as Id,'2013-12-12' as Visiteddate,70 as PutchaseAmount

union all

select 1 as Id,'2013-12-02' as Visiteddate,90 as PutchaseAmount

union all

select 1 as Id,'2013-11-11' as Visiteddate,100 as PutchaseAmount

union all

select 1 as Id,'2013-11-02' as Visiteddate,30 as PutchaseAmount

union all

select 1 as Id,'2013-10-18' as Visiteddate,70 as PutchaseAmount

union all

select 1 as Id,'2013-10-03' as Visiteddate,120 as PutchaseAmount

),

--Here ends the sample data and the solution starts

Months AS(

SELECT RIGHT(CONVERT(CHAR(11),DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0), 6),8) month_name,

DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,

DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate

FROM (VALUES(5),(4),(3),(2),(1),(0)) x(N)

)

select mon.month_name,

ISNULL( AVG( mem.PutchaseAmount), 0)

from Months mon

LEFT JOIN MemberVisit mem ON mem.Visiteddate >= mon.startdate

AND mem.Visiteddate < mon.enddate

GROUP BY mon.month_name,

mon.startdate

ORDER BY mon.startdate DESC

EDIT: Moved the Sample data to the CTE

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

born2achieve Hall of Fame Points: 3182 More actions · Answer 11

Thank you Genius. Really it helped me a lot. trying to understand the query and having tiny doubt that how the year is getting displayed along with the month name. if i want to remove displaying year what should i do?

Also if i don't want to consider the current month data and my desired output as

month_nameAmount

Dec 13 80.000000

Nov 13 65.000000

Oct 13 95.000000

Sep 13 0.000000

Aug 13 0.000000

July 0

where should i change.

If am not wrong that i need to change the below area. but bit confused about what to change

DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,

DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

You could just change the month_name column definition to your original code using datename.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

born2achieve Hall of Fame Points: 3182 More actions · Answer 13

to remove the current month data i tweaked the logix and it works fine

SELECT right(CONVERT(CHAR(11),DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0), 6),8) month_name,

DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,

DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate

FROM (VALUES (6),(5),(4),(3),(2),(1)) x(N)

only area is to remove the year from the month while display.

From your previous reply you meant to say something should i do on the below line?

SELECT right(CONVERT(CHAR(11),DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0), 6),8) month_name,

Not sure what should i change. any suggestions please

Luis Cazares SSC Guru Points: 183706 More actions · Answer 14

You previously posted a way to use only the month without the year.

Here's another way.

SELECT LEFT( DATENAME(MONTH, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)), 3) month_name,

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2