creation of table that type of format........

hello all
i want to create a phone table and it contains two fields empid ,ph.
the phone table following format:

Phone table
-------------
empid ph
-- ----------
office Mobile home
--- --- ---
100 9380768532 98455555 98822213

--------------

i want above type of format and then how to insert into values that
phone table . please help me.surya (suryaitha@.gmail.com) writes:
> i want to create a phone table and it contains two fields empid ,ph.
> the phone table following format:
> Phone table
> -------------
> empid ph
> -- ----------
> office Mobile home
> --- --- ---
> 100 9380768532 98455555 98822213
> --------------
> i want above type of format and then how to insert into values that
> phone table . please help me.

The INSERT statement would be:

INSERT phonetable (empid, office, mobile, home)
VALUES (100, '9380768532', '98455555', '98822213')

If you are using some application environment, it is not unlikely that
the Client API offers some interface that constructs the INSERT statement
under the covers, but gives you a "nicer" interface.

The table design as such is not unquestionable. You have indicated
that office, modbile and home are infact subfields of ph, but there
is no such thing in a database table.

Depending on your requirements, it may be better to do:

CREATE TABLE phonetypes
(phonetype char(3) NOT NULL,
phonetypename varchar(20) NOT NULL,
CONSTRAINT pk_phonetypes PRIMARY KEY(phonetypeid))

INSERT phonetypes (phonetypes, phonetypename)
VALUES ('OFC', 'Office')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('MOB', 'Mobile')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('HOME', 'Home')

CREATE TABLE phonenumbers
(empid int NOT NULL,
phoneno varchar(200) NOT NULL,
phonetype char(3) NOT NULL,
isdefault bit NOT NULL,
CONSTRAINT pk_phonenumbers PRIMARY KEY(empid, phoneno),
CONSTRAINT fk_phonetype FORIEGN KEY (phonetype)
REFERENCES phonetypes (phonetype),
CONSTRAINT fk_employees (empid)
REFERENCES employees(empid))

There would be a trigger on phonenumbers, that enforces that isdefault may
be 1 for at most one combination of (empid, phoneno).

This design buys you more flexibility. Some people have more than one mobile
phone. It also permits you to add other telephone types such as FAX or
IP telephone like Skype. Since on services like Skype you don't have
traditional telephone numbers, but, as I understand it, something that looks
more like an email address, I've made phoneno varchar(200).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"surya" <suryaitha@.gmail.com> wrote in news:1144477944.400250.34550
@.z34g2000cwc.googlegroups.com:

> hello all
> i want to create a phone table and it contains two fields empid ,ph.
> the phone table following format:

Aren't you going to fail your class if you don't
do your own homework?

creating the database tables

Hi,

I need to create a table and a few fields in the SQL database programmatically in asp.net.

I know the tabes name, the server which it resides on, username/password. Would anyone have a small sample of this code?

Thanks

radI meant to say, "I know the database name."|||You might check out this article to get you started:Creating a SQL Server Database Programmatically.|||Thanks|||This is the code that I came up with. When I load the page it tells me that my error is here "Line 22: cmdCreate.ExecuteNonQuery()". What am I doing wrong?

<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %
<Script Runat="Server"
Sub Button_Click( s As Object, e As EventArgs )

Dim conUsers As SqlConnection
Dim strCreate As String
Dim cmdCreate As SqlCommand

conUsers = New SqlConnection( "Server=servername;UID=user;PWD=password;database=databasename" )

strCreate = "CREATE TABLE myTable" + "(Application CHAR(100), Project CHAR(100), EnteredDate CHAR(100), EnteredBy CHAR(100), Descriptions CHAR(100), SNPT CHAR(100), Automation CHAR(100), ContactName CHAR(100), ContactNumber CHAR(100), Priority CHAR(100), Status CHAR(100))"

cmdCreate = New SqlCommand( strCreate, conUsers )
conUsers.Open()
cmdCreate.ExecuteNonQuery()
conUsers.Close()

End Sub

</Script>|||Exactly what is the error message? That will help a great deal.|||I could guestimate that it's probably because you aren't setting the cmdCreate.CommandType, your UID for your server isn't a dbo in that database, you should have a space after myTable (just personal thing, shouldn't generate error), table of that name already exists..

yeah, we need what the error is before we can help you.|||Kragie, I have no idea what most of these things you said mean. I am new at asp.net.

The only error it gives me is, "CREATE TABLE permission denied in database 'databasename'.|||OK, the problem is that the SQL Server user does not have permission to add a table in the database. Give the user permission using Enterprise Manager, or use SQL Script in Query Analyzer.

USE databasename
go

GRANT CREATE TABLE TO username
go|||Thanks for the help guys, although I think the error might be incorrect.

creating text files using bcp

I am using BCP and it works just fine. I get a txt file with tab seperated fields. Can i specify the character positions or lengths of each field so that all my fields line up at the same positions.

Help is appreciated.

I created a stored procedure with the below
declare @.filename varchar(50),
@.bcpcommand varchar(2000)

set @.filename = 'c:\report\media.txt'
print @.filename
set @.bcpcommand = 'bcp "select * from table" queryout "'+ @.filename -U -P'
exec master..xp_cmdshell @.bcpCommandYou could format your select as needed.|||Can u elaborate more ...how do i format my sql to seperate the fields.

like i want one field on position 1-10 and the seond from position 11-40 and so on...

Thanks|||Originally posted by hp1000
Can u elaborate more ...how do i format my sql to seperate the fields.

like i want one field on position 1-10 and the seond from position 11-40 and so on...

Thanks
Just create one long field or as many as you need like this:

select field1+replicate(' ',25-datalength(field1)+...
from table|||I am using

set @.bcpcommand = 'bcp "select medium_name + replicate(' ',10-datalength(medium_name)) from database..report_tbl" queryout "'+ @.filename + '" -U sa -P -c'

I get Line 6: Incorrect syntax near '

How can i escape the single quotes

Please help

Thsi method should work for what i want to do .

Thanks|||Originally posted by snail
Just create one long field or as many as you need like this:

select field1+replicate(' ',25-datalength(field1)+...
from table

I am using the above but it does not work with NULL values . How do i make it to work with null values. Help is very much appreciated.

Thanks|||If you attempt to format your fields within BCP...QUERYOUT you may very quickly reach the limitation on the length of the command line (I don't remember what it is, but you can check.)

What you can do instead, is create a stored procedure where you'd format whatever and whichever way you want, and in your BCP specify a call to that procedure along with QUERYOUT.|||Here's how I do it - much easier to read and manage changes.

Much like a structure or type, I create a temporary table in a stored procedure (TABLE vars won't work, as BCP won;t be able to see them), using CHAR data types, one field for each output field. I have a user-defined function for zero-padding, and one for right-justifying (space padding). I populate that table with all my data, then do a BCP statement consisting of SELECT field1 + field2 + field3 etc.

This gives my the spacing I want, and that one create table at the beginning of the procedure allows me to easily control the number spaces each field gets.

I can post a proc if it would be easier to understand.

-b|||My table def looks like this (for an SAP feed):

CREATE TABLE ##sap_table(sales_organization CHAR(4) NOT NULL,
distribution_channel CHAR(2) NOT NULL,
division CHAR(2) NOT NULL,
filler_1 CHAR(2) NOT NULL,
sold_to CHAR(8) NOT NULL,
delivering_plant CHAR(4) NOT NULL,
profit_center CHAR(10) NOT NULL,
material_code CHAR(10) NOT NULL,
pricing_date CHAR(8) NOT NULL,
charge_type CHAR(4) NOT NULL,
order_reason CHAR(3) NOT NULL,
project_number CHAR(24) NOT NULL,
contract_number CHAR(10) NOT NULL,
purchase_order_number CHAR(12) NOT NULL,
internal_sales_reference CHAR(12) NOT NULL,
usage_type CHAR(3) NOT NULL,
usage_detail CHAR(60) NOT NULL,
requestor_name CHAR(40) NOT NULL,
user_number CHAR(10) NOT NULL,
[user_name] CHAR(40) NOT NULL,
comments CHAR(100) NULL,
quantity CHAR(15) NOT NULL,
unit_of_measure CHAR(3) NOT NULL,
currency CHAR(5) NOT NULL,
extended_amount CHAR(15) NOT NULL,
customer_cost_object CHAR(40) NOT NULL,
cco_flag CHAR(3) NOT NULL,
xy_flag CHAR(1) NOT NULL,
filler_2 CHAR(6) NOT NULL )|||If you want fixed width data, why not use a format file?|||Thanks for all ur feedback . I am using coalesce and datalength functions and it did the trick.

I create a view with the fields i want in the format i want and then do the select on the view with my bcp and it writes to the text file just fine.

Thanks a lot,

Creating temporary table

Hi,
How can I create a temporary table say "Tblabc" with column fields
ShmCoy char(2)
ShmAcno char(10)
ShmName1 varchar(60)
ShmName2 varchar(60)
and fill the table from the data extracted from the statement...
"select logdetail from shractivitylog"
The above query returns single value field the data seperated with a '·'
Ex:
BR··Light Blue Duck··
in this case I should get
ShmCoy = 'BR'
ShmAcno = ''
ShmName1 = 'Light Blue Duck'
ShmName2 = ''
I want to do this job with single SQL query. Is it possible. Pls help.
Herewith I am providing the sample data
BR··Light Blue Duck··
·0234578···
BR··Aqua Duck··
·0234586···
UB··Aqua Duck··
Regards,
OmavYou might consider copying the data out to a .Txt file with BCP. You could
then insert the data using BULK INSERT
--
HTH
Ryan Waight, MCDBA, MCSE
"Omavlana" <kiran@.boardroomlimited.com> wrote in message
news:b14098ab.0310080228.30a01631@.posting.google.com...
> Hi,
> How can I create a temporary table say "Tblabc" with column fields
> ShmCoy char(2)
> ShmAcno char(10)
> ShmName1 varchar(60)
> ShmName2 varchar(60)
> and fill the table from the data extracted from the statement...
> "select logdetail from shractivitylog"
>
> The above query returns single value field the data seperated with a '·'
> Ex:
> BR··Light Blue Duck··
> in this case I should get
> ShmCoy = 'BR'
> ShmAcno = ''
> ShmName1 = 'Light Blue Duck'
> ShmName2 = ''
> I want to do this job with single SQL query. Is it possible. Pls help.
>
> Herewith I am providing the sample data
> BR··Light Blue Duck··
> ·0234578···
> BR··Aqua Duck··
> ·0234586···
> UB··Aqua Duck··
>
> Regards,
> Omavsql

Creating table using dynamic SQL

I have the following dataset using dynamic SQL which works when i execute it, when i try to create a table using this dataset i cant see any fields. Does anyone know why dynamic SQL doesnt work ?

Declare @.TopRange int
Declare @.BottomRange int
Declare @.SQL Varchar(1000)

IF @.Param_leadage = '91+'
SET @.TopRange = 91
ELSE
Set @.TopRange = RTRIM(LEFT(REPLACE(@.Param_leadage,'-',''),2))

IF @.Param_leadage = '91+'
SET @.BottomRange = 4000
ELSE
Set @.BottomRange = LTRIM(RIGHT(REPLACE(@.Param_leadage,'-',''),2))

SET @.SQL = 'SELECT dbo.tblCustomer.idStatus, dbo.tblCustomer.idCustomer, dbo.tblCustomer.DateSigned' +
' FROM dbo.tblCustomer' +
' WHERE DateDiff(day, dbo.tblCustomer.DateSigned, GetDate()) >= ' + convert(varchar,@.TopRange) + ' AND DateDiff(day,dbo.tblCustomer.DateSigned, GetDate()) <= ' + convert(varchar,@.BottomRange)

IF @.Param_status = 'Online Churn'
SET @.SQL = @.SQL + ' AND dbo.tblCustomer.idStatus = 4 or dbo.tblCustomer.idStatus = 5 or dbo.tblCustomer.idStatus = 11'
ELSE
SET @.SQL = @.SQL + ' AND dbo.tblCustomer.idStatus = ' + @.Param_idstatus

EXEC(@.SQL)

If the Refresh Fields toolbar button enabled on the Data tab is enabled, try clicking it to get the fields. Or, plug in only the SELECT statement and hit Refresh Fields to get the fields.Then, change it to the original SQL. Finally, you can create the dataset fields manually.

|||

Hi Teo,

This isnt working, when i try to select a data field from the table textbox properties value dropdown box, it says 'Dataset1 has no fields'

When I enter it in manually it gives an error which is obviously to do with the fields not being in the scope.

In the Data screen within VSS 2005 the sql executes fine, when i refresh it prompts for parameters off which i enter and the data is returned, it is not recognising any fields when i try to access any fields from any controls..

it has something to do with the exec(sql) command and dynamic data i would suspect

I also tried doing the SQL first off which the table fields are recognised but as soon as i add the other portions such as the single quotes etc it gives the same error

Can someone please help

thanks

|||

Try putting this code in a stored proc and changing the command type to be stored proc.

The other option is to not use dynamic SQL, instead use an expression based query and construct the SQL using expression syntax e.g.

= Iif(Parameters!x.value = "91+", "Select...", "select ...")

Again, in this case I follow the previous advice of just putting in the SQL query first, hitting refresh so RS auto-generates the Filed definitions and then swapping in the expression

|||

@. is interpreted as a query parameter in the Query Designer. You can make the whole darn thing expression-based as Adam suggested or move this query to a stored procedure.

|||

thanks guys, i put it in a stored proc and it is working now

creating table fields dynamically from another table row

Hello all:

Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.

The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.

create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100)... )

Table: Dummy
================================================== ==
field1 field2 field3........
Empid Empname empaage1 sam 23.........
2 rai 22.........
.
.
.
n raj 45..........
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..

create table emp (
empid int, empname varchar(100), empage int...)

The table should look like as:

Table: EMP
================================================== ==
Empid Empname empaage.........
1 sam 23............
2 rai 22............
.
.
.
n raj 45.............
================================================== ==

I want to do this dynamically....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]

A small rough snippet of the code will be appreciated...

Waiting for replies......
sabyyou can do something like below. but determining the exact filed type in CREATE table is difficult and avoiding that I have taken all fields as varchar(100)

create table #T (SQLTxt varchar(5000))

declare @.SQL varchar(5000)
declare @.FinalSQL varchar(5000)
declare @.ii int

set @.SQL=''
set @.ii=1
while @.ii <= 140
begin
set @.SQL = @.SQL + ' Field' + cast(@.ii as varchar) + ' + '' varchar(100) null,''+'
set @.ii=@.ii+1
end
set @.SQL = left(@.SQL,len(@.SQL)-3)+''''
set @.SQL = 'insert into #T (SQLTxt) select top 1 ' + '''create table Dummy2 (''+' + @.SQL + '+'')''' + ' from Dummy'

exec (@.SQL)
set @.FinalSQL = (select top 1 SQLTxt from #T)
exec (@.FinalSQL)
drop table #T

Creating relationships between 2 databases

Is it possible to create relationships and enforce constrains thereof between two fields with the same data type from 2 different databases ( i.e. 2 different .mdf files ) altogether, attached to an instance of SQL Server 2005 Express ?
This would help overcome the limitation of the 4GB database size it imposes, by splitting the info required into different databases, rather than just different tables. All the relationships could be then created and a program could be created using Visual Basic or C# or C++ to access all the data meaningfully.
I also say this, because in a scenario like where some common piece of data, like contact info, has to be shared for different purposes, which really require two different databases, and the 2 databases cannot be integrated into one, but have to stay separate, would lead to duplication of data, where the same contact info would have to be entered and stored twice. Now, instead, if we create three databases in all, with the third one being the contact info database, we can have the 2 original databases drawing all their required info from the contact database, there need not be any duplication of data. Actually, this is the very purpose of the RDBMS model, isn't it, to reduce unneccesary replicaion ?
So, is there any way to do it, because I tried creating relationships between two tables from 2 different databases in SQL Server Management Studio Express, and I was unable to find any option that would allow me to do the same. I just use the GUI tools in it, not T-SQL.

Mind you, you are speaking to a rookie here, who has absolutely no programming knowlwdge. I have just migrated here from MS Access, and I currently am using Managemnt Studio Express and Visual Basic 2005 Express and I just know how to use the GUI tools in all these programs. I do not know any T-SQL or where to issue T-SQL commands from.

? No, it is not possible. If your only goal is to get around the 4gb limit, then my suggestion is that you upgrade to Workgroup Edition, which has no such limitation. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:d2141cdb-833b-49ba-8bc4-98feb1cd8a83@.discussions.microsoft.com... Is it possible to create relationships and enforce constrains thereof between two fields with the same data type from 2 different databases ( i.e. 2 different .mdf files ) altogether, attached to an instance of SQL Server 2005 Express ?This would help overcome the limitation of the 4GB database size it imposes, by splitting the info required into different databases, rather than just different tables. All the relationships could be then created and a program could be created using Visual Basic or C# or C++ to access all the data meaningfully. I also say this, because in a scenario like where some common piece of data, like contact info, has to be shared for different purposes, which really require two different databases, and the 2 databases cannot be integrated into one, but have to stay separate, would lead to duplication of data, where the same contact info would have to be entered and stored twice. Now, instead, if we create three databases in all, with the third one being the contact info database, we can have the 2 original databases drawing all their required info from the contact database, there need not be any duplication of data. Actually, this is the very purpose of the RDBMS model, isn't it, to reduce unneccesary replicaion ?So, is there any way to do it, because I tried creating relationships between two tables from 2 different databases in SQL Server Management Studio Express, and I was unable to find any option that would allow me to do the same. I just use the GUI tools in it, not T-SQL. Mind you, you are speaking to a rookie here, who has absolutely no programming knowlwdge. I have just migrated here from MS Access, and I currently am using Managemnt Studio Express and Visual Basic 2005 Express and I just know how to use the GUI tools in all these programs. I do not know any T-SQL or where to issue T-SQL commands from.|||

No NNTP User,

my only goal is not to get around the 4GB limit. As I mentioned in the later part of my post, I also want to 'borrow' the data from one completely different database in another database. I want to eliminate duplication of data in the 2 separate databases by borrowing common data from one db. That's the main reason I wanted to do that.

Can you think of any workaround ?

|||? You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:7915ff66-3526-4b90-aea2-8762ed87d8dc@.discussions.microsoft.com... No NNTP User, my only goal is not to get around the 4GB limit. As I mentioned in the later part of my post, I also want to 'borrow' the data from one completely different database in another database. I want to eliminate duplication of data in the 2 separate databases by borrowing common data from one db. That's the main reason I wanted to do that. Can you think of any workaround ?|||To add on to NNTP User, you can also wrap them in distributed transactions. Just make sure the design of one table in one database is appropriate for "virtually" creating a relationship between the other table on the other database.|||

You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution.

I'm afraid I've lost you guys, I think I really need to go and learn some T-SQL first, as somebody else suggested to me a while back. Meanwhile, could you please give me the above directions again using the GUI tools of Management Studio Express and VB Express ? Also, what are 'distributed transactions', bass_player ? And how do I use DML triggers, NNTP user ? Isn't DML short for Data Markup Language or something ?

|||? Actually, you only need distributed transactions if you're going across servers or instances. Cross-database work in the same instance will not need to make use of a distributed transaction. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <bass_player@.discussions.microsoft.com> wrote in message news:793732c8-c600-43b9-8e1a-294100137ac6@.discussions.microsoft.com...To add on to NNTP User, you can also wrap them in distributed transactions. Just make sure the design of one table in one database is appropriate for "virtually" creating a relationship between the other table on the other database.|||? Unfortunately, you can't do most of this stuff via the UI. DML is Data Manipulation Language; DML triggers fire on DML events such as inserting, updating, or deleting data. I recommend that you pick up a good basic SQL book to start with. I'm not sure if there are any out for SQL Server 2005 yet, but you might just start with a SQL Server 2000 one. I've heard good things about "Murach's SQL For SQL Server" by Bryan Syverson. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:b6185a4a-9770-456b-86e3-feacd72df877@.discussions.microsoft.com... you can also wrap them in distributed transactions. You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution. I'm afraid I've lost you guys, I think I really need to go and learn some T-SQL first, as somebody else suggested to me a while back. Meanwhile, could you please give me the above directions again using the GUI tools of Management Studio Express and VB Express ? Also, what are 'distributed transactions', bass_player ? And how do I use DML triggers, NNTP user ? Isn't DML short for Data Markup Language or something ?|||

Rishi, it is not possible to enforce foreign key relationship across database. NNTP is right, you can use triggers. Triggers are programs which runs when there is a new rows created in a table. Or when there is a row deleted from a table. Or when a row is updated. That's DML trigger, there is something else called DDL trigger, which is a new feature in 2005, but let's not talk about this right now.
Compared to foreign key (FK), this DML trigger has a performance issue, it will make the insert (or delete or update) slower than usual. But it has advantages: it is more flexible than FK. A short answer to your question is that yes you can span across several databases of 4 GB each to create a larger system.
Vincent

|||

A short answer to your question is that yes you can span across several databases of 4 GB each to create a larger system.

But for that, I would have to write code, wouldn't I ? I mean, I cannot do all this stuff using the GUI ?

Anyway, I wouldn't want to implement a design which slows down the performance of the database much. Thanks for all the help guys.

Rishi.

|||

Say, is there anyway I can create the relationship in the front end application using VB Express ?

Because I really want to put all the info into one application. Its like having everything under one roof. A complete ERP solution ( for small proprietory concerns with no employees, not counting peons ). I have seen many organisations use different software for different purposes and I did not like the idea. For instance, they use different software for keeping track of their inventory, employee details (which covers employee attendance, performance, incentives etc.), financial records etc. Now for example, I have the inventory software, which has a separate table which stores employee names, and this is used so that the sales reports show the employee wise sales, but all the main employee information is stored in the database of another application altogether. Now, suppose I am the manager, and I am going through the sales by emp report, and I suddenly feel I want to know more details about the emp, so in this scenario, I would have to quit the inventory program, fire up the employee program, and then look up the employee's name, and then pull up the emp's info. Now wouldn't it be nice if I had both the databases linked in the front end application, so I could directly pull up the employee's info ? Why I don't want to put both these separate pieces of info in the same database is again performance, and I need to clarify this point - If I have both the inventory and the employee databases from the above example consolidated into a single database, would it slow down the performance of the database as it grows larger in size ? Or would it offer performance benefits if they are kept in separate dbs and are linked through the front end.

I know the idea given to me above about using DML triggers would considerably slow down the database, as the program would have to scan every record every time a record is created or updated or deleted. So I have scrapped the plan of using DML triggers ( after learning DML of course )

Any ideas about using the VB Express created front end application though ?

|||

>>I've heard good things about "Murach's SQL For SQL Server" by Bryan Syverson.

Anyone know if there's going to be a SQL Server 2005 version of this book? I just "discovered" it last night at B&N; it looks like a great book. However, there's nothing on Amazon.com nor the publisher's site about an upcoming revised edition.

Creating different New Fields Based on Calculation

Hi,
I am VERY new to SQL and I do not know if I am word this question correctly,
so forgive me.
I would like to create a new table that does calculations on some of the
same fields.
? IPA number (from the IPA_Num field)
? Total number of Rx's (sum of rx field )
? Total Paid( sum of amt_paid field)
? Total generic paid (If the field drugs = 1 then sum the amt_paid field)
? Percent generic (If the field drugs = 1 then sum and divide by the sum o
f
the field rx)
? Percent formulary (if the formulary field = A then sum and divide by th
e
rx field)
? Cost difference ( Average amount paid per brand Rx - average amount
paid per generic Rx )
Any help is greatly appreciated.
Blessing to all
YvonneHere is some info to get you started.
select [IPA number (from the IPA_Num field)]=IPA_Num,
[Total number of Rx's (sum of rx field )]=sum(rx),
[Total Paid( sum of amt_paid field)]=sum(amt_paid),
[Total generic paid (If the field drugs = 1 then sum the amt_paid
field)]=sum(case when drugs=1 then amt_paid else 0 end),
[Percent generic (If the field drugs = 1 then sum and divide by the sum of
the field rx)]=sum(case when drugs=1 then amt_paid else 0 end)/sum(rx),
[Percent formulary (if the formulary field = A then sum and divide by the
rx field)]=sum(case when formulary='A' then amt_paid else 0 end)/sum(rx),
[Cost difference ( Average amount paid per brand Rx - average amount
paid per generic Rx )]=Avg(rx)-Avg(case when drugs=1 then amt_paid else 0
end)
from your_table
group by IPA_Num
Btw, I suggest you post DDL (create table) + sample data (insert) + expected
output here. It would be much easier for us to help you.
-oj
"HasanaMonique" <HasanaMonique@.discussions.microsoft.com> wrote in message
news:2D71E579-4920-4366-A09F-A66013FCE824@.microsoft.com...
> Hi,
> I am VERY new to SQL and I do not know if I am word this question
> correctly,
> so forgive me.
> I would like to create a new table that does calculations on some of the
> same fields.
> . IPA number (from the IPA_Num field)
> . Total number of Rx's (sum of rx field )
> . Total Paid( sum of amt_paid field)
> . Total generic paid (If the field drugs = 1 then sum the amt_paid field)
> . Percent generic (If the field drugs = 1 then sum and divide by the sum
> of
> the field rx)
> . Percent formulary (if the formulary field = A then sum and divide by
> the
> rx field)
> . Cost difference ( Average amount paid per brand Rx - average amount
> paid per generic Rx )
> Any help is greatly appreciated.
> Blessing to all
> Yvonne
>|||Thank you. I am truly grateful for your help
"oj" wrote:

> Here is some info to get you started.
> select [IPA number (from the IPA_Num field)]=IPA_Num,
> [Total number of Rx's (sum of rx field )]=sum(rx),
> [Total Paid( sum of amt_paid field)]=sum(amt_paid),
> [Total generic paid (If the field drugs = 1 then sum the amt_paid
> field)]=sum(case when drugs=1 then amt_paid else 0 end),
> [Percent generic (If the field drugs = 1 then sum and divide by the sum of
> the field rx)]=sum(case when drugs=1 then amt_paid else 0 end)/sum(rx),
> [Percent formulary (if the formulary field = A then sum and divide by the
> rx field)]=sum(case when formulary='A' then amt_paid else 0 end)/sum(rx),
> [Cost difference ( Average amount paid per brand Rx - average amount
> paid per generic Rx )]=Avg(rx)-Avg(case when drugs=1 then amt_paid else 0
> end)
> from your_table
> group by IPA_Num
> Btw, I suggest you post DDL (create table) + sample data (insert) + expect
ed
> output here. It would be much easier for us to help you.
>
> --
> -oj
>
> "HasanaMonique" <HasanaMonique@.discussions.microsoft.com> wrote in message
> news:2D71E579-4920-4366-A09F-A66013FCE824@.microsoft.com...
>
>

creating data for a histogram.

I have a table, TableA with amongst other fields, a field for Qty.
Qty can range from 0 to 100.
How do I count the number of rows with a qty between 1 and 10, 11 and
20, 21 and 30, and so on using one SQL statement?

Regards,
Ciarn(chudson007@.hotmail.com) writes:
> I have a table, TableA with amongst other fields, a field for Qty.
> Qty can range from 0 to 100.
> How do I count the number of rows with a qty between 1 and 10, 11 and
> 20, 21 and 30, and so on using one SQL statement?

SELECT qty10, COUNT(*)
FROM (SELECT qty10 = ((qty - 1) / 10) * 10 + 1
FROM tbl) AS ds
GROUP BY qty10

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That seems to just count the number of times each qty appears, just
like

SELECT qty AS Expr1, COUNT(*) AS Expr2
FROM [Temp]
GROUP BY qty

How should I change it so that it counts the the number of qtys between
each range of 10?

Regards,
Ciarn|||Hi Erland Sommarskog ,

You Always give helpfull and informative answers.
I changed the query a bit to show the LowRange as well HiRange
SELECT LowRange,HiRange,COUNT(*)
FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
,HiRange=((qty - 1) / 10) * 10 + 10
FROM sales) AS ds
GROUP BY lowRange ,HiRange
but I am facing a problem can You guide me on this

This query {select q=qty+10 from sales order by q} works but
{select q=qty+10 from sales group by q} does not work .SQL Server2000
is not recognising Aliased Columns in second case .
---
With regards
Jatinder Singh (System Analyst )|||(chudson007@.hotmail.com) writes:
> That seems to just count the number of times each qty appears, just
> like
> SELECT qty AS Expr1, COUNT(*) AS Expr2
> FROM [Temp]
> GROUP BY qty
>
> How should I change it so that it counts the the number of qtys between
> each range of 10?

The query I posted was:

SELECT qty10, COUNT(*)
FROM (SELECT qty10 = ((qty - 1) / 10) * 10 + 1
FROM tbl) AS ds
GROUP BY qty10

I would expect to give the desired result, assuming that qty is integer.
If qty is float or decimal, it will indeed just be a roundabout way to
count single qtys.

I will have to admit that I did not test my query, but there is standard
recommendation that posting asking for help with queries should include:

o CREATE TABLE statement for your table(s).
o INSERT statement with sample data.
o The desired output given the sample data.

This makes it very easy for me or anyone else who anser to cut and paste
into Query Analyzer and test whatever we post.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> You Always give helpfull and informative answers.
> I changed the query a bit to show the LowRange as well HiRange
> SELECT LowRange,HiRange,COUNT(*)
> FROM (SELECT lowRange = ((qty - 1) / 10) * 10 + 1
> ,HiRange=((qty - 1) / 10) * 10 + 10
> FROM sales) AS ds
> GROUP BY lowRange ,HiRange
> but I am facing a problem can You guide me on this
> This query {select q=qty+10 from sales order by q} works but
> {select q=qty+10 from sales group by q} does not work .SQL Server2000
> is not recognising Aliased Columns in second case .

Correct. I believe that Access does this, but that's not in alignment with
the SQL standards.

Instead, the technique to use is a derived table as a above.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
I am facing another problem of displaying a summarised data along with
the detail data
i.e

Item Qty

Item1 10
Item1 10
Item1 20
40 ( Sum for Item1)
and so on .........

I wish to have a single query which runs on all RDBMS . Is it possible
?

USE pubs
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type

This Query works but it would work on MS SQLServer .

---------------
With regards
Jatinder Singh (System Analyst )|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I am facing another problem of displaying a summarised data along with
> the detail data
> i.e
> Item Qty
> Item1 10
> Item1 10
> Item1 20
> 40 ( Sum for Item1)
> and so on .........
> I wish to have a single query which runs on all RDBMS . Is it possible
> ?
> USE pubs
> SELECT type, price, advance
> FROM titles
> ORDER BY type
> COMPUTE SUM(price), SUM(advance) BY type
> This Query works but it would work on MS SQLServer .

Here is a query which I believe should be fairly portable. (But since
I only work with SQL Server, I can make no warranties):

SELECT type, x = '', price, advance
FROM titles
UNION
SELECT type, 'Total', SUM(price), SUM(advance)
FROM titles
GROUP BY type
ORDER BY type, x

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
Erland Sommarskog wrote:
> jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> > I am facing another problem of displaying a summarised data along
with
> > the detail data
> > i.e
> > Item Qty
> > Item1 10
> > Item1 10
> > Item1 20
> > 40 ( Sum for Item1)
> > and so on .........
> > I wish to have a single query which runs on all RDBMS . Is it
possible
> > ?
> > USE pubs
> > SELECT type, price, advance
> > FROM titles
> > ORDER BY type
> > COMPUTE SUM(price), SUM(advance) BY type
> > This Query works but it would work on MS SQLServer .
> Here is a query which I believe should be fairly portable. (But since
> I only work with SQL Server, I can make no warranties):
> SELECT type, x = '', price, advance
> FROM titles
> UNION
> SELECT type, 'Total', SUM(price), SUM(advance)
> FROM titles
> GROUP BY type
> ORDER BY type, x
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Hi Erland,
Thanks ,I think it will work on any RDBMS . Your analysis ablity is
really something. I cannot describe it in words.

Thanks Again
With warm regards
Jatinder Singh (System Analyst)|||Hi Erland,

Can we similarly replace CUBE operator in SQL Server by using simple
queries that will run on any RDBMS?

With warm regards
Jatinder Singh (System Analyst)|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> Can we similarly replace CUBE operator in SQL Server by using simple
> queries that will run on any RDBMS?

I don't use CUBE very often, so I may miss some fine detail. But the
two queries below returns the same result:

SELECT type, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY type, pub_id WITH CUBE
ORDER BY type, pub_id

SELECT type, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY type, pub_id
UNION
SELECT type, NULL, SUM(price), SUM(advance)
FROM titles
GROUP BY type
UNION
SELECT NULL, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY pub_id
UNION
SELECT NULL, NULL, SUM(price), SUM(advance)
FROM titles
ORDER BY type, pub_id

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland
I am bit confused by the output produced by the Query Analyzer and
finding it bit difficult to decide which one of the following query is
faster.

In query one I am using Correlated subquery Approach and it consumes
78% of batch time when run with 2nd query but time of mere 20
micrseconds

In query two I am using functions (these functions berely takes
PolicyNumber and Endrosment No to give output and does the same query )
Approach and it consumes 22% of batch time when run with 1st query but
time of 400 micrseconds

Query 1:-
select RowId,PolicyNumber,EndoNumber,status,SF,case when SubQ is Null
then 'No' else 'Yes' end as Lock,RCount from
(
SELECT RowId,PolicyNumber,EndoNumber,status,SF,
(select status from InProcessData052005MstM WHERE status in ('pen') and
IP.PolicyNumber=PolicyNumber and IP.EndoNumber=EndoNumber) as subQ,
(select count(*) from InProcessData052005MstM WHERE status in
('pen','cur') and IP.PolicyNumber=PolicyNumber and
IP.EndoNumber=EndoNumber) as RCount
--,case when SubQ is Null then 'No' else 'Yes' end as Lock
FROM InProcessData052005MstM IP
WHERE status in ('cur','pen')
) X
ORDER BY PolicyNumber

select getdate()

SELECT
RowId,PolicyNumber,EndoNumber,status,SF,dbo.fnTryG etPolicyCount(PolicyNumber,EndoNumber)
as RCount,
dbo.fnTryGetPolicyLock(PolicyNumber,EndoNumber) as Lock
--(select status from InProcessData052005MstM WHERE status in ('pen')
and IP.PolicyNumber=PolicyNumber and IP.EndoNumber=EndoNumber) as subQ,
--(select count(*) from InProcessData052005MstM WHERE status in
('pen','cur') and IP.PolicyNumber=PolicyNumber and
IP.EndoNumber=EndoNumber) as RCount
--,case when SubQ is Null then 'No' else 'Yes' end as Lock
FROM InProcessData052005MstM
WHERE status in ('cur','pen')
order by PolicyNumber
select getdate()

create function fnTryGetPolicyCount(@.p varchar(16),@.e varchar(3))
returns int
as
begin
return (select count(*) from InProcessData052005MstM WHERE status in
('pen','cur') and PolicyNumber=@.p and EndoNumber=@.e)
end

create function fnTryGetPolicyLock(@.p varchar(16),@.e varchar(3))
returns varchar(3)
as
begin
declare @.Lock varchar(3)
select @.Lock=status from InProcessData052005MstM WHERE status in
('pen') and PolicyNumber=@.p and EndoNumber=@.E
if @.Lock is null
set @.Lock='No'
else
set @.Lock='Yes'

return (@.Lock)
--(select status from InProcessData052005MstM WHERE status in ('pen')
and PolicyNumber=@.p and EndoNumber=@.E)
end

------------------
Jatinder|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I am bit confused by the output produced by the Query Analyzer and
> finding it bit difficult to decide which one of the following query is
> faster.
> In query one I am using Correlated subquery Approach and it consumes
> 78% of batch time when run with 2nd query but time of mere 20
> micrseconds
>
> In query two I am using functions (these functions berely takes
> PolicyNumber and Endrosment No to give output and does the same query )
> Approach and it consumes 22% of batch time when run with 1st query but
> time of 400 micrseconds

The difference in estimate may be because the function is not considered.

Anyway, the one way to benchmark queries is this:

DECLARE @.d datetime, @.tookms int
SELECT @.d = getdate()
-- run query here
SELECT @.tookms = datediff(ms, @.d, getdate())
PRINT 'This query took ' + ltrim(str(@.tookms) + ' ms to run.'

You need to consider the effect of the cache. If the two queries operates
on the same data, the easiest may be to run the queries several times
and discard the first result. You can also run DBCC DROPCLEANBUFFERS to
clean the cache, but that affects the entire server.

Also, beware that datetime has a resolution of 3.33 ms. For the
measurement method above, I have never seen any value between 0 and
13 ms. I consider values below 50 ms to be too inaccurate to be
taken as a significant. 400 ms is certainly significant.

Note: above you talk "microseconds". I assume this is a typo for
"milliseconds".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Thanks for your answer and time.
Sorry for the typo error.
I can be wrong in my assumption buut isn't it that the two queries are
working in simliar fashion. Both are taking a value(or two) passing it
to inner corelated query (funtion) and getting the result.

Waiting for your reply.

Jatinder|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> Thanks for your answer and time.
> Sorry for the typo error.
> I can be wrong in my assumption buut isn't it that the two queries are
> working in simliar fashion. Both are taking a value(or two) passing it
> to inner corelated query (funtion) and getting the result.

Just because two queries logically are the same, that does not mean that
performance is. There is quite some overhead with calls to saclar user-
defined functions. Also, when you stuff a subquery into a scalar function,
all the optimizer sees is a call, it does not see the contents of rhe
UDF, so it cannot take any shortcuts.

Table-valued functions are different. Particularly inline functions. Table-
valued inline functions are really just macros, and the query text is
pasted into the query, so the optimizer can rearrange as it likes.

As for the estimates you saw in Query Analyzer, they are just estimates, and
I would not pay too much attention on them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Thanks Again for your time .

Explaination is good. So may I consider that the UDF will always be
little slower because the Query Optimizer can never arrange it for
optimization. but using function make query more manageable

Please correct me if my assumption is wrong.

I have yet another question (query ) .
I have two tables
One empmast which store emp current designation
Other promotion table which store the promotions of an employee during
his service.It stores the information of employee designation promotion
date.

Empmast(empid int primary key,desigid int references desigmast
,.........)
PromotionDtls(empid int references Empmast,promotatedTo int references
desigmast, promotedFrom int references Desigmast,DateOfPromotion
smalldatetime)

EmpMast
empid desigid (current designation of employee)
1 3 ........................
2 1 ..................

PromotionDtls
empid promotedTo PromotedFrom effectiveDate
1 2 1 1-jan-2003
1 3 2 2-dec-2003
..........

Now I wish to use the designation Id in a query
such that if the employee data exists in Promotion Table the promotedTo
should be picked according to Effectivedate
otherwise the Empmast designation
e.g If I say desigId of employee having empid 1 on date 2-jun-2003 then
it should be desigId 2
I did this using isnull but I wish to find a better method.

select isnull( ( select top 1 promotedTo from promotionDtls where
empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
, (select desigid from empmast where empid=1) )

It did give the result but looking for better method to solve this.

With regards
Jatinder Singh|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I have yet another question (query ) .

Sorry for not coming back to you earlier, but I had limited time for
some days to read the posts in the newsgroups, so I deferred the
difficult stuff until later.

A general advice is that it's better to post a new problem to a new
thread. Then other people might be more keen to answer it.

> I have two tables
> One empmast which store emp current designation
> Other promotion table which store the promotions of an employee during
> his service.It stores the information of employee designation promotion
> date.
> ...

I've now looked at the problem again, but I still could not really
understand what you are looking for. Since I don't like guessing, I
answer with the standard suggestion that you include:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

The first two makes it simple to copy and paste into Query Analyzer,
and the last makes it possible to actually produce a tested query, and
also helps to clarify what you are looking for.

It's not only that I'm lazy - neither do I like guessing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp