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?
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?
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
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?
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?
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.
By last month i mean during the last 30 days.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
forgot the comma:
select programId, cpc from
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
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
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
(Impressions.cpa + Clicks.cpa + Leads.cpa + Sales.cpa) = TOTALCPA for each program
and then TOTALCPA/Select Count(*) from CLICKS
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(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
(
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
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
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())
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
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))
(convert(float,sum(cpa)) / convert(float,sum(clicks))
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))
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
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))
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
ASKER
There you go.... Now it works exactly as it should.... Thank you so much.... You just saved my day :)
Glad to help! thx
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.