CODEWITHSUNDEEP
sql-server
JNR_Prog123
JNR_Prog123

Reputation: 133

Calculate days remaining using in SQL Server

I need a SQL query that would allow me to show how many days are remaining for me to process an invoice, this query should indicate the time remaining to authorize quotes before automatic cancellation which is 14 days for un-processed and 7 days for partially processed.

Can anyone please help me?

Eg. if a quote came in today it should show 14 days, tomorrow 13 days etc.

Upvotes: 1

Views: 4758

Answers (3)

gofr1
gofr1

Reputation: 15997

You did not provide data, so here is example based on dummy data. As was mentioned in comments, you need to use DATEDIFF function. But before you find out days count between dates, you should add 14 to invoice date with the help of DATEADD function. That will give you countdown effect you need:

;WITH invoices AS (
SELECT  1 as invoice_num,
        '2016-07-10' as invoice_date,
        1 as invoice_status
UNION ALL
SELECT  2,
        '2016-07-01',
        1
UNION ALL
SELECT  3,
        '2016-07-12',
        2
UNION ALL
SELECT  4,
        '2016-07-18',
        2
), invoice_statuses AS (
SELECT  1 as invoice_status, 
        'un-processed' as status_description
UNION ALL
SELECT  2, 
        'partially processed'
)


SELECT  i.invoice_num, 
        i.invoice_date,
        s.status_description,
        DATEDIFF(day,GETDATE(),DATEADD(day,
            CASE WHEN i.invoice_status = 1 THEN 14 
                 WHEN i.invoice_status = 2 THEN 7
                 ELSE 0 END
                            ,i.invoice_date)) as days_left
FROM invoices i
LEFT JOIN invoice_statuses s 
    ON s.invoice_status = i.invoice_status

Output will be:

invoice_num invoice_date    status_description  days_left
1           2016-07-10      un-processed        4
2           2016-07-01      un-processed        -5
3           2016-07-12      partially processed -1
4           2016-07-18      partially processed 5

If you change GETDATE() to 2016-07-21 output will be:

invoice_num invoice_date    status_description  days_left
1           2016-07-10      un-processed        3
2           2016-07-01      un-processed        -6
3           2016-07-12      partially processed -2
4           2016-07-18      partially processed 4

Upvotes: 0

Cato
Cato

Reputation: 3701

to get your cancellation date, you need your initial date with either 7 or 14 days remaining (depending on status)

here is an example

declare @datStart as date = '2016-07-15';  --an initial start date
declare @proc as nvarchar(1) = 'U';  --unprocessed

--this would give a final process ing date
SELECT case when @proc ='U' THEN DATEADD(DAY, 14, @datStart) ELSE DATEADD(DAY, 7, @datStart) END

--this gives you days unil the delete date
SELECT datediff(day,getdate(), case when @proc ='U' THEN DATEADD(DAY, 14, @datStart) ELSE DATEADD(DAY, 7, @datStart) END)

Upvotes: 0

Chris Pickford
Chris Pickford

Reputation: 9001

As mentioned in the comments, this can be easily achieved using the DATEDIFF function:

DECLARE @InvoiceDate DATE = '2016-08-01';
SELECT DATEDIFF(DAY, GETDATE(), @InvoiceDate);

Upvotes: 1

Related Questions