How to join two table with aggrouped date value?

[arriel96]

A have two tables anda i want to relation this two tables by nember of events in a hour, i  manage to make a SQL query,  but struggle to do in splank. I send the data of this 2 tables for two diferent indexes (simple copy) and want to make this:

WITH count_reserved as (
    SELECT  count (ru.id) reserved,
        to_char(ru.date,'yyyy-mm-dd hh24') as time
    FROM  reserved ru
    GROUP   BY to_char(ru.date,'yyyy-mm-dd hh24')   

),
count_concluid as (
    SELECT  count (u.id) as concluid,
        to_char(u.date,'yyyy-mm-dd hh24') as time
    FROM  concluid u

    GROUP   BY to_char(u.date,'yyyy-mm-dd hh24')    
)
SELECT  coalesce(concluid,0) as concluid,
    reserved,
    count_reserved.time,
    ((coalesce(concluid::decimal,0)/reserved)*100) as percentage
FROM  count_reserved
    
    LEFT JOIN 
    count_concluid
    ON count_concluid.time=count_reserved.time
ORDER BY 3 ASC

the information that a want to return is the percentage value and the time to make a graph hour bar

[yuanliu]

Have you consulted Splunk SPL for SQL users? What is the sample code attempted?  If you want to get concrete help, you will also need to illustrate the data in your index, illustrate the result you want (and not make people reverse engineer from some SQL code), and illustrate the result from your sample code, and compare to your desired result.

[arriel96 · Accepted solution]

Sorry for the SQL, i have my data in Relational database and for B.I. query i want to use splunk so i'm importing all the data from tables in the relational database from splunk (1 table= become 1 index). So i'm basic strugle the construct more complex query's that use multiple features, in that case i managed to get the answer:

index=reserved_units_new_stock
| eval date=strftime(_time, "%y-%m-%d %H")
| STATS COUNT AS count_reserved_units BY date  
| JOIN type=left date 

  [SEARCH index=units_new_stock
    | eval date=strftime(_time, "%y-%m-%d %H")
    | STATS COUNT AS count_units1 BY date]
|eval count_units=coalesce(count_units1,0)
|eval porcentagem=round((count_units/count_reserved_units)*100,2)
| FIELDS count_units,count_reserved_units, date, porcentagem

[yuanliu]

Does this mean the question is answered?  In general, using join should be the last resort.  But if it works for you, you do not have to change, not while you are learning to use SPL.

[arriel96]

Yes it's resolve , yanks for the attention