SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 UNION ALL query - Solved
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sportsguy
Starting Member

USA
38 Posts

Posted - 03/07/2013 :  17:31:42  Show Profile  Reply with Quote
I am doing something obviously wrong, but I can't see it.
each query returns the correct totals, but the union ALL query
does not populate the table with the correct numbers. . . Its missing rows. . .


USE [Admin]
GO

TRUNCATE TABLE [Dev].[dbo].[tmp_BRIDGE]
GO
SET ANSI_WARNINGS OFF 

DECLARE @YYYYMM	INT
SET @YYYYMM = 201305


INSERT INTO [Dev].[dbo].[tmp_BRIDGE]
(FiscalPeriod, strDistrict, Location, Customer_Acct, Promo_Code, Nat_Local, [Contract], Modifier, Summary_Program, Product, YOR, MOR, 
	NC, NL, PC, RB, SL, RL, RI, SN, TE, DN, NR, AW, XF, UN, LR, XN, Action_Amt, ER)

SELECT fc.YYYYMM, rif.strDistrict, rif.Location, rif.Customer_Acct, rif.Promo_Code, rif.Local_National, rif.[Contract], rif.Modifier,
	sp.Common_Service_Plan, sp.ProdID, 
	sum(IIF(fc.Fiscal_Month_Nbr = 1,rif.Annual_Amt,0)) AS "YOR", 
	sum(rif.Annual_Amt) AS "MOR", 0 AS "NC",0 AS "NL",0 AS "PC",0 AS "RB",0 AS "SL",0 AS "RL",0 AS "RI",0 AS "SN",0 AS "TE",
	0 AS "DN",0 AS "NR",0 AS "AW",0 AS "XF",0 AS "UN",0 AS "LR",0 AS "XN",0 AS "Action_Amt",0 AS "ER"
FROM [dbo].[FISCAL_CALENDAR] fc
	INNER JOIN [dbo].[RIF_EXTRACT] rif ON fc.Prior_Period = rif.FiscalPeriod
	INNER JOIN [dbo].[SERVICE_PLAN_CODES] sp ON rif.Service_Program = sp.Svc_Plan
WHERE fc.YYYYMM = @YYYYMM
GROUP BY fc.YYYYMM, rif.strDistrict, rif.Location, rif.Customer_Acct, rif.Promo_Code, rif.Local_National, rif.[Contract], rif.Modifier,
	sp.Common_Service_Plan, sp.ProdID

UNION ALL
SELECT rif.FiscalPeriod, rif.strDistrict, rif.Location, rif.Customer_Acct, rif.Promo_Code, rif.Local_National, rif.[Contract], rif.Modifier,
	sp.Common_Service_Plan, sp.ProdID,
	0 AS "YOR",	0 AS "MOR", 0 AS "NC",0 AS "NL",0 AS "PC",0 AS "RB",0 AS "SL",0 AS "RL",0 AS "RI",0 AS "SN",0 AS "TE",
	0 AS "DN",0 AS "NR",0 AS "AW",0 AS "XF",0 AS "UN",0 AS "LR",0 AS "XN",0 AS "Action_Amt",sum(rif.Annual_Amt) AS "ER"
FROM [dbo].[RIF_EXTRACT] rif
	INNER JOIN [dbo].[SERVICE_PLAN_CODES] sp ON rif.Service_Program = sp.Svc_Plan
WHERE rif.FiscalPeriod = @YYYYMM
GROUP BY rif.FiscalPeriod, rif.strDistrict, rif.Location, rif.Customer_Acct, rif.Promo_Code, rif.Local_National, rif.[Contract], rif.Modifier,
	sp.Common_Service_Plan, sp.ProdID

UNION ALL
SELECT act.FiscalPeriod, act.strDistrict, act.Location, act.Customer_Acct, act.Promo_Code, act.National_Local, act.[Contract], act.Modifier,
	sp.Common_Service_Plan, sp.ProdID, 0 AS "YOR",	0 AS "MOR",
	Sum(IIF(ac.Action_Code = 'NC',act.Annual_Action_Amt, 0)) AS "NC", 
	Sum(IIF(ac.Action_Code = 'NL',act.Annual_Action_Amt, 0)) AS "NL",
	Sum(IIF(ac.Action_Code = 'PC',act.Annual_Action_Amt, 0)) AS "PC",
	Sum(IIF(ac.Action_Code = 'RB',act.Annual_Action_Amt, 0)) AS "RB",
	Sum(IIF(ac.Action_Code = 'SL',act.Annual_Action_Amt, 0)) AS "SL",
	Sum(IIF(ac.Action_Code = 'RL',act.Annual_Action_Amt, 0)) AS "RL",
	Sum(IIF(ac.Action_Code = 'RI',act.Annual_Action_Amt, 0)) AS "RI",
	Sum(IIF(ac.Action_Code = 'SN',act.Annual_Action_Amt, 0)) AS "SN",
	Sum(IIF(ac.Action_Code = 'TE',act.Annual_Action_Amt, 0)) AS "TE",
	Sum(IIF(ac.Action_Code = 'DN',act.Annual_Action_Amt, 0)) AS "DN",
	Sum(IIF(ac.Action_Code = 'NR',act.Annual_Action_Amt, 0)) AS "NR",
	Sum(IIF(ac.Action_Code = 'AW',act.Annual_Action_Amt, 0)) AS "AW",
	Sum(IIF(ac.Action_Code = 'XF',act.Annual_Action_Amt, 0)) AS "XF",
	Sum(IIF(ac.Action_Code = 'UN',act.Annual_Action_Amt, 0)) AS "UN",
	Sum(IIF(ac.Action_Code = 'LR',act.Annual_Action_Amt, 0)) AS "LF",
	Sum(IIF(ac.Action_Code = 'XN',act.Annual_Action_Amt, 0)) AS "XN",
	sum(act.Annual_Action_Amt) AS "Action_Amt", 0 AS "ER"
FROM [dbo].[RIF_ACTIONS_EXTRACT] act
	INNER JOIN [dbo].[SERVICE_PLAN_CODES] sp ON act.Service_Program = sp.Svc_Plan
	INNER JOIN [dbo].[RIF_ACTION_CODES] ac ON act.[Action] = ac.[Action]
WHERE act.FiscalPeriod = @YYYYMM
GROUP BY act.FiscalPeriod, act.strDistrict, act.Location, act.Customer_Acct, act.Promo_Code, act.National_Local, act.[Contract], act.Modifier,
	sp.Common_Service_Plan, sp.ProdID

GO


thanks in advance. . .

MS Access 20 years, SQL hack

Edited by - sportsguy on 03/07/2013 17:47:28

sportsguy
Starting Member

USA
38 Posts

Posted - 03/07/2013 :  17:45:57  Show Profile  Reply with Quote
Never mind, it was the validation query. . .

such an idiot for a hack!




MS Access 20 years, SQL hack
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000