Help needed to create a View from sql table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help needed to create a View from sql table

Author

Topic

pontingbaba
Starting Member

2 Posts

Posted - 2011-01-31 : 06:44:21

Hi,

I have a table tbl like
[cc] [char](2) COLLATE Latin1_General_CI_AS NOT NULL,
[employeeID] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[employeetype] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[employeeInstructed] [smalldatetime] NULL,
[employeeByAgent] [smallint] NULL

I want to create a view from this table. The required columns for view are
1.cc
2.employeetmtype
3.count(cc)
4.count(employeeInstructed) *100/count(cc) where employeeInstructed is not NULL
5.count(employeeByAgent) *100 /count(cc) where employeeByAgent = 1

I am not able to create column 4 and 5.
Baba

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-31 : 06:55:31

What do you have so far?

you could for columns 4 and 5 do something like:

SUM((Case when employeeInstructedis not null then 1 else 0 end)*100)/COUNT(cc),
SUM((Case when employeeByAgent = 1 then 1 else 0 end)*100)/COUNT(cc)

Assuming you want the denominator to match column 3.

Poor planning on your part does not constitute an emergency on my part.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-31 : 11:57:59

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

CREATE TABLE Personnel
cc CHAR(2) COLLATE NOT NULL, -- cubic centimeter?
employee_id NVARCHAR(50) NOT NULL PRIMARY KEY, -- very long identifier!
employee_type NVARCHAR(255) NOT NULL, -- very, very long type encoding!
employee_Instruction_date DATE, -- use date data type now
employee_agent_code SMALLINT);

CREATE VIEW Personnel_Counts (cc, employee_type, cc_cnt, instructed_percent, agent_one_percent)
AS
SELECT cc, employee_type,
COUNT(cc) OVER(),
COUNT(employee_instruction_date) OVER () *100.0/COUNT(cc)
SUM(CASE WHEN employee_agent_code = 1 THEN 1 ELSE 0 END)
*100.0 /COUNT(cc) OVER()
FROM Personnel;

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

pontingbaba
Starting Member

2 Posts

Posted - 2011-02-01 : 04:55:29

Thanks dataguru and jcelko.

dataguru, it worked well for me.

jcelko, I will make sure to follow your instructions for further questions.

baba

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources