Auto Pick
Feb 21, 2007
hi all,
anyone has any idea in what i want to accomplish here :-
how can we sort the select statement, say, we want to compare integer inputqty=60.. how can we match the suitable sum of column qty to reach the 60 figure...
EG -> qty column : 50, 25, 15, 10
i)how can we prompt the select statemnet to choose 50 and 10 to make it 60...
ii)for another eg, say inputqty=90, then how can we prompt it to select 50 + 25 + 15 = 90
iii)and for some cases, if no round qty, the sum could be more, but the least from the qty list... eg : inputqty=80, how to automate the select statement to choose the closest figure, 50 + 25 + 10 = 85
i dunno if this is possible for any select or function or sp...
thankss....
~~~Focus on problem, not solution~~~
View 20 Replies
ADVERTISEMENT
Apr 28, 2006
Hi, I have a question that may seem simple to you, but I am having trouble locating the answer.
I want to install sp1 for sql server 2005. The directions tell me to select the correct link and give me three choices. The choices are differentiated with x86, x64, and IA.
I installed the 32 bit sql server 2005 on a window xp professional gateway personal computer. I'm just learning, so I did not involve my home network.
Which sp1 should I select?
Thanks.
View 3 Replies
View Related
Feb 16, 2014
Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:
%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1
However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.
I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:
%userprofile%AppDataLocalTemp
View 2 Replies
View Related
Apr 10, 2001
I need to randomly pick one or more records from a query e.g
select c_id, c_name
from c_table
where cat_id = 52
There may be more than one records for cat_id = 52. and I need to pick 3 of them randomly.
Thanks in advance!
View 3 Replies
View Related
Jun 25, 2002
I need a way to select records at random from a SQL Server 2000 table. If the record I've selected at random meets a few simple conditions (column X <> 'A' and column Y == 'Z') then return this record and get the next. Ideally I'd be able to do this in a stored proc where I'd call it with:
parameter 1: table name
parameter 2: number of random records to get
parameter 3: value for condition 1
parameter 4: value for condition 2
Any help will be greatly appreciated!
Patrick
View 4 Replies
View Related
May 18, 2014
I have the below data with two columns (Posting Date and Pond Crop, construct an SQL where i will pick and display only the TOP 3 Posting date of EACH Pond Crop. I only attached less data but in reality there are lots of Ponds and posting date in this table.
Table Name : Weekly Harvest
Fields:
1. Posting Date
2. PondCrop
Desired Output:
Posting Date PondCrop
2011-12-12 00:00:00.00001PA01-15
2011-12-19 00:00:00.00001PA01-15
2011-12-26 00:00:00.00001PA01-15
2012-03-19 00:00:00.00001PA01-16
2012-03-20 00:00:00.00001PA01-16
2012-03-26 00:00:00.00001PA01-16
Raw Data
Posting Date PondCrop
2011-11-21 00:00:00.00001PA01-15
2011-11-28 00:00:00.00001PA01-15
2011-12-02 00:00:00.00001PA01-15
2011-12-05 00:00:00.00001PA01-15
[Code] ....
View 7 Replies
View Related
Aug 9, 2013
I need to increment values
I have written this script execute this :
This is for this scenario
Insert Into #String SELECT 'S601-S630',1,'Desc1'
Drop table #IDs
CREATE TABLE #IDs(ID INT IDENTITY(1,1) ,String VARCHAR(100),Flag Int,Description VARCHAR(1000))
CREATE TABLE #string(ID INT IDENTITY(1,1) ,String VARCHAR(100),Falg Int,Descript VARCHAR(1000))
DECLARE @min INT ,@Max INT ,@String VARCHAR(50),@Start VARCHAR(50),@End VARCHAR(50),@Flag INT,@Desc VARCHAR(1000)
[Code] ....
How I need to increment if the input is like this
Insert Into #String SELECT '6S01-6S30',1,'Desc1'
if alphabet is in middle of the number??
View 3 Replies
View Related
Jun 27, 2006
Mostly we are using to get 100 or more record with Top operator, but I want to take specific row in between like
10 - 100 or 100 to 200 etc.
How I can pick it. plz give suggestion
View 3 Replies
View Related
May 5, 2008
I have a table as below:
user_id app_id form_id
1 a 1245
1 a 1345
1 b 1548
2 a 1245
2 a 1345
2 a 1411
2 b 1678
2 c 1245
3 b 1678
Now I want compare user 1 with user 2, and find out the records for user 2 where there are no app_id and from_id the same as user 1, so the output should be"
2 a 1411
2 b 1678
2 c 1245
Please advise how I can do this. Many thanks.
View 8 Replies
View Related
Dec 8, 2006
I have installed the MS SQL Server (Developer Edition) that comes with Visual Studuo 2005 Professional Edition into my computer. Seems like install is OK and I can see both SQLEXPRESS and MSSQLSERVER in configuration manager. I can start/stop both of the servers OK. However, when I try to make a connection from Visual Studuo 2005, I can only see SQLEXPRESS in the pick list in "Add Connection" window. SQLSERVER is not in the list.
The very first task I wanted to do is to convert and Access database to SQL. For that reason, I also installed "Microsoft SQL Server Migration Assistant for Access". In that application I cannot even see any server name when the wizard asks which server to choose. I also tried to start/stop "Sql Server Browser" service. When I stop the service, the pick list becomes empty and when I start the service the pick list has only SQLEXPRESS.
So, how can I see or pick MSSQLSERVER to make a connection in either applications? Please note I am VERY NEW to SQL Server and I appreciate your answers with that consideration.
Thans in advance
View 2 Replies
View Related
May 29, 2007
I have three input columns from my flat file, defined as follows:
Col_A
Col_B
Col_C
Now, what I want to do is pick the one that is populated, and put it into my table, in the column called
ABC_INPUT
I assume that I want to use a derived column, but how do I filter out columns that contain only NULL values?
Thanks!!
jim work
View 5 Replies
View Related
Jan 19, 2007
We have two tables (one has DTS import of external spreadsheet - the other is the online table)
The scenario is that I need to add items (from the first table) to the second table, when they don't exist. However, it's harder than that - the second table, in some fields has integers (ID field)), referring to another table (let's call it the 3rd table), but the first table has text (which is in the description, in the third table).
I have to run through the first table, row by row, checking the part number if it already exists in the second table - nothing happens, and it goes to the next line. In each line, if the part number does NOT exist, I need to insert the data in that row. BUT - remember that third table, for the item(s) in the first table with text, I need to dynamically query the third table, to get the ID, in order to complete the insert correctly (Select ID from Third Table where Description=@FirstTableFieldText)
I've never done anything like this, and I have no idea how to put this together - especially if it's all in one Stored Procedure. I need some help here on recommended ways to do it (and how)..... Even the base structure (pseudo code- whatever) would help
View 1 Replies
View Related
Jun 26, 2000
Please identify the "Sort Order" you picked to implement?
Dictionary Order, Case Insensitive
Dictionary Order, Case In-sensitive
etc.
Why did you choose this "Sort Order"
Thanks!!
View 2 Replies
View Related
Jul 20, 2005
Can anybody help on this question?When I select 3 values from the drop down list box, how can I insert into atable?Please help
View 3 Replies
View Related
Nov 17, 2011
I have a case where I need to pick the right data based on two dates and month.
Here's the sample of data:
Code:
cash_box_id | balance | validfrom | validto
-----------+---------+-----------------+------------------
cb003 | 950 | 30june2010 | 24may2011
cb003 | 900 | 25may2011 | 01Jan5999
cb005 | 800 | 11jan2011 | 01Jan5999
cb007 | 300 | 24Feb2011 | 10may2011
cb007 | 385 | 11may2011 | 22may2011
cb007 | 590 | 23may2011 | 28jun2011
cb007 | 700 | 29june2011 | 01Jan5999
...
I wanted to pick data that valid on 31 May 2011. So, expected output should be only two records as blue highlight.
Code:
cash_box_id | balance | validfrom | validto
-----------+---------+-----------------+------------------
cb003 | 950 | 30june2010 | 24may2011
cb003 | 900 | 25may2011 | 01Jan5999
cb005 | 800 | 11jan2011 | 01Jan5999
cb007 | 300 | 24Feb2011 | 10may2011
cb007 | 385 | 11may2011 | 22may2011
cb007 | 590 | 23may2011 | 28jun2011
cb007 | 700 | 29june2011 | 01Jan5999
...
They are thousand of records like this.
View 2 Replies
View Related
Jul 29, 2015
My case is I have customers with multiple ordering dates, I'll to run a query to pull only records within 2 months period based on the previous picking records. Ex is below:
create table #tmp_CusttInfo
(patID varchar(20),
enc_date datetime)
go
insert into #tmp_CustInfo
select '111','2015-01-01 09:25:05.000'
[Code] ...
I'd like to get:
CustID OrderDate
111 2015-01-01 09:25:05.000
111 2015-03-01 09:25:05.000
111 2015-05-01 09:25:05.000
222 2015-01-01 09:25:05.000
222 2015-03-01 09:25:05.000
222 2015-05-01 09:25:05.000
Is that possible.
View 6 Replies
View Related
Feb 21, 2014
This is my table:
ID AppName DepCode DepName Group ModifiedDate YearlyAmount
1 Nestle NS Foods Products 01/12/14 451
1 Nestle NS Foods Products 01/17/14 495
2 Oracle OR Software Info 01/24/14 279
2 Oracle OR Soft & IT Info 01/26/14 310
2 Oracle ORL Software Info 01/25/14 219
2 Oracle ORL Soft IT 01/28/14 600
MonthlyAmount Funded AppCategory Research
37.5623 Yes NE NA
41.2365 No N NA
23.2568 Yes OR InProgress
25.8333 Yes ORL NA
18.2189 Yes SOF Approved
50.0000 No IT RejectedExpected Output:
ID AppName DepCode DepName Group ModifiedDate YearlyAmount
1 Nestle NS Foods Products 01/17/14 946
2 Oracle OR Soft & IT Info 01/26/14 589
2 Oracle ORL Soft IT 01/28/14 819
MonthlyAmount Funded AppCategory Research
78.7988 No N NA
49.0901 Yes ORL NA
68.2189 No IT Rejected
I want to pick the recent modified date for DepCode and sum Yearly and Monthly Amount. I have tried this query and not able to get the output. This is the single table.
select B1.[ID], B1.[AppName], B1.[DepCode], B1.[DepName], B1.[Group],
B2.ModifiedDate, B2.YearlyAmount, B2.MonthlyAmount,
B1.[FuBded], B1.[AppCategory], B1.[Research]
FROM Business B1
INNER JOIN
(select [ID], MAX(ModifiedDate) as ModifiedDate, SUM(YearlyAmount) as YearlyAmount,
SUM(MonthlyAmount) as MonthlyAmount
from Business
Group by ID) B2
ON B1.ID = B2.ID AND B1.ModifiedDate = B2.ModifiedDate
View 1 Replies
View Related
Feb 27, 2007
I want to write a SSIS which picks up the source and destination tables at runtime is it possible. As we have a SSIS which is used to pull data from oracle but the source and destination table name changes.
View 5 Replies
View Related
Jan 23, 2004
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
Thanks, Justin.
View 3 Replies
View Related
Oct 31, 2006
Good afternoon SQL dudes Does anyone have any experience of extracting data from IBM's UniData (http://en.wikipedia.org/wiki/UniData) (or any post-relational Pick nested relational multi-valued relational database) into a SQL Server?More info here (http://www.rpbourret.com/xml/ProdsXMLEnabled.htm), here (http://www.pick-ware.co.uk/) and here (http://en.wikipedia.org/wiki/Pick_operating_system) I don't (which is why I am asking) but I could imagine it being a right bugger. No need for detailed or technical info - I have no more info at this stage - just wondered if anyone has any similar experience. Super duper, thank you SQL troopers :)
View 2 Replies
View Related
Apr 1, 2008
I have a stored procedure that is pulling 3 parameters: @user_id, @, begin_date and @end_date. The parameters are setup in the 'parameters' tab of the data set, and also the 'report parameters', however, when I go to run report, I get textbox for user_id, instead of a drop down with pick list.
I tried creating a separate dataset to bring in user_id's only and manually create a parameter for it in 'report parameters'. I then get a drop down box with repeating data, and when I run the report, I get back all user_id's instead of the one I chose.
I'm finding the parameters are the most difficult concept within RS. Does anyone know I can make this work?
Thanks!
View 1 Replies
View Related
Jan 9, 2008
I have a MSSQL Report Services Report Model set up to allow users to create their own ad hoc reports. The data source for my model is a Named Query that queries a MS SQL view that actually pulls data from a series of tables & other views. When you create a report from this model and attempt to filter the data some of the fileds will provide you with a pick list to select which values you would like to filter on and other fields do not provide you with a pick list but require you to enter the data directly that you wish to filter on. Most of the data fields that I am trying to filter on are varchar fields and like I said some will create a pick list for you to selet from and others will not. This all seemed to start after I changed my data source to a Named Query rather than having my data source as the MS SQL View directly. I did this becuase it seemed that anytime I had to make a change to the views that the data source pulled from it would mess up any existing reports that had been created and this does not happen if I use a named query. I have gone crazy trying to figure this one out so any ideas would be greatly appreciated!
View 1 Replies
View Related
Mar 5, 2007
Hi,
I have a table in SQL Server 2005 which has [Id] and [Name] as its columns.
I also have a Oracle database which has a similar table.
What I want to do is as follows:
In a SSIS package, I want to pick up details from SQL Server and update the Oracle table. And then should be done without using a linked server connection.
Can someone guide me as to how I can specify a update statement in the destination dataflow.
Thanks.
View 7 Replies
View Related
Oct 4, 2007
I am designing a ssis package,This is intends to mine text data(Data extracted from websites).
Term lookup/Term extraction has been used as tools for mining.
I have lookup terms defined with me for reference table,but the main problem lie in extracting the nearby text/number/charcters to these lookup terms during mining.
For example :
I found noun "Email" 200 (frequency score) times in my text,Now I want to extract nearby email address(this is also true for PhoneNumber,Address attributes also).so how can I achieve this with SSIS.
If u have some idea/suggestion to carry out this challenge with or without Term Extraction/Term Lookup,plz do write here.
View 1 Replies
View Related
Jul 27, 2004
Does anyone know how to use for xml auto that will format an xml response to show the parent/child relationships? I currently have the parents and children in a temp table and can only get a formatting so that each one is returned at a parent. how can i select the results from the temp table so for xml auto will return the properly formatted xml file?
this is also for the TreeVeiw control.
View 1 Replies
View Related
Apr 17, 2007
The upgrade adviser for for 2k5 says something about derived tables being handled differently between 2k and 2K5 and it says to query the tables directly but this does not seem to make much sense because I thought FOR XML AUTO just created some generic XML for presentation purposes. These 2 stored procedures that it is complaining about do query the tables directly and they use the FOR XML AUTO to control the output.Does anyone know if I have to worry about this? I am tempted to let this slide and check out this part of the application after the migration happens tomorrow for QA to start testing.Yes I have been googling, checking my books and digging around in BOL. I am not seeing anything.DISREGARD: I found my derived table. It appears to change the output of the XML. Perfect.
View 2 Replies
View Related
Apr 3, 2007
how to auto increament fieldname id which is set as a primary key in sqlserver 2005 inasp.net2.0
View 1 Replies
View Related
Apr 11, 2007
I am using sql server 2000 and want to know how to get xml out of the database that looks like this using for xml auto
<Clients> <Client ID="1"> <Employer="Company1" /> <Employer="Company2" /> <Contact type="phone"> <contact type="email" value="test@test.com"> <contact type="phone" value="555-5555"> </Client> <Client ID="2"> <Employer="Company3" /> <Employer="Company4" /> <Contact type="phone"> <contact type="email" value="test@test.com"> <contact type="phone" value="555-5555"> </Client></Clients>
The problem I am having is that Contact is nested inside employer when I select Employer before Contact and the opposite happens when I select Contact first. They both join to the Client table so I would assume they both should nest directly under Client. How do I get different fields to nest directly under the same element like above?
View 2 Replies
View Related
Jun 15, 2007
Hi,
I have to generate mails automatically based on databse (SQL SERVER) table,In that table we have expirydate as one column and
based on expirydate I have to generate the mails automatically,Please guide me to solve this issue.
where we have to run the stored procedure.
Do we have to use jobscheduler?
please guide me how to use it
Thanks in avance
regards,
Raja.
View 1 Replies
View Related
Oct 5, 2007
Hi all,
I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it.
So if I for example select all Dates older than today's date, I would want something like this:
1
10/12/2006
2
10/18/2006
3
10/20/2006
4
10/22/2006
5
10/30/2006
Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually".
Is this possible? And if yes, how ? :)
Thanks in advance
Nick
View 6 Replies
View Related
Oct 9, 2007
is there a way to auto delete all the record that is more than 1 month old compare to the date field in that table.
View 1 Replies
View Related
Mar 24, 2008
DIAGID is the
field in database which is integer. i want to increment this when page is
loaded.but it is not working..
plz
find mistake ... thanks in advance
SqlCommand sqlCmd = new SqlCommand("Select max(DIAGID) from tblDxactual",
sqlCon);
SqlDataReader sqlDr;
sqlCon.Open();
sqlDr = sqlCmd.ExecuteReader();
if (sqlDr.Read())
{
txtbxDiagID.Text = sqlDr[0].ToString()+ "1"
;
}
else
txtbxDiagID.Text="1";
sqlCon.Close();
View 3 Replies
View Related
Sep 9, 2004
Hi All,
I want to write a console application to send email. There is a Date field in the SQL Server and I need to send email 2 weeks before that date.I have no idea how to write a console application and make it work.Does anybody have code for this? If so please post it.
Thanks a lot,
Kumar.
View 2 Replies
View Related
