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 5, 2012 4:52 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 5:00 PM
Points: 69, Visits: 254


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 5, 2012 5:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
Assuming you have DATEFIRST set to 1, change GETDATE() to your date value.

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




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1381323
Posted Tuesday, November 6, 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: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1381570
Posted Tuesday, November 6, 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: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1381579
Posted Tuesday, November 6, 2012 7:57 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 2, 2014 5:00 PM
Points: 69, Visits: 254
Thanks a lot Jeff,
it worked beautifully!!!
Post #1381583
Posted Tuesday, November 6, 2012 2:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
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