Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Near Friday Date Expand / Collapse
Author
Message
Posted Monday, November 05, 2012 4:52 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 64, Visits: 233


Our Acounts Payable changed the way they pay vendors, from now on they will cut checks only on fridays, I was able to calculate the invoice due date based on the vendor terms and invoice date, now for example if the due date is on '11/20/2012' - Tuesday- I have to show a new column 'To be paid on' -'11/23/2012'-.

Any suggestions?

Thanks
Post #1381316
Posted Monday, November 05, 2012 5:17 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:52 PM
Points: 960, Visits: 1,921
Assuming you have DATEFIRST set to 1, change GETDATE() to your date value.

DATEADD( dd, 5 - DATEPART( dw, GETDATE()), GETDATE())




Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1381323
Posted Tuesday, November 06, 2012 7:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790
Maique (11/5/2012)


Our Acounts Payable changed the way they pay vendors, from now on they will cut checks only on fridays, I was able to calculate the invoice due date based on the vendor terms and invoice date, now for example if the due date is on '11/20/2012' - Tuesday- I have to show a new column 'To be paid on' -'11/23/2012'-.

Any suggestions?

Thanks

If the due date is on a Friday, do you want the pay-on date to be that Friday or the following Friday?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1381570
Posted Tuesday, November 06, 2012 7:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790
If what I state in the previous post is true, the following will do the trick. Here's a test.
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
GO
--===== Populate the table with test data.
-- This is not a part of the solution.
SELECT TOP 100
DueDate = ABS(CHECKSUM(NEWID())) % DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),
PayDate = CAST(NULL AS DATETIME)
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== This calculates the pay-on date for each date
-- and stores it in the same table.
-- This is a solution that doesn't depend on DATEFIRST.
UPDATE #TestTable
SET PayDate = DATEADD(dd,DATEDIFF(dd,4,DueDate+6)/7*7,4)
;
--===== Display the results for verification.
-- This is not a part of the solution.
SELECT DueDate, DATENAME(dw,DueDate),
PayDate, DATENAME(dw,PayDate)
FROM #TestTable
;

Beware, though. The requirements you've given absolutely guarantee that every bill will be paid late. Not a good way to make your vendors happy.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1381579
Posted Tuesday, November 06, 2012 7:57 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 64, Visits: 233
Thanks a lot Jeff,
it worked beautifully!!!
Post #1381583
Posted Tuesday, November 06, 2012 2:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7, 7= Sunday) day of the week.

You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
WHERE sale_day LIKE '2012W26-[67]'

There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html

But what is the business rule for a three-day weekend (Good Friday, for example)? And you want to get to the coming Friday or the prior Friday if it is in the future?




Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1381713
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse