We all have functions and code for business day calculations, or last day of the month calculations, ect... I am adding a caveat to this for Last business day of the month, not a holiday. I.e. (hypothetical), Last day is 12/31/2015, this is a Friday, however the company give this day off as a holiday so 12/30/2015 needs to be determined as the last business day of the month.
I am looking at a couple of ways to approach this.
1) Calculate the Last Day of the month, Determine if it is not a Sat\sun, if is DateDiff to a business day, then take that date and compare to a Holiday table and adjust accordingly.
2) use a function that already exists for Last Business day of the month and then take that date and compare to a Holiday table and adjust accordingly.
3) some better what that I can not think of and no we can not use the XLeratorDB or what ever that is called.
The out put I am looking for is just a DATE, the date of the very last company business day of the month.
Thank you In advance!
No reason to re-invent the wheel, just improve upon existing.
use a function that already exists for Last Business day of the month
Maybe somneone already wrote one, but there's no such thing built in, AFAIK. What a "week" or "weekend" is, is determined by your locale, and a company may have multiple of those. Holidays are even more complex, and tend to change rather frequently.
Create a loop that inserts weekdays for your locale in a table; that way you have list with the weekends already eliminated. Next, delete every date in there that is mentioned in the holidays table (or join, or make a new table). Select the top result for that month.
There's your last theoretical work-day.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
I use a view and a holiday table as I need to keep holidays for a number of countries/currencies. The view starts at the beginning of last year and goes for 10 years, arbitrarily picked by me, it has date part DayOfWeek so I can exclude weekends.
It will be fairly simple to combine the two as Eddy suggested, fiddling with the filters/joins to get the last working sate.
Never underestimate the power of human stupidity
I am quite a beginner in Sql Server, I am testing some simple sql code on Sql Server 2012.
I have a table called "AcctTransaction" composed by 4 columns :
AccountId as Int,
TransactionId as Int,
TransactionDate as DateTime,
Amount as Decimal (a8,4)
I am trying to make this simple query work :
,SUM(Amount) OVER (PARTITIONBY AccountId) AS FinalBalance
,SUM(Amount) OVER (PARTITIONBY AccountId ORDERBY TransactionDate, TransactionId
) AS ProgressiveBalance
I keep getting the following error :
Incorrect syntax near "order"
and in effect if I comment out the
,SUM(Amount) OVER (PARTITIONBY AccountId ORDERBY TransactionDate, TransactionId ) AS ProgressiveBalance
statement, everything works correctly.
What's wrong with that statement ?
I've made this query copying almost identically a sql statement I saw in a Microsoft course, where it seems to work perfectly....
Thanks to everyone who would answer ...
A sudden thought ... is it possibile that I am getting this error only because I am using the Express version of Sql Server 2012 ? Maybe the express version, being not complete, lacks the "LAG" function....
Just a random thought .....
Well, that's what it says on "Help | About" section from the top menu ....
Other infos are :
Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 9.11.9600.17801
Microsoft .NET Framework 4.0.30319.34209
Operating System 6.1.7601
Is it possible I have some components that's missing ?