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 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply