Comparing 2 set of dates.

[Andy42k]

I have a DB where I want to compare 2 set of dates. For example how many items were sold this year for a specific 28 day, monthly and yearly period vs the same last year. I can run the query 3 times, once for each of the specific date ranges, but I want to be able to compare those dates ranges. i.e 1/1/2016 to 1/29/16 vs 1/1/15 to 1/1/15.

[CJ_London]

look at using the dateadd function to subtract 1 year from your current year dates.

not enough information to suggest how to apply this, might be a groupby query with or without crosstab, might require a subquery, might be a union query

[Andy42k]

What more enough would you need to suggest something a little more concrete?

look at using the dateadd function to subtract 1 year from your current year dates.

not enough information to suggest how to apply this, might be a groupby query with or without crosstab, might require a subquery, might be a union query

[CJ_London]

1. what is your table structure (relevant to this requirement)
2. provide examples of the current data
3. provide example of what the required output is from this data
4. provide the sql for what you are running 3 times
5. what are you comparing - for a single item or all items? for one kpi (you mention quantity) or many? quantity/value/cost
6. how are you comparing? on a form or a report? export to excel? for use in a graph?
7. are you calculating the difference in the query
8. does the data need to be updateable
9. clarify what you mean by 'I can run the query 3 times, once for each of the specific date ranges,' AND 'but I want to be able to compare those dates ranges. i.e 1/1/2016 to 1/29/16 vs 1/1/15 to 1/1/15.' a) there are only two dates and b) one is for a 29 day period and the other 1 day.