First, let me apologize for how easy this probably is:
DESCR TYPE SELL StartDate EndDate
65048 04 Price A 4/21/2004 4/26/2004
65048 06 Price C 4/20/2004 4/27/2004
65048 08 Price B 4/22/2004 4/28/2004
65049 04 Price A 4/19/2004 4/24/2004
65049 06 Price B 4/22/2004 4/25/2004
65049 09 Price C 4/20/2004 4/29/2004
65050 07 Price A 4/21/2004 4/25/2004
65050 06 Price B 4/18/2004 4/28/2004
65050 05 Price C 4/17/2004 4/29/2004
Descr, Type, Sell are CHAR
StartDate and EndDate are SmallDatetime
I need a simple query that would display the records with:
Highest TYPE for each DESCR with:
"Date I Enter" >= Startdate
"Date I Enter" <= Enddate
Results for ("Date I Enter" = 4/23/2004) should be:
65048 08 Price B 4/22/2004 4/28/2004
65049 09 Price C 4/20/2004 4/29/2004
65050 07 Price A 4/21/2004 4/25/2004
I would give you what I have done but it is such a mess I am better off
starting 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! =-----
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)
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!
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
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.
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
hi 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
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
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:
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.
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.
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! :-)
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
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.
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.
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.
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:
Ok - 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?
scenario 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?
I 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)
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.
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
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?
