T SQL Query:

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

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 23:49

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:50
נערך על-ידי Diane Sithoo יום שישי 24 פברואר 2012 23:55 Syntax
הוצע כתשובה על-ידי SergNL שבת 25 פברואר 2012 12:48
סומן כתשובה על-ידי Kalman TothMicrosoft Community Contributor, Moderator יום ראשון 04 מרץ 2012 16:02

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

0
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
- סומן כתשובה על-ידי Kalman TothMicrosoft Community Contributor, Moderator יום ראשון 04 מרץ 2012 16:02
- תגובה
- ציטוט
שבת 25 פברואר 2012 09:32

0
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).
- סומן כתשובה על-ידי Kalman TothMicrosoft Community Contributor, Moderator יום ראשון 04 מרץ 2012 16:02
- תגובה
- ציטוט
שבת 25 פברואר 2012 12:53

0

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 13:19

0

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 14:11

0

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:14

1

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/
- תגובה
- ציטוט
יום שני 27 פברואר 2012 23:13

0
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
- סומן כתשובה על-ידי Kalman TothMicrosoft Community Contributor, Moderator יום ראשון 04 מרץ 2012 16:03
- תגובה
- ציטוט
יום שני 27 פברואר 2012 23:19

0
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 TothMicrosoft Community Contributor, Moderator יום ראשון 04 מרץ 2012 16:03
- תגובה
- ציטוט

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

מנחה דיון

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

T SQL Query:

כל התגובות

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?