| Count non workink days between two dates, from calendar table [message #689123] |
Wed, 27 September 2023 10:32  |
 |
Nicha
Messages: 20
Registered: March 2020
|
Junior Member |
|
|
I have a customer table (Table1) that has a column with the creation date.
[Created_Date] : Cliente creation date.
and
[Num_days] : number of days (integer)
I also have a calendar table (table_calendar) with 2 columns:
[ref_date] : calendar days
[civil_util] : If = 1 -> Work day; If = 0 -> weekends and hollydays.
I need to get, for each Table1.[Created_Date], the count of weekends and hollydays between Table1.[Created_Date] and Table1.[Created_Date] + Table1.[Num_days] - guiven possibly by using date_add(Table1.[Created_Date], Table1.[Num_days]).
Can anyone help please? Impala does not permit to use a subquery on select statement, as a field.
My best regards in advance
|
|
|
|
| Re: Count non workink days between two dates, from calendar table [message #689125 is a reply to message #689123] |
Wed, 27 September 2023 11:40  |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From your previous topic:
Michel Cadot wrote on Wed, 16 August 2023 15:24
From your previous topic:
Michel Cadot wrote on Fri, 03 March 2023 17:49
With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements [...].
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
...
And don't forget mathguy's comment to which you replied "I agree with your criticism of my post. My sincere apologies."
Basically the answer is count(*) from calendar table where civil_util=0 and ref_date between Cliente creation date and same thing + number of days - 1.
|
|
|
|
|
|
|
|
| Re: Count non workink days between two dates, from calendar table [message #689139 is a reply to message #689137] |
Thu, 28 September 2023 04:34 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Then post your question on hadoop forums. If it would be Oracle:
SELECT T.CREATED_DATE,
SUM(C.CIVIL_UTIL) WORK_DAYS,
T.NUM_DAYS + 1 - SUM(C.CIVIL_UTIL) NONWORK_DAYS
FROM TABLE_CALENDAR C,
TABLE1 T
WHERE C.REF_DATE BETWEEN T.CREATED_DATE AND T.CREATED_DATE + T.NUM_DAYS
GROUP BY T.ROWID,
T.CREATED_DATE,
T.NUM_DAYS
/
I used ROWID since you didn't provide TABLE1 structure. Based on your post it is customer table. If it has unique customer_id:
SELECT T.CUSTOMER_ID,
T.CREATED_DATE,
SUM(C.CIVIL_UTIL) WORK_DAYS,
T.NUM_DAYS + 1 - SUM(C.CIVIL_UTIL) NONWORK_DAYS
FROM TABLE_CALENDAR C,
TABLE1 T
WHERE C.REF_DATE BETWEEN T.CREATED_DATE AND T.CREATED_DATE + T.NUM_DAYS
GROUP BY T.CUSTOMER_ID,
T.CREATED_DATE,
T.NUM_DAYS
/
SY.
[Updated on: Thu, 28 September 2023 04:42] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
