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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.