I put in the correct field names and tried it out, but unfortunately it's throwing an error

[Macromedia][SQLServer JDBC Driver][SQLServer]The multi-part identifier "imppressions.imp_date" could not be bound.

Here's what I have

    SELECT
imp_date,
imp_aff_uid,
imp_counter,
count(leads.lead_aff_uid)

    FROM impressions JOIN leads ON impressions.imp_date = leads.lead_date and

imp_aff_uid = leads.lead_aff_uid

WHERE imppressions.imp_date BETWEEN #createODBCdate(form_from)# AND

#createODBCdate(form_to)#

GROUP BY impressions.imp_date,impressions.imp_aff_uid

I also need to sort by a particular specified aff_UID, guess I can add that into the WHERE when done

Hey Dan

Any idea why I'm getting the wrong results? I can't seem to get this query right

Thanks

Mark

I'm at a bit of a loss with this, I pulled the whole WHERE statement out, and I also tried playing with teh GROUP varaibles but that just caused it to throw errors.

I double checked the data, and tweaked it to make sure I was not going mad. I have two records in IMPRESSIONS, 09/01 and 09/02 and two in LEADS, 09/03 and 09/04 , it displays no leads at all in this state, now if I change the dates in LEADS to match the dates in IMPRESSIONS, I get two records return from the IMPRESSIONS table.. very strange

Putting it back as it was a CFDUMP is giving this

SELECT imp_date, imp_aff_uid, imp_counter, count(leads.lead_aff_uid)       FROM impressions JOIN leads ON impressions.imp_date = leads.lead_date and imp_aff_uid = leads.lead_aff_uid  WHERE impressions.imp_date BETWEEN {d '2011-01-01'} AND {d '2011-09-08'}  GROUP BY impressions.imp_date,impressions.imp_aff_uid,impressions.imp_counter

And this gives no records instead of the four

I played a little more after posting this and found that what is happening is that if there are no records in the IMPRESSIONS table which have the same date as data that should be displayed in the LEADS table, then the leads data will not display for that date, so if I have a record in LEADS for 09/01 and I then add a record in IMPRESSIONS for 09/01 both display, on one row, but if I take out the data in IMPRESSIONS, then the data in LEADS does not display either

Thanks

Mark

Correct, that is what I am trying to do, although the aff_uid is actually lead_aff_uid.

I tried the query and unfortunately it did not work, I got two records from the IMPRESSIONS table, however the records in the LEADS table were not returned

Mark

Yes, the IMP_AFF_UID and LEAD_AFF_UID from IMPRESSIONS and LEAD table respectively are related back to the users UID

I created the tables manually, I guess I could create a script from the SQL management tool if needed?

this is all I have in the test

IMPRESSIONS table

imp_aff_uid,imp_date,imp_counter

525503,9/1/2011,100

525503,9/3/2011,200

LEADS table

lead_aff_uid,lead_date (also have some other unrelated data in here for this report)

525503,9/3/2011

525503,9/7/2011

525504,9/7/2011

525504,9/7/2011

So in theory I should get:

09/01/2011,100,0

09/03/2011,200,1

09/07/2011,0,3

Thanks

Mark

umm. I thought there used to be a place to attach files...

Here's a creation script for the two tables (indexes are not in place yet)

USE [__a_cb]
GO
/****** Object:  Table [dbo].[impressions]    Script Date: 09/12/2011 19:48:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[impressions](
    [imp_uid] [int] IDENTITY(1,1) NOT NULL,
    [imp_aff_uid] [int] NOT NULL,
    [imp_date] [smalldatetime] NULL,
    [imp_counter] [int] NULL CONSTRAINT [DF_impressions_imp_counter]  DEFAULT ((0)),
CONSTRAINT [PK_impressions] PRIMARY KEY CLUSTERED
(
    [imp_uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[leads]    Script Date: 09/12/2011 19:48:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[leads](
    [lead_uid] [int] IDENTITY(1,1) NOT NULL,
    [lead_aff_uid] [int] NULL,
    [lead_aff_master_uid] [int] NULL,
    [lead_offer_uid] [int] NULL,
    [lead_aff_percentage] [tinyint] NULL,
    [lead_aff_payment] [smallmoney] NULL,
    [lead_advertiser_payment] [smallmoney] NULL,
    [lead_date] [smalldatetime] NULL,
    [lead_pcode_uid] [int] NULL,
CONSTRAINT [PK_leads] PRIMARY KEY CLUSTERED
(
    [lead_uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

sorry, that is correct, there isa possibilty that there would be a date missing in either of the tables, so on a particular date there might be a record for IMPRESSIONS, but not LEADS, or vice versa, there could also be no records on a particular date.

So somehow I have to combine the data and report it all based on the dates that are available with some date in one or both tables

Mark

tried it, but unfortunately get the same results as bob, just the two records from the IMPRESSIONS table. Here's what I used

    SELECT
imp_date,
imp_aff_uid,
imp_counter,
count(leads.lead_aff_uid)

FROM impressions JOIN leads ON imp_aff_uid = leads.lead_aff_uid

WHERE impressions.imp_date BETWEEN #createODBCdate(form_from)# AND #createODBCdate

(form_to)# OR leads.lead_date BETWEEN #createODBCdate(form_from)# AND

#createODBCdate(form_to)#

GROUP BY impressions.imp_date,impressions.imp_aff_uid,impressions.imp_counter

Sorry, I should have pointed out, I'm not using MySQL. I am using MS SQL2005

I'

m not sure I'd be able to disect and redo what you just did !

So

which one are you saying should to it??

Well I thought that I had this one up and running, but once it went into production I found that it didn't give the desired results.

I have an account that has 3 'tracking'/aff accounts, so what it's doing is giving out 3 lines for each date rather than join them together into one. The values are also incorrect, I'm still trying to figure out just where it's even getting those numbers, I manually added up the numbers in the dbase and it didn't match

Here's the command

<CFQUERY name="GetStats" DATASOURCE="#datasource#">

SELECT

COALESCE(IMP.imp_date, LEAD.lead_date) AS report_date

, COALESCE(IMP.imp_counter,0) AS imp_counter

, COALESCE(LEAD.lead_count,0) AS lead_count

, COALESCE(LEAD.lead_aff_payment,0) AS lead_aff_payment

FROM

(

SELECT

imp_date

, imp_counter

FROM impressions

WHERE imp_date BETWEEN #createODBCdate(form_from)# AND #createODBCdate(form_to)#

<CFIF #val(ListGetAt(form_aff_UID,1))# IS "3">

AND imp_aff_uid  IN (SELECT aff_uid FROM aff WHERE aff_master_uid = #session.aff_uid#)

<CFELSEIF #val(ListGetAt(form_aff_UID,1))# IS "2">

AND imp_aff_uid  = #val(ListGetAt(form_aff_UID,2))#

<CFELSE>

AND imp_aff_uid  IN (SELECT aff_uid FROM aff WHERE aff_sub_uid = #val(ListGetAt(form_aff_uid,2))# AND aff_master_uid = #session.aff_uid#)

</CFIF>

) AS IMP

FULL OUTER JOIN

(

SELECT

lead_date, COUNT(*) AS lead_count, SUM(lead_aff_payment) AS lead_aff_payment

FROM leads

WHERE lead_date BETWEEN #createODBCdate(form_from)# AND #createODBCdate(form_to)#

AND lead_data_status = 1

<CFIF #val(ListGetAt(form_aff_UID,1))# IS "3">

AND lead_aff_uid  IN (SELECT aff_uid FROM aff WHERE aff_master_uid = #session.aff_uid#)

<CFELSEIF #ListGetAt(form_aff_UID,1)# IS "2">

AND lead_aff_uid = #val(ListGetAt(form_aff_uid,2))#

<CFELSE>

AND lead_aff_uid  IN (SELECT aff_uid FROM aff WHERE aff_sub_uid = #val(ListGetAt(form_aff_uid,2))# AND aff_master_uid = #session.aff_uid#)

</CFIF>

GROUP BY lead_date

) AS LEAD

ON ( IMP.imp_date = LEAD.lead_date )

ORDER BY report_date;

</CFQUERY>

and here are the results

$15.96 is NOT correct, I need to find out whats going on there, but the other issue is look how it's duplicating the dates, it's one line for each account by the look of it

Can anybody help! This SQL command already got a little beyond me

Thanks

Mark

After posting this I noticed something that was staring me in the face.

If you look at the report that I posted, you will see that because there are 3 accounts that it joined that it is giving the 3 results, causing it to repeat and add up the data 3 times,

The leads on the 3 days would be 1,7,6 as a total, and $0.38, $2,66 and $2.28 = total of $5.32 which would be correct.

If I could stop it repeating like that and just have the one like for all aff accounts it might resolve the issue!

Plus I need to stop full zero lines from appearing

Thanks

Mark