Group by month and year problem

• Steve Brett

  Mr or Mrs. 500

  Points: 589

  More actions

  April 27, 2009 at 3:19 am

  #130289

  Hi

  I have a query that produces metrics from a unioned view that contains page syslogs for a website.

  The problem I have is that I have written 2 versions of the same query, one runs over a month period and the other goes over the past year and groups by year and month. The problem is that both queries, when referencing the same month, are producing different results. I have gone through several methods of grouping on the month and year with no luck. I simply cannot understand why it doesn't work !

  Both queries are identical apart from the date range restriction and the grouping, unfortunately I can't post the entire queries here for commercial reasons.

  The date range and grouping is shown below:

  and ( Between dateadd(year,-1, getdate()) AND getdate())

  GROUP BY dateadd(month, datediff(month, 0, ),0)

  Any ideas ?

  Thanks in advance

  Steve

• arun.sas

  SSChampion

  Points: 11831

  More actions

  April 27, 2009 at 4:24 am

  #984244

  Hi Steve,

  Post your full statement which you write

  Meanwhile try this

  select year(order_Dt),month(order_Dt),count(order_No) from Order_table

  where order_Dt between dateadd(year,-1, getdate()) AND getdate()

  GROUP BY year(order_Dt),month(order_Dt)

  ORDER BY year(order_Dt),month(order_Dt)

  ARUN SAS

• Steve Brett

  Mr or Mrs. 500

  Points: 589

  More actions

  April 27, 2009 at 4:46 am

  #984250

  I'll knock up and equiv query.

  In the meantime I may have resolved it - there was inconsistent bracketting of restrictions so I think that was producing some funky joins ...

• Steve Brett

  Mr or Mrs. 500

  Points: 589

  More actions

  April 27, 2009 at 5:47 am

  #984271

  my bad. it was a bracketting problem. doh.