Blank data

  • Sachin 80451

    SSCommitted

    Points: 1642

    Hi,

    Was wondering if anyone knows if there is a way you can force blank values into a query for a calculation?

    I currently have a query which shows data from 2011. But there is no data in my table for 2012 yet, but i need to do a variance for it

    because next month there will be 2012 data but need to show this logic works currently. And if for some reason I dont get data for a particular agent for example then the logic still needs to return something?

    Is there some kind of function that does it or will I need to insert values into the table?

  • anthony.green

    SSC Guru

    Points: 112515

    take a look at the key word UNION and UNION ALL, it will allow you to specify an additional query which you can create a blank row with a dummy 2012 value.

  • Sean Lange

    SSC Guru

    Points: 286536

    Pretty sparse on the details here. I can think of several ways of doing but it all depends on exactly what you are trying to do. As Anthony said UNION to a dummy record is one way. There is also tally tables, calendar tables and some other options.

    If you can take a look at the first link in my signature it will explain best practices on posting questions. In a nutshell ddl, sample data and desired results along with a clear explanation.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • anthony.green

    SSC Guru

    Points: 112515

    Had a PM from this user on this, and he was using UNION inserting a dummy record but also passing in 0 for a division and also getting devide by 0 errors,

    these are the pm texts

    PM1

    Hi Anthony,

    I already have an UNION ALL. Where I am putting a '0' for '2012' field but its still not calculating?

    Some one mentioned using IS NULL function?

    PM2

    Query -

    declare @LastYear nvarchar(4)

    declare @CurrentMonth nvarchar(2)

    declare @CurrentYear nvarchar(4)

    select @CurrentYear=LEFT(MAX(Tpl_Month), 4),

    @CurrentMonth=RIGHT(MAX(Tpl_Month), 2),

    @LastYear= LEFT(MAX(Tpl_Month), 4)-1

    FROM TowergateAgency_MSCRM.dbo.Tpl_zonegwp

    SELECT A.[companyID] as CompanyID,

    TowergateAgency_MSCRM.dbo.Account.name as [Agent],

    CASE WHEN ZB.Tpl_ZoneCatergory = 1 THEN 'Commercial'

    WHEN ZB.Tpl_ZoneCatergory = 2 THEN 'Personal'

    WHEN ZB.Tpl_ZoneCatergory = 3 THEN 'Other' else ' ' END [Zone Catergory],

    A.Zone,

    SUM(A.[TGWP 2012]) [TG - 2012 YTD],

    SUM(A.[TGWP 2011]) [TG - 2011 YTD],

    SUM(A.[NBGWP 2012]) [NB - 2012 YTD],

    SUM(A.[NBGWP 2011]) [NB - 2011 YTD],

    SUM(A.[RNLGWP 2012]) [RNL - 2012 YTD],

    SUM(A.[RNLGWP 2011]) [RNL - 2011 YTD],

    SUM((A.[TGWP 2012] - A.[TGWP 2011])*100)/sum(A.[TGWP 2012]) as [TG Variance],

    SUM((A.[NBGWP 2012] - A.[NBGWP 2011])*100)/sum(A.[NBGWP 2012]) as [NB Variance],

    SUM((A.[RNLGWP 2012] - A.[RNLGWP 2011])*100)/sum(A.[RNLGWP 2012]) as [RNL Variance]

    FROM(

    select Tpl_CompanyId [CompanyID],

    Tpl_Month,

    Tpl_Channel,

    0 as [TGWP 2012],

    SUM(Tpl_TotalGWP) as [TGWP 2011],

    0 as [NBGWP 2012],

    0 as [NBGWP 2011],

    0 as [RNLGWP 2012],

    0 as [RNLGWP 2011],

    Tpl_ZoneId,

    Tpl_Zone [ZONE],

    LEFT(Tpl_Month, 4) as Year,

    RIGHT(Tpl_Month, 2) as Month

    from TowergateAgency_MSCRM.dbo.Tpl_zonegwp

    where LEFT(Tpl_Month, 4) = @lastyear and RIGHT(Tpl_Month, 2) <=@CurrentMonth AND DeletionStateCode = '0'

    GROUP BY Tpl_CompanyId,

    Tpl_Month,

    Tpl_Channel,

    Tpl_ZoneId,

    Tpl_Zone,

    LEFT(Tpl_Month, 4),

    RIGHT(Tpl_Month, 2)

    UNION ALL

    select Tpl_CompanyId [CompanyID],

    Tpl_Month,

    Tpl_Channel,

    SUM(Tpl_TotalGWP) as [TGWP 2012],

    0 as [TGWP 2011],

    0 as [NBGWP 2012],

    0 as [NBGWP 2011],

    0 as [RNLGWP 2012],

    0 as [RNLGWP 2011],

    Tpl_ZoneId,

    Tpl_Zone [ZONE],

    LEFT(Tpl_Month, 4) as Year,

    RIGHT(Tpl_Month, 2) as Month

    from TowergateAgency_MSCRM.dbo.Tpl_zonegwp

    where LEFT(Tpl_Month, 4) = @currentyear and RIGHT(Tpl_Month, 2) <=@CurrentMonth AND DeletionStateCode = '0'

    GROUP BY Tpl_CompanyId,

    Tpl_Month,

    Tpl_Channel,

    Tpl_ZoneId,

    Tpl_Zone,

    LEFT(Tpl_Month, 4),

    RIGHT(Tpl_Month, 2)

    )A

    INNER JOIN TowergateAgency_MSCRM.dbo.Account

    ON A.companyID = TowergateAgency_MSCRM.dbo.Account.AccountId

    INNER JOIN TowergateAgency_MSCRM.dbo.Tpl_zone ZB on ZB.Tpl_zoneId = a.Tpl_ZoneId

    GROUP BY ZB.Tpl_ZoneCatergory,A.Zone, A.[CompanyID],TowergateAgency_MSCRM.dbo.Account.name

    ORDER BY 2

    and Results want to be able to see GWP figures againt current year and last year. At the moment I get the following error - 'Msg 8134, Level 16, State 1, Line 10

    Divide by zero error encountered.'

    and dont get any results at the moment even though there are figures there.

    PM3

    But when I sum this in the outer query my calculation will always be out because of the '1'?

  • Rich Mechaber

    SSChampion

    Points: 10935

    As Sean posted, adding DDL code is useful to get the help you're seeking. Also, he mentioned tally tables, which is my usual approach.

    So, if you have a tally table with date ranges to include, you SELECT from that and LEFT JOIN to your "real" data, with a WHERE clause to constrain the desired date range. You'll get back rows for all dates, with NULLs for any missing dates.

    I'm sure if you search this forum or Google you'll find examples.

    Rich

  • tfifield

    SSCrazy Eights

    Points: 9655

    As Rich said before, you want to use a time based table as the anchor and left join to the real data.

    Here's an article with an example of using a calendar table as the anchor for the query:

    http://www.sqlservercentral.com/articles/T-SQL/70743/

    Todd Fifield

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply