November 21, 2019 at 6:58 pm
Hello,
Need a simple script that would pull back the previous business days' assigned tickets.
Would need to replace this current script that I manually change daily. So if I run on the morning of the 21st it is pulling back the previous days' tickets. Need this automated for nightly extract purposes. This will also come in useful for some other scenarios of mine.
WHERE CLOG.RecvdDate between '2019-11-20' and '2019-11-20'
Thanks,
Russ
November 22, 2019 at 10:51 am
I hope CLOG.RecvdDate is not a string
WHERE CLOG.RecvdDate >=DATEADD(DAY,-1,convert(date,getdate())) and CLOG.RecvdDate<convert(date,getdate()))
November 22, 2019 at 11:00 am
Yes, or WHERE CAST(CLOG.RecvdDate AS date) = '20191120'. I think casting as date preserves sargability (provided that the column is datetime in the first place).
John
November 22, 2019 at 12:21 pm
Jo and John -- Apologies for not giving the data type. It is varchar....I had forgot that basically this entire table is varchar.
November 22, 2019 at 12:33 pm
Yes, or WHERE CAST(CLOG.RecvdDate AS date) = '20191120'. I think casting as date preserves sargability (provided that the column is datetime in the first place).
John
It does and it's better than something non-Sargable, but it's rumored (I've not tested it because I never use it) to still be slower than doing it in a true SARGable fashion.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2019 at 3:42 pm
If you really want "previous business day" so that running it on Monday gives you Friday data, build a date table. Assign a "businessDay" column and populate another table with holidays. Run a script that recalculates the "businessDay" column by using ranking functions and doesn't increment the businessDay value over a holiday or weekend. Then, you can build a function that find the previous (or next) business day by looking up today's value and incrementing/decrementing.
My example, that Jeff will probably yell at me about because it uses a loop:
CREATE TABLE [dbo].[RefDate](
[MonthNo] [int] NOT NULL,
[YearNo] [int] NOT NULL,
[ReportDate] [datetime] NOT NULL,
[QtrNo] [int] NOT NULL,
[YearQtrNo] [char](8) NOT NULL,
[WeekNo] [smallint] NULL,
[WeekDay] [char](3) NULL,
[Holiday] [char](1) NULL,
[EndOfMonth] [smalldatetime] NULL,
[MonthDayCount] [int] NULL,
[BusinessDay] [float] NULL,
[iRow] [int] NOT NULL,
[YrMth] [varchar](15) NULL,
[StartOfMonth] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Holidays](
[HolDate] [datetime] NULL,
[Desc] [varchar](50) NULL,
[Type] [char](1) NULL,
[NbrDays] [numeric](4, 2) NULL
) ON [PRIMARY]
CREATE PROCEDURE [dbo].[updateRefDate_businessDays]
AS
SET NOCOUNT ON
DECLARE @counter int, @busdays int
SET @busdays=1
SET @counter=1
WHILE @counter < 25935
BEGIN
UPDATE dbo.refDate SET BusinessDay = @busdays
WHERE iRow=@counter
SELECT @busdays = @busdays + CASE
WHEN WeekDay IN ('Sat','Sun') THEN 0
WHEN Holidays.NbrDays IS NULL THEN 1
ELSE 1-Holidays.NbrDays
END
FROM dbo.refDate
LEFT JOIN dbo.Holidays ON Holidays.HolDate=refDate.reportdate
WHERE iRow=@counter
PRINT @counter
SET @counter=@counter + 1
END
CREATE FUNCTION [dbo].[fn_previousBusinessDay]
( @date datetime
)
RETURNS datetime
AS
BEGIN
Declare @dateVal datetime;
SET @dateVal = dateadd(dd,datediff(dd,'1/1/1900',@date),'1/1/1900');
SELECT @dateVal = reportdate
FROM dbo.RefDate
WHERE BusinessDay = (
Select businessday
FROM dbo.RefDate
WHERE reportDate = @dateVal
)-1
RETURN @dateVal;
END
CREATE FUNCTION [dbo].[fn_nextBusinessDay]
( @date datetime
)
RETURNS datetime
AS
BEGIN
Declare @dateVal datetime;
SET @dateVal = dateadd(dd,datediff(dd,'1/1/1900',@date),'1/1/1900');
SELECT @dateVal = reportdate
FROM dbo.RefDate
WHERE BusinessDay = (
Select businessday
FROM dbo.RefDate
WHERE reportDate = @dateVal
)+1
RETURN @dateVal;
END
CREATE FUNCTION [dbo].[fn_OffsetBusinessDays] (@offset int)
/******************************************************************************
Purpose:
This function takes the input integer, offsets the current date by that many business days,
and returns the corresponding date from dbo.refdate
Dependencies:
dbo.holidays and dbo.refdate must be up to date to work correctly
Revision History:
2/13/2010 initial implementation
******************************************************************************/
RETURNS datetime
AS
BEGIN
DECLARE @date datetime
SELECT @date=reportdate
FROM dbo.refDate
WHERE businessday = (SELECT businessday FROM dbo.refDate WHERE reportdate=dateadd(dd,datediff(dd,0,getdate()),0))+@offset
AND WeekDay NOT IN ('Sat','Sun')
RETURN @date
END
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
November 22, 2019 at 3:43 pm
side note, now I need to go update my holiday table and rerun my business day calculation....
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
November 22, 2019 at 4:05 pm
Jon, kinda sorta worked with you at Molina (Russ H. came in under Sampath). You gotta be the same guy because you were way too smart for me then also. i don't do a ton of SQL coding in my current gig, I use a mish mash of reporting tools and don't even have the required writing access you are recommending for this task.
That said, you did give me an idea. I could create a manual excel table for this that excludes any weekends and holidays, which in turn I could join to my input script coming into Tableau by my create date. Not sure how up you are with Tableau, but scripting seems limited to pretty straightforward pulls and subsequent joins.
That would get me the dates matched up, so then I can probably do a calculation in Tableau for the previous date fairly easily -- well, I say fairly easily, but that remains to be seen. What do you think?
--Russ
November 22, 2019 at 4:58 pm
This reply has been reported for inappropriate content.
Hey Russ, good to hear from you! Thanks for the compliment, but truth be told, it's all just painful experience, not smarts. I got the idea from one of these guys around here, stole the date table format from IT, etc. Laziness is the mother of invention.
I have zero Tableau experience, but if it will let you join two sets then your solution seems to make sense to me.
Alternatively, get IT to build it for you 🙂
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
November 23, 2019 at 1:50 am
What's really stupid is that Jonathan is absolutely correct (and, NO, I'm NOT saying Jonathan is stupid... Jonathan is far from stupid and only occasionally resorts to RBAR 😀 ). Lot's and lot's of people have written solutions for this stuff. Even more people have written solutions for "Tally-table-like" functionality. It's been more than a decade since Erland Sommarskog put in a highly approved suggestion for a machine language equivalent to a Tally Table. Shoot... even (IIRC) Postgres has a built in function for that.
It's not like the Gregorian Calendar was invented yesterday. You'd think that MS and Tableau and other monster companies would make life a little easier to build in a "business calendar" into their products.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2019 at 2:22 pm
Jeff -- The irony of this is that I created my own excel table in about 15 minutes that seems to have solved my problem from Jon's recommendation. I took the dates through next year, converted to the same varchar format, grabbed our company calendar and deleted the holidays. Then I opened my Outlook calendar and deleted all weekends.
Result is joining to my script by that varchar date field in Tableau that will remove any weird little one offs of someone assigning a ticket or two on the weekends etc.
Dependency is that it will have to be updated yearly (I was too lazy to keep going) or modified for any exceptions i.e. customer service working New Year's Day etc.
Creating and staging data for regular Tableau Server refreshes daily poses more challenges because it basically removes any middle men like Alteryx for data blending and manipulation, it all has to be done in Tableau or Tableau Prep. This is where I wish my SQL coding was much better, but I simply don't work in there enough to improve much. This staging method is also only conducive for this particular report type.
Jon, you would be a beast in Tableau. I am sure Jeff would too if he isn't already.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply