October 2, 2017 at 7:46 am
Hi Guys,
I am using the following code to pull back data based on the start of the month and yesterdays date. Essentially the report is tracking progress month date. I have only used this for a month so it has worked great up until today. The problem today however is that because yesterday (Sunday) was the start of the month the code now looks from yesterday but i want the query to show from the first of last month until the end of last month. Obviously tomorrow the code will work fine and show me what i want but for today its
So essentially i need the code to write - If today is the start of the month, then show all of last months data, else do below. I suppose it will also bring an issue if Saturday or Sunday or a bank holiday was the start of the month I hope this makes sense.
Thank you
where DBO.LogFile.Created >= DATEADD(month, DATEDIFF(month, 0, @mydate), 0)
AND DBO.LogFile.Created < Convert(Date, GetDate())
AND DBO.LogFile.Tran1 in ( 'IBB','W3B')
GROUP BY LogFile.Dealer, Dealers.Name
order by LogFile.Dealer
October 2, 2017 at 7:55 am
Based on your description, should today not display results for just yesterday? The current month is October and yesterday's date is 01 October 2017. Other wise, what results are you expect tomorrow, on 03 October 2017? The stats for only the 1st? That's 2 days prior, not 1.
I would hazard a guess that actually what you want is a date range of the start of the month of yesterday to yesterday. That would be:WHERE dbo.LogFile.Created >= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())),0)
AND dbo.LogFile.Created < CONVERT(date, GETDATE())
Today, those two values would return 01 Oct 2017 (the start of yesterday's month) and today (bringing back results of more than or equal to 01 Oct 2017 and less than 02 Oct 2017). Yesterday, however, it would have returned 01 Sep 2017 and 01 Oct 2017 (bringing back results of more than or equal to 01 Sep 2017 and less than 01 Sep 2017; aka All of September).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 2, 2017 at 8:04 am
Hi Thom A,
Thanks for coming back to me. You are correctly yesterday would have provided me with all of September which is correct but because Sunday is a non working day i actually need to see yesterdays results today if that makes sense? So if the first of the month is a Sat, Sun or Public holiday show last months data, else show the current month.
October 2, 2017 at 8:22 am
craig.jenkins - Monday, October 2, 2017 8:04 AMHi Thom A,Thanks for coming back to me. You are correctly yesterday would have provided me with all of September which is correct but because Sunday is a non working day i actually need to see yesterdays results today if that makes sense? So if the first of the month is a Sat, Sun or Public holiday show last months data, else show the current month.
Ok, that's not quite the same logic then. 🙂
Considering you need to include public holidays you'd be better off using a Calendar table; as SQL Server doesn't know when Public Holidays are. You'll likely then want to use a sub query or CROSS APPLY to get the dates you need from your table that are applicable for your needed date range.
As every company often has a different set up for their calendar table, I haven't posted an example, however, if you get stuck once you've set yours up I'll be happy to post one which sues my own. YOu should then be able to translate that for your environment.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 2, 2017 at 8:25 am
ok thanks a lot. I'll have a blast and see how i get on. New to SQL so will probably come back for more help. Thanks again. Craig
October 2, 2017 at 8:30 am
craig.jenkins - Monday, October 2, 2017 8:25 AMok thanks a lot. I'll have a blast and see how i get on. New to SQL so will probably come back for more help. Thanks again. Craig
No problem, any questions, please do reply. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 2, 2017 at 9:21 am
Sorry Thom, I have had a look at the calendar function and it is completely over my head, As mentioned i am completely new to SQL. If you could have a look at the below that would be great but completely appreciate if not.. Thanks, Craig
if todays date is any of the below dates in table1, then use the start of last month to the end of the last month else use
WHERE dbo.LogFile.Created >= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, 1,GETDATE())),0)
AND dbo.LogFile.Created < CONVERT(date, GETDATE())
table 1 =
01/10/17
02/10/17
02/12/17
03/12/17
04/12/17
SELECT logfile.Dealer,Dealers.Name
,COUNT(*) AS Booked
, SUM(CASE RepairCodes.[Service] WHEN 'Y' THEN 1 ELSE 0 END) AS SER
, SUM(CASE ServCode WHEN 'MST' THEN 1 WHEN 'S&M' THEN 1 ELSE 0 END) AS MST
, SUM(CASE ServCode WHEN 'MOT' THEN 1 ELSE 0 END) AS MOT
, SUM(CASE ServCode WHEN 'WAR' THEN 1 ELSE 0 END) AS WAR
, SUM(CASE PayType WHEN 'INTERNAL' THEN 1 ELSE 0 END) AS [INT]
,COUNT(DISTINCT RegNo) AS Veh
,COUNT(*)
-SUM(CASE RepairCodes.[Service] WHEN 'Y' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'MST' THEN 1 WHEN 'S&M' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'MOT' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'WAR' THEN 1 ELSE 0 END) AS Other
, SUM(TotalValue) AS [Value]
FROM LogFile
LEFT OUTER JOIN RepairCodes
ON RepairCode = ServCode
AND LogFile.Dealer = RepairCodes.Dealer
JOIN Dealers ON Dealers.Dealer=LogFile.Dealer
WHERE dbo.LogFile.Created >= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())),0)
AND dbo.LogFile.Created < CONVERT(date, GETDATE())
AND DBO.LogFile.Tran1 in ( 'IBB','W3B')
GROUP BY LogFile.Dealer, Dealers.Name
order by LogFile.Dealer
October 2, 2017 at 9:25 am
This was removed by the editor as SPAM
October 2, 2017 at 9:33 am
I'm not sure I understand why a bunch of dates in the relatively distant past are in any way relevant to what you are doing. Also, would you really want to look at the month of February's data between the 1st of February and the 9th of February, and then only on the 10th and 12th, show January's data, and then go back to February's data for the rest of the month? That's how I interpreted what you typed... It just seems like a rather odd method of reporting, and maybe you meant something else...
Also, most reporting scenarios need to be fairly consistent in terms of when they do what. As you've only provided some specific dates that are well in the past, a current report would not actually need to take those dates into account. Therefore, what's the objective here?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 2, 2017 at 9:50 am
Hi,
So sorry. ok, so the query pulls back data based on the current month to yesterdays date (which is great but sometimes this is not what i need). It works great but when when the 1st of the month falls on a Sat, Sun or bank holiday its not good. The dates listed are just examples I assumed I could just change the dates as needed or add to the list (BTW I an using UK dates). Apologies for that and actually the next time this will become and issue will be when I run the query on 2nd Jan 18. On the 2nd of Jan I do not want to look at Januarys data but want to show the full month of Dec 17. I assumed I could just list a number of dates and if today's date falls on one of those dates, rather than run for the current month, run the report for the whole of the previous month. So sorry am I making any sense here?
October 2, 2017 at 9:53 am
Ok, the first important part is to create your Calendar table. How to do that is explained in the link I provided earlier, so I'm going to skip over that part. Further in that table is has a heading for Expanded Calendar Table, which introduces adding Holidays to your table. This is the second important part, as your above logic specified that you need to do different logic when it's the first working day.
Once you have that you can then start writing your query to use the new logic. I'm not using the Calendar table in the Bones of SQL link, however, the logic is still similar. I don't, for example have a Holiday and Weekend column, just a working day one, which I'll be making use of.
Firstly, I worked out the logic to get the right date for the End. I did this with the following:SELECT CASE WHEN SUM(CONVERT(int,[Working Day])) = 0 THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0) ELSE CONVERT(date, GETDATE()) END AS EndDate
FROM DimDate DD
WHERE DD.Date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())),0)
AND DD.Date < CONVERT(date, GETDATE());
This (today) returns 01 Oct 2017. This is good, because today we want data older than then.
To explain the logic, the query is filtered to all the days of yesterday's month up to yesterday (in this case just 01 October). What it then does is sums the [Working Day] column (which is a bit on my environment, hence the CONVERT). If 0 working days are found, then the very first day of that month is returned. Otherwise today's date is returned.
Then, we can use that logic to create a WHERE clause, using CROSS APPLY.:SELECT [YourColumns]
FROM dbo.LogFile LF
CROSS APPLY (SELECT CASE WHEN SUM(CONVERT(int,[Working Day])) = 0 THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()),0) ELSE CONVERT(date, GETDATE()) END AS EndDate
FROM DimDate DD
WHERE DD.Date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())),0)
AND DD.Date < CONVERT(date, GETDATE())) ED
WHERE LF.Created >= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, ED.EndDate)),0)
AND LF.Created < ED.EndDate;
That query might seem a bit "daunting", but it's actually fair simple. Notice that the SQL statement I gave you earlier is inside the CROSS APPLY, so you know what that is doing. Then, instead of using GETDATE() in the WHERE clause, I used ED.EndDate, which has the correct date for where you want to terminate your data.
As I said before, you need your Calendar table in place first, so have a go at that. If you get stuck with it, the best to post about that particular table is the discussion for the article. If there's a part you don't understand you can ask here, however, if it's not working, the article is the best place as the author will likely have a subscription to the topic. The actual script to create it is at the bottom of article, so have a good read through.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 2, 2017 at 11:04 am
craig.jenkins - Monday, October 2, 2017 9:50 AMHi,So sorry. ok, so the query pulls back data based on the current month to yesterdays date (which is great but sometimes this is not what i need). It works great but when when the 1st of the month falls on a Sat, Sun or bank holiday its not good. The dates listed are just examples I assumed I could just change the dates as needed or add to the list (BTW I an using UK dates). Apologies for that and actually the next time this will become and issue will be when I run the query on 2nd Jan 18. On the 2nd of Jan I do not want to look at Januarys data but want to show the full month of Dec 17. I assumed I could just list a number of dates and if today's date falls on one of those dates, rather than run for the current month, run the report for the whole of the previous month. So sorry am I making any sense here?
Ah, okay... Now it makes more sense. Indeed, as Thom has posted, you need your calendar table to exist (not a bad idea to name it dbo.DimDate, by the way). There should be a date field and bit flags to indicate weekend day or holiday, along with weekday values and any other specialized reporting flags or values you might need. Your SQL code can automatically determine the reporting period from a CROSS APPLY of a query against dbo.DimDate that seeks the MIN() value of the day within the current month that is NOT a weekend or holiday, and checks the current date to see if it's in the same month and year and has a lower day number. If so, you get last month, otherwise you get this month.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 3, 2017 at 8:44 am
Thank you so much everyone for this. I have another issue now being that i do not have access to create tables in the database and the developers will not give me access so i cant'; try and create a temporary calendar table, Is there any other way around this?
October 3, 2017 at 9:03 am
I've actually just found some code that may help. This works perfectly if today's date is the first of the month. So if today's date is the first of the month it runs the data for the whole of last month, other wise it runs from the start of this month until current day. Would there be a way to change --IF DATEPART(DAY, GETDATE()) = 1 = 1 to something like:
IF DATEPART(DAY, GETDATE()) = '2017-11-01' or '2017-12-01' or '2018-01-04' etc etc so i can chose my own dates. 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 DATEPART(DAY, GETDATE()) = 1
BEGIN
-- if it's the first, we want last month instead
SET @sd = DATEADD(MONTH, -1, @sd);
END
SET @ed = DATEADD(MONTH, 1, @sd);
SELECT logfile.Dealer,Dealers.Name
,COUNT(*) AS Booked
, SUM(CASE RepairCodes.[Service] WHEN 'Y' THEN 1 ELSE 0 END) AS SER
, SUM(CASE ServCode WHEN 'MST' THEN 1 WHEN 'S&M' THEN 1 ELSE 0 END) AS MST
, SUM(CASE ServCode WHEN 'MOT' THEN 1 ELSE 0 END) AS MOT
, SUM(CASE ServCode WHEN 'WAR' THEN 1 ELSE 0 END) AS WAR
, SUM(CASE PayType WHEN 'INTERNAL' THEN 1 ELSE 0 END) AS [INT]
,COUNT(DISTINCT RegNo) AS Vehs
,COUNT(*)
-SUM(CASE RepairCodes.[Service] WHEN 'Y' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'MST' THEN 1 WHEN 'S&M' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'MOT' THEN 1 ELSE 0 END)
-SUM(CASE ServCode WHEN 'WAR' THEN 1 ELSE 0 END) AS Other
, SUM(TotalValue) AS [Value]
FROM LogFile
LEFT OUTER JOIN RepairCodes
ON RepairCode = ServCode
AND LogFile.Dealer = RepairCodes.Dealer
JOIN Dealers ON Dealers.Dealer=LogFile.Dealer
AND dbo.LogFile.Created >= @sd
AND dbo.LogFile.Created < @ed
AND DBO.LogFile.Tran1 in ( 'IBB','W3B')
GROUP BY LogFile.Dealer, Dealers.Name
order by LogFile.Dealer
October 3, 2017 at 9:04 am
craig.jenkins - Tuesday, October 3, 2017 8:44 AMThank you so much everyone for this. I have another issue now being that i do not have access to create tables in the database and the developers will not give me access so i cant'; try and create a temporary calendar table, Is there any other way around this?
Not really. For logic like this having a Calendar table is incredibly important. You could probably do something for weekends without a static table, however, that solution wouldn't be able to cater for Public Holidays. If this isn't acceptable for your business, then you need to propose the merits of having a Calendar table to your Manager, and ask them to ensure that the steps are taken to implement the changes you need.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply