none
T SQL Query:

    שאלה

  • Hi 

    I have a table of the following structure:

    CREATE TABLE [dbo].[Goal](
    	[RD] [nvarchar](255) NULL,
    	[Flxid] [nvarchar](255) NULL,
    	[Branch] [nvarchar](255) NULL,
    	[Associate] [nvarchar](255) NULL,
    	[Title] [nvarchar](255) NULL,
    	[Measure] [nvarchar](255) NULL,
    	[Jan] [nvarchar](255) NULL,
    	[Feb] [nvarchar](255) NULL,
    	[Mar] [nvarchar](255) NULL,
    	[Apr] [nvarchar](255) NULL,
    	[May] [nvarchar](255) NULL,
    	[Jun] [nvarchar](255) NULL,
    	[Jul] [nvarchar](255) NULL,
    	[Aug] [nvarchar](255) NULL,
    	[Sep] [nvarchar](255) NULL,
    	[Oct] [nvarchar](255) NULL,
    	[Nov] [nvarchar](255) NULL,
    	[Dec] [nvarchar](255) NULL
    ) ON [PRIMARY]

    This table stores all the goals for the associate for the current year (2012). 

    I need to write a query in such a way that the current month is dynamically determined and goals are rolled up. Example if I want to look at an associate goals for the month of March ( If March is the current month): The query should add up goals for Jan+Feb+March and show the results as 

    Associate Name  Current Goal ( Jan+Feb+March)

    Andy                  480

    The query that I have written will give me rolling goals for all the months ...but I need to dynamically decide from getdate the current month and add the current month + previous months from the start of the year and produce the result in single row as explained above .

    The query that I tried is :

    select 
    associate,
    case when datepart(mm,getdate()) =01 then dbo.fnExtractNumbersFromString(Jan) 
         else dbo.fnExtractNumbersFromString(Jan) end as January_goal,
    case when datepart(mm,getdate()) =02 then cast(dbo.fnExtractNumbersFromString(Jan)as int) 
                                             +cast( dbo.fnExtractNumbersFromString(Feb)as int) 
         else dbo.fnExtractNumbersFromString(Jan)
             + dbo.fnExtractNumbersFromString(Feb) end as February_Goal ,
    case when datepart(mm,getdate()) =03 then cast(dbo.fnExtractNumbersFromString(Jan)as int) 
            +cast( dbo.fnExtractNumbersFromString(Feb)as int) 
            +cast( dbo.fnExtractNumbersFromString(Mar)as int) 
    else  cast(.dbo.fnExtractNumbersFromString(Jan)as int) 
         +cast( dbo.fnExtractNumbersFromString(Feb)as int) 
          +cast( dbo.fnExtractNumbersFromString(Mar)as int)  end as March_Goal 
    from 
    [dbo].[Goal]
    where associate = 'Beveridge, Stephanie'

    The data in the goal table is stored alphanumeric and so I have written a small routine to extract numbers from the string. Please advice. 

    Thanks


    EVA05

    יום שישי 24 פברואר 2012 22:05

תשובות

  • Hi EVA05,

    I'm not sure why you are trying to create columns <Month>_Goal in your above query; why you have an ELSE which seems to be functionally the same as the WHEN clause; why you need a user-defined function to convert nvarchar to a number type when it seems built-in, and much faster CAST and CONVERT functions could be used; and why the return type of the udf isn't already in INT if that's what you need to begin with.

    Also, hopefully your Goal table is small, because this kind of query isn't very efficient.  It would have been much better to create the Goal table with a [Month] column, instead of flattening them out into individual columns, and inserting 12 records for each associate.  If the reasoning was to get Months as columns in a report, then you could have used PIVOT to accomplish that.

    Anyways, that's just my 2 cents about the design structurre here.  Assuming that it is set in stone, and can't be changed, then here's how to get your "original" resultset with just Associate and Current Goal columns:

    SELECT 
    	associate,
    	CAST(CASE DATEPART(mm, GETDATE()) 
    		WHEN 1
    			THEN dbo.fnExtractNumbersFromString([Jan]) 
    		WHEN 2
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    		WHEN 3
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    		WHEN 4
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    		WHEN 5
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    		WHEN 6
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    		WHEN 7
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    		WHEN 8
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    				+ dbo.fnExtractNumbersFromString([Aug])
    		WHEN 9
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    				+ dbo.fnExtractNumbersFromString([Aug])
    				+ dbo.fnExtractNumbersFromString([Sep])
    		WHEN 10
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    				+ dbo.fnExtractNumbersFromString([Aug])
    				+ dbo.fnExtractNumbersFromString([Sep])
    				+ dbo.fnExtractNumbersFromString([Oct])
    		WHEN 11
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    				+ dbo.fnExtractNumbersFromString([Aug])
    				+ dbo.fnExtractNumbersFromString([Sep])
    				+ dbo.fnExtractNumbersFromString([Oct])
    				+ dbo.fnExtractNumbersFromString([Nov])
    		ELSE
    			dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    				+ dbo.fnExtractNumbersFromString([Aug])
    				+ dbo.fnExtractNumbersFromString([Sep])
    				+ dbo.fnExtractNumbersFromString([Oct])
    				+ dbo.fnExtractNumbersFromString([Nov])
    				+ dbo.fnExtractNumbersFromString([Dec])
    	END AS int) AS Current_Goal 
    FROM [dbo].[Goal]
    WHERE associate = 'Beveridge, Stephanie'

    Hope it helps!

    Diane



    • נערך על-ידי Diane Sithoo יום שישי 24 פברואר 2012 23:55 Syntax
    • הוצע כתשובה על-ידי SergNL שבת 25 פברואר 2012 12:48
    • סומן כתשובה על-ידי Kalman TothModerator יום ראשון 04 מרץ 2012 16:02
    יום שישי 24 פברואר 2012 23:49
  • This is not a table and it can never be a table. You have no idea what a key or a normal form is. Look up the term “repeated groups” in that chapter of any book on RVBMS.  The magical NVARCHAR(255) will let you stored the Heart sutra in Unicode Chinese but it stinks for real data. You did not know that DEC is short for DECIMAL in SQL so it cannot be a valid column name. Since we have no specs or even valid DDL I will guess this is sales goals for associate in a company. Here is a skeleton of a normalized table

    CREATE TABLE Associate_Goals
    (associate_id CHAR(10) NOT NULL
        REFERENCES Personnel (associate_id),
     sales_month CHAR(10) NOT NULL
       CHECK (sales_month LIKE '2012-[01][0-9]-00'),
     PRIMARY KEY (associate_id), sales_month)
     sales_goal_amt DECIMAL (12,2) NOT NULL); 

    I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. I crippled this to just the year 2012, as per your specs. It is a bad design and should be generalized to multiple years with the regular expression '[12][0-9][0-9][0-9]-[01][0-9]-00'. 

    >> I need to write a query in such a way that the current month is dynamically determined and goals are rolled up. <<

    CREATE PROCEDURE Get_Associate_Goals_YTD (@in_sales_month CHAR(10))
    AS
    SELECT associate_id,
           SUM(sales_goal_amt)
           OVER (PARTITION BY associate_id 
                     ORDER BY sales_month
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS sales_goal_amt_running_tot
      FROM Associate_Goals
     WHERE sales_month = @in_sales_month
       AND SUBSTRING(sales_month, 1, 4) 
           = SUBSTRING(@in_sales_month, 1, 4) 
     GROUP BY associate_id;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    שבת 25 פברואר 2012 05:47
  • Step 1:

    create a normalized table with a Date column like Joe suggested. If there's a need to represent the data the way you currently have for calling applications you could always create a view using the CrossTab or Pivot method. Never let the application format drive your table design!! One of the biggest issues with the current design: For next year, you'll need another table and you'll have to modify all related code. And you'll probably have to write yet another sproc for those who require to see both years...

    Get the dsign right in the first place and you don't have to worry about all that stuff.

    As a side note: The numerous function calls Diane suggested might lead to a very poor performance (depending on the function code and the number of rows in theGoal table).

    שבת 25 פברואר 2012 09:32
  • Celko,

    Whether the database design is good or bad, I don't think I would tell anyone that they  "have no idea what a key or a normal form is".  EVA05 never said that this was his/her own design, so for all we know, someone else designed it and he/she is stuck working with it.  It's that kind of commenting that discourages people who truly need help from asking questions in future!

    Also, you're wrong when you said "You did not know that DEC is short for DECIMAL in SQL so it cannot be a valid column name".  Even keywords can be used as columns in a table, especially if they are wrapped in "[]" brackets.  Don't believe me, try it yourself!

    Anyways, I think I already commented about how a better design might be in order, but since we don't know whether this is already a working table in production, with ties to many other systems, my suggestion should work for EVA05, disregarding "possible"performance problems (since for all we know this is also a very small table), and without having to re-invent the wheel, if it's not practical to do so.


    Diane

    יום שני 27 פברואר 2012 23:13
  • As I commented earlier, we have no idea whether this is EVA05's design or not, and whether or not he/she is stuck with it.  I have already mentioned to him/her that there would be performance problems with that kind of design and even suggested a better design first, but that's not the question he/she is asking.  My suggestion only deals with the actual question at hand, not whether EVA05 should first have to re-design everything.

    Diane


    • נערך על-ידי Diane Sithoo יום שני 27 פברואר 2012 23:20
    • סומן כתשובה על-ידי Kalman TothModerator יום ראשון 04 מרץ 2012 16:03
    יום שני 27 פברואר 2012 23:19

כל התגובות

  • Hi EVA05,

    I'm not sure why you are trying to create columns <Month>_Goal in your above query; why you have an ELSE which seems to be functionally the same as the WHEN clause; why you need a user-defined function to convert nvarchar to a number type when it seems built-in, and much faster CAST and CONVERT functions could be used; and why the return type of the udf isn't already in INT if that's what you need to begin with.

    Also, hopefully your Goal table is small, because this kind of query isn't very efficient.  It would have been much better to create the Goal table with a [Month] column, instead of flattening them out into individual columns, and inserting 12 records for each associate.  If the reasoning was to get Months as columns in a report, then you could have used PIVOT to accomplish that.

    Anyways, that's just my 2 cents about the design structurre here.  Assuming that it is set in stone, and can't be changed, then here's how to get your "original" resultset with just Associate and Current Goal columns:

    SELECT 
    	associate,
    	CAST(CASE DATEPART(mm, GETDATE()) 
    		WHEN 1
    			THEN dbo.fnExtractNumbersFromString([Jan]) 
    		WHEN 2
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    		WHEN 3
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    		WHEN 4
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    		WHEN 5
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    		WHEN 6
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    		WHEN 7
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    		WHEN 8
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    				+ dbo.fnExtractNumbersFromString([Aug])
    		WHEN 9
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    				+ dbo.fnExtractNumbersFromString([Aug])
    				+ dbo.fnExtractNumbersFromString([Sep])
    		WHEN 10
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    				+ dbo.fnExtractNumbersFromString([Aug])
    				+ dbo.fnExtractNumbersFromString([Sep])
    				+ dbo.fnExtractNumbersFromString([Oct])
    		WHEN 11
    			THEN dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    				+ dbo.fnExtractNumbersFromString([Aug])
    				+ dbo.fnExtractNumbersFromString([Sep])
    				+ dbo.fnExtractNumbersFromString([Oct])
    				+ dbo.fnExtractNumbersFromString([Nov])
    		ELSE
    			dbo.fnExtractNumbersFromString([Jan])
    				+ dbo.fnExtractNumbersFromString([Feb])
    				+ dbo.fnExtractNumbersFromString([Mar])
    				+ dbo.fnExtractNumbersFromString([Apr])
    				+ dbo.fnExtractNumbersFromString([May])
    				+ dbo.fnExtractNumbersFromString([Jun])
    				+ dbo.fnExtractNumbersFromString([Jul])
    				+ dbo.fnExtractNumbersFromString([Aug])
    				+ dbo.fnExtractNumbersFromString([Sep])
    				+ dbo.fnExtractNumbersFromString([Oct])
    				+ dbo.fnExtractNumbersFromString([Nov])
    				+ dbo.fnExtractNumbersFromString([Dec])
    	END AS int) AS Current_Goal 
    FROM [dbo].[Goal]
    WHERE associate = 'Beveridge, Stephanie'

    Hope it helps!

    Diane



    • נערך על-ידי Diane Sithoo יום שישי 24 פברואר 2012 23:55 Syntax
    • הוצע כתשובה על-ידי SergNL שבת 25 פברואר 2012 12:48
    • סומן כתשובה על-ידי Kalman TothModerator יום ראשון 04 מרץ 2012 16:02
    יום שישי 24 פברואר 2012 23:49
  • This is not a table and it can never be a table. You have no idea what a key or a normal form is. Look up the term “repeated groups” in that chapter of any book on RVBMS.  The magical NVARCHAR(255) will let you stored the Heart sutra in Unicode Chinese but it stinks for real data. You did not know that DEC is short for DECIMAL in SQL so it cannot be a valid column name. Since we have no specs or even valid DDL I will guess this is sales goals for associate in a company. Here is a skeleton of a normalized table

    CREATE TABLE Associate_Goals
    (associate_id CHAR(10) NOT NULL
        REFERENCES Personnel (associate_id),
     sales_month CHAR(10) NOT NULL
       CHECK (sales_month LIKE '2012-[01][0-9]-00'),
     PRIMARY KEY (associate_id), sales_month)
     sales_goal_amt DECIMAL (12,2) NOT NULL); 

    I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. I crippled this to just the year 2012, as per your specs. It is a bad design and should be generalized to multiple years with the regular expression '[12][0-9][0-9][0-9]-[01][0-9]-00'. 

    >> I need to write a query in such a way that the current month is dynamically determined and goals are rolled up. <<

    CREATE PROCEDURE Get_Associate_Goals_YTD (@in_sales_month CHAR(10))
    AS
    SELECT associate_id,
           SUM(sales_goal_amt)
           OVER (PARTITION BY associate_id 
                     ORDER BY sales_month
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS sales_goal_amt_running_tot
      FROM Associate_Goals
     WHERE sales_month = @in_sales_month
       AND SUBSTRING(sales_month, 1, 4) 
           = SUBSTRING(@in_sales_month, 1, 4) 
     GROUP BY associate_id;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    שבת 25 פברואר 2012 05:47
  • Step 1:

    create a normalized table with a Date column like Joe suggested. If there's a need to represent the data the way you currently have for calling applications you could always create a view using the CrossTab or Pivot method. Never let the application format drive your table design!! One of the biggest issues with the current design: For next year, you'll need another table and you'll have to modify all related code. And you'll probably have to write yet another sproc for those who require to see both years...

    Get the dsign right in the first place and you don't have to worry about all that stuff.

    As a side note: The numerous function calls Diane suggested might lead to a very poor performance (depending on the function code and the number of rows in theGoal table).

    שבת 25 פברואר 2012 09:32
  • As a side note: The numerous function calls Diane suggested might lead to a very poor performance (depending on the function code and the number of rows in theGoal table).

    No, function will be called exactly times needed, 8 times per row for august for example. Given the source table design , unpivoting or whatever will not help calculate the sum for august with fewer calls.

    Serg

    שבת 25 פברואר 2012 12:53
  • Hy I m always satisfied when i put my question in microsoft forums i m so glad for that . I have a problem with a sql request i didn't find  the solution is there some one here to help me please

    Loubnas

    שבת 25 פברואר 2012 13:19
  • As a side note: The numerous function calls Diane suggested might lead to a very poor performance (depending on the function code and the number of rows in theGoal table).

    No, function will be called exactly times needed, 8 times per row for august for example. Given the source table design , unpivoting or whatever will not help calculate the sum for august with fewer calls.

    Serg

    For a table with 1 million rows it's quite a load to call the function 8x per row for August...

    A proper designed table would allow for SUM() OVER (PARTITION BY WhatverColumnIsAppropriate) as Joe correctly pointed out.

    And like I said before: it's going to be "fun" to return the values for March 2011 until Feb 2013...

    שבת 25 פברואר 2012 14:11
  • Hy I m always satisfied when i put my question in microsoft forums i m so glad for that . I have a problem with a sql request i didn't find  the solution is there some one here to help me please

    Loubnas

    I'm glad you've found these forumns useful.  You can ask a new question by clicking the "Ask a question in the forums" link on the upper right corner of this page.  Please follow the guidlines in: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c6d69372-af26-452c-963a-de22cf9d7985 so that we can better hep you.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    שבת 25 פברואר 2012 14:14
  • Celko,

    Whether the database design is good or bad, I don't think I would tell anyone that they  "have no idea what a key or a normal form is".  EVA05 never said that this was his/her own design, so for all we know, someone else designed it and he/she is stuck working with it.  It's that kind of commenting that discourages people who truly need help from asking questions in future!

    Also, you're wrong when you said "You did not know that DEC is short for DECIMAL in SQL so it cannot be a valid column name".  Even keywords can be used as columns in a table, especially if they are wrapped in "[]" brackets.  Don't believe me, try it yourself!

    Anyways, I think I already commented about how a better design might be in order, but since we don't know whether this is already a working table in production, with ties to many other systems, my suggestion should work for EVA05, disregarding "possible"performance problems (since for all we know this is also a very small table), and without having to re-invent the wheel, if it's not practical to do so.


    Diane

    יום שני 27 פברואר 2012 23:13
  • As I commented earlier, we have no idea whether this is EVA05's design or not, and whether or not he/she is stuck with it.  I have already mentioned to him/her that there would be performance problems with that kind of design and even suggested a better design first, but that's not the question he/she is asking.  My suggestion only deals with the actual question at hand, not whether EVA05 should first have to re-design everything.

    Diane


    • נערך על-ידי Diane Sithoo יום שני 27 פברואר 2012 23:20
    • סומן כתשובה על-ידי Kalman TothModerator יום ראשון 04 מרץ 2012 16:03
    יום שני 27 פברואר 2012 23:19
  • CREATE TABLE [dbo].[Goal](
    	[RD] [nvarchar](255) NULL,
    	[Flxid] [nvarchar](255) NULL,
    	[Branch] [nvarchar](255) NULL,
    	[Associate] [nvarchar](255) NULL,
    	[Title] [nvarchar](255) NULL,
    	[Measure] [nvarchar](255) NULL,
    	[Jan] [nvarchar](255) NULL,
    	[Feb] [nvarchar](255) NULL,
    	[Mar] [nvarchar](255) NULL,
    	[Apr] [nvarchar](255) NULL,
    	[May] [nvarchar](255) NULL,
    	[Jun] [nvarchar](255) NULL,
    	[Jul] [nvarchar](255) NULL,
    	[Aug] [nvarchar](255) NULL,
    	[Sep] [nvarchar](255) NULL,
    	[Oct] [nvarchar](255) NULL,
    	[Nov] [nvarchar](255) NULL,
    	[Dec] [nvarchar](255) NULL
    ) ON [PRIMARY]

    This looks like a staging table created by some automatic ETL software.

    As suggested above, you need to move the data into "real" RDBMS table(s).


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    יום ראשון 04 מרץ 2012 16:07