September 17, 2015 at 10:37 am
Good morning,
I would like to know how I can make a script in SQL Server you can determine the difference days with bills based on date.
I show you an example:
Invoice Date Difference Day Amount
0001 1/1/2015 0 500.00
0003 18/1/2015 17 600.00
0008 2/2/2015 15 300.00
It is with DATEDIFF, but I'm stuck with logic, I could help.
Thank you.
September 17, 2015 at 11:09 am
reynoso.gonzalez (9/17/2015)
Good morning,I would like to know how I can make a script in SQL Server you can determine the difference days with bills based on date.
I show you an example:
Invoice Date Difference Day Amount
0001 1/1/2015 0 500.00
0003 18/1/2015 17 600.00
0008 2/2/2015 15 300.00
It is with DATEDIFF, but I'm stuck with logic, I could help.
Thank you.
If you're using SQL Server 2012... You can use the following...
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
CREATE TABLE #temp (
Invoice CHAR(4),
[Date] DATE,
Amount MONEY
);
INSERT #temp (Invoice,Date,Amount) VALUES
('0001','1/1/2015',500.00),
('0003','1/18/2015',600.00),
('0008','2/2/2015',300.00);
SELECT
t.Invoice,
t.Date,
t.Amount,
COALESCE(DATEDIFF(dd, LAG(t.Date, 1) OVER (ORDER BY t.Date), t.Date), 0) AS [Difference]
FROM
#temp t
September 17, 2015 at 11:10 am
Is this between a static date, like @CompareDate DATE?
Or is this between sequential Invoice Dates?
Either way you use DATEDIFF, but if you want to look at the previous date, you have to use LAG() to get the value of the date in the previous record.
September 17, 2015 at 11:10 am
reynoso.gonzalez (9/17/2015)
Good morning,I would like to know how I can make a script in SQL Server you can determine the difference days with bills based on date.
I show you an example:
Invoice Date Difference Day Amount
0001 1/1/2015 0 500.00
0003 18/1/2015 17 600.00
0008 2/2/2015 15 300.00
It is with DATEDIFF, but I'm stuck with logic, I could help.
Thank you.
You can use the LAG function:
CREATE TABLE #SampleData(
Invoice char(4),
InvoiceDate date,
DifferenceDay int,
Amount decimal(10,4)
)
INSERT INTO #SampleData
VALUES
('0001', '20150101', 0 , 500.00),
('0003', '20150118', 17, 600.00),
('0008', '20150202', 15, 300.00);
SELECT *, DATEDIFF( dd, LAG( InvoiceDate,1,InvoiceDate) OVER(ORDER BY InvoiceDate), InvoiceDate)
FROM #SampleData;
GO
DROP TABLE #SampleData;
EDIT: It seems that I was too slow.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply