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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 UNION ALL query - Solved

Author  Topic 

sportsguy
Starting Member

39 Posts

Posted - 2013-03-07 : 17:31:42
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

sportsguy
Starting Member

39 Posts

Posted - 2013-03-07 : 17:45:57
Never mind, it was the validation query. . .

such an idiot for a hack!




MS Access 20 years, SQL hack
Go to Top of Page
   

- Advertisement -