How To Pick Up The Diffrent Record
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
ADVERTISEMENT
Aug 22, 2007
hi
i have 2005 express on 2000 sp4
i made a backup devices for the app db
and for every one of the system database.
and with those devices backup and restore.
can i manually configure the system db or i must restore them?
i have an issue where i downloaded and installed v.9.00.2047
while my backups where on v.9.00.1399
tahnks
View 1 Replies
View Related
Oct 10, 2007
Good day
I'm having a problem where I get diffrent results between SQL2000 and 2005.
I have have the following view:
Code Block
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER VIEW [dbo].[vUsrMsgInfo] AS
SELECT dbo.vMsgInfo.Body, dbo.vMsgInfo.FinalBody, dbo.vMsgInfo.BodyType, dbo.vMsgInfo.MsgID, dbo.vMsgInfo.Status, dbo.vMsgInfo.Creator, dbo.vMsgInfo.Created,
dbo.vMsgInfo.StatusTime, dbo.vMsgInfo.StatusReason, dbo.vMsgInfo.Via, dbo.vMsgInfo.RouteAt, dbo.vMsgInfo.Source, dbo.vMsgInfo.Priority, dbo.vMsgInfo.Subject,
dbo.vMsgInfo.RouteRuleName, dbo.UsrMsgInfo.Usr, dbo.vMsgInfo.SequenceNo, dbo.vMsgInfo.SendEnv, dbo.vMsgInfo.EnvCount, dbo.vMsgInfo.OwnerOrg,
dbo.vMsgInfo.OwnerPerson, dbo.vMsgInfo.ToWildAddr, dbo.vMsgInfo.BodySize, dbo.vMsgInfo.RoutedRoute, dbo.vMsgInfo.NoteBody, dbo.vMsgInfo.ToOrg,
dbo.vMsgInfo.CreatorOrg, dbo.vMsgInfo.FaxMode
FROM dbo.UsrMsgInfo RIGHT OUTER JOIN
dbo.vMsgInfo ON dbo.UsrMsgInfo.Message = dbo.vMsgInfo.MsgID
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
I run the following query:
Code Block
Select * from vUsrMsgInfo where usr='cdk'
In SQL2000 I get a resultset that includes nulls and cdk in the usr column.
In SQL2005 however I only get results that has cdk in the usr column.
Is there a way to replicate the 2000 results in 2005??
Thanks & Regards
Corne.
View 12 Replies
View Related
Apr 3, 2008
I have a problem with the dates and "datetime" picker in my report (sqlserver 2005 sp2).
My reginal settings on the server are: "hebrew" - "Isreal".
I have a report with a datetime picker.
If I'm opening the report from: http://myserver/REPORTSERVER and I'm writting in the datepicker textbox the value "01/04/2008", and then click on the calander icon it opens on the on the 4th of january 2008.
If I'm opening the report from: http://myserver/REPORTS and I'm writting in the datepicker textbox the value "01/04/2008", and then click on the calander icon it opens on the on the 1th of april 2008.
Does anybody know this bug and how to wotk with it?
Thanks.
View 3 Replies
View Related
Nov 19, 2007
I have two databses SIS and SIS_Pro. Users tables should be used in both of them because I have some relations between this table with other table in SIS and SIS_Pro. Users in SIS only have one column and it is the UserId which is the primary Key in both of them, but in SIS_Pro Users table have Firstname Lastname and... now. In my program I need some informatin from SIS and some from SIS_Pro so I create a view which is joining of forexample exam in SIS and Users in SIS_Parnian, becuase I don't have the firstname and lastname in a Users table which is in SIS_Pro databse.Does it reduce the performance?is it better to copy datas which are in Users in SIS to Users in SIS_Pro( I mean all columns firstname, lastname ,,.....)
Sincerely
Kianoosh
View 1 Replies
View Related
Aug 10, 2007
Hello,
I am new in SSIS.
I am using an OLEDB source and setted as SQL Command.
The Query is a JOIN between different databases.
How can I make the QUERY with different source (different databases or SQL Servers)?
I mean, any solution is OK, the important is to make queries against different databases with SSIS.
Thank
View 9 Replies
View Related
Oct 31, 2007
i am having 2 sql servers one is web sql server server and other server is my local sql server. i am making web application through which i want to connect both sql servers at a time and by selecting data from web server i want to insert it into my local sql server both having same database on which i want to work , i want to know how it can be done
my idea as follows in web.config i specify 2 connection string and by selecting data from websql server database table i going to store it in dataset and then i return dataset to another function which connects to local sqlserver database.but database records are much large. can any body guide me .
i am going to place my web application on local server (C# asp.net 2.0) .
thank you,
i tried this code
c# file
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Uupdatedata();
}
public void Uupdatedata()
{
string constr1=ConfigurationSettings.AppSettings["abccon1"];
string constr2 = ConfigurationSettings.AppSettings["abccon2"];
System.Data.SqlClient.SqlConnection dbcon1 = new System.Data.SqlClient.SqlConnection(constr1);
dbcon1.Open();
System.Data.SqlClient.SqlConnection dbcon2 = new System.Data.SqlClient.SqlConnection(constr2);
dbcon2.Open();
SqlDataReader dr;
SqlCommand cmd = new SqlCommand("select * from Unit_Master",dbcon1);
dr = cmd.ExecuteReader();
while (dr.Read())
{
string Sql = "insert into Table1(Unit_Id,Unit_Desc) values(" + dr.GetInt32(0).ToString() + "," + dr.GetString(1) + ")";
SqlCommand cmd1=new SqlCommand (Sql,dbcon2);
//cmd1.EndExecuteNonQuery ();
}
//dr.Close();
//dbcon2.Close();
}
web.config<configuration>
<appSettings>
<add key="abccon1" value="Data Source=comp01;Initial Catalog=abc;User Id=sa ; Password=sa"/>
<add key="abccon2" value="Data Source=comp01;Initial Catalog=abctest;User Id=sa ; Password=sa"/>
</appSettings>
<connectionStrings/>
this 2 databases are from single my loacl server
it wont work it gives exception
please healp me.
View 4 Replies
View Related
Jul 18, 2001
hi everybody.
Scenario
Company X has Sql server and domain x.com
so
on sql server
they have logins
XA
XB
XC
Back of database Production was done
database Production was restored in Company Y
use Production
select name from sysusers
name
----
XA
XB
XC
sp_dropuser 'XA'
Server: Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
User 'XA' does not exist in the current database.
How to drop 'XA' ?
View 2 Replies
View Related
Sep 19, 2006
andre writes "I have two questions:
1.)How do i determine if fields are not unique??
2) How to update one table with values from another table when the row names are diffrent??
Thank you!!"
View 1 Replies
View Related
Jan 12, 2007
Is it possible to force the SLQ 2005 Client Components to be installed into a diffrent location other than the default location?
What I'm trying to do is run the setup command though the command line to create an unattended client installation with all the client componets to a diffrent location that
Thanks, Any information or examples would be great!
View 1 Replies
View Related
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 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
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
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
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
Jul 20, 2005
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
View 6 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Apr 6, 2007
Hey all!
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
Thanks in advance,
Johan
View 5 Replies
View Related
