March 19, 2012 at 2:34 am
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?
March 19, 2012 at 2:38 am
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.
March 19, 2012 at 7:26 am
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/
March 19, 2012 at 7:29 am
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'?
March 20, 2012 at 8:46 am
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
March 21, 2012 at 2:01 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply