show only the last value per hour

[geogou1973]

Hello,

i have the following script

LOAD COMPANY_CODE,

    CUSTOMERS,

    INVOICES,

    RETAIL,

    DATE,

    HOUR,

    STORE;

SQL SELECT COMPANY_CODE,

    MAX(CUSTOMERS) AS CUSTOMERS,

    MAX(INVOICES) AS INVOICES,

    MAX(RETAIL) AS RETAIL,

    TO_CHAR(TRANS_DATE,'DD/MM/YYYY') AS DATE,

    SUBSTR(TRANS_TIME,1,2) AS HOUR,

    STORE

FROM SALES

WHERE

TO_CHAR(TRANS_DATE,'YYYYMMDD') < TO_CHAR(SYSDATE,'YYYYMMDD') AND

SUBSTR(TRANS_TIME,1,2) BETWEEN '08' AND '21'

GROUP BY COMPANY_CODE,TO_CHAR(TRANS_DATE,'DD/MM/YYYY'), SUBSTR(TRANS_TIME,1,2),STORE;

this brings the max value of CUSTOMERS,INVOICES,RETAIL per COMPANY_CODE,DATE,HOUR (08,09,10 .....) and STORE

the form of the data is like

COMPANY_CODE        DATE          STORE    HOUR    RETAIL    INVOICES   CUSTOMERS

           01                   30/08/2017         101        08:05      1.000           200                10

           01                   30/08/2017         101        08:10      1.200           250                15

           01                   30/08/2017         101        08:15      1.180           250                15

According to the script the data i will have per hour is like

COMPANY_CODE        DATE          STORE    HOUR    RETAIL    INVOICES   CUSTOMERS

           01                   30/08/2017         101           08        1.200            250                15

but is not correct because is not the last record. If you see the RETAIL increased because we had refund.

COMPANY_CODE        DATE          STORE    HOUR    RETAIL    INVOICES   CUSTOMERS

           01                   30/08/2017         101           08        1.180            250                15

Some times we have the same line many times like the following example

COMPANY_CODE        DATE          STORE    HOUR    RETAIL    INVOICES   CUSTOMERS

           01                   30/08/2017         101        08:05      1.000           200                10

           01                   30/08/2017         101        08:10      1.200           250                15

           01                   30/08/2017         101        08:15      1.180           250                15

           01                   30/08/2017         101        08:15      1.180           250                15

           01                   30/08/2017         101        08:15      1.180           250                15

My question is how can i have only the last record per HOUR ?

Thank you in advance.

[Anonymous]

how could you recognize the "last record"?

IS there a unique ID that will be counted up or are the rows in correct order in your database?

[Anil_Babu_Samineni]

For your question you can manage your fields as my script. Note - This script return Maximum hour from the table

Sample:

Load @1, @2, Hour From table

Final:

NoConcatenate

Load Max(Hour) as Hour

Resident Sample

Group By @1, @2 Order By @1;

Drop Table Sample;

[Kushal_Chawda]

try below

LOAD COMPANY_CODE,

    CUSTOMERS,

    INVOICES,

    RETAIL,

    DATE,

    HOUR,

    STORE;

SQL SELECT COMPANY_CODE,

    MAX(CUSTOMERS) AS CUSTOMERS,

    MAX(INVOICES) AS INVOICES,

    MAX(RETAIL) AS RETAIL,

    TO_CHAR(TRANS_DATE,'DD/MM/YYYY') AS DATE,

   max(SUBSTR(TRANS_TIME,1,2)) AS HOUR,

    STORE

FROM SALES

WHERE

TO_CHAR(TRANS_DATE,'YYYYMMDD') < TO_CHAR(SYSDATE,'YYYYMMDD') AND

SUBSTR(TRANS_TIME,1,2) BETWEEN '08' AND '21'

GROUP BY COMPANY_CODE,TO_CHAR(TRANS_DATE,'DD/MM/YYYY'),STORE;

[effinty2112]

Hi Gou,

After loading your data try something like:

DistinctData:

LOAD

DISTINCT

COMPANY_CODE,

    CUSTOMERS,

    INVOICES,

    RETAIL,

    DATE,

    HOUR,

Hour(time#(HOUR,'HH:MM')) as HourOfDay,

    STORE;

From YourTableName;

Drop Table YourTableName;

Inner Join

Load

Max(HOUR) as HOUR,

HourOfDay,

DATE

Resident DistinctData

Group by HourOfDay, DATE;

Good luck

Andrew

[geogou1973]

Hello Andrew

If you load the following data

COMPANY_CODE,DATE,STORE,HOUR,RETAIL,INVOICES,CUSTOMERS

01,30/08/2017,101,08:05,1.000,200,10

01,30/08/2017,101,08:10,1.200,250,15

01,30/08/2017,101,08:15,1.180,250,15

01,30/08/2017,101,08:15,1.180,250,15

01,30/08/2017,101,08:15,1.180,250,15

01,30/08/2017,101,09:05,2.000,300,20

01,30/08/2017,101,09:10,2.200,310,25

01,30/08/2017,101,09:15,2.380,400,30

01,30/08/2017,101,09:25,2.480,350,30

01,30/08/2017,101,09:40,2.400,380,35

01,30/08/2017,101,09:40,2.400,380,35

and you run your script you will have the following results

01	30/08/2017	101	08:10	1.200	250	15	8
01	30/08/2017	101	09:10	2.200	310	25	9

The correct rows that we will expect to have are

01,30/08/2017,101,08:15,1.180,250,15

01,30/08/2017,101,09:40,2.400,380,35

any idea ?

[effinty2112]

Hi Gou,

I found the problem. Run it again after replacing

Hour(time#(HOUR,'HH:MM')) as HourOfDay,

with

Hour(time#(HOUR,'hh:mm')) as HourOfDay,

Result:

COMPANY_CODE	DATE	STORE	HOUR	RETAIL	INVOICES	CUSTOMERS
01	30/08/2017	101	08:15	1.180	250	15
01	30/08/2017	101	09:40	2.400	380	35

Regards

Andrew

[geogou1973]

Andrew it is working.

I will try to apply the code to the master data in order to see the results.

I will keep you inform.

Thank you for your help.

[effinty2112]

Good luck!!

[maxgro]

The correct rows that we will expect to have are

01,30/08/2017,101,08:15,1.180,250,15

01,30/08/2017,101,09:40,2.400,380,35

RESULT

SCRIPT

Test:

load * inline [

COMPANY_CODE,DATE,STORE,HOUR,RETAIL,INVOICES,CUSTOMERS

01,30/08/2017,101,08:05,1.000,200,10

01,30/08/2017,101,08:10,1.200,250,15

01,30/08/2017,101,08:15,1.180,250,15

01,30/08/2017,101,08:15,1.180,250,15

01,30/08/2017,101,08:15,1.180,250,15

01,30/08/2017,101,09:05,2.000,300,20

01,30/08/2017,101,09:10,2.200,310,25

01,30/08/2017,101,09:15,2.380,400,30

01,30/08/2017,101,09:25,2.480,350,30

01,30/08/2017,101,09:40,2.400,380,35

01,30/08/2017,101,09:40,2.400,380,35

];

Test2:

NoConcatenate load

*,

Time(FLOOR(Time#(HOUR, 'hh:mm'), 1/24)) as HOUR_F

RESIDENT Test

WHERE FLOOR(Time#(HOUR, 'hh:mm'), 1/24) <> FLOOR(Time#(Peek('HOUR'), 'hh:mm'), 1/24)

ORDER BY COMPANY_CODE, STORE, DATE, HOUR desc;

DROP TABLE Test;