Okay I am trying to compare allele1 values for differences for two or
three different member having the same trackID. The list of markers
would always be same for all members belonging to a particular
trackID.
So in the above sample data. The trackID is same which is 4734. There
are two members 4577 and 4578. The markers are same for each members
but the corresponding allele1 values are different.
I want to compare these two and state whether they are same or not. I
would like to do this on a form containing subforms.
I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:
check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where h.freight_wagon_serial_number = fw.freight_wagon_serial_number and fw.freight_wagon_type = fwt.freight_wagon_type and h.train_number = t.train_number) < (select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))
The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.
I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState 1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE 1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?
I need Insert rows in the OrderDetails Table based on values in the Orders Table
In the Orders table i have a columns called OrderID and ISale. In the OrdersDetails i have columns called OrderID and SaleType
For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD. That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD
Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.
I have tables in my database, tblNames1, tblNames2, tblNames3 for ex, and for addresses tblAddresses, can I match the address IDs in the names tables to the address table based on the values of a field? In this case CompanyName. Can they be matched automatically? My issue is that I have a huge CSV file with all the address information that needs to be assigned a foreign key based on the company name field which is also present in all the names tables, all the names are normalized.
Newbie transiting from VBA to TSQL, using SQL Server 2005 Enterprise:Need help to do this:Open Table_AWITH TableADO UNTIL .EOFRead value from TableA.ColumnARun SQL Statement on TableB based on valueMove to the next recordLOOPENDHow do I do this in TSQL?Thanks,Bubbles
Hi i am trying to create an insert statement that will insert rows into a table based on the information in the table already. the table looks like this
INSERT Into table1(groupid,field1,field2) select -1,@passedvalue,field2 from table1 where field1 = @passedvalue1
assume @passedvalue = 700, @passwedvalue1 = 100 Now this is fine however i cannot have a duplicate key (key is comibantion of all 3 fields) thus the first time this runs it works however if it runs again it fails - how can i change the where clause to ignore rows that already exist? eg if @passedvalue = 300 and passedvalue1 = 500
I have 2 tables, one that contains a set of codes and their definitions, and another where each record has a field that contains several of these codes separated by commas:
Tab1
SubCode | Definition --------------- S100 | Def of S100 S101 | Def of S101 S102 | Def of S102
I'm trying to create a query against Tab1 so that it retrieves a recordset of Subcodes and definitions based on the contents of the Subcodes field for a record in Tab2. I've tried this using a subquery, as follows:
SELECT SubCode ,Definition FROM Tab1 WHERE SubjectCode IN (SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39 + CHAR(44)+ CHAR(39)) + CHAR(39) FROM Tab2 WHERE DepID = 1 AND PurposeCode = 'P101')
The subquery will return: 'S100','S101' and I expect the final recordset to be:
SubCode | Definition --------------- S100 | Def of S100 S101 | Def of S101
However, it's not returning any records. If I execute the subquery separately and then plug its results into the main query e.g.
SELECT SubCode ,Definition FROM Tab1 WHERE SubjectCode IN ('S100','S101')
it returns the expected recordset. Does anyone have any pointers? It's driving me nuts..
Cheers Greg
Complete DDL, Sample Data, and Query below:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[SubjectCodeDefinition] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[DepartmentReturn] GO
INSERT INTO SubjectCodeDefinition(SubjectCode, Definition) SELECT 'S100', 'Definition of Code S100' UNION ALL SELECT 'S101', 'Definition of Code S101' UNION ALL SELECT 'S102', 'Definition of Code S102' UNION ALL SELECT 'S103', 'Definition of Code S103' UNION ALL SELECT 'S104', 'Definition of Code S104' UNION ALL SELECT 'S105', 'Definition of Code S105' GO
INSERT INTO DepartmentReturn(DeptID,PurposeCode,SubjectCodes) SELECT 1,'P100','S100,S101,S104' UNION ALL SELECT 1,'P101','S102,S103' UNION ALL SELECT 1,'P102','S100,S101,S105' UNION ALL SELECT 2,'P100','S100,S101,S104,S105' UNION ALL SELECT 2,'P103','S103,S104,S105' UNION ALL SELECT 3,'P100','S100,S102,S104' GO
SELECT SubjectCode ,Definition FROM SubjectCodeDefinition WHERE SubjectCode IN (SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39)+ CHAR(44)+ CHAR(39)) + CHAR(39) FROM DepartmentReturn WHERE DeptID = 1 AND PurposeCode = 'P102')
When a record is inserted or updated records in Table1 I want a record to be inserted into table3 for each record ID that is in table2 if the table2.id does not already exist in table3. What is the best way to do this? Could this be done with a trigger? If it could how would I write such a trigger. I have never written one before and this sounds like a hand full for my first effort. Your help will be greatly appreciated.
I receive the input file with some 100 columns and some 20k+ rows and I want to check the incoming input row is existed in the database or not based on 2 key columns. If the row is existed then I need to check all the columns (nearly 100 columns) values in input and the database are equal or not. If both are equal I need to treat them seperately if not there is a seperate logic. How Can I do that check for each row and for each column?
Basically the algorithm is like this, if the input file row is not existed in the database then treat that as new row else if the input row is existed in the database then check all the columns are equal or not. If all the columns are equal then treat that as existing row and do nothing else if some columns are not equal then treat this row seperately.
I found some thing to achieve the above thing. 1. Take the input row and check in the database. 2. If the row is not found in the database then treat it as new row. 3. If row is found in the database then a) Take the source row and prepare a concatenated string for all the columns b) Take the database row and prepare a concatenated string for all the columns c) Find out the hash code for the 2 strings and then compare hash codes for equal.
The disadvantage of this is running a loop 2*m*n times where m is the number of rows and n is the number of columns. It should be done 2 times for input file row and database row.
Can anybody suggest a good method to do this?
What does the function "GetHashCode" for InputBuffer in method "Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)" will do? Will it generates hash code based on all the columns values?
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT. I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId". What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display. Sample: ProjectId - SiteId 1 - 5 1 - 9 1 - 16 1 - 18 1 - 20 1 - 27 1 - 31 ProjectId stays the same, only values for SiteId are being different. I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
Can someone give me a clue on this. I'm trying to insert values based off of values in another table.
I'm comparing wether two id's (non keys in the db) are the same in two fields (that is the where statement. Based on that I'm inserting into the Results table in the PledgeLastYr collumn a 'Y' (thats what I want to do -- to indicate that they have pledged over the last year).
Two questions
1. As this is set up right now I'm getting NULL values inserted into the PledgeLastYr collumn. I'm sure this is a stupid syntax problem that i'm overlooking but if someone can give me a hint that would be great.
2. How would I go about writing an If / Else statement in T-SQL so that I can have the Insert statement for both the Yes they have pledged and No they have not pledged all in one stored proc. I'm not to familar with the syntax of writing conditional statements within T-SQL as of yet, and if someone can give me some hints on how to do that it would be greatly appriciated.
Thanks in advance, bellow is the code that I have so far:
RB
Select Results.custID, Results.PledgeLastYr From Results, PledgeInLastYear Where Results.custID = PledgeInLastYear.constIDPledgeInLastYear Insert Into Results(PledgeLastYr) Values ('Y')
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1) Days_in_warehouse 20 30 40
For example we've got a row from [Formula_Calc] table 'F1+F3' as a string that needs to be transformed as 240+160=400
The below code works for the above example but if I pick 'F11+F3' instead , returns 2561 which comes from 2401+16. Probably replaces F1 value instead of F11 and adds 1st digit (1) if I got it right ...
DECLARE @formula NVARCHAR(100); DECLARE @Total NVARCHAR(100); SET @formula = 'F11+F3';
SELECT @formula = REPLACE(@formula,RowNo,Total) FROM [Totals]
I am pulling down out of range values from a single table on one database to a different table on a different database on a different server (one i have full access to). Basically, it looks something like this:
id1 value1 prev_value1 value2 prev_value2 date prev_date id2 value1 prev_value1 value2 prev_value2 date prev_date id3 value1 prev_value1 value2 prev_value2 date prev_date
all the "prev"'s are null. I want to do one do one query that will get me the previous values and dates for each id from the original database. how to do this.
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth How can I get the keepmonth? can I use parameters ?
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT) INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT) INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H SET CHANNEL1= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=1 -- updates only channel1 UPDATE H SET CHANNEL2= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=2 -- updates only channel2 SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1 SET FeeInvalid = 'Y' FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID WHERE t1.CustID = t2.CustID AND t2.OrderDate > t1.OrderDate AND t2.FeeDate > t1.CancelledDate CREATE TABLE [dbo].[MockData]( [OrderID] [float] NULL,
A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9] ... [8_4],[8_7] ... [14_1], [14_2],...[14_10] etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null drop table #tempAllergy select * into #tempAllergy from dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
create table #myfirst (id int, city varchar(20)) insert into #myfirst values (500,'Newyork') insert into #myfirst values (100,'Ediosn') insert into #myfirst values (200,'Atlanta') insert into #myfirst values (300,'Greenwoods') insert into #myfirst values (400,'Hitchcok') insert into #myfirst values (700,'Walmart') insert into #myfirst values (800,'Madida')
-- My Second Data
create table #mySecond (id int, city varchar(20),Sector varchar(2)) insert into #mySecond values (1500,'Newyork','MK') insert into #mySecond values (5500,'Ediosn','HH') insert into #mySecond values (5060,'The Atlanta','JK') insert into #mySecond values (7500,'The Greenwoods','DF') insert into #mySecond values (9500,'Metro','KK') insert into #mySecond values (3300,'Kilapr','MK') insert into #mySecond values (9500,'Metro','NH')
--Third Second Data
create table #myThird (id int, city varchar(20),Sector varchar(2)) insert into #myThird values (33,'Walmart','PP') insert into #myThird values (20,'Ediosn','DD') select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city /* idcitySector 500NewyorkMK 100EdiosnHH */
i have doubt on two things
1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second
2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found
--i tried below to solve first doubt, it is working but want to know any other wasys to do it
select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')
--Expected results wull be
create table #ExpectResults (id int, city varchar(20),Sector varchar(2)) insert into #ExpectResults values (200,'Atlanta','JK') insert into #ExpectResults values (100,'Ediosn','HH') insert into #ExpectResults values (300,'Greenwoods','DF') insert into #ExpectResults values (500,'Newyork','MK') insert into #ExpectResults values (700, 'Walmart','PP') insert into #ExpectResults values (800, 'Madidar','')
Here is my statement: Dim MyCommand As SqlDataAdapter MyCommand = new SqlDataAdapter("select * from Request T1, Users T2 where RunID= "+ intRun_Num +" and T1.UserID = T2.UserID ORDER By Username", MyConnection)
I'm getting error saying: Input string was not in a correct format.
the problem is the intRunNum. When I put intRunNum.toString(), there is no error but no results are coming up.
i have a matrix which displays data for different products.i have grouped them by year and month.now i want to compare each month to the previous month and show an indicator that this month the sales was down or up.for that i need to compare a month with the previous month.how can i do this in reporting service.please help.
i have a matrix which displays data for different products.i have grouped them by year and month.now i want to compare each month to the previous month and show an indicator that this month the sales was down or up.for that i need to compare a month with the previous month.how can i do this in reporting service.please help.
