October 14, 2013 at 2:39 pm
Hi
In one of my query I need data to be sorted starting with current month. Here is sample script:
create table OrderTest(OrderDate datetime,orderCount int)
GO
insert into OrderTest values('2013/10/15',30)
insert into OrderTest values('2013/10/17',40)
insert into OrderTest values('2013/09/13',60)
insert into OrderTest values('2013/08/21',45)
insert into OrderTest values('2013/07/13',38)
insert into OrderTest values('2013/06/15',23)
insert into OrderTest values('2013/05/15',56)
insert into OrderTest values('2013/04/15',267)
insert into OrderTest values('2013/03/19',67)
insert into OrderTest values('2013/02/15',45)
insert into OrderTest values('2013/01/15',90)
insert into OrderTest values('2012/11/15',22)
insert into OrderTest values('2012/12/15',76)
GO
select datepart(mm,OrderDate) MonthNum,AVG(orderCount) AvgOrderCount
FROM OrderTest
GROUP BY datepart(mm,OrderDate)
Order BY datepart(mm,OrderDate)
I need data like:
1030
1122
1276
190
245
367
4267
556
623
738
845
960
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
October 14, 2013 at 2:55 pm
What is your expected output if we'd add another row like INSERT INTO OrderTest VALUES('2011/11/17',11) ?
Your current query only aggregate by month, so the above value will be added to the month November, together with the data for year 2012.
Is this intended?
October 14, 2013 at 2:58 pm
oh, ok. Sorry I should have mentioned it. There will be where caluse , SO that the query only picks current one year data.
So there will always be 12 months data in resultset.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
October 14, 2013 at 3:19 pm
Something like this (I used a cte to get a sort criteria added to the group by statement that is omitted in the final output)?
WITH cte as
(
SELECT datepart(mm,OrderDate) MonthNum,AVG(orderCount) AvgOrderCount, MIN(OrderDate) as m_date
FROM OrderTest
GROUP BY datepart(mm,OrderDate)
)
SELECT MonthNum,AvgOrderCount
FROM cte
Order BY m_date
October 14, 2013 at 3:25 pm
Thanks So much. it worked. But I think the order by clause should have been:
Order BY m_date desc
So that I get current month at top.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
October 14, 2013 at 3:27 pm
Yep, you're correct.
That's one of the pitfalls when not seeing the complete date - I totally missed the sort order. Sorry.
October 14, 2013 at 3:31 pm
bit late......
SELECT DATEPART(mm, OrderDate) AS MonthNum, AVG(orderCount) AS AvgOrderCount
FROM OrderTest
GROUP BY DATEPART(mm, OrderDate), DATEADD(mm, DATEDIFF(mm, 0, OrderDate), 0)
ORDER BY DATEADD(mm, DATEDIFF(mm, 0, OrderDate), 0) DESC
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 14, 2013 at 3:36 pm
J Livingston SQL (10/14/2013)
bit late......
SELECT DATEPART(mm, OrderDate) AS MonthNum, AVG(orderCount) AS AvgOrderCount
FROM OrderTest
GROUP BY DATEPART(mm, OrderDate), DATEADD(mm, DATEDIFF(mm, 0, OrderDate), 0)
ORDER BY DATEADD(mm, DATEDIFF(mm, 0, OrderDate), 0) DESC
Agreed, more efficient (avoids one sort operation).
Edit: but it requires to guarantee only 12 month in the data set (as mentioned before). Otherwise it'll return more than 12 rows.
October 14, 2013 at 3:46 pm
Perfect..perfect..
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
October 14, 2013 at 11:45 pm
S_Kumar_S (10/14/2013)
Perfect..perfect..
Really? I would have thought from your expected results you'd want something more like this:
SELECT TOP 12 DATEPART(mm, OrderDate) AS MonthNum, AVG(orderCount) AS AvgOrderCount
FROM OrderTest
GROUP BY DATEPART(mm, OrderDate), DATEDIFF(mm, 0, OrderDate)
ORDER BY
CASE
WHEN DATEDIFF(mm, 0, GETDATE()) = DATEDIFF(mm, 0, OrderDate) THEN 0
WHEN DATEDIFF(mm, 0, GETDATE()) - DATEDIFF(mm, 0, OrderDate) >= 12 THEN 9999
ELSE DATEDIFF(mm, 0, OrderDate)
END
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 15, 2013 at 2:17 pm
I actually needed what above query returned but thank you for trying it out.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply