November 27, 2017 at 7:03 am
Hi Guys,
I am using the below which does the following. Show the data for the current month unless today's date is the 1st, then show last months data, Is there a way to change this to do something similar but for the current and previous quarter, So show the data for this quarter unless today is the 1st OF THE NEW QUARTER then show last months quarter. Many thanks
DECLARE @sd DATETIME, @ed DATETIME;
-- set the start date to the first day of this month
SET @sd = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
-- if it's the first, we want last month instead
IF DATEPART(DAY, GETDATE()) = 1
BEGIN
SET @sd = DATEADD(MONTH, -1, @sd);
END
ELSE
-- set end dates
IF DATEPART(DAY, GETDATE()) = 1
SET @ed = DATEADD(MONTH, 1, @sd)
ELSE
SET @ed = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
SELECT logfile.Dealer, Dealers.Name
,SUM (CASE Logfile.Tran1 WHEN 'HOU' THEN 1 ELSE 0 END) AS [No Hours Available]
,SUM (CASE Logfile.Tran1 WHEN 'CAR' THEN 1 ELSE 0 END) AS [No Suitable Transport Options]
,SUM (CASE logfile.Tran1 WHEN 'NON' THEN 1 ELSE 0 END) AS [Other]
,SUM (CASE Logfile.Tran1 WHEN 'PRI' THEN 1 ELSE 0 END) AS [Price too high]
,SUM (CASE logfile.tran1 WHEN 'NAV' THEN 1 ELSE 0 END) AS [Training]
,COUNT (*) AS [Total]
FROM LogFile
LEFT JOIN Dealers on logfile.Dealer=Dealers.Dealer
WHERE dbo.LogFile.Created >= @sd AND dbo.LogFile.Created < @ed
AND logfile.Booking = ''
AND logfile.ContCode =''
GROUP BY logfile.Dealer, Dealers.Name
ORDER BY Dealers.Name
November 27, 2017 at 7:27 am
DECLARE @StartDate date, @EndDate date;
SET @StartDate = CASE WHEN DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0) = CONVERT(date,GETDATE()) THEN DATEADD(QUARTER, DATEDIFF(QUARTER, 0, DATEADD(DAY, -1,GETDATE())), 0)
ELSE DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0)
END;
SET @EndDate = CASE WHEN DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0) = CONVERT(date,GETDATE()) THEN DATEADD(DAY,-1,DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0))
ELSE GETDATE()
END;
SELECT @StartDate, @EndDate;
(Poorly aligned due to SSC pasting).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 27, 2017 at 8:10 am
This is one of the cases where changing your point of reference greatly simplifies your calculations. If your point of reference is today, it gets complicated, but if your point of reference is yesterday, it's easy.
Also, your end date is always today at midnight. There is a better way to calculate that.
Here is the code:DECLARE @StartDate DATETIME = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()-1), 0)
, @EndDate DATETIME = CAST(GETDATE() AS DATE)
SELECT @StartDate, @EndDate
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 27, 2017 at 8:35 am
Thanks guys works a treat. Just for my info is there anyway someone can explain what bit of code does what in the code THOM A wrote just so i can understand going forward. Still very new to SQL.
November 27, 2017 at 8:39 am
drew.allen - Monday, November 27, 2017 8:10 AMThis is one of the cases where changing your point of reference greatly simplifies your calculations. If your point of reference is today, it gets complicated, but if your point of reference is yesterday, it's easy.Also, your end date is always today at midnight. There is a better way to calculate that.
Here is the code:
DECLARE @StartDate DATETIME = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()-1), 0)
, @EndDate DATETIME = CAST(GETDATE() AS DATE)SELECT @StartDate, @EndDate
Drew
Much better answer tbh Drew. Not sure why I even took the route I did earlier (just gave the literal answer. I blame it being Monday!).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 27, 2017 at 8:50 am
sorry guys, which code should i being using now? thanks Thom A or drew.allen? thanks
November 27, 2017 at 9:01 am
you should be using your own code ! 🙂
don't run code you don't understand in production
November 27, 2017 at 9:07 am
complete agree arron thats why i asked if someone could briefly explain what section does what? I much prefer to understand than just copy and paste
November 27, 2017 at 9:17 am
craig.jenkins - Monday, November 27, 2017 9:07 AMcomplete agree arron thats why i asked if someone could briefly explain what section does what? I much prefer to understand than just copy and paste
No Prob.
DECLARE @StartDate DATETIME = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()-1), 0)
, @EndDate DATETIME = CAST(GETDATE() AS DATE)
@EndDate is quite simple here, it's taking the value of GETDATE() and casting it to a date. This effectively stripes the time off the value.
@StartDate we're going to work from the inside out. Firstly: DATEDIFF(QUARTER, 0, GETDATE()-1)
Simply, this works out the number of quarters between the date 0 (in SQL Server that's 1900-01-01), and the current date - 1 day (today being 2017-11-27, so 2017-11-26). That gives a value of 471.DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()-1), 0)
Then, you add that many quarters (471, from the expression above) to the date 0 (again 1900-01-01), giving a value of 2017-10-01.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 27, 2017 at 9:28 am
Great thank you Thom A. Which part does if '1st day of the the new quarter then show the last quarter - where is this in the code?' Many thanks appreciate it
November 27, 2017 at 9:32 am
craig.jenkins - Monday, November 27, 2017 9:28 AMGreat thank you Thom A. Which part does if '1st day of the the new quarter then show the last quarter - where is this in the code?' Many thanks appreciate it
GETDATE() - 1
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 27, 2017 at 9:50 am
Thank very much, i clearly need to do a lot more reading. Thanks again
November 27, 2017 at 10:33 am
craig.jenkins - Monday, November 27, 2017 9:28 AMGreat thank you Thom A. Which part does if '1st day of the the new quarter then show the last quarter - where is this in the code?' Many thanks appreciate it
This is what I meant about changing the perspective. You described your issue from the perspective of today: If today is the first day of the quarter use the last quarter (that is use yesterday's quarter), otherwise use today's quarter( which will also be the same as yesterday's quarter unless today is the first day of the quarter ). This caused you to need different conditions depending on whether it was the first day of the quarter. By changing your perspective, this can be simplified because you only have one condition: use yesterday's quarter.
So instead of using today as your frame of reference (GETDATE()), use yesterday as your frame of reference (GETDATE() - 1).
craig.jenkins - Monday, November 27, 2017 9:50 AMThank very much, i clearly need to do a lot more reading. Thanks again
You're not going to learn this by reading. This requires a different way of thinking about the problem.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 27, 2017 at 9:05 pm
drew.allen - Monday, November 27, 2017 10:33 AMcraig.jenkins - Monday, November 27, 2017 9:28 AMGreat thank you Thom A. Which part does if '1st day of the the new quarter then show the last quarter - where is this in the code?' Many thanks appreciate itThis is what I meant about changing the perspective. You described your issue from the perspective of today: If today is the first day of the quarter use the last quarter (that is use yesterday's quarter), otherwise use today's quarter( which will also be the same as yesterday's quarter unless today is the first day of the quarter ). This caused you to need different conditions depending on whether it was the first day of the quarter. By changing your perspective, this can be simplified because you only have one condition: use yesterday's quarter.
So instead of using today as your frame of reference (GETDATE()), use yesterday as your frame of reference (GETDATE() - 1).
craig.jenkins - Monday, November 27, 2017 9:50 AMThank very much, i clearly need to do a lot more reading. Thanks againYou're not going to learn this by reading. This requires a different way of thinking about the problem.
Drew
Heh... which you can't learn unless you first read. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply