Hi I'm new to SQL and I'm stuggling with a simple query in MS-server 2005 and wondered if anyone can help me.
I'm trying to devide the ansewrs of two seperate queries, but both the queries use the same coloumn in the where clause to get the answer. Please can some one help!
eg.
select Sum(column1)
where column1 = 'x'
devided by
select Sum(column1)
where column1 = 'y'
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
hi,
I have this simple sql query - it should be pretty obvious what I'm trying to achieve but this syntax isn't accepted on SQL 2005, any suggestions?
SELECT Name FROM Users WHERE UsersID IN (EXEC dbo.ReturnDataByModule 'Groups',1200)
I have a table like this:Name, SSNJoe Smith, 1111Tom Why, 2222Larry Sam, 3333Paul Tom, 4444Steve bob, 1111I want a query to pull offJoe Smith, 1111Steve bob, 1111because someone accidently put in two different names with the sameSSN. There should only be one 1111 in the SSN field in the wholedatabase. I want to pull the duplicate SSN with the name. How can onequery do this. I can write a VB program to do it but I think a queryshould work.I know how to do this:SELECT SSN, COUNT(*) AS cntFROM testGROUP BY fSSNHAVING (COUNT(*) > 1)to find the duplicate SSN but I need the name listed with the SSN also.Any help?? Thanks!!Sheila
View 3 Replies View RelatedFirst, let me apologize for how easy this probably is:DESCR TYPE SELL StartDate EndDate65048 04 Price A 4/21/2004 4/26/200465048 06 Price C 4/20/2004 4/27/200465048 08 Price B 4/22/2004 4/28/200465049 04 Price A 4/19/2004 4/24/200465049 06 Price B 4/22/2004 4/25/200465049 09 Price C 4/20/2004 4/29/200465050 07 Price A 4/21/2004 4/25/200465050 06 Price B 4/18/2004 4/28/200465050 05 Price C 4/17/2004 4/29/2004Descr, Type, Sell are CHARStartDate and EndDate are SmallDatetimeI need a simple query that would display the records with:Highest TYPE for each DESCR with:"Date I Enter" >= Startdate"Date I Enter" <= EnddateResults for ("Date I Enter" = 4/23/2004) should be:65048 08 Price B 4/22/2004 4/28/200465049 09 Price C 4/20/2004 4/29/200465050 07 Price A 4/21/2004 4/25/2004I would give you what I have done but it is such a mess I am better offstarting over.Thanks!!-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----http://www.newsfeeds.com - The #1 Newsgroup Service in the World!-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
View 3 Replies View RelatedHello everyone,
I hope you mind a questions from an inexperienced SQL 2000 user.
I don't have much experience with building queries for SQL2000. So I am lookinf for a software tool that will help me create simple queries. I'm also looking for an online (either on the web or a downloadable program) query reference to help remine me how to make these queries.
Thank you,
mike
I have two tables (tblRequest and tblDev) whose items have many-to-manyrelationships with each other. I have set things up like this(simplified):Table 1 fields: tblRequest.PrimaryKey tblRequest.Descriptionexample: 10 45 Elm11 63 Green12 123 Main Street13 23 PineTable 2 fields: tblDev.PrimaryKey tblDev.Descriptionexample: 20 Local. No issues21 City owned and main22 Out of county23 Not seen by officerThen Table 3 keeps track of the relationships between tables 1 and 2 bytracking the primary key of the linked items in tables 1 and 2.Table 3 fields: tblLink.RequestPrimaryKey tblLink.DevPrimaryKey10 2010 2111 2212 22Items from tables 1 and 2 may or may not have a relationship listed intable 3.So given an item in tblRequest:tblRequest.PrimaryKey tblRequest.Description12 123 Main StreetI need two queries...The first query would return ALL items from tblDev that are notassociated with this item in tblRequest - in this case 20, 21, 23.The second query would return ALL items from tblDev that ARE associatedwith this item in tblRequest - in this case 22.That's it. Thanks in advance
View 1 Replies View Relatedhi all,I have a table of customers.I have a table of products they have ordered.How can I find all customers who have ordered productA and productB atany time.It sounds so easy, but I can't quite get my head around it!thanksTim
View 7 Replies View RelatedHi,
Could someone confirm that the following query:
update table set x=1, y=1, z=1 where a = 1 or b = 1 or c = 1 or d = 1
is more efficient than this query:
update table set x=1, y=1, z=1 where e <> 1 and f <> 1 and g <> 1
Thanks!
Hi,
I have this query:
Code:
SELECT LB.WBS2, LB.WBS3, LedgerAR.WBS2 AS Expr1, LedgerAR.WBS3 AS Expr2, LB.AmtBud AS amtbud, SUM(LedgerAR.Amount * - 1) AS amt
FROM LB LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = LB.WBS1 AND LedgerAR.WBS2 = LB.WBS2 AND LedgerAR.WBS3 = LB.WBS3
WHERE (LB.WBS1 = '001-298')
GROUP BY LB.WBS2, LB.WBS3, LedgerAR.WBS2, LedgerAR.WBS3, LB.AmtBud
it produces the following output:
Code:
WBS2WBS3Expr1Expr2amtbudamt
014101014101300095
1217010121701080007290
12170804000
121709012170903200065960
121711012171101800034450
121712012171204400038010
12171402000
1217170121717013500935
12171804500
1217220500
12172601000
12175001217500800622.5
12221604000
I want to sum the amtbud column like I did for the amt column. and group everything based on WBS2. However, I keep getting an outrageous amount for the amtbud. This is what is seems to be summing up:
Code:
01410101410130000
014101014101300047.5
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
12170901217090320000
etc....
Any help will be appreciated I am just stumped.
I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl'
T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.
strSQL = strSQL & "ORDER.OS_T_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_T_Sort, "
strSQL = strSQL & "ORDER.OS_W_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_W_Sort, "
strSQL = strSQL & "ORDER.OS_L_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_L_Sort, "
I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl'
T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.
strSQL = strSQL & "ORDER.OS_T_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_T_Sort, "
strSQL = strSQL & "ORDER.OS_W_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_W_Sort, "
strSQL = strSQL & "ORDER.OS_L_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_L_Sort, "
How do I rewrite this query so that I do not use a temp table?
SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1
INTO #TEMP1
FROM SITECONTROLDATA
WHERE CALC_DATE > GETDATE() - 12
GROUP BY SITEID
SELECT * FROM #TEMP1 WHERE MINR1 = MAXR1
SQL Server 2000. Here's what I have so far. The section of the query I need help with is highlighted in blue.
CREATE PROCEDURE dbo.GetByVersion
(
@targetVersion varchar(30),
@product varchar(50)
)
AS
SET NOCOUNT ON;
SELECT * FROM MyTable
WHERE (product = @product)
AND
CASE
WHEN @targetVersion='' THEN (targetVersion='')
ELSE (targetVersion LIKE @targetVersion + '%')
END
GO
I get a syntax error in the Stored Procedure editor on an equal sign in this line:
WHEN @targetVersion='' THEN (targetVersion='')
What I want is this (in psuedocode):
if @targerVersion is blank
search for records where the targetVersion column = blank
else
search for records where the targetVersion column starts with @targetVersion
Can anyone offer any suggestions as to how I might modify my query to do what I want? Any help is very much welcome - Thanks in advance! :-)
I am looking for a simple example or guidance on how to move the results of a query from a mysql database to sql server 2005 using SSIS.
It seems rather difficult to do this or at least I am stuggling to figure it out. It was easy to do in DTS, how do you do it in SSIS?
Hi,
we've created a ata Flow task to execute several aggregations. Our Task access database using OLE DB source and selects data out of our staging tables (we've analyzed the query using MS SQL Management Studio which didn't showed any issues). But when we try to run our dataflow task using SSIS (debug mode and DTEXEC from command line) we experince that tasks seem to stop during processing.
Unfortunately we didn't found a way to see long logfile entries which explain the issue to us.
We do use several aggregation tasks divided in 4 sequences. Unfortunately we just see one logical processor out of 4 logical processors working. It is a Windows 2003 SP2 machine with SQL 2005 SP2 on top of it.
Is there any solution to use all processors to one package for parallel execution?
So basically we experience two issues:
- SSIS seems to stop somewhere in thre middle
- SSIS just uses one processor instaed of all four
your advice is appreciated
I'm banging my head against the wall. It's probably pretty simple so here goes...
I have a form that supposed to add records to a table on the database. I'm pretty sure I have the dataAdapter and Dataset set right but when I add a record, it complains that the primary key can’t be null. I’ve tried different things, including removing those fields and variables from the stored Proc, changing the Identity Properties of the Table, changing the AutoIncrement settings on the Dataset for that field, but all give me different errors. Nothing actually makes it work. This database has many records in it and the Primary key is a decimal field that starts at 5, then next was 10, then 28, then it started getting consecutive after 110. (111, 112, 113, etc.) (I'm porting over an old database to a new web site, so I'm somewhat contrained by the database.)
I guess my question is for adding records in to a database that has a primary unique key constraint. Certainly this is a pretty common thing. Obviously I do not want users to enter that in themselves, I thought it should simply autocreate/autonumber. So what is the best procedure and gotcha’s I need to watch out for?
if i have the following code, how do i know if the transaction was actually successful, so basically where in this code can i write TRANSACTION SUCCESFUL?
try
{
//Open up the connection
conn.Open();
//Setup the Transaction
trans = conn.BeginTransaction();
//First query in transaction
pre_query = "Delete from Menu where spec_name="
+ "'"
+ spec_name_array[i].Text.ToString()
+ "'" ;
//Second query in transaction
query = "INSERT into Menu VALUES ('"
+ Calendar1.SelectedDate
+"','"
+ spec_name_array[i].Text.ToString()
+"','"
+ spec_desc_array[i].Text.ToString()
+"','"
+spec_price_array[i].Text.ToString()
+"',1)";
SqlCommand comm = new SqlCommand(pre_query,conn);
//Setup the command to handle transaction
comm.Transaction = trans;
//Execute first query
comm.ExecuteNonQuery();
//Add in second query
comm.CommandText = query;
//Execute second query
comm.ExecuteNonQuery();
trans.Commit();
}
catch(SqlException ex)
{
com_label.Text = "Submission Not complete, did you forget the date or something else?";
//Undo all queries
trans.Rollback();
}
finally
{
conn.Close();
}
This may not be the right place for this post...but my head is hurting and I can't think right now.
I have a SQL table of users. Each record has a required 'Birthdate' field. I'd like to order this by the days left till the give birthday, keeping in mind that the year may be 1980 or their birthday might have been 2 weeks ago.
Can someone cure my headache?
I have written 2 custom connection mgr€™s.
One connects the data from an oracle source
One connects so I can put the data on a sql server.
These both work well and it makes it a lot easier across development, test, production.
But now I would like a good way to do this
I have 10 tables to copy from oracle. The only difference is just the name of the table.
static string[] g_tables =
{
"table_name_1",
"table_name_2",
"table_name_3",
€¦
};
foreach (string str_table in g_tables)
{
Oracle.DataAccess.Client.OracleCommand OracleCommand = new Oracle.DataAccess.Client.OracleCommand
(
"select * from " + str_table,
OracleConnection
);
Oracle.DataAccess.Client.OracleDataReader OracleDataReader =
OracleCommand.ExecuteReader();
// dump out to raw file destination
}
I understand there are some pieces left out but that is the basic idea. It seems like I€™m going to have write custom pieces for each step.
It does not seem like there is any advantage to using SSIS. Should I give up now and stick to writing windows services for stuff like this?
I could just write a specific piece for each table but the only difference is the table name.
I need to determine the top 20 most frequent occurences of a value in a specific field using SQL.
My table structure...
enddt datetime(8)
status varchar(20)
when enddt time equal to server time.
I want to change status field to expired.
What is the easy way to do this?.
enddt status
8/22/2005 7:00:00 PM expired
9/22/2005 8:30:00 PM expired
10/30/2005 7:30:00 PM live
11/22/2005 9:30:00 PM live
I completely forgot how to do this. I want one of my groups in the matrix to take its week number (1-7) as Monday-Sunday.
so it would kind of be like this:
=IIF(Fields!DayOfWeek.Value=2,"Mon",
ELSE(Fields!DayOfWeek.Value=3,"Tues",
Else(Fields!DayOfWeek.Value=4,"Wed",
etc etc
Am i doing this right, or am i off??
If I have a 64 bit version of SQL 2005 installed, will "select @@version" indicate it is a 64 bit version? If not, how do I determine such? Thanks in advance.
View 3 Replies View Related
I have a column in my report that i want to show percentage change from the previous year to this year. So i enter all kinds of different type of formats for the formula, and keep getting the wrong answers or error messeges. Here's what i have tried recently:
=SUM((Fields!LYTD_Amount.Value - Fields!YTD_Amount.Value) (Fields!YTD_Amount.Value*100))
Its pretty much Last year to date Minues Year to Date , Divided by Year to Date, Multiplied by 100.
any suggestions? Everytime i try to put an aggregate in the box, its always tricky.
What is the equivalent of Double for SQL Server?
View 1 Replies View RelatedOk - I am still a bit weak on SQL. My understanding of FK relationships in a database are to reinforce the data integrity - Correct? For example, does it make sure that the id given for SiteID does indeed exists. Can it link tables like JOIN in select statements? If not, is there any gains by creating them, such as performance?
View 1 Replies View Relatedscenario I have 2 tables one is called "Site" which contains columns ID, Address1, Address2,City,StateID,ZipCode and the second table is called "State" which contains columns ID,Name,Abbreviation. I am looking to make a view that contains all the sites with the state inner joined on stateid to make my all my other queries easier. That is so I can just select from the view without having to do the inner join on the state. My question is when a new record is added to table Site do I have "recreate"/update the view?
View 3 Replies View RelatedI am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.
This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how??? Any help would be awesome.
Sub Button1_Click(sender As Object, e As EventArgs)
???? = MyQueryMethod(txtPhone.Text)
Thanks,
Matt
I have a simple SQL table. The data looks like this:
products_ID----category----product_local
1----CORE----0
2----BASE----0
3----BRANCH----1
4----LEAF----3
I need to create a SQL statement that produces all category items where that ROW's products_ID is not found in ANY product_local in the table. So in the table above only the category BASE and LEAF would be printed because their products_ID are not found in any product_local in the table.
TIA as I am completely stuck!
OK, I have two cols of data:
COL1 COL2
test ing
tams ert
test pan
ted ted
hom jis
ted sam
tams dom
test ut
I need to simply pull one row of data from the given rows. Whats in COL2 is not relevant. So an example would be:
I want to retrieve a row(any row, but only one) where COL1 = 'test'
An ecceptable result woould be:
COL1 COL2
test ing
or
COL1 COL2
test pan
or
COL1 COL2
test ut
I would appreciate any help. This is driving me nuts. Seems like it would be easy.
TIA,
STue
Hi there,
I'm using bcp to import data into my database. the datetime format in the bcp file is ymd and my database has mdy datetime format. Is there any easy way to convert ymd into mdy when using bcp?
thanks a lot for your help!
Lena
Is there an easy way to update a Value field
for all records in One Table from a Value Field in another?
Both Tables have the same number of records.