I have this SELECT
statement:
SELECT SUM(dbo.DLData.Quantity)
FROM dbo.MasterDates
JOIN dbo.DLData ON MasterDates.[ID] = dbo.DLData.DownloadID
WHERE dbo.MasterDates.[Date] BETWEEN @From AND @To
AND dbo.MasterDates.SiteID = @X
If you plug in the @From
, @To
and @X
it works. This is to be the right hand side of a comparison. However in the comparison the @X
needs to come from the left hand side of the as the SELECT
on the outside is performing test on every SiteID
in the set. Without this clause the main clause works. They need to go together. Here is the full select statement with the above select left in. Eventually the two having clauses will determine the key products for a site.
--INSERT INTO SiteKeyProducts
SELECT dbo.MasterDates.SiteID as SiteID , dbo.DLData.Product as ProductID
FROM dbo.MasterDates
JOIN dbo.DLData ON dbo.MasterDates.[ID] = dbo.DLData.DownloadID
WHERE dbo.MasterDates.[Date] BETWEEN @From AND @To
GROUP BY dbo.MasterDates.SiteID , dbo.DLData.Product
HAVING
SUM(dbo.DLData.Quantity) > 960 OR
SUM(dbo.DLData.Quantity) > (SELECT SUM(dbo.DLData.Quantity)
FROM dbo.MasterDates
JOIN dbo.DLData ON MasterDates.[ID] = dbo.DLData.DownloadID
WHERE dbo.MasterDates.[Date] BETWEEN @From AND @To
AND dbo.MasterDates.SiteID = @X)
ORDER BY dbo.MasterDates.SiteID
How do you use the outer clause on an inner statement? Hell, I don't even know if I am using the correct terminology to describe my problem.
Arrgghh - Set based logic does my head in!!