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 · Sep 12, 2011

So

which one are you saying should to it??

Report · Sep 13, 2011

So which one are you saying should to it??

Either. If you had tested the union, it should have worked under ms sql too. But like I said, that technique is just a poor man's full outer join.

Again, I would strongly recommend you read up on full outer joins and unions so you understand what the sql is doing and are not just using it blindly

Report · Sep 13, 2011

Agreed. I've used them before but not for a while and not very often so I have a basic understanding, I just seem to come unglued when I have to GROUP etc, I often end up with the wrong data

Appreciate the help from everybody!

Mark

Report · Sep 13, 2011

Look at my sample again. Pay attention to parentheses.

Report · Sep 13, 2011

ACS LLC,

Below is a sample query that may work for you. It uses a full join, as previously suggested in this thread. I also suggest that you research full joins and any other keywords in the query you are not familiar with.

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

FROM

(

SELECT

imp_date

, imp_counter

FROM impressions

WHERE imp_date BETWEEN '9/1/2011' AND '09/07/2011'

) AS IMP

FULL OUTER JOIN

(

SELECT

lead_date

, COUNT(*) AS lead_count

FROM leads

WHERE lead_date BETWEEN '9/1/2011' AND '09/07/2011'

GROUP BY lead_date

) AS LEAD

ON ( IMP.imp_date = LEAD.lead_date )

ORDER BY report_date;

Report · Sep 13, 2011

Hey Bob, it worked!!! Fantastic ... thanks for your help. I've modified it slightly so it only pulls accounts that I want rather than the whole dbase and it works like a charm

I have to admit that I have never used COALESCE before, I see that it is tied into NULL records but I have to admit I'm not 100% sure exactly how it is working in here and how efficient this setup is, but hey it works!

I've got a few modifications to work now depending on required report, but I THINK I can handle those changes myself.. hopefully I won't be back on this thread 😉

Thanks again

Mark

Report · Sep 13, 2011

I would try removing the COALESCE and looking at how the results differ, that should help you visualize what COALESCE is doing. I encourage you to do some research and testing so that when you need to troubleshoot the query later you'll understand what each part of the SQL statement is doing.

Report · Sep 13, 2011

I just played around with it, so it basically fills any EMPTY STRING results with zeros. got it

Mark

Report · Sep 13, 2011

Not exactly, COALESCE returns the first non-null item in the list. I would refer to your database documentation.

Report · Sep 13, 2011

umm.. when I took it out, I got the same results, but where there were zeros in the one with it, I had empty string.

Report · Jan 21, 2012

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

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

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

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

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

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

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

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

TRAFFIC REPORT: 01/01/2012 - 01/21/2012 All Accounts
Date	Offers Served	Total Leads	Revenue

01/19/2012	249	1	$0.38

01/19/2012	11	1	$0.38

01/19/2012	30	1	$0.38

01/20/2012	1,006	7	$2.66

01/20/2012	40	7	$2.66

01/20/2012	1,090	7	$2.66

01/21/2012	582	6	$2.28

01/21/2012	33	6	$2.28

01/21/2012	515	6	$2.28

Total	3,556	42

Sub-Total			$15.96
Referral Revenue			$0.00
Total			$15.96

$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

Report · Jan 21, 2012

I notice the it's not omitting zero lines either, I thought I had that working also

01/23/2012	0	0	$0.00

01/23/2012	0	0	$0.00

01/24/2012	0	0	$0.00

01/24/2012	0	0	$0.00

01/24/2012	0	0	$0.00

Total	3,563	42

Sub-Total			$15.96
Referral Revenue			$0.00
Total			$15.96

Report · Jan 21, 2012

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

Report · Jan 21, 2012

Thought it might help to post a CFDUMP to try demonstrate where it's going wrong:

query

RESULTSET

query
	IMP_COUNTER	LEAD_AFF_PAYMENT	LEAD_COUNT	REPORT_DATE
1	249	0.3800	1	2012-01-19 00:00:00.0
2	11	0.3800	1	2012-01-19 00:00:00.0
3	30	0.3800	1	2012-01-19 00:00:00.0
4	1006	2.6600	7	2012-01-20 00:00:00.0
5	40	2.6600	7	2012-01-20 00:00:00.0
6	1090	2.6600	7	2012-01-20 00:00:00.0
7	584	2.2800	6	2012-01-21 00:00:00.0
8	33	2.2800	6	2012-01-21 00:00:00.0
9	520	2.2800	6	2012-01-21 00:00:00.0

CACHED

false

EXECUTIONTIME

0

SQL

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 {d '2012-01-01'} AND {d '2012-01-21'} AND imp_aff_uid IN (SELECT aff_uid FROM aff WHERE aff_master_uid = 213261) ) 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 {d '2012-01-01'} AND {d '2012-01-21'} AND lead_data_status = 1 AND lead_aff_uid IN (SELECT aff_uid FROM aff WHERE aff_master_uid = 213261) GROUP BY lead_date ) AS LEAD ON ( IMP.imp_date = LEAD.lead_date ) ORDER BY report_date;

Report · Jan 24, 2012

Start by looking at why you are getting multiple records per date. Is it due to the data (ie multiple impression records per date) or the JOIN?

Combining data from SQL query

Photos