how to use the loop filtering date and put in to excel for 1 month

Database
Enthusiast

how to use the loop filtering date and put in to excel for 1 month

Hi , any one could Help me. with this. 

Want to create a loop automatice fetch the info for 25 days. 

 

the Loading data store in the DB in the formate as  2017-03-05 21:13:20.340000

 

I use this for the single date

SyntaxEditor Code Snippet

sel count(1) as N_record,  error_desc
from xxxx.xxxx
where SUBSTR(LOAD_DTTM,1,10) = '2017-03-05' 
group by error_desc
order by N_record

 I want ot have a loop for the date. let this single script run 25 times from 2017-03-05   to 2017-03-30

 

How can I do that any suggestion?

 

Thank you

 

 

 

  • date calculation
  • excel
  • loop
4 REPLIES
Enthusiast

Re: how to use the loop filtering date and put in to excel for 1 month

One more thing, I want to out put each one with a date tag to the excel formate which I can then store in a excel doc. 

Junior Contributor

Re: how to use the loop filtering date and put in to excel for 1 month

Why looping? Simply add the date to GROUP BY.

 

What's the datatype LOAD_DTTM? Can't be a timestamp because the SUBSTR would fail.

 

 

 

Enthusiast

Re: how to use the loop filtering date and put in to excel for 1 month

The LOAD_DTTM is time stam. and the SQL works

Yes. group by works. 

 

but could you please teach me how to use the loop? then I can use that in the other situation

and how to make date increase ?

 

Thank you!

Junior Contributor

Re: how to use the loop filtering date and put in to excel for 1 month

You can only loop using WHILE/REPEAT/LOOP in a Stored Procedure, but here you better run a single Select:

 

sel count(*) as N_record, error_desc, 
-- SUBSTR(LOAD_DTTM,1,10) as dt -- better avoid the type cast and use
CAST(LOAD_DTTM AS DATE) as dt from xxxx.xxxx where LOAD_DTTM >= timestamp '2017-03-05 00:00:00' and LOAD_DTTM < timestamp '2017-03-31 00:00:00' group by error_desc, dt order by dt, N_record