Nested SQL problem

Hi,

Although I am quite familiar with MS Access-grade SQL, I am struggling a bit with proper grown up SQL Server. My usual approach to counting things in Access is to first create a query with the conditions on the data, then use this as the basis of a second query that does the actual counting of the presorted data. I believe the way to do this in SQL server is to use a nested query. I want to generate the top 10 highest counts for each pesticide detected (detection is level>0) for a client between two dates.

Currently I am using

<code>

SELECT top 10 Count(Pesticide) AS CountOfPesticide, Pesticide FROM (SELECT tblData.Pesticide, tblData.Level, tblData.Clast, tblData.Client FROM tblData WHERE (((tblData.Day>@.sdate) AND (tblData.Day<@.edate))) and (tbldata.level>0) and (tbldata.clast=@.clast) and (tbldata.client=@.client)) as monkeyboy GROUP BY Pesticide ORDER BY Count(Pesticide) ASC"

</code>

The results that the above SQL turns out though are not reliable. For example, if I set the dates to now and 14 days ago, it produces higher counts for some pesticides then if I set the dates to now and 30 days ago. Any pointers or general advice about nested sql is gratefully accepted!

thanks

Mike

I had to split out your SQL statement so that I could understand the logic:

SELECT TOP 10

   Count(Pesticide) AS CountOfPesticide,

   Pesticide

FROM

   (

      SELECT

         tblData.Pesticide,

         tblData.Level,

         tblData.Clast,

         tblData.Client

      FROM

         tblData

      WHERE

         (

            (

               (tblData.Day>@.sdate) AND

               (tblData.Day<@.edate)

            )

         ) AND

         (tbldata.level>0) AND

         (tbldata.clast=@.clast) AND

         (tbldata.client=@.client)

   ) AS monkeyboy

GROUP BY

   Pesticide

ORDER BY

   Count(Pesticide) ASC

I think it could be simplified to this (which does not change the logicat all but removes extraneous punctuation and columns that are notneeded and only confuse issues) :

SELECT TOP 10

   Count(Pesticide) AS CountOfPesticide,

   Pesticide

FROM

   (

      SELECT

         tblData.Pesticide

      FROM

         tblData

      WHERE

         tblData.Day>@.sdate AND

         tblData.Day<@.edate AND

         tbldata.level>0 AND

        tbldata.clast=@.clast AND

        tbldata.client=@.client

   ) AS monkeyboy

GROUP BY

   Pesticide

ORDER BY

   1 ASC

And unless I am missing something, I think it could be further simplified to this:

SELECT TOP 10

   Count(Pesticide) AS CountOfPesticide,

   Pesticide

FROM

   tblData

WHERE

   tblData.Day>@.sdate AND

   tblData.Day<@.edate AND

   tbldata.level>0 AND

  tbldata.clast=@.clast AND

  tbldata.client=@.client

GROUP BY

   Pesticide

ORDER BY

   1 ASC

The nested query is usually only needed if you need to perform a GROUP BY within in.
If the missing GROUP BY is not the problem, then my next guess thatthere is something unusual going on with the data types. Thequestions I have are:
What is the data type of tblData.Day?|||

Thanks Terri,

This is very helpful and I'll go and play with the new simplified SQL.

The tblData.Day and @.sdate (startdate) and @.edate (enddate) are all dates.

Since I am in the UK, I have had to manually convert the parameter dates to strings in the "MM/DD/YYYY" format so that SQL server behaves itself (I believe it works in US-style date formats or ISO ones only). I have been caught out before with date-related conversion problems and so have taken exeptional care here to make sure I haven't been caught out again.

thanks for your help

Mike

|||Try using YYYYMMDD format instead to avoid those sorts of problems(this format is called ISO). You would still declare @.sdate and@.edate as one of the datetime data types.