Search code examples
sqlanalyticsdata-analysiszoho

Issue with Joining Tables in SQL

SQL newbie here, using Zoho Analytics to do some reporting, specifically with prorated forecasting of lead generation. I successfully created some tables that contain lead goals, and joined them onto matching leads based off of the current month. The problem I am having is I would like to be able to access my prorated goals even if I filter so that there are no leads that have been created yet. This will make more sense in the picture I attached, with an RPM gauge that cannot pull the target or maximum because no leads match the filter criteria. How do I join the tables (with maybe an ifnull statement?) so that even if no lead ID's match, I can still output my goals? Thanks so much in advance. RPM Gauge With prorated target and monthly goal RPM gauge settings, distinct count of Lead Id's Base table with goal used in Query table Query table, forgive me I am new

Sorry for what I am sure is a fundamental misunderstanding of how this works, I have had to teach myself everything I know about SQL, and I am apparently not a terribly great teacher.

Thanks!

I have tried using a right join, and an ifnull statement but it did not improve matters.

Edit- Sorry for the first post issues- here is the code and tables not in image form

Lead Table Example-

ID Lead Created Time Lead Type
12345 11/21/2022 Charge
12346 10/17/2020 Store
12347 08/22/2022 Enhance

I purposefully left out an entry that would match my filter criteria, as for the first few days of the month this often comes up. Ideally I would still like to get the prorated and total goals returned.

The table the query is pulling from to determine the prorated numbers-

Start Date End Date Prorating decimal Charge Enhance Store Service Charge[PR] Enhance[PR] Store[PR] Service[PR] Total Leads Total Leads[PR]
Jan 01 2022 Jan 31 2022 .1 15 12 15 20 1.5 1.2 1.5 2.0 62 6.2
Feb 01 2022 Feb 28 2022 .1 15 12 15 20 1.5 1.2 1.5 2.0 62 6.2
Mar 01 2022 Mar 31 2022 .1 15 12 15 20 1.5 1.2 1.5 2.0 62 6.2

^For simplicity's sake I did not change the goals month to month, but they would in reality.

Idea for a successful data table, [PR] meaning prorated-

Sum of Lead Id's Storage Goal Storage Goal[PR] Charge Goal Charge Goal [PR]
14 10 1 15 2
1 10 1 15 2
0 10 1 15 2

The SQL Query that I have that returns the blank gauge when no leads match my criteria(Created this month, and lead type=Store)

SELECT
         "Leads"."Id",
         "SSS - 2022 Leads Forecast [Job Type]".*
FROM  "Leads"
RIGHT JOIN "SSS - 2022 Leads Forecast [Job Type]" ON ((GETDATE()  >= "Start Date")
     AND    (GETDATE()  <= "End Date"))

Thanks so much to everyone who helped me reformat, first time poster so still learning the ropes. Let me know if I can provide more context or better info.

Solution

  • Figured this out! I used subqueries, filtering manually in the query instead of through the analytics widget, and did a distinct count to return zero instead of null, as well as coalescing for the dollar amount to return zero. (Not applicable in the below example) Below I have an example of some of the queries I used, as well as the resulting data table that is giving me the result that I want.

    SELECT
         (      SELECT count(*)
        FROM (          SELECT DISTINCT "Leads"."Id"
            FROM  "Leads" 
            WHERE    "Lead Type"  = 'Charge'
             AND    month_name("Created Time")  = month_name(GETDATE())
             AND    year("Created Time")  = year(GETDATE())
) AS  'test1' 
) AS 'Charge Leads',
         (      SELECT count(*)
        FROM (          SELECT DISTINCT "Leads"."Id"
            FROM  "Leads" 
            WHERE    "Lead Type"  = 'Store'
             AND    month_name("Created Time")  = month_name(GETDATE())
             AND    year("Created Time")  = year(GETDATE())
) AS  'test2' 
) AS 'Store Leads',
         (      SELECT count(*)
        FROM (          SELECT DISTINCT "Leads"."Id"
            FROM  "Leads" 
            WHERE    "Lead Type"  = 'Enhance'
             AND    month_name("Created Time")  = month_name(GETDATE())
             AND    year("Created Time")  = year(GETDATE())
) AS  'test3' 
) AS 'Enhance Leads',
         (      SELECT count(*)
        FROM (          SELECT DISTINCT "Leads"."Id"
            FROM  "Leads" 
            WHERE    "Lead Type"  = 'Service'
             AND    month_name("Created Time")  = month_name(GETDATE())
             AND    year("Created Time")  = year(GETDATE())
) AS  'test4' 
) AS 'Service Leads',
         "SSS - 2022 Leads Forecast [Job Type]".*
FROM  "SSS - 2022 Leads Forecast [Job Type]" 
WHERE    ((GETDATE()  >= "Start Date")
 AND    (GETDATE()  <= "End Date"))

    I am 100% sure that there is a more efficient way to do this, but it works and that was the most pressing thing.

    Here is the resulting data table, which is exactly what I needed-

    Charge Leads Store Leads Enhance Leads Service Leads Start Date End Date [PR] Charge [PR] Enhance [PR] Store [PR] Service [PR] Total Leads [Total] Charge [Total] Enhance [Total] Store [Total] Service [Total] Total Leads Prorating Decimal
    7 0 5 35 01 Dec 2022 31 Dec 2022 64 34 17 56 171 152 81 40 134 407 .419

    The [PR] are the prorated goals, so where we should be at this point in the month, and [Total] is the total goal for the month.