39 Replies Latest reply: Jan 24, 2012 8:40 AM by -==cfSearching==- RSS

    Combining data from SQL query

    ACS LLC Community Member

      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/01/2011,999

      08/01/2011,999

      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

        • 1. Re: Combining data from SQL query
          Dan Bracuk Community Member

          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

          • 2. Re: Combining data from SQL query
            ACS LLC Community Member

            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

            • 3. Re: Combining data from SQL query
              ACS LLC Community Member

              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

              • 4. Re: Combining data from SQL query
                ACS LLC Community Member

                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.

                • 5. Re: Combining data from SQL query
                  Dan Bracuk Community Member

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

                  • 6. Re: Combining data from SQL query
                    ACS LLC Community Member

                    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

                    • 7. Re: Combining data from SQL query
                      ACS LLC Community Member

                      Hey Dan

                       

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

                       

                      Thanks

                       

                      Mark

                      • 8. Re: Combining data from SQL query
                        Dan Bracuk Community Member

                        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. 

                        • 9. Re: Combining data from SQL query
                          ACS LLC Community Member

                          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

                          • 10. Re: Combining data from SQL query
                            JR "Bob" Dobbs Community Member

                            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.

                            • 11. Re: Combining data from SQL query
                              ACS LLC Community Member

                              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

                              • 12. Re: Combining data from SQL query
                                JR "Bob" Dobbs Community Member

                                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.

                                • 13. Re: Combining data from SQL query
                                  ACS LLC Community Member

                                  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

                                  • 14. Re: Combining data from SQL query
                                    JR "Bob" Dobbs Community Member

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

                                    • 15. Re: Combining data from SQL query
                                      ACS LLC Community Member

                                      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

                                      • 16. Re: Combining data from SQL query
                                        JR "Bob" Dobbs Community Member

                                        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."

                                        • 17. Re: Combining data from SQL query
                                          ACS LLC Community Member

                                          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

                                          • 18. Re: Combining data from SQL query
                                            Dan Bracuk Community Member

                                            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)

                                            • 19. Re: Combining data from SQL query
                                              ACS LLC Community Member

                                              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

                                              • 20. Re: Combining data from SQL query
                                                -==cfSearching==- Community Member

                                                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.

                                                • 21. Re: Combining data from SQL query
                                                  ACS LLC Community Member

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


                                                  • 22. Re: Combining data from SQL query
                                                    -==cfSearching==- Community Member

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

                                                     

                                                    -Leigh

                                                    • 23. Re: Combining data from SQL query
                                                      ACS LLC Community Member

                                                      I'

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

                                                      • 24. Re: Combining data from SQL query
                                                        -==cfSearching==- Community Member

                                                        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.

                                                        • 25. Re: Combining data from SQL query
                                                          ACS LLC Community Member

                                                          So

                                                          which one are you saying should to it??

                                                          • 26. Re: Combining data from SQL query
                                                            Dan Bracuk Community Member

                                                            Look at my sample again.  Pay attention to parentheses.

                                                            • 27. Re: Combining data from SQL query
                                                              JR "Bob" Dobbs Community Member

                                                              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;

                                                              • 28. Re: Combining data from SQL query
                                                                ACS LLC Community Member

                                                                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

                                                                • 29. Re: Combining data from SQL query
                                                                  JR "Bob" Dobbs Community Member

                                                                  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.

                                                                  • 30. Re: Combining data from SQL query
                                                                    ACS LLC Community Member

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

                                                                     

                                                                    Mark

                                                                    • 31. Re: Combining data from SQL query
                                                                      JR "Bob" Dobbs Community Member

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

                                                                      • 32. Re: Combining data from SQL query
                                                                        ACS LLC Community Member

                                                                        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.

                                                                        • 33. Re: Combining data from SQL query
                                                                          -==cfSearching==- Community Member

                                                                          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

                                                                          • 34. Re: Combining data from SQL query
                                                                            ACS LLC Community Member

                                                                            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

                                                                            • 35. Re: Combining data from SQL query
                                                                              ACS LLC Community Member

                                                                              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

                                                                               

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

                                                                                     

                                                                                          

                                                                                01/19/20122491$0.38       
                                                                                01/19/2012111$0.38       
                                                                                01/19/2012301$0.38       
                                                                                01/20/20121,0067$2.66       
                                                                                01/20/2012407$2.66       
                                                                                01/20/20121,0907$2.66       
                                                                                01/21/20125826$2.28       
                                                                                01/21/2012336$2.28       
                                                                                01/21/20125156$2.28       
                                                                              Total3,55642
                                                                              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

                                                                              • 36. Re: Combining data from SQL query
                                                                                ACS LLC Community Member

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

                                                                                 

                                                                                01/23/201200$0.00       
                                                                                  01/23/201200$0.00       
                                                                                  01/24/201200$0.00       
                                                                                  01/24/201200$0.00       
                                                                                  01/24/201200$0.00       
                                                                                Total3,56342
                                                                                Sub-Total$15.96       
                                                                                Referral Revenue$0.00       
                                                                                Total$15.96       

                                                                                  

                                                                                      

                                                                                • 37. Re: Combining data from SQL query
                                                                                  ACS LLC Community Member

                                                                                  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

                                                                                  • 38. Re: Combining data from SQL query
                                                                                    ACS LLC Community Member

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

                                                                                     

                                                                                    query
                                                                                    RESULTSET
                                                                                    query
                                                                                    IMP_COUNTERLEAD_AFF_PAYMENTLEAD_COUNTREPORT_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;
                                                                                    • 39. Re: Combining data from SQL query
                                                                                      -==cfSearching==- Community Member

                                                                                      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?