view

• sivaj2k

  Ten Centuries

  Points: 1188

  More actions

  December 4, 2014 at 2:26 am

  #292594

  Hi

  We have a sample gender data, with personid,firstname,lastname, gender etc. before creating a view we should consider, if the gender doesn't exists, we have to take unique gender value of other person whose first name matches with the first name of this person. If two different gender for a first name or first name not found, then we should display it as 'U' other wise the default gender . can we implement this logic in a view?

  Regards

  Siva

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  December 4, 2014 at 3:26 am

  #1762643

  WITH Names (personid, firstname, lastname, gender) AS ( -- sample data

  SELECT 1, 'John', 'Smith', 'M' UNION ALL

  SELECT 2, 'Sandy', 'Smith', 'F' UNION ALL

  SELECT 3, 'Sandy', 'Jones', 'M' UNION ALL

  SELECT 4, 'Sandy', 'Shaw', 'F' UNION ALL

  SELECT 5, 'Kim', 'Hughes', 'M' UNION ALL

  SELECT 6, 'Kim', 'Cattrall', 'F' UNION ALL

  SELECT 7, 'Susan', 'Sarandon', 'F' UNION ALL

  SELECT 8, 'Susan', 'Boyle', 'F' UNION ALL

  SELECT 9, 'John', 'Jones', 'M' UNION ALL

  SELECT 10, 'John', 'Unknown', NULL UNION ALL

  SELECT 11, 'Sandy', 'Unknown', NULL UNION ALL

  SELECT 12, 'Kim', 'Unknown', NULL UNION ALL

  SELECT 13, 'Susan', 'Unknown', NULL UNION ALL

  SELECT 14, 'Billy', 'Nomates', NULL UNION ALL

  SELECT 15, 'Billy', 'Unknown', NULL

  )

  , Genders AS ( -- get max and min gender for each fname to see whether there's a mix

  SELECT

  personid

  ,firstname

  ,lastname

  ,gender

  ,MAX(gender) OVER (PARTITION BY firstname) MaxGen

  ,MIN(gender) OVER (PARTITION BY firstname) MinGen

  FROM

  Names

  )

  SELECT

  personid

  ,firstname

  ,lastname

  ,CASE WHEN gender IS NULL THEN -- only do calculation for unknown gender

  CASE WHEN MaxGen = MinGen THEN MaxGen -- if max = min then only one gender

  ELSE 'U' -- if we're not sure, use U

  END

  ELSE gender

  END computed_gender

  FROM

  Genders

  John

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  December 4, 2014 at 3:43 am

  #1762648

  Another possibility....

  USE [tempdb]

  GO

  -- We have a sample gender data, with personid,firstname,lastname, gender etc. before creating a view we should consider,

  -- if the gender doesn't exists, we have to take unique gender value of other person whose first name matches with the first name of this person.

  -- If two different gender for a first name or first name not found, then we should display it as 'U' other wise the default gender . can we implement this logic in a view?

  -- Test data:

  IF OBJECT_ID('tempdb..Person') IS NOT NULL DROP TABLE Person ;

  CREATE TABLE Person

  (

  personid int,

  firstname varchar(50),

  lastname varchar(50),

  gender char(1)

  );

  INSERT Person (personid,firstname,lastname, gender)

  VALUES (1, 'Fred', 'Bloggs', 'M')

  INSERT Person (personid,firstname,lastname, gender)

  VALUES (2, 'Fred', 'Smith', '')

  INSERT Person (personid,firstname,lastname, gender)

  VALUES (3, 'Julie', 'Richards', '')

  INSERT Person (personid,firstname,lastname, gender)

  VALUES (4, 'Lyn', 'Bloggs', 'M')

  INSERT Person (personid,firstname,lastname, gender)

  VALUES (5, 'Lyn', 'Bloggs', 'F')

  INSERT Person (personid,firstname,lastname, gender)

  VALUES (6, 'Lyn', 'Bloggs', '')

  --INSERT Person (personid,firstname,lastname, gender)

  --VALUES (7, 'Julie', 'Hughes', 'F')

  IF OBJECT_ID('vw_Person') IS NOT NULL DROP VIEW vw_Person ;

  GO

  -- View definition:

  CREATE VIEW vw_Person

  AS

  (

  SELECT P.personid, P.firstname, P.lastname, gender = CASE WHEN P.Gender = '' OR P.Gender IS NULL THEN IIF(a.cnt=1, b.Gender, 'U')

  ELSE P.Gender END

  FROM Person P

  OUTER APPLY (SELECT cnt=count(DISTINCT Gender) FROM Person P1 WHERE P1.FirstName = P.FirstName AND Gender<> '' GROUP BY FirstName) a -- Counts no of genders for firstname

  OUTER APPLY (SELECT TOP 1 Gender FROM Person P2 WHERE P2.FirstName = P.FirstName AND Gender<> '' GROUP BY FirstName, Gender) b -- Select TOP 1 to avoid duplicate rows. Only used if a.cnt=1.

  );

  GO

  -- View results:

  SELECT * FROM vw_Person;