Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sportsguy
Starting Member

USA
39 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
39 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  
 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.17 seconds. Powered By: Snitz Forums 2000