need sql rewite to get better accuracy and avoid cursor

I have the following sql server/sybase and oracle statements that count the number of days from a hard coded date without entries.  The problem with the hard coded start date is that some stations may not have come online before 1/1/2005, thus causing a misrepresentation of missing days.  example: I don't want to add 180 missing days to my count if a station comes online mid year 2005

 I need to rewrite it (without using a cursor) for use in SQL Server/Sybase and Oracle to cycle through each station_id, get it's datediff between (today-7 days) and min(date_reported), subtract the total # of each station from that total, then sum all those total days up for a grand total of missing days over all stations.
-- SQL Server/Sybase
SELECT COUNT(DISTINCT station_id) * Datediff(dd, '2005-01-01', Dateadd(dd, -7, Getdate())) - COUNT(1) as missing
  FROM station_data d
 WHERE d.date_reported > '2005-01-01'
   AND d.date_reported <= Dateadd(DAY, -7, Getdate()) 

--Oracle
SELECT COUNT(DISTINCT station_id) * ROUND(SYSDATE – 7 – TO_DATE('2005-01-01',’yyyy-mm-dd’)) - COUNT(1) as missing
  FROM station_data d
 WHERE d.date_reported > '2005-01-01'
   AND d.date_reported <= SYSDATE - 7

Open in new window

STLOracleDudeAsked:
Who is Participating?
 
cyberkiwiCommented:
Hi there,

I have used two different approaches for Oracle and SQL, I suspect the SQL one can use the Oracle approach quite easily. It only clicked that there is only one report (max) per date, having looked at the query closer - by the fact that the date_reported is not part of the select portion.
-- SQL Server / Sybase
select sum(missing) from (
select missing =
	datediff(dd, min(date_reported)-1, dateadd(dd, -7, getdate())) -
	count(distinct date_reported)
from station_data
where date_reported <= dateadd(dd, -7, getdate())
group by station_id) innerquery


-- Oracle P/SQL
SELECT COUNT(DISTINCT station_id) * ROUND(SYSDATE – 7 – Min(date_reported)) - COUNT(1) as missing
  FROM station_data d
 WHERE d.date_reported <= SYSDATE - 7

Open in new window

0
 
wilcoxonCommented:
For Sybase, this should work.
SELECT station_id, max(min(date_reported), '2005-01-01') min_date, count(*) cnt
  INTO #tmp
  FROM station_data d
 WHERE d.date_reported > '2005-01-01'
   AND d.date_reported <= Dateadd(DAY, -7, Getdate()) 
 GROUP BY station_id

select sum(Datediff(dd, min_date, Dateadd(dd, -7, Getdate()))-cnt) missing
from #tmp

Open in new window

0
 
STLOracleDudeAuthor Commented:
the SQL Server approach worked perfectly.  The logic behind the Oracle version causes an overcount since all the stations do not have the same min(date_reported).

0
 
cyberkiwiCommented:
Try this for Oracle. Other than the way to diff dates and SYSDATE vs getDate(), the SQL is Ansi-92 compliant so quite portable
select sum(missing) from (
select missing =
        SYSDATE -7 - (min(date_reported)-1) - count(distinct date_reported)
from station_data
where date_reported <= SYSDATE -7
group by station_id) innerquery

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.