Result Set

Question

Post reply

Result Set

Sachin 80451

SSCommitted

Points: 1642
More actions
March 19, 2012 at 10:20 am

#258086

I currently have an UNION ALL script which combines two scripts together. Which SUM two figures.
At the moment the script only returns data for 'zone' where there is data in the table,for it.
Is there a way of shows all the 'zones' in my result set regardless of there being data in the table for it?
If for example, my Zones are 'A', 'B', 'C', 'D' and 'E'. And where is £50 in against Zone 'B', in my results set can I show
there being £0 for Zones A,C,D and E?

Viewing 5 posts - 1 through 4 (of 4 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Yes you can.

Can you provide us some sample table strucutre and and some sample data to work with?

Please read here on how to do that: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Sachin 80451 SSCommitted Points: 1642 More actions · Answer 2

CREATE TABLE [dbo].[Tpl_zonegwpBase](

[Tpl_zonegwpId] [uniqueidentifier] NOT NULL,

[Tpl_name] [nvarchar](100) NULL,

[Tpl_CompanyId] [uniqueidentifier] NULL,

[Tpl_Month] [nvarchar](6) NULL,

[Tpl_Channel] [nvarchar](100) NULL,

[Tpl_NBGWP] [money] NULL,

[Tpl_RenewalGWP] [money] NULL,

[Tpl_TotalGWP] [money] NULL,

[Tpl_Zone] [nvarchar](100) NULL,

CONSTRAINT [PK_Tpl_zonegwpExtensionBase] PRIMARY KEY CLUSTERED

(

[Tpl_zonegwpId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]

) ON [PRIMARY]

INSERT INTO tpl_zoneGWPbase

(Tpl_zonegwpId, Tpl_name, Tpl_CompanyId, Tpl_Month, Tpl_NBGWP,Tpl_RenewalGWP,Tpl_TotalGWP,Tpl_Zone)

SELECT '1','Insurance224','22426','201101','111','433','1000','ZONE A' UNION ALL

SELECT '2','Insurance089','94589','201102',565','433','1000','ZONE B' UNION ALL

SELECT '3','Insurance243','05532','201101','989','433','1000','ZONE C' UNION ALL

SELECT '4','Insurance902','05983','201102','483','433','1000','ZONE D' UNION ALL

SELECT '5','Insurance088','27349','201103','543','433','1000','ZONE E' UNION ALL

SELECT '6','Insurance230','28958','201101','776','433','1000','ZONE A' UNION ALL

SELECT '7','Insurance029','02834','201102','908','433','1000','ZONE C' UNION ALL

SELECT '8','Insurance935','25479','201103','243','433','1000','ZONE E'

Hope this helps. If I had more Zones such as Zone, F,G,H,I,J,K and wanted these to be blank in my results, how could i include these?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

That helps a lot.. Now, last question, do u maintain a "Zones" table where u ll have the list of zones?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

I took the libetry to add a new table.

Would this suffice?

DECLARE @Zones TABLE (Zoneid INT IDENTITY, Zone CHAR(6))

INSERT @Zones (Zone)

SELECT 'ZONE A'

UNION ALL SELECT 'ZONE B'

UNION ALL SELECT 'ZONE C'

UNION ALL SELECT 'ZONE D'

UNION ALL SELECT 'ZONE E'

UNION ALL SELECT 'ZONE F'

UNION ALL SELECT 'ZONE G'

UNION ALL SELECT 'ZONE H'

UNION ALL SELECT 'ZONE I'

UNION ALL SELECT 'ZONE J'

UNION ALL SELECT 'ZONE K'

UNION ALL SELECT 'ZONE L'

UNION ALL SELECT 'ZONE M'

UNION ALL SELECT 'ZONE N'

UNION ALL SELECT 'ZONE O'

UNION ALL SELECT 'ZONE P'

UNION ALL SELECT 'ZONE Q'

UNION ALL SELECT 'ZONE R'

UNION ALL SELECT 'ZONE S'

UNION ALL SELECT 'ZONE T'

UNION ALL SELECT 'ZONE U'

UNION ALL SELECT 'ZONE V'

UNION ALL SELECT 'ZONE W'

UNION ALL SELECT 'ZONE X'

UNION ALL SELECT 'ZONE Y'

UNION ALL SELECT 'ZONE Z'

select z.Zone , ISNULL( SUM(zb.Tpl_RenewalGWP) , 0) , isnull (sum(zb.Tpl_TotalGWP),0) , isnull( sum(zb.Tpl_NBGWP) ,0)

from @Zones z

left join Tpl_zonegwpBase zb

on z.Zone = zb.Tpl_Zone

group by z.Zone

If not, let us know what are the business rules and what your required output out of the sample data you provided.