This wretched SQL has me stumped!

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Edward
    #1

    This wretched SQL has me stumped!

    SCENARIO:

    SQL Server 2000 Back End.

    The customer calls in with a problem and a QUERY is raised. The date
    of creation of the record is in the fldDateQuery column.

    The user responds to the customer and a RESPONSE is raised. It
    contains the ID of the associated QUERY and the date of creation of
    the RESPONSE record is in the fldDateResponse column.

    The user wishes to know how many queries in the last month were
    responded to on the same day.

    CANDIDATE SQL:

    SELECT COUNT(*) AS NumResponses
    FROM tblQuery
    INNER JOIN tblResponses ON tblQuery.fldQue ryID =
    tblResponses.fl dQueryID
    WHERE (tblQuery.fldDa teQuery < CONVERT(DATETIM E,'2003-9-8 00:00:00',
    102))
    AND (tblQuery.fldDa teQuery > CONVERT(DATETIM E,'2003-8-8 00:00:00',
    102))
    AND (DATEDIFF(Day, tblQuery.fldDat eQuery,
    tblResponses.fl dDateResponse) <= 1)

    PROBLEM:

    If a query has more than one response raised on it within a day of the
    query being logged, it counts all those responses. In other words,
    the SQL counts the number of matching RESPONSES, and not the number of
    QUERIES.

    TIA

    Edward
    Tags: None
    • Shervin Shapourian
      #2
      Re: This wretched SQL has me stumped!

      Edward,

      Use COUNT(DISTINCT tblResponses.fl dQueryID) instead of COUNT(*)

      Shervin


      "Edward" <teddysnips@hot mail.com> wrote in message
      news:25080b60.0 310080640.2033c 6b@posting.goog le.com...[color=blue]
      > SCENARIO:
      >
      > SQL Server 2000 Back End.
      >
      > The customer calls in with a problem and a QUERY is raised. The date
      > of creation of the record is in the fldDateQuery column.
      >
      > The user responds to the customer and a RESPONSE is raised. It
      > contains the ID of the associated QUERY and the date of creation of
      > the RESPONSE record is in the fldDateResponse column.
      >
      > The user wishes to know how many queries in the last month were
      > responded to on the same day.
      >
      > CANDIDATE SQL:
      >
      > SELECT COUNT(*) AS NumResponses
      > FROM tblQuery
      > INNER JOIN tblResponses ON tblQuery.fldQue ryID =
      > tblResponses.fl dQueryID
      > WHERE (tblQuery.fldDa teQuery < CONVERT(DATETIM E,'2003-9-8 00:00:00',
      > 102))
      > AND (tblQuery.fldDa teQuery > CONVERT(DATETIM E,'2003-8-8 00:00:00',
      > 102))
      > AND (DATEDIFF(Day, tblQuery.fldDat eQuery,
      > tblResponses.fl dDateResponse) <= 1)
      >
      > PROBLEM:
      >
      > If a query has more than one response raised on it within a day of the
      > query being logged, it counts all those responses. In other words,
      > the SQL counts the number of matching RESPONSES, and not the number of
      > QUERIES.
      >
      > TIA
      >
      > Edward[/color]


        Comment

        • John Bell
          #3
          Re: This wretched SQL has me stumped!

          Hi

          I can't check this out, but try counting fldDateResponse formatted to a day
          (i.e. use CONVERT).

          e.g COUNT( CONVERT(char(8) ,fldDateRespons e,112) )

          John

          "Edward" <teddysnips@hot mail.com> wrote in message
          news:25080b60.0 310080640.2033c 6b@posting.goog le.com...[color=blue]
          > SCENARIO:
          >
          > SQL Server 2000 Back End.
          >
          > The customer calls in with a problem and a QUERY is raised. The date
          > of creation of the record is in the fldDateQuery column.
          >
          > The user responds to the customer and a RESPONSE is raised. It
          > contains the ID of the associated QUERY and the date of creation of
          > the RESPONSE record is in the fldDateResponse column.
          >
          > The user wishes to know how many queries in the last month were
          > responded to on the same day.
          >
          > CANDIDATE SQL:
          >
          > SELECT COUNT(*) AS NumResponses
          > FROM tblQuery
          > INNER JOIN tblResponses ON tblQuery.fldQue ryID =
          > tblResponses.fl dQueryID
          > WHERE (tblQuery.fldDa teQuery < CONVERT(DATETIM E,'2003-9-8 00:00:00',
          > 102))
          > AND (tblQuery.fldDa teQuery > CONVERT(DATETIM E,'2003-8-8 00:00:00',
          > 102))
          > AND (DATEDIFF(Day, tblQuery.fldDat eQuery,
          > tblResponses.fl dDateResponse) <= 1)
          >
          > PROBLEM:
          >
          > If a query has more than one response raised on it within a day of the
          > query being logged, it counts all those responses. In other words,
          > the SQL counts the number of matching RESPONSES, and not the number of
          > QUERIES.
          >
          > TIA
          >
          > Edward[/color]


            Comment

            • Rich P
              #4
              Re: This wretched SQL has me stumped!

              Try this sql in Sql Server Query Analyzer:

              Select Count(t3.Querie s) From
              (
              SELECT COUNT(t2.fldQue ryID) AS Queries
              FROM tblQuery t1
              INNER JOIN tblResponses t2 ON t1.fldQueryID =
              t2.fldQueryID
              WHERE (t1.fldDateQuer y Between '8/8/03' And '9/8/03')
              AND (DATEDIFF(Day, t1.fldDateQuery ,
              t2.fldDateRespo nse) <= 1)
              Group By t2.fldQueryID Having Count(t2.fldQue ryID)>1)
              ) t3

              This should give you the count of just the queries and not the count of
              responses. Like say query1 has 3 responses for a day that would be a
              count of 1

              Count(t2.fldQue ryID)>1

              Query 2 has only one response for a day, no grouping - wont show up,
              query3 has 2 responses for a day, that would be a count of 1. So Query1
              and Query3 are counted. That would be a count of 2 queries. Note: I
              only set this up for one day. Haven't tried it for multiple day. Just
              to give you some ideas. Oh yeah, I think it would be simpler if you
              used the Between statement instead of Convert, well based on what I saw
              in your original query, looks like Between would work (maybe other
              criteria to consider, if not - try between).

              Rich

              *** Sent via Developersdex http://www.developersdex.com ***
              Don't just participate in USENET...get rewarded for it!

                Comment

                • Edward
                  #5
                  Re: This wretched SQL has me stumped!

                  "Shervin Shapourian" <ShShapourian@h otmail.com> wrote in message news:<vo8gshad7 mlvab@corp.supe rnews.com>...[color=blue]
                  > Edward,
                  >
                  > Use COUNT(DISTINCT tblResponses.fl dQueryID) instead of COUNT(*)
                  >
                  > Shervin
                  >
                  >
                  > "Edward" <teddysnips@hot mail.com> wrote in message
                  > news:25080b60.0 310080640.2033c 6b@posting.goog le.com...[color=green]
                  > > SCENARIO:
                  > >
                  > > SQL Server 2000 Back End.
                  > >
                  > > The customer calls in with a problem and a QUERY is raised. The date
                  > > of creation of the record is in the fldDateQuery column.
                  > >
                  > > The user responds to the customer and a RESPONSE is raised. It
                  > > contains the ID of the associated QUERY and the date of creation of
                  > > the RESPONSE record is in the fldDateResponse column.
                  > >
                  > > The user wishes to know how many queries in the last month were
                  > > responded to on the same day.
                  > >
                  > > CANDIDATE SQL:
                  > >
                  > > SELECT COUNT(*) AS NumResponses
                  > > FROM tblQuery
                  > > INNER JOIN tblResponses ON tblQuery.fldQue ryID =
                  > > tblResponses.fl dQueryID
                  > > WHERE (tblQuery.fldDa teQuery < CONVERT(DATETIM E,'2003-9-8 00:00:00',
                  > > 102))
                  > > AND (tblQuery.fldDa teQuery > CONVERT(DATETIM E,'2003-8-8 00:00:00',
                  > > 102))
                  > > AND (DATEDIFF(Day, tblQuery.fldDat eQuery,
                  > > tblResponses.fl dDateResponse) <= 1)
                  > >
                  > > PROBLEM:
                  > >
                  > > If a query has more than one response raised on it within a day of the
                  > > query being logged, it counts all those responses. In other words,
                  > > the SQL counts the number of matching RESPONSES, and not the number of
                  > > QUERIES.
                  > >
                  > > TIA
                  > >
                  > > Edward[/color][/color]

                  This one worked! Note, however, that because fldQueryID is a SQL
                  Server Uniqueidentifie r field it had to be cast thus:

                  SELECT COUNT( DISTINCT (CONVERT(varcha r(38),
                  tblResponses.fl dQueryID))) AS NumResponses

                  Many, many thanks to the other people who took the trouble to respond.

                  Edward

                    Comment

                    Previous template Next
                    Working...