July 16, 2013 at 3:12 pm
I have written the following query and it is returning negative values for some records when calculating the number of days between two dates:
set dateformat mdy
select b.VINTAGE,FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30), b.CR_SCR_BAND,
case
when a.DAYSPD < 1 then 'CURRENT'
when a.DAYSPD between 1 and 30 then '<30DPD'
when a.DAYSPD between 31 and 60 then '30DAY'
when a.DAYSPD > 60 then '60+DAY'
else 'ERROR' end as 'ME_DQ', count (a.account), sum(a.currtotalbal-a.CurrDisc) as 'BALANCE'
from dbo.ME_MASTER a left join #temp b on a.ACCOUNT = b.ACCOUNT
where a.OpenDt >=1110101 and VINTAGE is not null
group by b.VINTAGE, FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30), b.CR_SCR_BAND,
case
when a.DAYSPD < 1 then 'CURRENT'
when a.DAYSPD between 1 and 30 then '<30DPD'
when a.DAYSPD between 31 and 60 then '30DAY'
when a.DAYSPD > 60 then '60+DAY'
else 'ERROR' end
order by b.VINTAGE, FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30), b.CR_SCR_BAND,
case
when a.DAYSPD < 1 then 'CURRENT'
when a.DAYSPD between 1 and 30 then '<30DPD'
when a.DAYSPD between 31 and 60 then '30DAY'
when a.DAYSPD > 60 then '60+DAY'
else 'ERROR' end
set dateformat mdy
select top 10 b.VINTAGE, FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30) as 'MTH_ON_BOOKS', b.CR_SCR_BAND,
case
when a.DAYSPD < 1 then 'CURRENT'
when a.DAYSPD between 1 and 30 then '<30DPD'
when a.DAYSPD between 31 and 60 then '30DAY'
when a.DAYSPD > 60 then '60+DAY'
else 'ERROR' end as 'ME_DQ', a.account, (a.currtotalbal-a.CurrDisc) as 'BALANCE',
cast(right(a.OpenDt,6)as date) as 'Book Date', CAST(right(a.ASOFDATE,6)as date) as 'Month End'
from dbo.ME_MASTER a left join #temp b on a.ACCOUNT = b.ACCOUNT
where a.OpenDt >=1110101
--and b.VINTAGE is null
and FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30) < 0
order by b.VINTAGE,FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30), b.CR_SCR_BAND,
case
when a.DAYSPD < 1 then 'CURRENT'
when a.DAYSPD between 1 and 30 then '<30DPD'
when a.DAYSPD between 31 and 60 then '30DAY'
when a.DAYSPD > 60 then '60+DAY'
else 'ERROR' end
I would rather be using datefiff by months, but no matter which I choose I get negative values on some records. This sometime occurs on accounts where the two dates do not cross a year threshold. I have read several potential solutions but none seem to make sense. Any help would be greatly appreciated.
Thanks!
July 16, 2013 at 3:27 pm
If the first date is greater than the second date, you will get a negative value.
If you want to treat all date differences as positive, add ABS() function around the DATEDIFF() functions.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 16, 2013 at 3:32 pm
DATEDIFF can return negative numbers. FWIW, you don't need to use floor. You are dividing datediff / 30 which is integer division, the result will be an int. For example:
select 42/30
This will return 1. Not 1.4 as you might expect. 😀
We can certainly help you with the issues you facing. However, in order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 17, 2013 at 12:00 am
As ScottPletcher allready pointed out, a negative result value indicates the first date in the DATEDIFF function is later then the second date.
Make sure you enter complete date (yyyymmdd) or datetime values in the function to avoid ambiguity. I also notice you CAST one of the columns to a date type, before using it in the DATEDIFF function. Is that really necessary?
Second you get the difference in days and then you divide it by 30. Is this to get the difference in months? If you enter MONTH (instead of DAY) as datepart in the datediff function you get the number of months returned.
Please post the datatypes of the columns used, or even better (as Sean posted): complete DDL of the table and include some sample data.
July 17, 2013 at 2:12 am
APPLY simplifies this query nicely;
SELECT
b.VINTAGE,
x.DaysDiff,
b.CR_SCR_BAND,
x.ME_DQ,
count(a.account),
[BALANCE] = sum(a.currtotalbal-a.CurrDisc)
FROM dbo.ME_MASTER a
LEFT JOIN #temp b
on a.ACCOUNT = b.ACCOUNT
CROSS APPLY (
SELECT
[ME_DQ] = CASE
when a.DAYSPD < 1 then 'CURRENT'
when a.DAYSPD between 1 and 30 then '<30DPD'
when a.DAYSPD between 31 and 60 then '30DAY'
when a.DAYSPD > 60 then '60+DAY'
else 'ERROR' END,
[DaysDiff] = FLOOR(DATEDIFF(day,b.BOOK_MTH,CAST(right(a.asofdate,6) as DATE))/30)
) x
WHERE a.OpenDt >=1110101
AND VINTAGE is not null
GROUP BY b.VINTAGE, x.DaysDiff, b.CR_SCR_BAND, x.ME_DQ
ORDER BY b.VINTAGE, x.DaysDiff, b.CR_SCR_BAND, x.ME_DQ
-- what datatypes are a.asofdate and a.OpenDt?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply