CODEWITHSUNDEEP
sql-serverdate
Anon
Anon

Reputation: 33

Subtract date from day field

Original Question

I need to write a Microsoft SQL statement where I subtract a date from number of days from another table. So, essentially, the table example is below:

Table 1 (date)           Table 2 (number of days)
2013-05-08 23:59:13.000     7
2013-05-08 23:59:16.000     7
2013-05-08 23:59:06.000     7

Any help would be appreciated.

Updated Question

The only two item I have to work with is the following information:

enter image description here

I need to know how long an invoice has been in the invoice queue. TimeoutDate is when the invoice will timeout of the queue, and TimeOutDays is the length of days this queue is given before it times out, but it's business days, so excluding weekends.

So, if you calculate based on this information, the invoice has been in the queue for 8 business days <-- this is the answer I need to get from the query.

Extra Information

The invoice entered into the system 04/30/2013, but since the webapp does not count the day the invoice entered the system, the time-out counter starts the day after, which is 05/1/2013. Hope this explains a little clearer!

I have a quick query, but that's not going to help, since it doesn't show what I need, but in effect below is the results of what I need:

enter image description here

Upvotes: 3

Views: 376

Answers (2)

peterm
peterm

Reputation: 92785

Try

SELECT DATEADD(day, -(t2.days), t1.datefield) new_date
  FROM table1 t1 JOIN
       table2 t2 ON t1.id = t2.id

Output:

|                   NEW_DATE |
------------------------------
| May, 01 2013 23:59:13+0000 |
| May, 01 2013 23:59:16+0000 |
| May, 01 2013 23:59:06+0000 |

SQLFiddle

UPDATE: A solution to the completely changed question

SELECT timeoutdate,
       timeoutdays,
       timeoutdays
       -((DATEDIFF(dd, GETDATE(), timeoutdate) + 1)
       -(DATEDIFF(wk, GETDATE(), timeoutdate) * 2)
       -(CASE WHEN DATENAME(dw, GETDATE()) = 'Sunday' THEN 1 ELSE 0 END)
       -(CASE WHEN DATENAME(dw, GETDATE()) = 'Saturday' THEN 1 ELSE 0 END)) daysinqueue
FROM table1

Output:

|             TIMEOUTDATE | TIMEOUTDAYS | DAYSINQUEUE |
-------------------------------------------------------
| 2013-05-14 23:59:13.000 |          10 |           9 |

SQLFiddle

Thanks @CMS for a solution for getting number of working days between two dates

Upvotes: 1

Sachin
Sachin

Reputation: 40970

You can try something like this

Select DATEADD(month, -(Table2.day) , Table1.date) 
FROM Table1 Inner Join Table2
ON Table1.Col=Table2.Col

Of-course you can modified the join as per your structure.

Upvotes: 0

Related Questions