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 ASCThe 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.