SQL Server 2005 - Return record for each type, for each site

I have a very large table containing aggregate data about leads coming from our clients sites, by date and by lead type. Data looks like this:

LeadType	CalendarDay		fkSites	LeadCount
New Vehicles	2007-02-10 00:00:00	1363	1
Used Vehicles	2008-02-10 00:00:00	3594	3
Contests	2008-09-15 00:00:00	1962	1
Quick Contact	2008-11-18 00:00:00	3061	4
Quick Contact	2007-07-04 00:00:00	2086	1
Financing 	2008-04-18 00:00:00	2328	1
Service		2008-09-16 00:00:00	4131	1
Financing 	2010-02-02 00:00:00	983	1
Employment	2009-02-02 00:00:00	3700	1
Parts		2006-07-23 00:00:00	1724	1

I’m pulling this information into a report interface which sums the count data for each site and lead type, and ends up looking like this:

siteid	leadType		cnt
1363	New Vehicles		37
1363	Preowned Vehicles	29
1363	Special Financing 	10
1363	Financing 		7
1363	Quick Contact		7
1363	VP Company		1

I now want to add in additional dealerships to the report and still have it grouped by siteid and leadtype. Here’s the catch though; so that the report can compare apples to apples, I need to return ALL leadtypes for ANY of the provided sites, giving a 0 if a site doesn’t have leads in that category. So what I want is this:

siteid	leadType		cnt
1363	New Vehicles		37
1363	Preowned Vehicles	29
1363	Special Financing 	10
1363	Financing 		7
1363	Quick Contact		7
1363	VP Company		1
2086	New Vehicles		14
2086	Preowned Vehicles	23
2086	Special Financing 	0
2086	Financing 		0
2086	Quick Contact		14
2086	VP Company		0

Anyone have an idea of what the SQL might look like?

i think it was the change in the GROUP BY that fixed the totals

:slight_smile:

Okay…

I’m so close I can taste it…here’s what I’ve got now.

SELECT	s.displayname
		,allTypes.leadtype
--		,lt.Calendarday
--		,SUM(lt.LeadCount)
FROM DealerskinsStatistics.dbo.[fn_splitList](@SiteList) sl
CROSS JOIN (
	SELECT DISTINCT leadtype
	FROM DealerskinsStatistics.dbo.StatsDFormsLeadType lt
	INNER JOIN DealerskinsStatistics.dbo.[fn_splitList](@SiteList) sl on sl.element = lt.fkSites
	WHERE (CalendarDay >= @DateTimeStart AND CalendarDay <= @DateTimeEnd)
) AS allTypes
INNER JOIN [DealerskinsDB1].dealerskinsversion2.dbo.Sites S WITH (NOLOCK) ON S.ID = sl.element
INNER JOIN DealerskinsStatistics.dbo.StatsDFormsLeadType lt ON lt.leadtype = allTypes.leadtype
WHERE (lt.CalendarDay >= @DateTimeStart AND CalendarDay <= @DateTimeEnd)
GROUP BY s.displayname, allTypes.leadtype--, lt.Calendarday, lt.LeadCount
ORDER BY s.displayname, allTypes.leadtype--, lt.Calendarday, lt.LeadCount

Which returns this

displayname		leadtype
Dealership One		Financing 
Dealership One		New Vehicles
Dealership One		Preowned Vehicles
Dealership One		Quick Contact
Dealership One		Special Financing 
Dealership One		VP Company
Dealership Two		Financing 
Dealership Two		New Vehicles
Dealership Two		Preowned Vehicles
Dealership Two		Quick Contact
Dealership Two		Special Financing 
Dealership Two		VP Company
Dealership Three	Financing 
Dealership Three	New Vehicles
Dealership Three	Preowned Vehicles
Dealership Three	Quick Contact
Dealership Three	Special Financing 
Dealership Three	VP Company

But when I uncomment the SUM expression I get the following counts:

displayname		leadtype		cnt
Dealership One		Financing		1947
Dealership One		New Vehicles		2852
Dealership One		Preowned Vehicles	4950
Dealership One		Quick Contact		1677
Dealership One		Special Financing	1644
Dealership One		VP Company		18
Dealership Two		Financing		1947
Dealership Two		New Vehicles		2852
Dealership Two		Preowned Vehicles	4950
Dealership Two		Quick Contact		1677
Dealership Two		Special Financing	1644
Dealership Two		VP Company		18
Dealership Three	Financing		1947
Dealership Three	New Vehicles		2852
Dealership Three	Preowned Vehicles	4950
Dealership Three	Quick Contact		1677
Dealership Three	Special Financing	1644
Dealership Three	VP Company		18

I think I’m like 90% there, but I’m not sure if I’ve got the grouping wrong, or I don’t have the right columns added or anything. Ideas?

SELECT s.fkSites
     , l.LeadType
     , COUNT(t.fkSites) AS cnt
  FROM ( SELECT DISTINCT fkSites
           FROM daTable ) AS s
CROSS
  JOIN ( SELECT DISTINCT LeadType
           FROM daTable ) AS l
LEFT OUTER
  JOIN daTable AS t
    ON t.fkSites  = s.fkSites
   AND t.LeadType = l.LeadType
GROUP
    BY s.fkSites
     , t.LeadType

I was told that Cross Join gave you all the records from t1 times the recrods from t2 which is exactly what I wanted. I actually solved it myself, and this was the final query:

SELECT	s.displayname as sitename,
		allTypes.leadtype
		,COALESCE(
			(
				SELECT SUM(lt.LeadCount) as cnt
				FROM DealerskinsStatistics.dbo.StatsDFormsLeadType lt
				WHERE lt.fkSites = sl.element
				AND (lt.CalendarDay >= @DateTimeStart
				AND lt.CalendarDay <= @DateTimeEnd)
				AND lt.leadtype = allTypes.leadtype
			)
			,0
		) AS cnt
FROM DealerskinsStatistics.dbo.[fn_splitList](@SiteList) sl
CROSS JOIN (
	SELECT DISTINCT leadtype
	FROM DealerskinsStatistics.dbo.StatsDFormsLeadType lt
	INNER JOIN DealerskinsStatistics.dbo.[fn_splitList](@SiteList) sl on sl.element = lt.fkSites
	WHERE (CalendarDay >= @DateTimeStart AND CalendarDay <= @DateTimeEnd)
) AS allTypes
INNER JOIN [DealerskinsDB1].dealerskinsversion2.dbo.Sites S WITH (NOLOCK) ON S.ID = sl.element
INNER JOIN DealerskinsStatistics.dbo.StatsDFormsLeadType lt ON lt.leadtype = allTypes.leadtype
WHERE (lt.CalendarDay >= @DateTimeStart AND CalendarDay <= @DateTimeEnd)
GROUP BY s.id, s.displayname, allTypes.leadtype, sl.element
ORDER BY s.displayname, allTypes.leadtype

the function isn’t how i would do it, but that can’t be what’s causing the duplication of numbers

my cross join idea was to be used with a LEFT OUTER JOIN

why are you using CROSS JOIN?

Sorry Rudy…hate being one of those guys who doesn’t provide enough information.

The function returns a temp table containing x number of rows, where x == the number of sites passed in. In this case, I’m passing in 3 sites.

The final resultset is wrong because it appears to be summing ALL leadtypes for all sites, for each set of site and lead type.

you’ve got a query
which returns some results
and the uncommented SUM produces some more results
and you think you’re 90% there

what are we supposed to give you ideas on? we don’t know if those results are okay or not!!

by the way, why are you using a function as a table? how many rows does it produce?

try adding in the date range with AND instead of WHERE

That’s closer than what I came up with. I think the problem is that I also need a date range in there. When I add the date range in the WHERE clause, the results drop from 20ish down to 7. I think that it’s because the report types aren’t contained within the date range. I wish that whomever architected this table would have split out the lead types.

Cest la vie.