sundrejser

asked on

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?

Last Comment

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.

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.

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))) as cpc

(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))) 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

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

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

TRUSTED BY

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.