Avatar of sundrejser
sundrejser

asked on 

Calculation made from different SQL Tables

I am working on an affiliate system...

The SQL DB contains 4 tables conaining actions made in the system: "Impressions", "Clicks", "Leads", Sales - each contain the columns named "ProgramId" and "CPA"...

I need a Stored Procedure, which returns the best performing programs for a specified period measured by CPC - The sum of CPA in the four action tables/number of clicks grouped by programId.

Any ideas of how to make this?
Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
knightEknight
Avatar of knightEknight
knightEknight
Flag of United States of America image

Hi sundrejser,

Your question is a little vague.  Please show us exactly what the tables look like (names, column names and types), and perhaps a sample of the data and the desired output.
Avatar of sundrejser
sundrejser

ASKER

OK... The structure of the tables looks like this (the "clicks" table):

clickId int (key)
programId int
adId int
affiliateId int
clientIp varchar
clickDateTime DateTime
cpa decimal

The structure is the same for the other 3 tables (Impressions, Leads, Sales)

So what I need is a function, that gets the cpc.... Something like (Impressions.cpa + Clicks.cpa + Leads.cpa + Sales.cpa)/Selec Count (*) FROM Clicks GROUP BY clicks.programId... The result should return the cpc values for the last month
ok, good ... please clarify a few more things:

by Count (*)  do you mean a count of the clicks table, or a count of all rows in all four tables?

also, by "last month" do you mean the last 30 days from the current date, or do you mean the first of the previous month thru the last day of the previous month?
Avatar of sundrejser
sundrejser

ASKER

By count i mean the number of rows in the clicks table grouped by "programId" - each row represents an ad click.

By last month i mean during the last 30 days.
ASKER CERTIFIED SOLUTION
Avatar of knightEknight
knightEknight
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
forgot the comma:

select programId, cpc from
oops, sorry - i goofed on the date too.

select programId, cpc from
(
 select programId, sum(cpa)/count(*) as cpc
 from Impressions(nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa)/count(*) as cpc
 from Clicks (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa)/count(*) as cpc
 from Leads (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa)/count(*) as cpc
 from Sales (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

) tmp
Avatar of sundrejser
sundrejser

ASKER

Think it's close.... But i need the total CPA number:
(Impressions.cpa + Clicks.cpa + Leads.cpa + Sales.cpa) = TOTALCPA for each program
and then TOTALCPA/Select Count(*) from CLICKS
Avatar of sundrejser
sundrejser

ASKER

Sorry, if i don't make myself very clear.... I'm not English, so there might be a little language problems
of course, it was clear, I just got in a hurry because I had a meeting ...

select programId, sum(cpc) as cpc  from
select programId, sum(cpa) as cpa, sum(cpc) as cpc  from
(
 select programId, sum(cpa) as cpa, sum(cpa)/count(*) as cpc
 from Impressions(nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, sum(cpa)/count(*) as cpc
 from Clicks (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, sum(cpa)/count(*) as cpc
 from Leads (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, sum(cpa)/count(*) as cpc
 from Sales (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

) tmp

DOH!  sorry, I have no environment to test this code.


select programId, sum(cpa) as cpa, sum(cpc)/count(*) as cpc  from
(
 select programId, sum(cpa) as cpa, sum(cpa)/count(*) as cpc
 from Impressions(nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, sum(cpa)/count(*) as cpc
 from Clicks (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, sum(cpa)/count(*) as cpc
 from Leads (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, sum(cpa)/count(*) as cpc
 from Sales (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

) tmp
group by programId


Avatar of sundrejser
sundrejser

ASKER

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ea_GetTop5CPC]
AS
select programId, sum(cpa) as cpa from
(
 select programId, sum(cpa) as cpa
 from ea_Impressions(nolock)
 where impressionDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa
 from ea_Clicks (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa
 from ea_Leads (nolock)
 where leadDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa
 from ea_Sales (nolock)
 where saleDateTime >= dateadd(day,-30,getdate())
 group by programId


) tmp group by programId


This one gives me the right sum of the CPA for each program - now I just need to divide this number with the number of clicks for each program for the period - SELECT Count(*) as clicks FROM ea_clicks WHERE clickDateTime >= dateadd(day,-30,getdate())
how about this:



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ea_GetTop5CPC]
AS
select programId, sum(cpa) as cpa, sum(cpa)/sum(clicks) as cpc   from
(
 select programId, sum(cpa) as cpa, count(*) as clicks
 from ea_Impressions(nolock)
 where impressionDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, count(*) as clicks
 from ea_Clicks (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, count(*) as clicks
 from ea_Leads (nolock)
 where leadDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, count(*) as clicks
 from ea_Sales (nolock)
 where saleDateTime >= dateadd(day,-30,getdate())
 group by programId


) tmp group by programId
Also, you may want to convert the CPC to a float for greater precision:

(convert(float,sum(cpa)) / convert(float,sum(clicks))) as cpc
Avatar of sundrejser
sundrejser

ASKER

It doesn't work because it returns the count of rows in ea_impressions, ea_clicks, ea_leads and ea_sales - it should only divide by the number of rows for each program in ea_clicks
oooh, I mis-understood that.  ok, a minor change -- just force the click count in the other tables to be zero:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ea_GetTop5CPC]
AS
select programId, sum(cpa) as cpa, (convert(float,sum(cpa)) / convert(float,sum(clicks))) as cpc
from
(
 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Impressions(nolock)
 where impressionDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, count(*) as clicks
 from ea_Clicks (nolock)
 where clickDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Leads (nolock)
 where leadDateTime >= dateadd(day,-30,getdate())
 group by programId

union

 select programId, sum(cpa) as cpa, 0 as clicks
 from ea_Sales (nolock)
 where saleDateTime >= dateadd(day,-30,getdate())
 group by programId


) tmp group by programId

Avatar of sundrejser
sundrejser

ASKER

There you go.... Now it works exactly as it should.... Thank you so much.... You just saved my day :)
Glad to help!  thx
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo