complex query grouped by dates with inner select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

complex query grouped by dates with inner select

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-28 : 13:58:38

Paul Lower writes "Hi I have 4 tables from which I need to extract the total responses and subsequent orders per campaign between two specified dates and hopefully a further query of per campaign, per day between the given dates. The server is SQL Server 2000. The query is working within a SPROC with the parameter @StartDate SmallDateTile and @EndDate SmallDateTime.

The Campaigns and Affiliates tables are fairly straightforward. The CampaignResponses table contains a log of every ‘response’ with:

ID (int) [PK] [Identity],
CampaignId (int),
ResponseDate (DateTime)

Also the Orders table has CampaignId[int] which is <NULL> if the order was not placed as a result of a campaign.

The following SQL seems to work:

SELECT
c.CampaignId,
c.AffiliateId AS AffiliateId_0,
c.AffiliateId, c.Description,
c.RedirectUrl, c.DateAdded,
c.Name,
COUNT(cr.CampaignId) AS Responses,
(
SELECT
COUNT(id)

FROM
CampaignResponses

WHERE
(CampaignId = c.CampaignId)
AND
(DATEDIFF(hour, ResponseDate, getdate()) <= 24)
) AS CountLatest,

(
SELECT
COUNT(ID)

FROM
Orders

WHERE
(CampaignId = c.CampaignId)
)
AS OrderCount

FROM
Campaigns c LEFT OUTER JOIN
CampaignResponses cr ON c.CampaignId = cr.CampaignId

GROUP BY
c.CampaignId,
c.AffiliateId,
c.Description,
c.RedirectUrl,
c.DateAdded,
c.Name
RETURN

However I can’t seem to get the final query to work. Here is where I have got to. I have been using the Query builder in EM / VS.NET but it is now giving a syntax error from what (to my limited knowledge) looks like it should work:

SELECT
COUNT(CampaignResponses.CampaignId) AS Responses,
Campaigns.Name AS CampaignName,
Affiliates.Name AS Affiliate,
(SELECT COUNT(ID) FROM Orders WHERE (CampaignId = Campaign.CampaignId)) AS OrderCount

FROM
CampaignResponses INNER JOIN
Campaigns ON CampaignResponses.CampaignId = Campaigns.CampaignId INNER JOIN
Affiliates ON Campaigns.AffiliateId = Affiliates.AffiliateId

GROUP BY
CONVERT(smalldatetime, CONVERT(varchar(12),CampaignResponses.ResponseDate, 103), 103),
Campaigns.Name,
Affiliates.Name

HAVING
(CONVERT(smalldatetime, CONVERT(varchar(12), CampaignResponses.ResponseDate, 103), 103) >= @StartDate)
AND
(CONVERT(smalldatetime, CONVERT(varchar(12), CampaignResponses.ResponseDate, 103), 103) <= @EndDate)

ORDER BY
Campaigns.Name,
CONVERT(smalldatetime, CONVERT(varchar(12), CampaignResponses.ResponseDate, 103), 103)

Please, please, please can you help what I’m trying to do goes beyond my understanding of more than a few areas of SQL. Many thanks."

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-28 : 16:31:55

What is the syntax error you're getting?

I don't think you need the inner 103 in your convert expressions.

;-]

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-28 : 17:15:10

Try this.

SELECT
COUNT(cr.CampaignId) AS Responses,
c.Name AS CampaignName,
Affiliates.Name AS Affiliate,
o.OrderCount
FROM
CampaignResponses cr
INNER JOIN Campaigns c ON cr.CampaignId = c.CampaignId
INNER JOIN Affiliates a ON c.AffiliateId = a.AffiliateId
INNER JOIN (
SELECT COUNT(ID) AS OrderCount, CampaignId
FROM Orders
GROUP BY CampaignId) o ON c.CampaignId = o.CampaignId
GROUP BY
CONVERT(smalldatetime, CONVERT(varchar(12),cr.ResponseDate), 103),
c.Name,
a.Name
HAVING
(CONVERT(smalldatetime, CONVERT(varchar(12), cr.ResponseDate), 103) >= @StartDate)
AND (CONVERT(smalldatetime, CONVERT(varchar(12), cr.ResponseDate), 103) <= @EndDate)
ORDER BY
c.Name,
CONVERT(smalldatetime, CONVERT(varchar(12), cr.ResponseDate), 103)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources