January 29, 2014 at 12:54 pm
Maybe it can become clearer if you work with 2 CTEs.
WITH Months AS(
SELECT DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate
FROM (VALUES (6),(5),(4),(3),(2),(1)) x(N)
),
MonthData AS(
SELECT LEFT( DATENAME(MONTH, startdate), 3) AS month_name,
startdate,
DATEADD( MM, 1, startdate) as enddate
FROM Months
)
SELECT *
FROM MonthData
January 29, 2014 at 12:57 pm
Great and here is the final version of working query. even i did rounded the amount to nearest values.
WITH Months AS(
SELECT LEFT( DATENAME(MONTH, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)), 3) 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)
)
select month_name,
ISNULL(CONVERT(int,ROUND(AVG(PutchaseAmount),0)), 0) as Amount
from Months
LEFT JOIN(
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) MemberVisit
ON MemberVisit.Visiteddate >= Months.startdate
AND MemberVisit.Visiteddate < Months.enddate
GROUP BY Months.month_name, Months.startdate
ORDER BY Months.startdate DESC
Thanks a lot Luis for your precious time on this post.
January 29, 2014 at 1:22 pm
You're very welcome. I hope that you learned something from this and you fully understand how the solution works. Remember to comment the code as you feel necessary to avoid confusions.
If you have any more doubts, feel free to ask. 😉
January 29, 2014 at 1:54 pm
Yes I learnt CTE today. Thank you so much
February 4, 2014 at 11:43 am
Hi Luis,
got into tricky situation on this logic, Is it possible to make the below line as dynamic
FROM (VALUES (6),(5),(4),(3),(2),(1)) x(N)
for example i have one more column called "RecordCreationDate" and assume that "RecordCreationDate" = 12/01/2013(mm/dd/yyyy) then i have to show the december/january month data only.
obviously the "from statement" is as like below
FROM (VALUES (6),(5)) x(N)
Is there any possible to make this as dynamic? please help me on this.
February 4, 2014 at 1:41 pm
Why would you choose 5 & 6 instead of 1 & 2?
To make it dynamic, add a WHERE clause to the Months CTE and you might want to add more values if needed.
February 4, 2014 at 2:22 pm
Hi Luis, Thanks for coming back,
Here is the scenario,
for example :
declare @val int
if @val = 2 then below should be constructed
WITH Months AS(
SELECT UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) month_name,
DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,
DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (VALUES (2),(1)) x(N)
)
select month_name from months
if @val = 3 then below should be constructed
WITH Months AS(
SELECT UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) month_name,
DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,
DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (VALUES (3),(2),(1)) x(N)
)
select month_name from months
if @val = 4 then below should be constructed
WITH Months AS(
SELECT UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) month_name,
DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,
DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (VALUES (4),(3),(2),(1)) x(N)
)
select month_name from months
if @val = 5 then below should be constructed
WITH Months AS(
SELECT UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) 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)) x(N)
)
select month_name from months
The reason i ma stating from (1),(2) will be most recent passed month. In mycase it will start from jan, dec,.....
how to achieve this dynamic construction. please help me
February 4, 2014 at 3:31 pm
As I told you, a WHERE clause will do the trick. And you can add values (or change to a full numbers table/view/function) to have even more months.
DECLARE @val int = 8;
WITH Months AS(
SELECT UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) month_name,
DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0) startdate,
DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (VALUES (12),(11),(10),(9),(8),(7),(6),(5),(4),(3),(2),(1)) x(N)
WHERE N <= @val
)
select month_name
from months
February 4, 2014 at 7:37 pm
Hi Luis,
That's awesome and thanks for your time on this. It works perfect. having one final question,
on the below sample
DECLARE @val int = 3;
WITH Months AS(
SELECT UPPER(convert(varchar(3),datename(month, DATEADD(MM, DATEDIFF( MM, 0, GETDATE()) - N, 0)))) 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) where N <= @val
)
select month_name,
ISNULL(CONVERT(int,ROUND(AVG(cast(PutchaseAmount as float)),0)), 0) as Amount
from Months
LEFT JOIN(
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,-1 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,-1 as PutchaseAmount
union all
select 1 as Id,'2013-10-03' as Visiteddate,-1 as PutchaseAmount) MemberVisit
ON MemberVisit.Visiteddate >= Months.startdate
AND MemberVisit.Visiteddate < Months.enddate
GROUP BY Months.month_name, Months.startdate
ORDER BY Months.startdate DESC
while taking average i should not consider if the value is -1. if you could see the sample the JAN month has values of -1 ans 10 , so when taking average i should not consider the -1. so the average must be 10. In order to achieve i Cast as float. i am able to get it. please correct me if am i doing wrongly.
February 5, 2014 at 8:38 am
2 hints for you:
1. Aggregate functions don't consider NULL values.
2. Take a look at NULLIF function.
EDIT: You don't need to cast your data, it will only get you wrong results. And if you continue to work with integers, you don't need to round it.
February 5, 2014 at 9:05 am
Hi Luis,
Thanks for your hints. I am thinking about how to make aggregate function not to consider the value -1. So i tried with casting as float. am i doing wrong here? please suggest me
February 5, 2014 at 9:18 am
Yes, converting to float won't be useful because the aggregate will continue to consider it. You need to convert it to a null value to prevent that. Or you need to consider what average do you need. You have 2 options:
(20+10+0)/3 = 10 or (20+10)/2 = 15
The first one will use a zero and the second one will use a null value. What do you need?
February 5, 2014 at 9:23 am
Hi Luis,
Thanks a lot for your precious time on this post.
I prefer the second one because i should not consider the -1 when taking average. Where should i change the code to achieve this. Also this condition needs to apply when negative and positive values are present for the same month.
if all the values of the month is -1 then i need to display the -1
February 5, 2014 at 9:35 am
Have you read about NULLIF?
You could use something like this:
CASE WHEN MAX(PutchaseAmount) = -1 /*AND MIN( PutchaseAmount) = -1*/ --Uncomment if you have other negative values
THEN -1
ELSE ISNULL(AVG(NULLIF(PutchaseAmount ,-1)), 0) END as Amount
February 5, 2014 at 9:59 am
Great. Last but not least, If i need to ignore 0 as well when taking average should i have to add one more case condition?
when MIN(PutchaseAmount) = 0 then AVG(NULLIF(PutchaseAmount ,0)
or do you have any other suggestion for me.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply