Combining data from SQL query

Report · Aug 31, 2011

Hoping there is a SQL guru out there that can help me with this

I have two tables that I want to pull data from and display a report, based on a particular date range, for this example let's say 08/01/2011 to 08/31/2011

In one table there would be an affiliate ID - AFF_UID and also the date and a counter for that date, IMP_COUNTER , now there may only be a few records not every day might be covered, say

TABLE IMP

date,aff_uid,imp_counter

08/01/2011,999,2000

08/02/2011,999,2050

08/20/2011,999,2030

etc

I then have another date, similar set up only there are multiple records in here for a single day, again not all days covered, I would need to total up each row for each day to get a count. So it might be:

TABLE LEAD

date,aff_uid

08/01/2011,999

08/12/2011,999

So we have different dates covered, sometimes the same date, I need the counter from the first table, and from the second table I need to add them up for a total, and then display any date that has a value and put both counter and lead count together

Result from above

date,imp total,lead total

08/01/2011,2000,4

08/02/2011,2050,0

08/12,2011,0,1

08/20/2011,2030,0

I am sure there must be a SQL command that can gel all of this together with some grouping? Last thing I need is a ton of SQL statements in a loop!

My alternative is to add a counter to the IMP table for each lead, so every time I update the lead table with a record, I will also update the counter, but that's unncessary storage of data I already have an an extra update statement that I might be able to do without, but maybe it would make generating reports faster, just pulling to counters with no 'addition' required.

Appreciate any feedback

Thanks

Mark

Report · Aug 31, 2011

select imp.date

, imp.aff_uid

, count(lead.aff_uid)

, counter

from imp join lead on imp.date = lead.date and imp.aff_uid = lead.aff_uid

where imp.date >= somedate

and imp.date < someotherdate

group by imp.date, imp.aff_uid

This is pretty basic stuff. Since you had to ask, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta

Report · Aug 31, 2011

Thanks Dan, I'll give it a shot later on. I've done grouping and joins before but often seem run into problems, it was the ability to join more than one item that got me there, should have known that!

Thanks

Mark

Report · Aug 31, 2011

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

Report · Aug 31, 2011

the typo in the word IMPRESSIONS did not help! just noticed that, correct, but still an error

[Macromedia][SQLServer JDBC Driver][SQLServer]Column 'impressions.imp_counter' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Report · Aug 31, 2011

We both forgot to put that in the group by clause.

Report · Sep 01, 2011

I added it into the group, but it doesn't work, it gives no results for the LEADS, even though there is a record in there that should show, and only lists one record in the IMPRESSIONS table, when there are two, one for the 09/01 and one for 09/02

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 #createODBCdate(form_from)# AND

#createODBCdate(form_to)#

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

CFDUMP showed 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-08-01'} AND {d '2011-09-30'} GROUP BY impressions.imp_date,impressions.imp_aff_uid,impressions.imp_counter

Report · Sep 07, 2011

Hey Dan

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

Thanks

Mark

Report · Sep 08, 2011

When I expect to get data and don't, I try to isolate the cause by either removing constraints one by one, or removing them all and putting them back one by one.

In this case, I'd start by taking away the where clause. If I got results, I'd put the dates back one at a time to see which one caused the problem.

If I didn't get results by taking away the where clause, I'd start looking at the two fields used in the join.

Report · Sep 08, 2011

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

Report · Sep 09, 2011

Let me see if I understand your requirements, please correct the following statements if they are wrong:

1. You wish to query for a given date range defined by form_from and form_to variables.

2. You wish to include every row in the impressions table that matches the date range criteria.

3. You wish to include the count of matching rows from the leads table for each row from the impressions table based on matching aff_uid and date.

You might try something like the query below. This uses a subquery to calculate the count from the leads table based on the aff_uid and date from the impressions row. Note that I have included the date parameters in CFQUERYPARAM tags.

SELECT
    I.imp_date
    , I.imp_aff_uid
    , I.imp_counter
    , (
        SELECT COUNT(*)
        FROM leads AS L
        WHERE L.aff_uid = I.imp_aff_uid
            AND L.lead_date = I.imp_date
        ) AS lead_total

FROM impressions AS I

WHERE I.imp_date BETWEEN <cfqueryparam value="#form_from#" cfsqltype="cf_sql_timestamp"> AND <cfqueryparam value="#form_to#" cfsqltype="cf_sql_timestamp">

ORDER BY I.imp_date, I.imp_aff_uid;

You might also consider posting your question to a database specific form.

Report · Sep 12, 2011

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

Report · Sep 12, 2011

Do the ID and date field values in the LEADS table exactly match the related values in the IMPRESSIONS table?

In order to assist you further it would be useful to have:

1. The creation scripts for your tables.

2. A sample of data for each table.

3. The desired resultsest based on the sample data.

4. The database server you are using: vendor and version.

Report · Sep 12, 2011

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

So in theory I should get:

09/01/2011,100,0

09/03/2011,200,1

09/07/2011,0,3

Thanks

Mark

Report · Sep 12, 2011

Knowing the data types for each column and the database server (vendor and version) would be useful.

Report · Sep 12, 2011

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

Report · Sep 12, 2011

So based on your sample result set I need to assume that not every record will have a row in the IMPRESSIONS table..

You wish to get a resultset containing 3 columns:
1. An uid from IMPRESSIONS (or LEADS if there is no match in the IMPRESSIONS table).
2. A date from IMPRESSIONS (or LEADS if there is no match in the IMPRESSIONS table).
3. The count of records in the LEADS table that math the uid and date.

Is this correct? It contradicts the assumptions I stated on Sept 9 where I assumed that all records in the results would have a record in the IMPRESSIONS table, to quote "You wish to include every row in the impressions table that matches the date range criteria."

Report · Sep 12, 2011

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

Report · Sep 12, 2011

Take the date portion out of the join. It becomes this:

FROM impressions JOIN leads ON imp_aff_uid = leads.lead_aff_uid

Then, in the where clause do something like this

where (impressions.date >= something and impressions.date < something)

or (leads.date >= something and leads.date < something)

Report · Sep 12, 2011

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

Report · Sep 12, 2011

Seems like what you need is a FULL OUTER JOIN. But I do not think they are supported in MySQL. If not, you could simulate it using a UNION.

The first query should grab all impressions, with or without matching leads. The second should retrieve only leads without matching impression records. In other words, dates not already included in the first query. Something like

SELECT i.imp_date, i.imp_counter, COUNT(l.lead_aff_uid) AS leadCount

FROM impressions i LEFT JOIN leads l

ON i.imp_aff_uid = l.lead_aff_uid

AND i.imp_date = l.lead_date

WHERE i.imp_date BETWEEN ...{your filters} ...

GROUP BY i.imp_date, i.imp_counter

UNION ALL

SELECT l.lead_date, 0 AS imp_counter, COUNT(l.lead_aff_uid) AS leadCount

FROM leads l LEFT JOIN impressions i

ON i.imp_aff_uid = l.lead_aff_uid

AND i.imp_date = l.lead_date

WHERE l.lead_date BETWEEN ...{your filters} ...

AND i.imp_aff_uid IS NULL

GROUP BY l.lead_date

The result should be:

>> 09/01/2011,100,0

>> 09/03/2011,200,1

>> 09/07/2011,0,3

However you would not get those totals as long you are including the "uid" column in the SELECT list.

Report · Sep 12, 2011

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

Report · Sep 12, 2011

Then take a look at the documentation on FULL OUTER JOIN's. They are certainly much simpler than the UNION I just posted

-Leigh

Report · Sep 12, 2011

I'

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

Report · Sep 12, 2011

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

No reason to redo it. It is a poor substitute anyway. The final query should be much simpler. You already know the results you need. You just need to read up on full outer joins for yourself.

Combining data from SQL query

Photos