June 14, 2017 at 12:02 pm
Hello Everyone,
I'm hoping someone could help me out, so here's my scenario:
I have a table that tracks orders from clients. This table has a column "OpenDate" which I am basing my count on.. My superiors would like to get a count of orders by client on a month to month basis going back 5 years. What I need to do is count the number of opened orders for each month going back 5 years. Now here's the tricky part, it has to be dynamic. In other words, they should be able to run this query at any time and get 5 years worth of counts, even if they run it next month or in 3 months or even next year.
I'm thinking of using an if statement with case statements but I don't know if that would be the most efficient way of doing it.
thanks for your help!
June 14, 2017 at 12:29 pm
I don't think you need an IF or CASE here, you should be able to compute the starting OpenDate WHERE clause like one of these, depending on if you need criteria to be the last 5 calendar years or the last 60 months:
--5 years by entire calendar year
SELECT DATEADD(year, DATEDIFF(year, 0, GetDate())-5, 0) AS eo5y
--5 years worth of months
SELECT DATEADD(month, DATEDIFF(month, 0, GetDate())-60, 0) AS eo60m
June 14, 2017 at 12:32 pm
meichmann - Wednesday, June 14, 2017 12:02 PMHello Everyone,
I'm hoping someone could help me out, so here's my scenario:I have a table that tracks orders from clients. This table has a column "OpenDate" which I am basing my count on.. My superiors would like to get a count of orders by client on a month to month basis going back 5 years. What I need to do is count the number of opened orders for each month going back 5 years. Now here's the tricky part, it has to be dynamic. In other words, they should be able to run this query at any time and get 5 years worth of counts, even if they run it next month or in 3 months or even next year.
I'm thinking of using an if statement with case statements but I don't know if that would be the most efficient way of doing it.
thanks for your help!
SELECT
Yr = YEAR(t.OpenDate)
, mth = MONTH(t.OpenDate)
, ct = COUNT(1)
FROM tbl t
WHERE t.OpenDate> DATEADD(YEAR, -5, GETDATE())
GROUP BY
YEAR(t.OpenDate)
, MONTH(t.OpenDate)
ORDER BY
Yr
, mth;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 14, 2017 at 12:42 pm
Phil Parkin - Wednesday, June 14, 2017 12:32 PMmeichmann - Wednesday, June 14, 2017 12:02 PMHello Everyone,
I'm hoping someone could help me out, so here's my scenario:I have a table that tracks orders from clients. This table has a column "OpenDate" which I am basing my count on.. My superiors would like to get a count of orders by client on a month to month basis going back 5 years. What I need to do is count the number of opened orders for each month going back 5 years. Now here's the tricky part, it has to be dynamic. In other words, they should be able to run this query at any time and get 5 years worth of counts, even if they run it next month or in 3 months or even next year.
I'm thinking of using an if statement with case statements but I don't know if that would be the most efficient way of doing it.
thanks for your help!
SELECT
Yr = YEAR(t.OpenDate)
, mth = MONTH(t.OpenDate)
, ct = COUNT(1)
FROM tbl t
WHERE t.OpenDate> DATEADD(YEAR, -5, GETDATE())
GROUP BY
YEAR(t.OpenDate)
, MONTH(t.OpenDate)
ORDER BY
Yr
, mth;
You sir are a genius! I added some additional filters and got the result I was looking for. thank you so much!
June 14, 2017 at 1:56 pm
meichmann - Wednesday, June 14, 2017 12:42 PMPhil Parkin - Wednesday, June 14, 2017 12:32 PMmeichmann - Wednesday, June 14, 2017 12:02 PMHello Everyone,
I'm hoping someone could help me out, so here's my scenario:I have a table that tracks orders from clients. This table has a column "OpenDate" which I am basing my count on.. My superiors would like to get a count of orders by client on a month to month basis going back 5 years. What I need to do is count the number of opened orders for each month going back 5 years. Now here's the tricky part, it has to be dynamic. In other words, they should be able to run this query at any time and get 5 years worth of counts, even if they run it next month or in 3 months or even next year.
I'm thinking of using an if statement with case statements but I don't know if that would be the most efficient way of doing it.
thanks for your help!
SELECT
Yr = YEAR(t.OpenDate)
, mth = MONTH(t.OpenDate)
, ct = COUNT(1)
FROM tbl t
WHERE t.OpenDate> DATEADD(YEAR, -5, GETDATE())
GROUP BY
YEAR(t.OpenDate)
, MONTH(t.OpenDate)
ORDER BY
Yr
, mth;You sir are a genius! I added some additional filters and got the result I was looking for. thank you so much!
Be careful with the data you get from this query. As it's grouped by the month, but the date is being bounded by exactly 5 years ago to any give day, the first month of that 5 years could have as little as 1 day in it, depending on what day of the month it happens to be when it runs. You'd be far better served to always use whole months worth of data if you're going to group the data that way. I'd change the query as follows:
DECLARE @MONTH_END AS date = DATEADD(day, 1 - DATEPART(day, GETDATE()), GETDATE());
DECLARE @MONTH_START AS date = DATEADD(year, -5, @MONTH_START);
SELECT
Yr = YEAR(t.OpenDate)
, mth = MONTH(t.OpenDate)
, ct = COUNT(1)
FROM tbl t
WHERE t.OpenDate BETWEEN @MONTH_START AND @MONTH_END
GROUP BY
YEAR(t.OpenDate)
, MONTH(t.OpenDate)
ORDER BY
Yr
, mth;
You'll always get 5 years of data, and it will always be the last 60 full months.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 15, 2017 at 6:03 am
To This:
DECLARE @MONTH_START AS date = DATEADD(year, -5, @MONTH_END);
is there any way I can tweak this so I can show 5 full calendar years? In other words, If I run the query now (June 2017), I would like to go back to January of 2012. Same thing if they run the query in October, I need to go back to January 2012???
Thank you for all your help!
June 15, 2017 at 6:40 am
meichmann - Thursday, June 15, 2017 6:03 AMThank you! I did change:
DECLARE @MONTH_START AS date = DATEADD(year, -5, @MONTH_START); --- Was causing an error to declare the scalar variableTo This:
DECLARE @MONTH_START AS date = DATEADD(year, -5, @MONTH_END);is there any way I can tweak this so I can show 5 full calendar years? In other words, If I run the query now (June 2017), I would like to go back to January of 2012. Same thing if they run the query in October, I need to go back to January 2012???
Thank you for all your help!
I think I got it. I changed this line:
DECLARE @MONTH_START AS date = DATEADD(year, -5, @MONTH_END);
To This:
DECLARE @MONTH_START AS datetime = DATEADD(yy, DATEDIFF(yy, 0, @MONTH_END) - 5, 0)
When I run the query, I get data from January 2012.
Thank you again everyone for all your help!!!!
June 15, 2017 at 10:08 am
Build a look up table of reporting periods
CREATE TABLE Report_Periods
(report_name VARCHAR(30) NOT NULL PRIMARY KEY,
report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
etc);
These reports can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them;etc.
I like the MySQL convention of using zeroes in the ISO-8601 display format because it sorts correctly and is language independent. This uses "yyyy-mm-00" for months within a year and "yyyy-00-00" for entire years.
The basic skeleton for use with these tables is
SELECT R.report_name, << summary computations >>
FROM ReportRanges AS R, Events AS E
WHERE E.event_date BETWEEN R.report_start_date AND report_end_date
AND R.report_name IN (<<report name list>>)
GROUP BY R.report_name;
Please post DDL and follow ANSI/ISO standards when asking for help.
June 15, 2017 at 10:08 am
Build a look up table of reporting periods
CREATE TABLE Report_Periods
(report_name VARCHAR(30) NOT NULL PRIMARY KEY,
report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
etc);
These reports can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them;etc.
I like the MySQL convention of using zeroes in the ISO-8601 display format because it sorts correctly and is language independent. This uses "yyyy-mm-00" for months within a year and "yyyy-00-00" for entire years.
The basic skeleton for use with these tables is
SELECT R.report_name, << summary computations >>
FROM ReportRanges AS R, Events AS E
WHERE E.event_date BETWEEN R.report_start_date AND report_end_date
AND R.report_name IN (<<report name list>>)
GROUP BY R.report_name;
Please post DDL and follow ANSI/ISO standards when asking for help.
June 15, 2017 at 10:35 am
meichmann - Thursday, June 15, 2017 6:03 AMis there any way I can tweak this so I can show 5 full calendar years? In other words, If I run the query now (June 2017), I would like to go back to January of 2012. Same thing if they run the query in October, I need to go back to January 2012?
the original queries I posted should help you get that, so:
DECLARE @MONTH_START AS date = DATEADD(year, DATEDIFF(year, 0, GetDate())-5, 0);
June 16, 2017 at 10:46 am
meichmann - Thursday, June 15, 2017 6:40 AMmeichmann - Thursday, June 15, 2017 6:03 AMThank you! I did change:
DECLARE @MONTH_START AS date = DATEADD(year, -5, @MONTH_START); --- Was causing an error to declare the scalar variableTo This:
DECLARE @MONTH_START AS date = DATEADD(year, -5, @MONTH_END);is there any way I can tweak this so I can show 5 full calendar years? In other words, If I run the query now (June 2017), I would like to go back to January of 2012. Same thing if they run the query in October, I need to go back to January 2012???
Thank you for all your help!
I think I got it. I changed this line:
DECLARE @MONTH_START AS date = DATEADD(year, -5, @MONTH_END);To This:
DECLARE @MONTH_START AS datetime = DATEADD(yy, DATEDIFF(yy, 0, @MONTH_END) - 5, 0)
When I run the query, I get data from January 2012.
Thank you again everyone for all your help!!!!
Just so you know, 2012 through 2016 is indeed 5 calendar years, but do you really want to have to wait until 2018 to see any 2017 data ? 60 Months is usually a better choice, but one could also say, give me everything in the current year along with the previous X number of calendar years. Working out the formula with date math is trivial, but just something to remain aware of.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 16, 2017 at 11:27 am
If you want a rolling 60 months:
WHERE OpenDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 60, 0) /* or -59 if you count the current month as one of the 60 */
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply