May 20, 2008 at 5:14 am
I have a table like:
1Key_mint40
0GeographicCodevarchar101
0Year_census_estimateint41
0Sexint41
0Age_startint41
0Age_endint41
0Age_rangevarchar101
0Person_countdecimal91
And a lookup table, that you may want to copy into Excel, like:
Age_rangeAge_start_charAge_end_charAge_start_INTAge_end_INTKey_m
1_414141
5_959592
10_14101410143
15_19151915194
20_24202420245
25_29252925296
30_34303430347
35_39353935398
40_44404440449
45_494549454910
50_545054505411
55_595559555912
60_646064606413
65_696569656914
70_747074707415
75_797579757916
80_848084808417
85_115851158511518
0_0000019
This is defined as:
1Age_rangevarchar101
0Age_start_charvarchar251
0Age_end_charvarchar251
0Age_start_INTint41
0Age_end_INTint41
0Key_mint40
There are missing records in the first table, where the populations should be recorded as zero (actually I want to put in a value of 0.25 in as the population).
I would like to 'find' these missing records.
Foreach missing record I want to insert a population of 0.25 along with the rest of the record that is missing (ie. age group, sex, geographic code). Can anyone help me with this please.
My idea how this could be done:
IDEA A
To create a complete table without the population figures. Then to fill in the population figures that we do have using our original table. I then can update this new table and insert 0.25 where the population is NULL.
May 20, 2008 at 8:26 am
Could you Able to Post with some more information to understand your requirement,so that we will try to help you
Regards,
Rajesh
May 20, 2008 at 8:57 am
I've edited my post. Thanks.
May 20, 2008 at 10:11 am
Where are you getting your distinct list of geographic codes? That seems to be missing from the equation.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 20, 2008 at 11:14 am
I'm nearly there with the following:
ALTER PROCEDURE Populations_1991_onwards
AS
--EXEC Populations_1991_onwards
if exists(select 1 from INFORMATION_SCHEMA.tables where table_name = 'Populations_PREP') DROP TABLE Populations_PREP;
CREATE TABLE [Populations_PREP] (
[Key_m] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[GeographicCode] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Year_census_estimate] [int] NULL ,
[Sex] [int] NULL ,
[Age_start] [int] NULL ,
[Age_end] [int] NULL ,
[Age_range] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Person_count] [decimal](18, 9) NULL
) ON [PRIMARY]
DELETE FROM Corrected_population_data WHERE Col002 LIKE 'O.A.'
INSERT INTO [Populations_PREP](
[GeographicCode] ,
[Year_census_estimate] ,
[Sex],
[Age_start] ,
[Age_end] ,
[Age_range] ,
[Person_count] )
SELECT [Col002] ,
[Col003] ,
[Col004],
[Col005] ,
[Col006] ,
[Col007] ,
[Col008]
--Cast([Col008] as decimal)
FROM Corrected_population_data
if exists(select 1 from INFORMATION_SCHEMA.tables where table_name = 'Populations_TEMP') DROP TABLE Populations_TEMP;
CREATE TABLE [Populations_TEMP] (
[Key_m] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[GeographicCode] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Year_census_estimate] [int] NULL ,
[Sex] [int] NULL ,
[Age_start] [int] NULL ,
[Age_end] [int] NULL ,
[Age_range] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Person_count] [decimal](18, 9) NULL
) ON [PRIMARY]
if exists(select 1 from INFORMATION_SCHEMA.tables where table_name = 'Temp_table') DROP TABLE Temp_table;
CREATE TABLE [Temp_table] (
[Year_census_estimate] [int] NULL ,
[Sex] [int] NULL ,
[Age_start] [int] NULL ,
[Age_end] [int] NULL ,
[Age_range] [varchar] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Temp_table ([Year_census_estimate],[Sex], [Age_start], [Age_end], [Age_range])
SELECT DISTINCT Year_census_estimate, Sex, Age_start, Age_end, Age_range
FROM Populations_PREP
ORDER BY Year_census_estimate, Sex, Age_start
DECLARE @Year AS INT
SET @Year = 1991
while 1=1 begin
INSERT INTO [Populations_TEMP] (
[GeographicCode],
[Year_census_estimate],
[Sex],
[Age_start],
[Age_end],
[Age_range])
SELECT DISTINCT M.GeographicCode, @Year AS Year, T.Sex, T.Age_start, T.Age_end, T.Age_range
FROM Temp_table T, Populations_PREP M
ORDER BY
M.GeographicCode,
Year,
T.Sex,
T.Age_start,
T.Age_end,
T.Age_range
SET @Year = @Year +1
if @Year = 2006 begin
GOTO here
end
end
here:
if exists(select 1 from INFORMATION_SCHEMA.tables where table_name = 'Populations') DROP TABLE Populations;
CREATE TABLE [Populations] (
[Key_m] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[GeographicCode] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Year_census_estimate] [int] NULL ,
[Sex] [int] NULL ,
[Age_start] [int] NULL ,
[Age_end] [int] NULL ,
[Age_range] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Person_count] [decimal](18, 9) NULL
) ON [PRIMARY]
INSERT INTO Populations([GeographicCode], [Year_census_estimate], [Sex], [Age_start], [Age_end], [Age_range], [Person_count])
SELECT B.GeographicCode, B.Year_census_estimate, B.Sex, B.Age_start, B.Age_end, B.Age_range, A.Person_count
FROM Populations_PREP A, Populations_TEMP B
UPDATE Populations SET Person_count = 0.25 WHERE Person_count IS NULL
However, things seem to go a bit pear shaped in the while loop.
May 20, 2008 at 1:24 pm
Mark,
I went back to the beginning and read your original post. Based on the number of unknowns from your original post, I am not about to begin to go through the SP that you've posted to see if it solves your problem. I imagine that since you are using a WHILE loop, there's most likely a much more efficient way to do this.
It would help us help you if we had a better description of your desired results. Can you post table DDL along with sample data and an example of how you want your data to look after the code runs? See this thread for an example of how to format a good post: http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 21, 2008 at 2:48 am
Fair point,
I have updated the original post. Don't worry I think that I have nearly fixed all the problems.
May 21, 2008 at 7:05 am
My code now takes the form:
ALTER PROCEDURE Populations_1991_onwards
AS
if exists(select 1 from INFORMATION_SCHEMA.tables where table_name = 'Populations_PREP') DROP TABLE Populations_PREP;
-- Create table with correctly defined fields
CREATE TABLE [Populations_PREP] (
[Key_m] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[GeographicCode] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Year_census_estimate] [int] NULL ,
[Sex] [int] NULL ,
[Age_start] [int] NULL ,
[Age_end] [int] NULL ,
[Age_range] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Person_count] [decimal](18, 9) NULL
) ON [PRIMARY]
INSERT INTO [Populations_PREP](
[GeographicCode] ,
[Year_census_estimate] ,
[Sex],
[Age_start] ,
[Age_end] ,
[Age_range] ,
[Person_count] )
SELECT [Col002] ,
[Col003] ,
[Col004],
[Col005] ,
[Col006] ,
[Col007] ,
[Col008]
--Cast([Col008] as decimal)
FROM Corrected_population_data
if exists(select 1 from INFORMATION_SCHEMA.tables where table_name = 'Temp_table') DROP TABLE Temp_table;
-- This table is to hold all the distinct combinations that each geographic area should have records for
CREATE TABLE [Temp_table] (
[Year_census_estimate] [int] NULL ,
[Sex] [int] NULL ,
[Age_start] [int] NULL ,
[Age_end] [int] NULL ,
[Age_range] [varchar] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Temp_table ([Year_census_estimate],[Sex], [Age_start], [Age_end], [Age_range])
SELECT DISTINCT Year_census_estimate, Sex, Age_start, Age_end, Age_range
FROM Populations_PREP
ORDER BY Year_census_estimate, Sex, Age_start
-- This allows all filed combinations to be inserted into the population table with population count of zero given for each record
INSERT INTO Populations_PREP
([GeographicCode] ,
[Year_census_estimate] ,
[Sex] ,
[Age_start] ,
[Age_end] ,
[Age_range], Person_count)
SELECT DISTINCT M.GeographicCode, T.Year_census_estimate, T.Sex, T.Age_start, T.Age_end, T.Age_range, 0
FROM Temp_table T, Populations_PREP M
ORDER BY T.Year_census_estimate, M.GeographicCode, T.Sex, T.Age_start, T.Age_end, T.Age_range
if exists(select 1 from INFORMATION_SCHEMA.tables where table_name = 'Populations') DROP TABLE Populations;
CREATE TABLE [Populations] (
[Key_m] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[GeographicCode] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Year_census_estimate] [int] NULL ,
[Sex] [int] NULL ,
[Age_start] [int] NULL ,
[Age_end] [int] NULL ,
[Age_range] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Person_count] [decimal](18, 9) NULL
) ON [PRIMARY]
-- Where only a population count of zero is present this record is preserved. If any other population count is also present this record is preserved and takes prescident as the zero plus population count is equal to population count.
INSERT INTO Populations([GeographicCode], [Year_census_estimate], [Sex], [Age_start], [Age_end], [Age_range], [Person_count])
SELECT B.GeographicCode, B.Year_census_estimate, B.Sex, B.Age_start, B.Age_end, B.Age_range, SUM(B.Person_count)
FROM Populations_PREP B
GROUP BY B.GeographicCode, B.Year_census_estimate, B.Sex, B.Age_start, B.Age_end, B.Age_range
ORDER BY B.Year_census_estimate, B.GeographicCode, B.Sex, B.Age_start, B.Age_end, B.Age_range
-- This is to convert all cases of zero population to 0.2
UPDATE Populations SET Person_count = 0.2 WHERE Person_count = 0
It doesn't seem to work though. However I can't quite see why it doesn't work.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply