Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: More SQL Help (97 SR2)

    Reporting information that is NOT there is always a challenge. The only thing I can think of is to create a Dates table that includes the dates you want, and then use an Outer Join to link it to your regular table, based on date. I can remember if this is a right or left outer join, but essentially you want every date from the Dates table, plus any matching records from your regular table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More SQL Help (97 SR2)

    Alright, so i've got a nice little SQL statement:
    <pre>SELECT Format(tblmain.DateStarted,"dddd") AS Day,
    Count(tblmain.DateStarted) AS Entered,
    Abs(Sum([Status]="Complete")) AS Complete,
    Abs(Sum([Status]="Pending")) AS Pending
    FROM tblmain
    GROUP BY tblmain.DateStarted
    HAVING (((tblmain.DateStarted) Between Date() And Date()-7));
    </pre>


    The problem with this is, if for any reason nothing was entered on a particular day, the resulting query skips right over it. Jumping for example, from monday to wednesday if nothing was entered on Tuesday. This adds irregularity to my report. Is there a way for me to represent that if nothing was done on Tuesday, then the values for tuesday would be Zero?

    Thanks!

    Edited to eliminate horizontal scrolling--Charlotte
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More SQL Help (97 SR2)

    I agree with Mark's suggestion, I think it's the correct way to do it.

    You'll want an left outer join from the dates table to the existing table. Something like:-

    <pre>SELECT
    Format(tblmain.DateStarted,"dddd") AS Day,
    Count(tblmain.DateStarted) AS Entered,
    Abs(Sum([Status]="Complete")) AS Complete,
    Abs(Sum([Status]="Pending")) AS Pending
    FROM
    tblDates
    LEFT JOIN
    tblMain
    ON
    tblDates.DateWanted=tblMain.DateStarted
    GROUP BY
    tblmain.DateStarted
    HAVING
    (((tblmain.DateStarted) Between Date() And Date()-7));
    </pre>


    N.B. in the above example your dates table will be called 'tblDates' with a field named 'DateWanted' containing the days you want to run your report for...

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: More SQL Help (97 SR2)

    >>You'll want an left outer join from the dates table to the existing table. <<

    One of these days I'll pull out Joe Celko's book again, and brush up on outer joins. I always get these confused!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More SQL Help (97 SR2)

    Do I want to manually populate the dates field with every concievable date for the next 5 years or so? or is there a better way? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks for all your help!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More SQL Help (97 SR2)

    The better way is to write code that will create the dates for you, one day at a time, for the desired number of years.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •