CODEWITHSUNDEEP
sqlverticasqlclientvsql
Neha
Neha

Reputation: 233

SQL Runnnig the same Query multiple times

I have a query that gives me the COUNT between two dates, start_date & end_date and is grouped by various columns. Is there any way I can get the COUNT for each day ? As in say start_date is date1 and end_date is date5, so I need to run the query once for 'date1 to date2', the 'date1 to date3', then 'date1 to date4', then 'date1 to date5'. As in running the same query multiple times based on the start & the end dates ? My query looks like 

Select COUNT(A), B, C, D
FROM TABLE 
WHERE CONDITION1 AND DATE BETWEEN start_date AND end_date
GROUP BY B, C, D

Upvotes: 5

Views: 4247

Answers (2)

Mark Leiber
Mark Leiber

Reputation: 3138

I'll show how I achieve this with Oracle and then how the same can possibly be applied to Vertica.

I start by writing a query to get a list of dates. Like this:

select to_date('01-APR-2015','dd-mon-yyyy') + rownum -1 as begin_date
from all_objects
where rownum <= to_date('05-APR-2015','dd-mon-yyyy') - to_date('01-APR-2015','dd-mon-yyyy')+1;

This returns:

01-APR-15 12:00:00 AM
02-APR-15 12:00:00 AM
03-APR-15 12:00:00 AM
04-APR-15 12:00:00 AM
05-APR-15 12:00:00 AM

I'm not too familiar with Vertica, but it looks like this can be achieved with this query:

SELECT ts::DATE
  FROM (SELECT '04/01/2015'::TIMESTAMP as tm
        UNION
        SELECT '04/05/2015'::TIMESTAMP as tm) as t
TIMESERIES ts as '1 Day' OVER (ORDER BY tm);

(Source: http://www.vertica-forums.com/viewtopic.php?t=1333)

I then use a cartesian/cross join to the same query to create date ranges:

select *
from (
    select to_date('01-APR-2015','dd-mon-yyyy') + rownum -1 as begin_date
    from all_objects
    where rownum <= to_date('05-APR-2015','dd-mon-yyyy')-to_date('01-APR-2015','dd-mon-yyyy')+1
) q1, (
    select to_date('01-APR-2015','dd-mon-yyyy') + rownum -1 as end_date
    from all_objects
    where rownum <= to_date('05-APR-2015','dd-mon-yyyy')-to_date('01-APR-2015','dd-mon-yyyy')+1
) q2
where begin_date <= end_date;

The results look like this:

BEGIN_DATE              END_DATE
01-APR-15 12:00:00 AM   01-APR-15 12:00:00 AM
01-APR-15 12:00:00 AM   02-APR-15 12:00:00 AM
01-APR-15 12:00:00 AM   03-APR-15 12:00:00 AM
01-APR-15 12:00:00 AM   04-APR-15 12:00:00 AM
01-APR-15 12:00:00 AM   05-APR-15 12:00:00 AM
02-APR-15 12:00:00 AM   02-APR-15 12:00:00 AM
02-APR-15 12:00:00 AM   03-APR-15 12:00:00 AM
02-APR-15 12:00:00 AM   04-APR-15 12:00:00 AM
02-APR-15 12:00:00 AM   05-APR-15 12:00:00 AM
03-APR-15 12:00:00 AM   03-APR-15 12:00:00 AM
03-APR-15 12:00:00 AM   04-APR-15 12:00:00 AM
03-APR-15 12:00:00 AM   05-APR-15 12:00:00 AM
04-APR-15 12:00:00 AM   04-APR-15 12:00:00 AM
04-APR-15 12:00:00 AM   05-APR-15 12:00:00 AM
05-APR-15 12:00:00 AM   05-APR-15 12:00:00 AM

If you don't want single day ranges (e.g., 4/1/2015 - 4/1/2015) just change begin_date <= end_date to begin_date < end_date.

Once you have that, you can join the entire query to the query you're running:

Select q.begin_date, q.end_date, t.B, t.C, t.D, count(t.A)
FROM tmp t, (
    select *
    from (
        select to_date('01-APR-2015','dd-mon-yyyy') + rownum -1 as begin_date
        from all_objects
        where rownum <= to_date('05-APR-2015','dd-mon-yyyy')-to_date('01-APR-2015','dd-mon-yyyy')+1
    ) q1, (
        select to_date('01-APR-2015','dd-mon-yyyy') + rownum -1 as end_date
        from all_objects
        where rownum <= to_date('05-APR-2015','dd-mon-yyyy')-to_date('01-APR-2015','dd-mon-yyyy')+1
    ) q2
    where begin_date <= end_date 
) q
where t.theDate between q.begin_date and q.end_date
group by q.begin_date, q.end_date, t.B, t.C, t.D
order by q.begin_date, q.end_date;

Here's a SQLFiddle: http://sqlfiddle.com/#!4/9628d/9

I hope that helps.

Upvotes: 1

backtrack
backtrack

Reputation: 8154

Select COUNT(A), B, C, D,DATE 
FROM TABLE 
WHERE CONDITION1 AND DATE BETWEEN start_date AND end_date
GROUP BY B, C, D,DATE

Add DATE in group by

If the field is datetime then use the convert ( from @ta.speot.is : SQL Server 2005 does not have DATE as a type)

Select COUNT(A), B, C, D,convert(date,DATETime) 
    FROM TABLE 
    WHERE CONDITION1 AND DATE BETWEEN start_date AND end_date
    GROUP BY B, C, D,convert(date,DATETime)

Add DATE in group by

Edited based on the OP : 

declare increment int;
set increment  = 1
declare tempdate date;

set tempdate  = start_date  
while (tempdate  < end_date)


Select COUNT(A), B, C, D,DATE 
    FROM TABLE 
    WHERE CONDITION1 AND DATE BETWEEN start_date AND DATEADD(day,increment,  start_date )
    GROUP BY B, C, D,DATE 

set increment = increment   + 1
set tempdate   =  DATEADD(day,1,tempdate )
end

you have to use the loop and do some thing like above

Upvotes: 4

Related Questions