October 3, 2017 at 9:07 am
Thank Thom, already had that chat with the manager but its a no go unfortunately as we don't own the database its a third party and they have only given me read access as a last option. Thanks anyway appreciate all your help with this
October 3, 2017 at 9:33 am
craig.jenkins - Tuesday, October 3, 2017 9:07 AMThank Thom, already had that chat with the manager but its a no go unfortunately as we don't own the database its a third party and they have only given me read access as a last option. Thanks anyway appreciate all your help with this
Do you have other databases? Your Calendar table does''t have to be in the same one.
The database I use for our application is similar, although I can write to it, it's one a one way system (data goes in, it doesn't go out). Thus we have other databases which we use for different projects (which interact with the software database). You'd just need to use 3 part naming conventions.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 3, 2017 at 10:46 am
Given your requirements, you appear to only need to worry about holidays that occur within the first 3 calendar days of the month. US only has Independence Day (July 4th, and only when the 3rd is a business day off because it's a Monday before a Tuesday holiday), and Labor Day (first Monday in September). Not sure what other countries might have, but school districts often have other days designated for special events or special holiday scenarios, so there can always be a local variation. That said, it may actually be possible in some cases to have code that will take all that into account.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
October 5, 2017 at 2:49 am
Thanks all, i think the best way around it as i don't have access to any other database is something like the following. I know its not the ideal way but the i think this will work. The next day this will affect me will be 2nd Jan 18. One last question, the current code looks at the start of the month to current day. Ideally i want it to look for current day minus one (so do not include any date from today). I thought it i could change SET @ed = DATEADD(MONTH, 1, @sd); TO GETDATE()-1 but no joy. Sorry guys like i said completely new to this but i am learning loads from this forum.
SET @ed = GETDATE()-1
==============================
--MY NEW CODE
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
-- if its 2nd jan 18
IF GETDATE() >='2018-01-02' AND GETDATE() <'2017-01-03'
BEGIN
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 5, 2017 at 2:57 am
sgmunson - Tuesday, October 3, 2017 10:46 AMGiven your requirements, you appear to only need to worry about holidays that occur within the first 3 calendar days of the month. US only has Independence Day (July 4th, and only when the 3rd is a business day off because it's a Monday before a Tuesday holiday), and Labor Day (first Monday in September). Not sure what other countries might have, but school districts often have other days designated for special events or special holiday scenarios, so there can always be a local variation. That said, it may actually be possible in some cases to have code that will take all that into account.
Does USA not have 1st January as a holiday? Or the following Monday if New Year's Day falls on a weekend?
In the UK, we have a couple of others to worry about:
Good Friday / Easter Monday - moveable
May Day holiday - first Monday in May
The others can be ignored given we're only talking about special handling for the first few days of the month...
--edit-- I see the OP is UK-based.
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 5, 2017 at 3:00 am
Hi, Yes UK has more hols. I will add in once i have fixed the other issue with data looking for previous day SET @ed = DATEADD(MONTH, 1, @sd);
Thanks
October 5, 2017 at 3:01 am
ThomasRushton - Thursday, October 5, 2017 2:57 AMDoes USA not have 1st January as a holiday? Or the following Monday if New Year's Day falls on a weekend?In the UK, we have a couple of others to worry about:
Good Friday / Easter Monday - moveable
May Day holiday - first Monday in MayThe others can be ignored given we're only talking about special handling for the first few days of the month...
I "love" the maths for Good Friday/Easter Monday in our Calendar Table. :sick:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2017 at 3:14 am
Thank you all, i believe i have sorted this now (code below). Thanks for everyone's input i have learnt a lot and this will seriously speed up my reporting.
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
-- if its 2nd jan 18
IF GETDATE() >='2018-01-02' AND GETDATE() <'2017-01-03'
BEGIN
SET @sd = DATEADD(MONTH, -1, @sd);
END
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
,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 5, 2017 at 3:25 am
I'm not sure that is correct. For example, one item I noticed is:IF GETDATE() >='2018-01-02' AND GETDATE() <'2017-01-03'
How can the current date be both greater or equal to 2018-01-02 AND less than '2017-01-03'? 2018-01-02 is greater than 2017-01-03, so this expression can never evaluate to TRUE.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2017 at 3:31 am
great spot thankyou Thom, it should be IF GETDATE() >='2018-01-02' AND GETDATE() <'2018-01-03'
October 5, 2017 at 3:48 am
craig.jenkins - Thursday, October 5, 2017 3:31 AMgreat spot thankyou Thom, it should be IF GETDATE() >='2018-01-02' AND GETDATE() <'2018-01-03'
Ok, but my concern is that this is still very much year specific. What happens in 2019? The Wednesday 02 January 2019 is the first working day of that month. You probably, long term, want to look at something that isn't year specific; otherwise you'll need to be editing your code every year (not ideal).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2017 at 3:53 am
yep its a complete nightmare, i'm hoping once the report is live and scheduled they will see the benefit from it and grant me access so i can then set up a calendar.
October 5, 2017 at 5:35 am
one last question on this topic guys
How do i make the 4 below exactly the same but for the previous year? I have tried to add DATEADD(YEAR, -1 at the beginning of the code but no joy.
-- 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
SET @sd = DATEADD(MONTH, -1, @sd);
SET @ed = DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
October 5, 2017 at 6:34 am
ThomasRushton - Thursday, October 5, 2017 2:57 AMsgmunson - Tuesday, October 3, 2017 10:46 AMGiven your requirements, you appear to only need to worry about holidays that occur within the first 3 calendar days of the month. US only has Independence Day (July 4th, and only when the 3rd is a business day off because it's a Monday before a Tuesday holiday), and Labor Day (first Monday in September). Not sure what other countries might have, but school districts often have other days designated for special events or special holiday scenarios, so there can always be a local variation. That said, it may actually be possible in some cases to have code that will take all that into account.Does USA not have 1st January as a holiday? Or the following Monday if New Year's Day falls on a weekend?
In the UK, we have a couple of others to worry about:
Good Friday / Easter Monday - moveable
May Day holiday - first Monday in MayThe others can be ignored given we're only talking about special handling for the first few days of the month...
--edit-- I see the OP is UK-based.
Yep, missed that one. Another brain fart... gee whiz folks... hope its not gettin' stinky in here... :crazy:
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply