Good afternoon all. I'm going to post the stored procedure in it's entire glory. Feel free to rip it to shreds. The author won't mind.
DECLARE @itemTypeID INT
SELECT @itemTypeID=ItemTypeID FROM dbo.ItemTypes WHERE ItemTypeName = 'Advert'
BEGIN
SELECT a.Active,
a.ParentClass,
a.Classification,
a.Variant,
FV."Full Views",
PV."Print Views",
EE."Email Enquiries",
a.ItemRef,
a.SiteID
FROM
(
SELECT DISTINCT i.ItemID,
i.ItemRef,
i.SiteID,
i.ParentClass,
i.Classification,
i.Summary AS "Variant",
i.Active
FROM Items i
JOIN Actions a
ON a.ItemID = i.ItemID
JOIN ActionTypes at
ON a.ActionTypeID = at.ActionTypeID
WHERE i.ItemTypeID = 1
AND a.DateAndTime BETWEEN @startDate AND @endDate
AND at.ActionTypeName IN ('Full view', 'Print view', 'Email enquiry')
AND ((@siteID = -1) OR (i.SiteID = @siteID))
AND ((@parentClass = '%') OR (i.ParentClass = @parentClass))
AND ((@class = '%') OR (i.classification = @class))
) a LEFT JOIN
(
SELECT i.ItemID,
COUNT(*) AS "Full Views"
FROM CustomerSites cs JOIN Items i
ON cs.SiteID = i.SiteID
JOIN Actions a
ON a.ItemID = i.ItemID
JOIN ActionTypes at
ON a.ActionTypeID = at.ActionTypeID
JOIN Sites s
ON cs.SiteID = s.SiteID
WHERE a.DateAndTime BETWEEN @startDate AND @endDate
AND i.ItemTypeID = @itemTypeID
AND at.ActionTypeName = 'Full view'
AND ((@customerID IS NULL) OR (cs.CustomerID = @customerID))
AND ((@siteID = -1) OR (cs.SiteID = @siteID))
AND ((@parentClass = '%') OR (i.ParentClass = @parentClass))
AND ((@class = '%') OR (i.classification = @class))
GROUP BY
i.ItemID
) FV
ON a.ItemID = FV.ItemID
LEFT JOIN
(
SELECT i.ItemID,
COUNT(*) AS "Print Views"
FROM CustomerSites cs JOIN Items i
ON cs.SiteID = i.SiteID
JOIN Actions a
ON a.ItemID = i.ItemID
JOIN ActionTypes at
ON a.ActionTypeID = at.ActionTypeID
JOIN Sites s
ON cs.SiteID = s.SiteID
WHERE a.DateAndTime BETWEEN @startDate AND @endDate
AND i.ItemTypeID = @itemTypeID
AND at.ActionTypeName = 'Print view'
AND ((@customerID IS NULL) OR (cs.CustomerID = @customerID))
AND ((@siteID = -1) OR (cs.SiteID = @siteID))
AND ((@parentClass = '%') OR (i.ParentClass = @parentClass))
AND ((@class = '%') OR (i.classification = @class))
GROUP BY
i.ItemID
) PV
ON a.ItemID = PV.ItemID
LEFT JOIN
(
SELECT i.ItemID,
COUNT(*) AS "Email Enquiries"
FROM CustomerSites cs JOIN Items i
ON cs.SiteID = i.SiteID
JOIN Actions a
ON a.ItemID = i.ItemID
JOIN ActionTypes at
ON a.ActionTypeID = at.ActionTypeID
JOIN Sites s
ON cs.SiteID = s.SiteID
WHERE a.DateAndTime BETWEEN @startDate AND @endDate
AND i.ItemTypeID = @itemTypeID
AND at.ActionTypeName = 'Email enquiry'
AND ((@customerID IS NULL) OR (cs.CustomerID = @customerID))
AND ((@siteID = -1) OR (cs.SiteID = @siteID))
AND ((@parentClass = '%') OR (i.ParentClass = @parentClass))
AND ((@class = '%') OR (i.classification = @class))
GROUP BY
i.ItemID
) EE
ON a.ItemID = EE.ItemID
UNION
SELECT '0','','','','','','','',''
Now ultimately all this does is return some records and the number of times a particular action has occured against them.
A small subset would look like.
Item Description Views Printed Emails
Item1 Desc1 12 NULL 1
Item2 Desc2 NULL NULL 3
Item3 Desc3 5 6 2
Hopefully you can see what's going on.
I want a list of items who have had actions against them for a particular date range for a particular customer for a particular site and the query should be filterable on parent class and classification. Nice
The first select returns all distinct items that fall within the selection criteria.
The other 3 queries all simply returning counts of 1 type of action against each item. The query is pants slow even against a small amount of data. This is never going to go live it just won't work.
Hopefully you can see the error of the 'authors' ways and correct him/her.