May 21, 2008 at 7:55 am
--PLEASE DON'T SPEND TIME ON THIS YET> I THINK I HAVE FOUND THE SOURCE OF THE ERROR
My code takes the following form (it includes notes):
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]
-- It appears to be the following section that does not work correctly
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 field 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 nearlly works but just gives me what I started with.
May 21, 2008 at 8:03 am
Without looking too deeply, because I don't have the time right now. You should get a performance boost by removing the ORDER BY's
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply