Site Sections
Posts
83
Comments
600
Trackbacks
40
<< How do I search a database for the umpteenth time | Home | West Orange Ridership Community >>
How to post a question for a database

Since, I'm tired of retyping this...If you want to get an answer fast about a database question (doesn't matter the platform) and it's sql related....Do this

Please state your problem in the context of a business requirement. Please do not force a narrowly focused technical solution, which may or may not be of any value.  It may also be a distraction to what the actual solution would be. To aid in the solution please do the following if possible

1. State the question

"How do I find the earliest row entered"

2. Please post the DDL of your tables (Including Indexes, and constraints)

Like

CREATE TABLE myTable99(Col1 int, Col2...

3. Post some sample data in the form of DML

Like

INSERT INTO myTable99(Col1, Col2, ect)
SELECT 1, 'x', ect UNION ALL
SELECT 1, 'x', ect UNION ALL
SELECT 1, 'x', ect UNION ALL

4.  Post whatever DML that you have attempted already...

SELECT * FROM myTable99 CROSS JOIN myTable99 [:D]

5. Post the expected results

Thank you, and come again.

 EDIT:  Please post your question in the appropriate category here at SQLTeam

And don't forget to use [ code] [ /code] tags when posting code, just eliminate the spaces I have used here.


EDIT2: A FAQ Section

Q: How do I create DDL as requested in step 2?

A:

1. Go to Enterprise Manager.

2. Open the database folder to display all of the tables.

3. Right Click on the table you want.

4. Choose Menu options All Tasks>Generate SQL Scripts

5. Look at the dialog, there are three tabs. Make sure you pick all the correct options (indexes, keys, ect)

6. Click Preview.

7. Copy and paste the code.

posted on Wednesday, May 25, 2005 4:37 PM Print
Comments
# re: How to post a question for a database
ClaesW(rockmoose)
5/26/2005 3:10 PM
Great!!!
Even I could understand that instruction.
# re: How to post a question for a database
Joe Momma
5/26/2005 4:36 PM
I still dont get it.
WHY DOES EVERYONE MAKE EVERYTHING SO COMPLICATED.

# a Joke?
Brett (Not just a Number...huh?)
5/31/2005 9:21 AM
Joe, that's a joke...right?
# Stored Procedures
Shereen
6/24/2005 1:18 PM
i am using the following SQL statement repeatedly in the MS SQL databse. Therefore, i was wondering if i should store it as a trigger or a storedprocedure and reuse it everytime i need it. I am new stored procedures, so i do not know how to what i need to... Can you help me out...

What i need is store the following "Delete" SQL,
CREATE PROCEDURE [dbo].[CleanPhonesDraftTbl] AS
delete FROM PhonesDraft
WHERE EXISTS
(select *
from phones
where phones.phone = PhonesDraft.phone);

Can you write the storedprocedure for me please?Thanks
you can also email me at batarsehs@hotmail.com
# re: How to post a question for a database
Ashok
7/6/2005 3:24 AM
can anyone suggest the books are dumps to attempt sqlserver 70-229 exam
# Extract social secutriy numbers
Benzonafunk
7/7/2005 10:47 AM
I need to extract some SSN's. They are nested in other characters, but the pattern is ___-__-____. I can't seem to get rid of stuff on the left and right when i use PATINDEX.
# re: How to post a question for a database
Brian
8/9/2005 3:21 PM
I'm trying to do some calculations from one set of numbers from another. The column names are Broker Price, our Price and Savings. Application fee and Processing fee's are only categories. The cell names would be AFBP= Application fee and Broker price for example. AFBP would be a text box on a form. subtracting from AFOP= Application fee and Our Price. With the total of APBP-AFOP = AFsav (application Fee savings) I hope this helps. Had to put dashes in so you can see what I'm trying to do. Thanks, TIA

category------------Broker-price----Our price---savings

Application-fee-------250------------175---------75
processing-fee---------50-------------30---------20

Total---------------- 300----------- 205---------95
# Want to subtract two difeferent types of data
Brian
8/9/2005 5:11 PM
I want to subtract two different numbers to get a total and a grand total from each column.

Create Table Savings(ID int, afbc money, afoc money, afsav money, ofbc money, ofoc money, ofsav money etc...

stored Procedure
AS Select ID, sum(afbc)- sum(afoc) as ofsav,
sum(ofbc)- sum(ofoc) as ofsav,
afbc, afoc, ofbc, ofoc
from savings

order by ID

(I also want to add afbc and ofb; ofbc and ofoc; afsav and ofsav to give a total in each column.
# re: How to post a question for a database
Xerxes
8/24/2005 11:23 AM
Huh?
# re: How to post a question for a database
Xerxes from SQLTeam
8/29/2005 11:52 AM
Brett,

The proper abbreviation for etcetera is 'etc.' not 'ect' which you have in your example.

Xerx...
# Stored Proc - jay_cee_25
jay_cee_25
9/14/2005 10:33 PM
1. Please post the DDL of your tables (Including Indexes, and constraints)

Tables:
TacDatabaseHist:
(TranSource,TranType,TranDate,TranTime,Msgtype,TraceNo,PAN,BIN,Amount,MerchantID,TerminalID,ResponseCde,Visa1Description,NII,BIN2,TranDesc)

LocalTerminalID:
(SerialNo,TerminalID,TranDate,MerchantName,Addres1,Address2,Address3)

tmpTransPerTerminal:
(N3000,N2001,N1001,N501,N251,N101,N1,N0 Total)

CREATE PROCEDURE SP_TransPerTerminal

@pMonthFrom nchar(8),
@pMonthTo nchar(8),
@pError integer OUTPUT

AS

DECLARE
@vSerialNo nchar (10),
@vSum numeric(9),
@vTOTAL numeric(9)


2. Post some sample data in the form of DML

INSERT INTO tmpTransPerTerminal(N3000,N2001,N1001,N501,N251,N101,N1,N0,TOTAL)
VALUES (0,0,0,0,0,0,0,0,0)

UPDATE tmpTransPerTerminal
SET TOTAL =(SELECT COUNT(TacDatabaseHist.TerminalID) FROM TacDatabaseHist INNER JOIN LocalTerminalID
ON TacDatabaseHist.TerminalID= LocalTerminalID.TerminalID
WHERE left(TacDatabaseHist.Trandate,6) BETWEEN @pMonthFrom AND @pMonthTo)
***this set total gets the total of all the terminals that had a transaction.

SET @vSum = (SELECT COUNT(TacDatabaseHist.TerminalID) FROM TacDatabaseHist INNER JOIN LocalTerminalID
ON TacDatabaseHist.TerminalID= LocalTerminalID.TerminalID
WHERE left(TacDatabaseHist.Trandate,6) BETWEEN @pMonthFrom AND @pMonthTo AND LocalTerminalID.SerialNo = @vSerialNo)
***this set @vSum gets the total number of transactions per terminal and groups it if it has 100++ transactions it goes to the group N101. Then another terminal will be analyze until all of the terminals are analyze.

3. Post whatever DML that you have attempted already...

NA

4. Post the expected results

N3000 - no. of terminals that have 3000++ transactions in a single month(it goes the same with the other N...)
N2001
N1001
N501
N251
N101
N1
N0
TOTAL - total number of all terminals for that month.
# re: How to post a question for a database
FAIZAL
9/20/2005 5:55 AM
how to export data from excel sheet to sql sever
# re: Substring Searching
syscomments
10/5/2005 9:34 AM
# re: How to post a question for a database
Pinto
11/4/2005 4:28 AM
If I follow the instuctions in
EDIT2: A FAQ Section

Q: How do I create DDL as requested in step 2?

A:

1. Go to Enterprise Manager.

2. Open the database folder to display all of the tables.

3. Right Click on the table you want.

4. Choose Menu options All Tasks>Generate SQL Scripts **** I don't see this at all


# re: How to post a question for a database
Brett
11/4/2005 9:01 AM
You don't?

What version of SQL Server are you using?

# find the number of days corresponding to a date
CynthiaSahm
11/15/2005 1:06 PM
I'd like to know if there is an sql function that converts a date to the number of days from some given time to that date.
# re: How to post a question for a database
David A
1/31/2006 5:19 PM
Thanks but That query generated just values. I want to see only duplicates in different duplicates of id in various states

This is what the current code that I am using looks like

SELECT TOP 100 PERCENT State, [Debt Number], ID, [Last Name], [First Name]
FROM dadedeji.[Extract File]
WHERE (ID IN
(SELECT [ID]
FROM dadedeji.[Extract File] AS Tmp
GROUP BY [ID]
HAVING COUNT(*) > 1))AND (State<>State)
ORDER BY SSN, State
# QUERY TO CHANGE THE VARCHAR FORMAT TO DATA DATA TYPE
GANESH
2/14/2006 2:13 AM
HOW TO CHANGE THE VARCHAR DATATYPE TO DATE DATA TYPE TO RUN A QUERY TO GET THE ROWS BETWEEN TWO DATES I TRIED BUT IT IS GIVING OUT OF RANGE VALUE PLS HELP ME IN THIS ISSUE
# QUERY TO CHANGE THE VARCHAR FORMAT TO DATA DATA TYPE
GANESH
2/14/2006 2:14 AM
HOW TO CHANGE THE VARCHAR DATATYPE TO DATE DATA TYPE TO RUN A QUERY TO GET THE ROWS BETWEEN TWO DATES I TRIED BUT IT IS GIVING OUT OF RANGE VALUE PLS HELP ME IN THIS ISSUE
# re: How to Add Months in to Milliseconds Value
Ganesh
2/20/2006 6:30 AM
My Table having one Field with values like "1130387557". Data type is Int. ( This is actually Date column). I want to add months with above mentioned value. I am doing integration with third party tool
# how i join two char values into one char and store it in the same table using Trigger
mohamed
4/15/2006 9:11 AM
as soon as p
# re: How to post a question for a database
Tammy
5/11/2006 3:25 PM
Using query analyzer, i am pulling values from table to create a flat file. Dates are appearing as 4/1/1999. In order to import them i need them to be formated as 1999-04-01. What would be the syntax to get this format to be displayed in the query analyzer results window?

thanks
# help to solve this query
chiragvm
6/9/2006 1:06 AM
heir are onre table = parametter
fields = currentuse, timestamps, paramettername

in this table every five minut data are dump from my application

now
i want to a 3 collum of current use like

select currentuse as cur1 , currentuse as cur2, currentuse as cur3 from parametter

i define my problem in last my tow note

i write this query
please solve it

SELECT PARAMETER.CurrentUse, PARAMETER_1.CurrentUse AS Expr1, PARAMETER_2.CurrentUse AS Expr2
FROM PARAMETER INNER JOIN
PARAMETER PARAMETER_1 ON PARAMETER.ServerName = PARAMETER_1.ServerName AND PARAMETER.Device_id = PARAMETER_1.Device_id AND
PARAMETER.AGENT_ID = PARAMETER_1.AGENT_ID INNER JOIN
PARAMETER PARAMETER_2 ON PARAMETER.ServerName = PARAMETER_2.ServerName AND PARAMETER.Device_id = PARAMETER_2.Device_id AND
PARAMETER.AGENT_ID = PARAMETER_2.AGENT_ID
WHERE (PARAMETER.ServerName = N'SUN') AND (PARAMETER.AGENT_ID = N'007365-1878') AND (PARAMETER.CurrentUse =
(SELECT currentuse
FROM parameter
WHERE parameter.timestamps = '2006-05-01 18:29:36.000')) AND (PARAMETER_1.CurrentUse =
(SELECT currentuse
FROM parameter
WHERE parameter_1.timestamps = 2006 - 05 - '01 18:34:51.000)
')) AND (PARAMETER_2.CurrentUse =
(SELECT currentuse
FROM parameter
WHERE parameter_2.timestamps = 2006 - 05 - '01 18:40:04.000)
0'))
# How do I create a table with data from a query
Julie W.
6/21/2006 6:27 PM
I have a database named Grow, with a table named Variety. I concatenated 3 columns from this table to make one column. I also tied in the VarietyID. How do I get the results from this query saved in a new table named Herbs

Use Grow
Create Table Herbs (VarietyID int, CommonNameFirst nvarchar(50) not Null, LatinNameFirst nvarchar (50) not Null)

Insert into Herbs values (ID, CommonName, LatinName)
select VarietyID as 'ID' VarietyName + ', ' + Family + ' ' + Genus as 'CommonName', Family + ' ' + Genus + ', ' + VarietyName as 'LatinName'
from Variety where Category = 'Herbs'

I need the data that is returned from the query saved in the new table Herbs.
# Data Type s
Oliver
6/27/2006 11:00 PM
Whats the equivalent Data Type Access MEMO in SQL Server?
# re: How to post a question for a database
watcha
7/9/2006 7:57 AM
Hi,
I am running SQL SERVER 2000 on XP Prof.A server and a note book are connected wireless on ad-hoc.Both machines see each other.but I can't get to connect to the SQL.or configure the odbc.Other Machines connected directly via lan to the server can connectto the SQL.

Thanks
# re: How to post a question for a database
Peter Larsson
7/20/2006 4:30 PM
This is really important. But I think you have to do a new blog with "the other side".

We who supply help and aid beginners must also test our own solutions before posting them. The solution might not be the one the original poster asked for due the misinterpretation, but nevertheless must the solution work.
# re: How to post a question for a database
Shybi
8/25/2006 2:36 AM
i have 5 tables as given below.i want to write an update statemnt with the fields UserID,FirstName,LastName,Location,Deot,Groups,Company.

table: Users
field:UserID(PK),FirstName(FK),LastName(FK),DeptID(FK),LocationID(FK),CompanyID(FK),GroupID(FK)

table: DeptDetail
Field: DeptID(PK),Dept

table: LocationDetail
Field: LocationID(PK),Location

Table:CompanyDetail
Field:CompanyID(PK),Company

Table:GroupDetail
Field:GroupID(PK),Groups

how can i write it?

shybi
Comments have been closed on this topic.
Article Categories
Archives
Post Categories
Image Galleries
Misc
SQL Server