Loading ...
Sorry, an error occurred while loading the content.
 

Creating a table of dates out of thin air

Expand Messages
  • Ruffin Bailey
    Occasionally I d like to take a certain joined table structure and right outer join it (joined itself to, eg, perhaps a table of targetted or active clients)
    Message 1 of 5 , Jan 7, 2004
      Occasionally I'd like to take a certain joined table structure and right
      outer join it (joined itself to, eg, perhaps a table of targetted or
      active clients) to, say, all the days in a user selected month. I'd
      like to keep that all on the server and not do it programmatically in a
      DataSet or the like (thus the SQLNoCode question).

      I'd like to keep this as crossplatform as I can, so ANSI SQL would be
      best if there's an easy way. Basically I'm just looking to see if
      there's a way to do it without messing with DATEADD type functions that
      might be rdbms/implementation specific, which is probably [nearly]
      impossible [without doing some nasty ASCII gymnastics], and then if that
      is impossible, dig up the easiest way to get it done. All I can think
      of is bullheading it in a sproc that checks a month parameter to
      determine how many DATEADDs it should run (the 1st plus 29 DATEADDs hath
      September...) -- or checks the "month" attribute of the date until it
      changes -- etc.

      Thanks!

      Ruffin Bailey
    • Jeff Burgess
      Perhaps you could get let the client calculate first & last dates of a month (9/1/2004, 9/30/2004), then limit your result set to that date range using the
      Message 2 of 5 , Jan 7, 2004
        Perhaps you could get let the client calculate first & last dates of a month (9/1/2004, 9/30/2004), then limit your result set to that date range using the WHERE clause. I'm not sure what you mean in your first paragraph by involving a join. It would help if you posted the table structures (simplified if necessary) of the two tables you want to join.
        -----Original Message-----
        From: Ruffin Bailey [mailto:rbailey@...]
        Sent: Wednesday, January 07, 2004 10:11 AM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] Creating a table of dates out of thin air

        Occasionally I'd like to take a certain joined table structure and right
        outer join it (joined itself to, eg, perhaps a table of targetted or
        active clients) to, say, all the days in a user selected month.  I'd
        like to keep that all on the server and not do it programmatically in a
        DataSet or the like (thus the SQLNoCode question).

        I'd like to keep this as crossplatform as I can, so ANSI SQL would be
        best if there's an easy way.  Basically I'm just looking to see if
        there's a way to do it without messing with DATEADD type functions that
        might be rdbms/implementation specific, which is probably [nearly]
        impossible [without doing some nasty ASCII gymnastics], and then if that
        is impossible, dig up the easiest way to get it done.  All I can think
        of is bullheading it in a sproc that checks a month parameter to
        determine how many DATEADDs it should run (the 1st plus 29 DATEADDs hath
        September...) -- or checks the "month" attribute of the date until it
        changes -- etc.

        Thanks!

        Ruffin Bailey




        Yahoo! Groups Links

      • Ruffin Bailey
        ... Okay, I see the confusion. Nope, not trying to simply limit with a WHERE. Rather, I d like to have rows returned for every date in a month even if there
        Message 3 of 5 , Jan 8, 2004
          > From: "Jeff Burgess" <jeffburgess1975@...>
          > Subject: RE: Creating a table of dates out of thin air
          >
          > Perhaps you could get let the client calculate first & last dates of a month
          > (9/1/2004, 9/30/2004), then limit your result set to that date range using
          > the WHERE clause. I'm not sure what you mean in your first paragraph by
          > involving a join. It would help if you posted the table structures
          > (simplified if necessary) of the two tables you want to join.

          Okay, I see the confusion. Nope, not trying to simply limit with a
          WHERE. Rather, I'd like to have rows returned for every date in a month
          even if there are no values in table for that date.

          So, simplest case, say we have...
          CREATE TABLE [birdWatch] (
          [id] [bigint] IDENTITY (1, 1) NOT NULL ,
          [day] [datetime] NULL ,
          [birdsSeen] [int] NULL
          ) ON [PRIMARY]
          GO

          Now let's say the user isn't entering anything if they didn't see any
          birds a particular day. So not only is there no entry with birdsSeen >
          0, there's not even a row with birdsSeen = 0 -- or even birdsSeen IS NULL.

          So if we...

          SELECT DISTINCT([day]) FROM birdWatch WHERE [day] >= '12/01/2003' AND
          [day] <= '12/31/2003'

          ... we won't get back every day in the month; just those that have entries.

          Now if I'd like to fill a DataSet in .NET (just as a purely hypothetical
          example), but I'd like to have a row for each date in the month,
          regardless of whether there's an entry, what should I do [to get that
          done entirely on the rdbms]?

          Of course a best case would be if I could do something like...

          SELECT *
          FROM
          birdWatch bw
          LEFT OUTER JOIN
          (SELECT [day]
          FROM tableWithEveryDayInTheYear
          WHERE [day] >= '12/01/2003' AND [day] <= '12/31/2003'
          ) AS dayTable
          ON bw.[day] = dayTable.[day]

          (Prolly smarter to move the WHERE below for legibility, but you get the
          idea.)

          But rather than have a silly table of every date in every year (ha) like
          tableWithEveryDayInTheYear, I was trying to think of a clever way to
          "drum up" a temp table with all the days I wanted, give or take,
          regardless of range. What I'd *really* like is someone to say, "Ah, but
          you haven't heard of ANSI SQL's 'FAUX_DATE_TABLE' command; that does
          exactly what you want without the overhead!" but I think that's pretty
          obviously a pipe dream. ;^)

          So I was wondering if someone could weigh in on their preferred method
          to get that happening -- Have to use a sproc? Course you could use
          string manipulation functions, but that's just plain silly. DateAdd and
          variables in a sproc seem the best way, but that's a lot of overhead for
          what's really a pretty common task in my experience. Handling in client
          app logic, however, is not the best way imo, thus the post here.

          Thanks for the reply,

          Ruffin Bailey
        • hansve2000
          Usual trick is to create a 1 column Numbers table with integers from 0 to the largest number of days you will use in range. Then outer join to that table. As
          Message 4 of 5 , Jan 8, 2004
            Usual trick is to create a 1 column "Numbers" table with integers
            from 0 to the largest number of days you will use in range. Then
            outer join to that table.

            As your example is SQL Server, here is T-SQL example:

            DECLARE @Start datetime, @End datetime
            SET @Start = '20031201'
            SET @End = '20031231'

            CREATE TABLE #Numbers(Number smallint)
            DECLARE @i smallint
            SET @i = 0
            SET NOCOUNT ON
            WHILE @i <= DATEDIFF(d,@Start,@End) BEGIN
            INSERT #Numbers VALUES(@i)
            SET @i = @i + 1 END

            SELECT DATEADD(d,n.Number,@Start) [Day],
            SUM(COALESCE(b.BirdsSeen,0)) SeenThatDay
            FROM #Numbers n LEFT JOIN BirdWatch b
            ON DATEADD(d,n.Number,@Start) = b.[Day]
            --WHERE n.Number <= DATEDIFF(d,@Start,@End)
            GROUP BY n.Number

            Will be problem as generic code because date functions differ so
            between SQL extensions. Include commented line if you create Numbers
            table as permanent not temporary.

            Hans.

            --- In SQLQueriesNoCode@yahoogroups.com, Ruffin Bailey
            <rbailey@r...> wrote:
            > > From: "Jeff Burgess" <jeffburgess1975@y...>
            > > Subject: RE: Creating a table of dates out of thin air
            > >
            > > Perhaps you could get let the client calculate first & last
            dates of a month
            > > (9/1/2004, 9/30/2004), then limit your result set to that date
            range using
            > > the WHERE clause. I'm not sure what you mean in your first
            paragraph by
            > > involving a join. It would help if you posted the table
            structures
            > > (simplified if necessary) of the two tables you want to join.
            >
            > Okay, I see the confusion. Nope, not trying to simply limit with
            a
            > WHERE. Rather, I'd like to have rows returned for every date in a
            month
            > even if there are no values in table for that date.
            >
            > So, simplest case, say we have...
            > CREATE TABLE [birdWatch] (
            > [id] [bigint] IDENTITY (1, 1) NOT NULL ,
            > [day] [datetime] NULL ,
            > [birdsSeen] [int] NULL
            > ) ON [PRIMARY]
            > GO
            >
            > Now let's say the user isn't entering anything if they didn't see
            any
            > birds a particular day. So not only is there no entry with
            birdsSeen >
            > 0, there's not even a row with birdsSeen = 0 -- or even birdsSeen
            IS NULL.
            >
            > So if we...
            >
            > SELECT DISTINCT([day]) FROM birdWatch WHERE [day] >= '12/01/2003'
            AND
            > [day] <= '12/31/2003'
            >
            > ... we won't get back every day in the month; just those that have
            entries.
            >
            > Now if I'd like to fill a DataSet in .NET (just as a purely
            hypothetical
            > example), but I'd like to have a row for each date in the month,
            > regardless of whether there's an entry, what should I do [to get
            that
            > done entirely on the rdbms]?
            >
            > Of course a best case would be if I could do something like...
            >
            > SELECT *
            > FROM
            > birdWatch bw
            > LEFT OUTER JOIN
            > (SELECT [day]
            > FROM tableWithEveryDayInTheYear
            > WHERE [day] >= '12/01/2003' AND [day] <= '12/31/2003'
            > ) AS dayTable
            > ON bw.[day] = dayTable.[day]
            >
            > (Prolly smarter to move the WHERE below for legibility, but you
            get the
            > idea.)
            >
            > But rather than have a silly table of every date in every year
            (ha) like
            > tableWithEveryDayInTheYear, I was trying to think of a clever way
            to
            > "drum up" a temp table with all the days I wanted, give or take,
            > regardless of range. What I'd *really* like is someone to
            say, "Ah, but
            > you haven't heard of ANSI SQL's 'FAUX_DATE_TABLE' command; that
            does
            > exactly what you want without the overhead!" but I think that's
            pretty
            > obviously a pipe dream. ;^)
            >
            > So I was wondering if someone could weigh in on their preferred
            method
            > to get that happening -- Have to use a sproc? Course you could
            use
            > string manipulation functions, but that's just plain silly.
            DateAdd and
            > variables in a sproc seem the best way, but that's a lot of
            overhead for
            > what's really a pretty common task in my experience. Handling in
            client
            > app logic, however, is not the best way imo, thus the post here.
            >
            > Thanks for the reply,
            >
            > Ruffin Bailey
          • Ruffin Bailey
            ... Welp, a platform-specific soln is about what I figured (ie, I could use JDBC/Java or ADO[.NET] and friends to do it, which is what I ve been doing, but
            Message 5 of 5 , Jan 9, 2004
              > SELECT DATEADD(d,n.Number,@Start) [Day],
              > SUM(COALESCE(b.BirdsSeen,0)) SeenThatDay
              > FROM #Numbers n LEFT JOIN BirdWatch b
              > ON DATEADD(d,n.Number,@Start) = b.[Day]
              > --WHERE n.Number <= DATEDIFF(d,@Start,@End)
              > GROUP BY n.Number
              >
              > Will be problem as generic code because date functions differ so
              > between SQL extensions. Include commented line if you create Numbers
              > table as permanent not temporary.

              Welp, a platform-specific soln is about what I figured (ie, I could use
              JDBC/Java or ADO[.NET] and friends to do it, which is what I've been
              doing, but that loses the whole rdbms advantage). Oh well.

              But I do like the temp-table of ints/DATEADD number trick. Thanks for
              pointing that out; saves me a lot of trouble finding the most elegant
              solution. Probably still best in a sproc, but in terms of simplest case
              examples, that's money.

              Ruffin Bailey
            Your message has been successfully submitted and would be delivered to recipients shortly.