Filling Sparse Data In An SQL Query

August 7, 2006 10:33 AM

Filling Sparse Data In An SQL Query

Let us say I have an sql query that returns data similar to


date count
----------------
3/1 1
3/5 2
3/7 4


I know that if I were using data from two tables, I could make the results "dense" by doing an outer join. But there's no real table to join on for the date range, and I'm a bit baffled at this point. Searching for answers brought me to a series of Oracle articles which recommend doing a 'partition', which don't exist on SQL Server 2000. What can I do to make the results look like this?



date count
----------------
3/1 1
3/2 0
3/3 0
3/4 0
3/5 2
3/6 0
3/7 4

posted by boo_radley to Computers & Internet (5 answers total)

Best answer: "I could make the results 'dense' by doing an outer join. But there's no real table to join on

Do an outer join.

No table? Make a table. I always keep a dummy table around just for this.

create table ArtificialRange( id int not null primary key auto_increment, name varchar( 255 ) null ) ;
-- or whatever your database requires for an auto increment column
-- make sure the first id is zero, not one as is the default; different db platform require different techniques to do this

insert into ArtificialRange ( name ) values ( null )
-- create one row.

insert into ArtificialRange ( name ) select name from ArtificialRange ;
-- you now have two rows

insert into ArtificialRange ( name ) select name from ArtificialRange ;
-- you now have four rows

insert into ArtificialRange ( name ) select name from ArtificialRange ;
-- you now have eight rows

--etc.

insert into ArtificialRange ( name ) select name from ArtificialRange ;
-- you now have 1024 rows, with ids 0-1023

create view DaysOfThisYear as select dateadd( day, date( datepart( year, getdate() ) ), id ) as julianDate from ArtificialRange where id < 366
-- natural ordering will suffice here for any sane db platform
-- this assumes sybase-style dateadd and getdate and datepart functions; I'm too lazy to check the SQL Server 2000 equivalents until somebody pays cash

Now join with DaysOfThisYear as the outer table. Use a where clase to "where year( DaysOfThisYear.juliandate ) = year ( getdate() )" (year is a MySQL function, agian, check the equivalent for your platform) or "where datepart( month, DaysOfThisYear.juliandate ) = 3 and datepart( dayofmonth, DaysOfThisYear.juliandate ) <= 7" for your example.
posted by orthogonality at 11:18 AM on August 7, 2006 [1 favorite]

Response by poster: Excellent. Thank you. I made one small change to adjust for SQL Server, which I record here for posterity:

create view daysOfThisYear as SELECT DATEADD(DAY, id, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) AS julianDate
FROM dbo.ArtificialRange
WHERE (id < 366)/code>
posted by boo_radley at 12:44 PM on August 7, 2006

Yeah, sorry, I haven't used sybase in a while, and was doing it off-the-cuff. (MS SQL Server is basically Sybase's T-SQL, so I figured that was the closest match for you without my having to screw with Microsoft's nasty documentation.)
posted by orthogonality at 12:58 PM on August 7, 2006

Response by poster: No need to apologize; I wasn't taking you to task for any reason, just adding a little note for the boo radley of the future.
posted by boo_radley at 1:09 PM on August 7, 2006

Oh, I know, and you did right by providing corrected SQL the next guy looking here can use. I'm just tiresomely pedantic.
posted by orthogonality at 10:54 PM on August 7, 2006