Results 1 to 15 of 18
Thread: Query Sql  Day Count

041707, 18:37 #1Registered User
 Join Date
 Apr 2007
 Posts
 51
Unanswered: Query Sql  Day Count
I HAVE A TABLE WITH, AMONG OTHERS, THE FIELDS DATE1 AND DATE2 IN WHICH
DATA ARE IN THE FORMAT (20070417, ....) (THEY ARE NUMBERS AND NOT DATES!!!!!).
I WANT TO CALCULATE THE DIFFERENCE BETWEEN DATE2 AND DATE1 ACCORDING
TO THE 30/360 DAY COUNT (EVERY MONTH HAS THIRTY DAYS). HOW CAN I
TRANSFORM THE NUMBERS INTO DATES AND THEN CALCULATE THE DIFFERENCE?
PLEASE ANSWER TO ME AS SOON AS POSSIBLE.
THANK YOU IN ADVANCE
ANNA from Verona (Italy)

041707, 20:03 #2SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
which database? date functions vary wildly from one to the next

041807, 02:16 #3Registered User
 Join Date
 Apr 2007
 Posts
 51
date count
Originally Posted by r937
Please help me doing my query.
Anna  Verona Italy

041807, 04:57 #4SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
thread moved to DB2 forum

041807, 05:05 #5Registered User
 Join Date
 Jun 2006
 Posts
 471
you have to convert integer to date to get yyyymmdd
eg
select date(
substr(char(col1),1,4) ''substr(char(col1),5,2) ''substr(char(col1),7,2) ) from tt;
for both columns. dates can be compared/substracted... any operation. see online info center and date comparisonBest Regards, Guy Przytula
DB2 UDB LUW certified V6/7/8

041807, 06:13 #6Registered User
 Join Date
 Dec 2005
 Posts
 273
To get the year out of the numeric value you might use the formula
TRUNC(DATE1/10000,0)
the month you get with
TRUNC(MOD(DATE1,10000)/100,0)
and the day you get with
MOD(DATE1,100)
the difference of two dates can be calculated by
difference = ( TRUNC(DATE2/10000,0)  TRUNC(DATE1/10000,0) ) *360 + ( TRUNC(MOD(DATE2,10000)/100,0)  TRUNC(MOD(DATE1,10000)/100,0) ) *30 + ( MOD(DATE2,100)  MOD(DATE1,100) )

041807, 14:34 #7Registered User
 Join Date
 Apr 2007
 Posts
 51
Originally Posted by umayer
unfortunately it doesnt' work. Everything is okay with years and months but
days are not counted properly.
I'd better explain to you how days are counted with 30/360 day count:
each month you count each day as 1 but only up to 30
in february you count each day as 1 and on the 28th (if it's a normal year  365 days) you count 30; if it's a leap year (366 days) you count 28 on the
28th and you count 30 on the 29th.
Your formula doesn't take into account dates with day 31 or 28 or 29.
(end date day 31  beg.date day 30) = 1 which is wrong, it should be = zero
(end date day 5  beg. date day 31) = 26 but it should be 25
I tried to imagine some selections or 'when' clauses that change 31, 29 and
28 (the latter only if it's not a leap year  maybe you can say if 29/2/year doesn't exist) into 30, but I' not very expert at SQL and I'm not able to write
it.
Before knowing that there are differences among SQL's languages depending on the databases used, I asked SQLTEAM.COM for help and someone sent
me this function:
CREATE FUNCTION dbo.fnGiorno360
(
@Date1 INT,
@Date2 INT
)
RETURNS INT
AS
BEGIN
DECLARE @Months INT,
@Days INT,
@dt1 DATETIME,
@dt2 DATETIME
IF @Date1 > @Date2
SELECT @Days = @Date1,
@Date1 = @Date2,
@Date2 = @Days
SELECT @dt1 = CAST(@Date1 AS VARCHAR),
@dt2 = CAST(@Date2 AS VARCHAR),
@Months = DATEDIFF(MONTH, @dt1, @dt2)
IF @Months > 0
SET @Months = @Months  1
SELECT @Days = 30  CASE WHEN DATEPART(DAY, @dt1) > 30 THEN 30 ELSE DATEPART(DAY, @dt1) END,
@Days = @Days + CASE WHEN DATEPART(DAY, @dt2) > 30 THEN 30 ELSE DATEPART(DAY, @dt2) END
RETURN @Days + 30 * @Months
END
Then use it with
select dbo.fnGiorno360(20070331, 20070815)
I think he didnt' think about february.
Of course this function wouldn't work with my database because it's for
SQL Server. Moreover, I myself can't insert functions in my queries because
only programming staff can do that and I'm an accountant.
Is there a hope to solve this problem?
I have another question to make: once I have created a new column by
making operations among db2 columns, how can I select my new column to
make other operations? (When I try the answer is that column xxx is not present in my table).
I hope to hear from you soon and thank you in advance.
Anna  from Verona (Italy)

041907, 02:34 #8Registered User
 Join Date
 Dec 2005
 Posts
 273
try this to get the desired value for day:
CASE
WHEN MOD(DATE1,1000)= 229 THEN 30
WHEN MOD(DATE1,1000)= 228 AND MOD(TRUNC(DATE1/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(DATE1,100),30)
END

041907, 02:54 #9Registered User
 Join Date
 Dec 2005
 Posts
 273
Originally Posted by annamaria
You can use a "nested table" expression.
e.g.:
SELECT (year2  year1)*360 + (month2  month1)*30 + (day2  day1) as difference
FROM
(
SELECT
TRUNC(DATE1/10000,0) as year1 ,
TRUNC(MOD(DATE1,10000)/100,0) as month1 ,
CASE
WHEN MOD(DATE1,1000)= 229 THEN 30
WHEN MOD(DATE1,1000)= 228 AND MOD(TRUNC(DATE1/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(DATE1,100),30)
END as day1,
TRUNC(DATE2/10000,0) as year2 ,
TRUNC(MOD(DATE2,10000)/100,0) as month2 ,
CASE
WHEN MOD(DATE2,1000)= 229 THEN 30
WHEN MOD(DATE2,1000)= 228 AND MOD(TRUNC(DATE2/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(DATE2,100),30)
END as day2
FROM your.table
) correl

041907, 16:07 #10Registered User
 Join Date
 Apr 2007
 Posts
 51
date count
Originally Posted by umayer
With the query I'm making (that would be of no use without this formula) I will turn a threedayspermonth work into a fiveminutespermonth work.
Could you explain to me what calculation is made in the expression
MOD(TRUNC(DATE2/10000,0),4)? It seem to consider leap years but how does it do it? I couldn't find anything in my handbook.
Another question:
Can I only use one nested table or can I nest more tables one into another?
i.e. select from xxxxxx
from ( select xxxxx
from (select xxxx
from (select xxxxx
If that's possible, how should I do it? I tried to but unsuccessfully.
Thank you again and congratulations.
Ciao. Anna

042007, 03:40 #11Registered User
 Join Date
 Dec 2005
 Posts
 273
Originally Posted by annamaria
If the year is divisible by four without remainder, it is a leap year.
The formula to get the year is:
TRUNC(DATE2/10000,0)
if the remainder of the division by four is not zero
MOD(year,4) > 0
then it is no leap year.
now simply combine the two formulas:
if MOD(TRUNC(DATE2/10000,0),4) > 0
then it is no leap year.
Originally Posted by annamaria
eg:
SELECT * FROM
( SELECT * FROM
( SELECT * FROM
( SELECT * FROM SYSIBM.SYSDUMMY1 ) A
) B
) C
you have to use a correlation character after each nested table expressionLast edited by umayer; 042007 at 03:48.

042407, 15:42 #12Registered User
 Join Date
 Apr 2007
 Posts
 51
Mysterious behaviour
I'm having problems with some calculations:
Here is the query:
SELECT
SEC, VER, DES, CURC, CALC, B_DATE, E_DATE, RATE, PER,
GG_CED_C, GG_MAT_C,
CASE WHEN (CALC = 801 OR CALC = 306 OR CALC = 307 OR CALC = 811
OR CALC = 871) THEN (RATE/NR_CED)/GG_CED_S* GG_MAT_S
WHEN CALC = 315 THEN RATE/GG_CED_S* GG_MAT_S
WHEN CALC = 300 OR CALC = 301 OR CALC = 834 OR CALC = 822
THEN RATE / 360 * GG_MAT_C
WHEN CALC = 303 OR CALC = 304 OR CALC = 835
THEN RATE/ 360 * GG_MAT_S
WHEN CALC = 876 THEN ROUND((RATE/ 360 * GG_MAT_S),2)
WHEN CALC = 877 OR CALC = 325 THEN
ROUND(( RATE/ 360 * GG_MAT_S),3)
WHEN CALC = 878 THEN ROUND((RATE/ 360 * GG_MAT_S),5)
END AS RATEO
FROM (
SELECT SEC, VER, DES, CURC, CALC, B_DATE, E_DATE, RATE, PER,
GG_CED_S, GG_MAT_S,NR_CED
(YEAR2  YEAR1)*360 + (MONTH2  MONTH1)*30 + (DAY2  DAY1)AS GG_CED_C,
(YEARRIL  YEAR1)*360 + (MONTHRIL  MONTH1)*30 + (DAYRIL  DAY1)AS GG_MAT_C,
FROM (
SELECT
A.SEC, A.VER, A.DES, CURC, CALC, B_DATE, E_DATE, RATE,
PER,
TRUNC(B_DATE/10000,0) AS YEAR1 ,
TRUNC(MOD(B_DATE,10000)/100,0) AS MONTH1 ,
CASE
WHEN MOD(B_DATE,1000)= 229 THEN 30
WHEN MOD(B_DATE,1000)= 228
AND MOD(TRUNC(B_DATE/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(B_DATE,100),30)
END AS DAY1,
TRUNC(E_DATE/10000,0) AS YEAR2 ,
TRUNC(MOD(E_DATE,10000)/100,0) AS MONTH2 ,
CASE
WHEN MOD(E_DATE,1000)= 229 THEN 30
WHEN MOD(E_DATE,1000)= 228
AND MOD(TRUNC(E_DATE/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(E_DATE,100),30)
END AS DAY2,
TRUNC(&DRIL/10000,0) AS YEARRIL ,
TRUNC(MOD(&DRIL,10000)/100,0) AS MONTHRIL ,
CASE
WHEN MOD(&DRIL,1000)= 229 THEN 30
WHEN MOD(&DRIL,1000)= 228
AND MOD(TRUNC(&DRIL/10000,0),4) > 0 THEN 30
ELSE MIN(MOD(&DRIL,100),30)
END AS DAYRIL,DAYS(CHAR((SUBSTR(CHAR(E_DATE),3,4)) !! '' !! (SUBSTR(CHAR(E_DATE),7,2)) !! '' !!
(SUBSTR(CHAR(E_DATE),9,2)))) 
DAYS(CHAR((SUBSTR(CHAR(B_DATE),3,4)) !! '' !! (SUBSTR(CHAR(B_DATE),7,2)) !! '' !!
(SUBSTR(CHAR(B_DATE),9,2)))) AS GG_CED_S,
DAYS(CHAR(&DATA)) 
DAYS(CHAR((SUBSTR(CHAR(B_DATE),3,4)) !! '' !! (SUBSTR(CHAR(B_DATE),7,2)) !! '' !!
(SUBSTR(CHAR(B_DATE),9,2)))) AS GG_MAT_S,
CASE WHEN PER = 'T' THEN 4 WHEN PER = 'S' THEN 2 WHEN PER =
'A' THEN 1 END AS NR_CED
FROM TABLEONE A, TABLETWO C
the formulas that create columns with date differences are perfect. The day
count is always correct.
Problems rise when I make the calculation written in blue: if I calculate
RATE/ 360 * GG_MAT_S
or RATE/ GG_CED_C*GG_MAT_C
everything works perfectly.
But when I calculate RATE/360*GG_CED_C it sometimes calculate it and
sometimes the related columns are returned empty.
I can't find anything that distinguish records that are calculated from records
that are not. GG_MAT_C with low values seem to be always calculated but
for examples there are two records with the same dates and in one case the calculation is made and in the other it isn't.
What's the problem? 360 and GG_CED_C doesn't get along together!
Now that I'm writing I'm wondering if maybe I could solve the problem by
creating a new column calculating RATEO/360 AS RAT_360 and then
calculate RAT_360 * GG_MAT_C_. What do you think?
In any case now that I've seen what's happened with this query, I wonder
if SQL IS APT TO MAKE CALCULATIONS (???) After all its duty is to query data and not to make calculations among them.
I'd like to know from you experts if I can consider SQL reliable when it comes
to calculations, because in this case it was easy to spot the failure, but
it might not be so in other cases.
Thank you for your attention.
Anna  Verona (Italy)

042407, 16:39 #13Registered User
 Join Date
 Jan 2007
 Location
 Jena, Germany
 Posts
 2,721
Originally Posted by annamaria
Btw, could you do regular date arithmetics or are you fixed on the 30 days/month rule? Regular date arithmetics would be much, much simpler to write down. For your stuff, I'd also recommend that you write some SQLbodied functions that encapsulate something like calculating the difference between two dates.
But when I calculate RATE/360*GG_CED_C it sometimes calculate it and
sometimes the related columns are returned empty.
I can't find anything that distinguish records that are calculated from records
that are not.
GG_MAT_C with low values seem to be always calculated but
for examples there are two records with the same dates and in one case the calculation is made and in the other it isn't.
What's the problem? 360 and GG_CED_C doesn't get along together!
Also, you should really try to reduce your query to the bare minimum that the problem still occurs but all unnecessary stuff is removed. (Something like 5 lines of SQL code should be sufficient in this case.) Then dig further into the details to understand why things don't work as you hope.
In any case now that I've seen what's happened with this query, I wonder if SQL IS APT TO MAKE CALCULATIONS (???)
I'd like to know from you experts if I can consider SQL reliable when it comes
to calculations, because in this case it was easy to spot the failure, but
it might not be so in other cases.
(Your call to decide whether I fall in the "expect category.)Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development

042407, 17:15 #14Registered User
 Join Date
 Apr 2007
 Posts
 51
Originally Posted by stolze
The calculations of date differences are correct. Of course I can have
get a negative month or days count, but it's okay. For example:
01/05/2007  (minus) 01/03/2008: it's true that I have a negative month
count. But before counting the month I've made the difference between
years, which is (2008  2007) * 360: I have counted a whole year from
which I have to subtract two months (the difference between the two date
is 10 months i.e. less than one year.
Day difference columns are perfect! And they were created by making the
calculations you find in the query.
So, now I have column GG_CED_C that was created by using the formula in
red. There are numbers in it, and they are successfully used to made
calculations, in other cases. But when I use 360 (a number I type) in the
calculation with GG_CED_C it can give the right result or it can give no result (if I use 360 with GG_CED_S everything is okay).
Take this example:
20061229 20070630 3,995 180 90 0,99875
20061229 20070630 4,185 180 90
The dates are identical, day differences are identical, but in the first record
I have my count (RATEO), in the second there is nothing.
I reduce my query. For example I took off the first (from the beginning of
the query) 'when clause' and simply told SQL to calculate every record with
/360*GG_CED_C and the result was the same: some records are calculated
and when they are, they are calculated correctly, and some don't.
Now what I think is that since GG_CED_C is created using 360 in the formula,
then the programm gets crazy when it finds another 360. But what I don't
understand is why it behaves in different ways. With some records it makes
the calculation, for others it doesn't.
With 'spotting the failure' I meant that I could realize that there was
something wrong. Not that I understood WHAT was wrong.
Of course I think that you are an expert. The fact is that in my post I put
a very long query and I knew that it would be difficult to find the patience
to read it carefully.
As for Valentino Rossi, of course he had problems with his tyres, but I was
referring to the mistake he made in the bend a few minutes after starting.
I don't think it was due to tyres. It was his fault.
By the way, who's your favourite rider? And what's your favourite motorcycle
brand?
Could you recommend me some good SQL book? Not in German!
Thank you again.
Anna

042507, 05:58 #15Registered User
 Join Date
 Jan 2007
 Location
 Jena, Germany
 Posts
 2,721
Originally Posted by annamaria
So, now I have column GG_CED_C that was created by using the formula in
red. There are numbers in it, and they are successfully used to made
calculations, in other cases. But when I use 360 (a number I type) in the
calculation with GG_CED_C it can give the right result or it can give no result (if I use 360 with GG_CED_S everything is okay).
Take this example:
20061229 20070630 3,995 180 90 0,99875
20061229 20070630 4,185 180 90
The dates are identical, day differences are identical, but in the first record
I have my count (RATEO), in the second there is nothing.
I reduce my query. For example I took off the first (from the beginning of
the query) 'when clause' and simply told SQL to calculate every record with
/360*GG_CED_C and the result was the same: some records are calculated
and when they are, they are calculated correctly, and some don't.
Now what I think is that since GG_CED_C is created using 360 in the formula,
then the programm gets crazy when it finds another 360. But what I don't
understand is why it behaves in different ways. With some records it makes
the calculation, for others it doesn't.Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development