August 14, 2017 at 11:29 am
Hi all, i am doing a date time based calculation. I have a table with basic format having two date columns:
businessname businessAmt BusinessDt createdatetime updatedatetime justification IsActive
For the first time when i drop some numbers for a business date for any particular business, createdatetime is populated and if i do it second time then, createdatetime remains same but updatedatetime gets populated with new row and IsActive ='y' and converting previous one with 'n'. User then also have to provide justification for those numbers.
BusinessDt is the usual business date and does not count weekends. For eg, 11-Aug is Friday so businessdt will be 11-Aug for those records which i dropped on Friday till 09:00 pm. After Friday 09:00 pm, businessdate changes to 14-Aug for rest of the Friday, sat, sun and Monday. That's how business date is calculated.
I am adding a new column to mark late justification by checking difference between createdatetime and updatedatetime based on businessdate calculation i.e. ignoring weekend. If it's greater then 2 then it should be marked as late else on time. Here i have to ignore Sat-Sun, month-end bounday and year-end boundary. Is it possible to do it in efficient way ?
August 14, 2017 at 1:20 pm
Sample data and expected results, please. You can find a link in my signature that explains how.
The best way to do this is with a computed column, but it's not clear from your description whether a computed column will work. A computed column can only use information from a single row, and it's not clear whether the information you want to use in your calculation will exist on a single row.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 14, 2017 at 2:39 pm
Given the complexity of the criteria of determining business days, this would probably be most easily solved if you had some kind of calendar table in your system. A calendar table has 1 row per calendar day, and you could have a column indicating business days. At a very minimum you would need a table with 1 row per non-business day, so you could handle holidays as well. The general idea is that you count the number of rows in your calendar table that are marked as business days between your 2 dates, in this case createdatetime and updatedatetime.
Here is a good article talking about calendar tables:
http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply