SQL Runnnig the same Query multiple times

Question

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

Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Apr 21 '15 at 05:02

backtrack · Answer 1 · 2015-04-21T05:04:18.480

4

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

edited Apr 21 '15 at 05:04

answered Apr 21 '15 at 04:43

backtrack

7,996
5
52
99

What if date is not available in table but show 0 against that date? – Jankya Apr 21 '15 at 04:45
meaning the date filed is not available .. ? – backtrack Apr 21 '15 at 04:47
*If the field is datetime then use the convert* If it matters, SQL Server 2005 does not have `DATE` as a type. – ta.speot.is Apr 21 '15 at 04:47
@Backtrack. No. Means date not available in table from specified range. Say From Date A to Date C, Date B is not available in table but wants to show Count 0 against Date B. – Jankya Apr 21 '15 at 04:50
In that case add a additional if condition or Isnull – backtrack Apr 21 '15 at 04:51
Note there will be syntax errors. Please rectify it.Let us know if you need any help – backtrack Apr 21 '15 at 05:07
@Backtrack Hey, Thanks a ton! It solves the problem but I doubt that will work for me. I am working on SQL client. I do not have write access. Cant use a PL/SQL block. – Neha Apr 21 '15 at 06:57

score 1 · Answer 2 · answered Apr 21 '15 at 13:57

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.

SQL Runnnig the same Query multiple times

2 Answers2