Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    14

    Unanswered: subqueries-CHALLENGE!

    I am trying to get the number(total) of distinct dates in a report. I have tried the following(along w/ a million other ways):

    Select count (*)
    from (select distinct mbdate from DateTable
    where MBDATE between [parameter passed from form] and [parameter2 from form] and datepart("w", mbdate) = 1.

    I only want to count the number of sundays... also they have to be distinct because i am using this number for an average... distinct b/c there are several items w/ the same date... i only want to count unique dates.
    Thank you for your help.

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    Select count (mbdate) as Sundays
    from (select distinct mbdate from DateTable
    where MBDATE (between [parameter passed from form] and [parameter2 from form]) and (datepart("w", mbdate) = 1));

    should work.
    ghozy.

Posting Permissions

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