UPDATE Statement With Multiple Criteria

Apr 5, 2006

I am trying write a query to update a column of data in my xLegHdr table however the update is based on multiple criteria. I was trying to use "IF..ELSE" statements but that is not working.

I would like to update the "SMiles" column based on the data in the "Dist" column. If the number in the "Dist" column is less than 250 then subtract 25 and multiply it by 1.15 the result should go in the "SMiles" column. If the number is grater than 250 then subtract 40 and multiply by 1.15 and place the result in the "SMiles" column; like so:

UPDATE xLegHdr
SET SMiles =
IF Dist<250 THEN Round(Dist-25)*1.15)
ELSE Round(Dist-40)*1.15)
END IF

Any ideas?

View 1 Replies


ADVERTISEMENT

Multiple Update, Several Where Criteria

Nov 2, 2007



Im sorry if this has been covered, I tried a search but I couldnt seem to find what I was after.

Anyway, I need an Update procedure which is actually several bunched into one. Ive had a stab at it myself, and perhaps my pseudo SQL might explain what I need..




Code Block
UPDATE [TW].[dbo].[TBLSalesPart]
SET

CASE WHEN [Part] = 'MONTV-' AND [YN] = 'False' THEN [SubCategory] = 20440 END
CASE WHEN [Part] = 'TC-' AND [YN] = 'False' THEN [SubCategory] = 20444 END




Hopefully this makes sense, cheers..

View 5 Replies View Related

Update Records Matching Multiple Criteria

Feb 13, 2008

I have an 'update' query that looks like this:

update wce_contact
set blank = 'missing'
where website in ('www.name1.co.uk','www.name2.co.uk','www.name3.co.uk')

I know this query will set 'blank' to missing when it matches the above websites. However if i wanted to set blank to 'missing' where mail1date is not null and mail2date is not null (keep going to mail18date not null) how exactly would i go about this?

I guess it would be a case of adding another bracket somewhere but im unsure?

View 3 Replies View Related

SELECT Statement With Multiple Criteria That Returns The Criterion Matched

May 18, 2006

Hello:
I need assistance writing a SELECT statement.  I need data from a table that matches one (or more) of multiple criteria, and I need to know which of those criteria it matched.  For instance, looking at the Orders table in the Northwind database, I might want all the rows with an OrderDate after Jan 1, 1997 and all the rows with a ShippedDate after June 1, 1997.  Depending on which of those criteria the row matches, it should include a field stating whether it is in the result set because of its OrderDate, or its ShippedDate.  One way of doing this that I've already tried is:
SELECT 'OrderDate' AS [ChosenReason], Orders.*FROM OrdersWHERE OrderDate > '1-1-1997'UNIONSELECT 'ShippedDate' AS [ChosenReason], Orders.*FROM OrdersWHERE ShippedDate > '6-1-1997'
In my application, scanning a table with thousands of records for five sets of criteria takes a few seconds to run, which is not acceptable to my boss.  Is there a better way of doing this than with the UNION operator?
Thank you

View 2 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

Multiple Update Statement

Nov 14, 2000

If I want to update 2 columns at 1 time with a where clause
like

update table a
set column1 = 8 and
set column2 = 9
where id = 10

I know you can't use the and statement, what is the correct syntax?

View 1 Replies View Related

Multiple Column Update Statement

Aug 25, 2006

Hi,

I'm new to SQL Server but not new to SQL because I used it with Oracle. I wonder if it is possible to do this kind of statement on SQL Server:

UPDATE TableX M
SET (M.column1, M.column2, M.column3)=
(SELECT T.column1, T.column2, G.column3)
FROM Table_Y T,
Table_Z G
WHERE join condition))
WHERE EXISTS (join condition for TableX)

Basically, I'd like to update multiple columns in one statement but for some reason I can not get it to work.

What would be the equivalent on SQL Server?

Thanks for the help,

Laszlo M

View 4 Replies View Related

IF Statement To Update Multiple Rows

Oct 12, 2007



Hi,

I am writting a bit of SQL that takes data from one table then inserts it into another one. There is a field that can be any value (and is usually null), but when I insert the value in the new table then I want to execute:

IF table.field>0 then tabl2.field='400'. In other words for every row in the selection that has a field that is greater than 0 then '400' will be put into the new table.

I am not sure if the IF stamement can loop through a number of rows and execute depending on the value of a field in that row??

Thanks

View 7 Replies View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?

Aug 22, 2007

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

View 1 Replies View Related

Update Multiple Column Set Select Statement

Nov 14, 2013

i have a table named masterlist wherein the columns are :

name-----age------sex
andrew---19-------male
trisha---23------female

and i have also another table which is namelist that is linked to the masterlist table.. after i search for the record andrew in the table namelist..i updated andrew as 25 and sex is female..now i want reset andrew's record, same to the records that andrew has in the table masterlist..

View 3 Replies View Related

Update Statement To Include Multiple Parameterised Input

Oct 18, 2006

I want to do an update query like the following:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE (EmployeeNumber = @selectedusersparam)Is there some simple way to add the @selectedusersparam as value1,value2,value3 etc. or do I have to input it with this type of syntax:UPDATE dbo_tblUserDetails SET dbo_tblUserDetails.DeploymentNameID = 102WHERE (((dbo_tblUserDetails.EmployeeNumber)=value1 Or (dbo_tblUserDetails.EmployeeNumber)=value2));Help appreciated.Many thanks.

View 5 Replies View Related

Update Multiple Varbinary Records With Single Sql Statement

Jan 16, 2007

I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101;
UPDATE HashedPassword = someOtherValue WHERE UserID = 102;
...

What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery().
Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx

'2. Create a command object for the query
Dim strSQL as String = _
"INSERT INTO UserAccount(Username,Password) " & _
"VALUES(@Username, @Password)"
Dim objCmd as New SqlCommand(strSQL, objConn)

'3. Create parameters
Dim paramUsername as SqlParameter
paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25)
paramUsername.Value = txtUsername.Text
objCmd.Parameters.Add(paramUsername)

Dim paramPwd as SqlParameter
paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16)
paramPwd.Value = hashedBytes
objCmd.Parameters.Add(paramPwd)

'Insert the records into the database
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
 

View 1 Replies View Related

Update Multiple Columns In One Table With Case Statement

Nov 15, 2013

I want to update multiple column in one table using with case statement. i need query pls..

stdidnamesubject result marks
1 arun chemistry pass 55
2 alias maths pass 70
3 babau history pass 55
4 basha hindi NULL NULL
5 hussain hindi NULL nULL
6 chandru chemistry NULLNULL
7 mani hindi NULLNULL
8 rajesh history NULLNULL
9 rama chemistry NULLNULL
10 laxman maths NULLNULL

View 2 Replies View Related

Update Statement - Correlation Name Is Specified Multiple Times In FROM Clause

Jun 11, 2014

I have this update statement that I need to have joined by MSA and spec.

I keep getting an error.
Msg 1011, Level 16, State 1, Line 3
The correlation name 't1' is specified multiple times in a FROM clause.

Here is my statement below. How can I change this?

UPDATE MSA
SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t1 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])

View 3 Replies View Related

Updating The Same Column Multiple Times In One Update Statement

Jul 23, 2005

I have a single update statement that updates the same column multipletimes in the same update statement. Basically i have a column thatlooks like .1.2.3.4. which are id references that need to be updatedwhen a group of items is copied. I can successfully do this withcursors, but am experimenting with a way to do it with a single updatestatement.I have verified that each row being returned to the Update statement(in an Update..From) is correct, but that after the first update to acolumn, the next row that does an update to that same row/column combois not using the updated data from the first update to that column.Does anybody know of a switch or setting that can make this work, or doI need to stick with the cursors?Schema detail:if exists( select * from sysobjects where id = object_id('dbo.ScheduleTask') and type = 'U')drop table dbo.ScheduleTaskgocreate table dbo.ScheduleTask (Id int not null identity(1,1),IdHierarchy varchar(200) not null,CopyTaskId int null,constraint PK_ScheduleTask primary key nonclustered (Id))goUpdate query:Update ScheduleTask SetScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.')FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0Query used to verify that data going into update is correct:selectScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.'FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0

View 8 Replies View Related

Transact SQL :: Update Statement To Include Multiple Records At Once

Apr 20, 2015

I have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.

update
mklopt
set
 FRMDAT =
'12/31/2014'
where
 JOBCOD =
'PH14789' 

I also want to include the following instead of running it one at a time

PH17523    
PH17524    
PH17525    
PH17553    
PH17555    
PH17556    
PH17557    
PH17558    
PH17571    
PH17573    
PH17574    
PH17575    
PH17576    
PH17577    
PH1757

View 9 Replies View Related

Updating Multiple Rows With Multiple Criteria?

Oct 15, 2009

is there a way to update multiple rows in one update query in tsql? what I wanted to do is for example I got a table containing

code : desc
1 : a
2 : b
3 : c
4 : d
1 : e
3 : f

I wanted to update it to

code : desc
1 : x
2 : b
3 : y
4 : d
1 : x
3 : y

how to do it?

View 5 Replies View Related

Get Multiple MAX With Where Criteria

Mar 17, 2015

use of Row_Number() over ( partition... but I dont understand how.

Imagine I have a table like
CustomerID, PartNum, QtyinOrder, shipped
1 6 3 0
1 6 2 0
2 6 1 0
2 5 1 0
2 5 2 0
2 5 3 0
2 5 4 1
1 6 4 1
2 6 2 1

But I wanted to return

CustomerID, PartNum, MaxQtyOrderedNotShipped

That would be just the rows
1 6 3 0
2 6 1 0
2 5 3 0

If I use this:

Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1

there is no restriction, so I get the shipped...If I alter the where clause to work only on not shipped, I get no records...as below

Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1 and shipped=0

View 2 Replies View Related

FilterExpressions With Multiple Criteria

Sep 10, 2007

I am creating a .aspx page that links with Miscrosoft SQL Server 2005 Express. It includes a GridView control that displays all the table data on the page. You can then select a record from the control (currently by clicking an image button to the left of each record- is there any way of selecting the record by clicking anywhere on the row? How would that be done?) and it displays the data in a detailsview control below where the data can be changed etc.
 The data is like a phonebook (Name, Telephone number, and some other misc fields) and the user should be able to search by either name or number to filter out the records shown in the gridview control. I have two textboxes for this, and I started with the name text box and it works fine. i.e. with one filterparameter and one filterexpression. So that if you just enter 'Da' it filters out the records displaying only those whose name starts with 'Da'.
 I have experimented but have found no way of including filter expressions to use the number as a search. I added the second filter parameter (under sqldatasource control so that:
 <FilterParameters>
<asp:ControlParameter Name="DestinationName" ControlID="txtName" /><asp:ControlParameter Name="DestinationNumber" ControlID="txtNumber" />
</FilterParameters>
But I don't know what to do for the FilterExpressions. currently I just have:
FilterExpression="DestinationName LIKE '{0}%'"
i have tried using "DestinationName LIKE '{0}%' OR DestinationNumber LIKE '{0}%'" but it requires that both text boxes have data entered.
 
What I want is something that allows the user to enter either a name or number or both (all or part of so don't need to enter in full name/number) and it filters out the records accordingly. I.e. if you enterd 'Dav' and '079' it would bring back all the records who had a name starting with Dav and a number starting with 079. However if you enterd just 079 then it should just bring back all records with numbers starting 079 whatever their associated name.
 
Thanks

View 9 Replies View Related

Deleting With Multiple Criteria

Aug 27, 2004

I have a table with a record that looks like the attached TXT.


I need to keep the most recently entered value where flag_out = 1 and delete those duplicate
records, and this should only apply to records where there's also an flag_in value of 1.

I've tried a bunch of delete statements without avail....

TIA

View 3 Replies View Related

Multiple Criteria In WHERE Clause (was T-SQL)

Oct 5, 2005

How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.

So is there any other way to handle multiple criteria in T-SQL ?

View 12 Replies View Related

Need A Count Of '0', Even If Criteria Is Not Met In A Select SQL Statement

Feb 19, 2008

I have the following Select SQL Statement in which I get the count of the 'Code' column based upon a criteria and Group By clause:BEGIN
SELECT Code, COUNT(Code)as exprCount1a
FROM dbo.[Test]
WHERE Section = '1' and Item = 'a' GROUP BY Code ORDER BY Code
END
The results of the statement:
Code | exprCount1a 
1              22              44              1
I would like the following results:
Code | exprCount1a 
1              22              4
3              04              1
Note: Code ' 3 ' doesn't have any rows that meet the select count statement criteria but I still need to populate ' 0 ' in the results.
Thank you in advance

View 2 Replies View Related

Help With DATE Criteria In SELECT Statement

Jun 25, 2004

I am trying to create a SELECT statement that would allow my users to type in a date parameter like 6/25/04. My SELECT statement would then pull all entries for that date. The problem I am running into is that it seems SQL wants the date to be parameterized as between 6/25/04 and 6/25/04 11:59:30 PM. Is there any way around that? Again I would like my users to simply enter 6/25/04 and have all entries pulled. Thanks for any help.

View 1 Replies View Related

Case Statement Based On Criteria

Sep 23, 2013

I have to produce a report, but not sure of the best way to get the required results

Aim - to count how many [FDMSAccountNo] there are per given [Month_end_date], and then do a case when on the[Retail_amount]

E.g.

10 Fdmsaccountno in Jan

Those 10 Fdmsaccountno vary in [Retail_amount]

I have 5 fdmsaccountno which are between %0 & £5
2fdmaccounno beterrn £6& £10
3 fdmsaccountno £10>

My query is

SELECT [FDMSAccountNo]
,[Month_end_date]
,[Retail_amount]
FROM [FDMS].[dbo].[Fact_Fee_History]
where [Fee_Sequence_Number] = '32r'
and Month_end_date between '2013-01-01' and '2013-12-01'

View 5 Replies View Related

Update With Criteria And Value From Another Table

May 16, 2006

Hi, I have a problem which is probably really simple to solve but I have gotten a tilt and cant get it...Hoping you can help me.
What Im trying to do in pseudo-code:

Get A from a tableX where B = 0

Check tableY for each A and get B from there

Update all B in tableX where TableX.LotNo=TableY.Lotno with the value from TableY.B

Anyone feel generous enuf to help a newbie out?

View 10 Replies View Related

Computed Column, IF, Multiple Criteria.

Jan 30, 2008

Hi.
 I have this method in a class, it produces a string value based on the value of another property in the object (which represents a field in the database). I would like to turn this into a computed column in SQL server... but need help converting the formula if this is even possible. Thanks in advance.public string GetVendorEvalRating(int vendorevaltotal)
{
string vendorevalrating = "";if (vendorevaltotal >= 26)
{vendorevalrating = "Critical";
}else if ((vendorevaltotal >= 10) && (vendorevaltotal <= 25))
{vendorevalrating = "Material";
}else if ((vendorevaltotal >= 0) && (vendorevaltotal <= 9))
{vendorevalrating = "Minor";
}return vendorevalrating;
}

View 16 Replies View Related

Handling Multiple Search Criteria

Mar 20, 2012

I am working on SQL Server in VB 2008. I have a table 'Records' having 8 columns. I have a search page where I can choose 5 different parameters to search as 'Category' , 'Name' , 'Date' etc.

I can successfully search with a single criteria selected either Category Name Or Date. But I want to create a single SQL command that can search my 'Records' table for either two or all the parameters depending on the selections made by the user.

View 5 Replies View Related

Join Tables On Multiple Criteria

Oct 14, 2013

I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.

Here is table a:

naics ust_code port all_qty_1_yr all_qty_2_yr all_val_yr all_air_val_yr all_air_wgt_yr all_ves_val_yr all_ves_wgt_yr all_cnt_val_yr all_cnt_wgt_yr all_border_val_yr
11111000 2010 2002 8070569.14298579 0 2335641254.30021 0 0 2335641254.30021 8156408492.66667 0 0 0
11111000 2230 2010 280841.478063446 0 84622385.9133129 0 0 84622385.9133129 299600780.773355 0 0 0
11111000 2410 1401 25735 0 12305667 0 0 12305667 25719794 0 0 0

[Code] ....

and here is table b:

naics ust_code port stat_month Cum_qty_1_mo Cum_qty_2_mo Cum_all_val_mo Cum_air_val_mo Cum_air_wgt_mo Cum_ves_val_mo
11111000 1220 0106 01 2 0 3440 0 0 0
11111000 1220 0107 03 14 0 3442 0 0 0
11111000 1220 0108 09 0 0 0 0 0 0

[Code] ....

I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’

/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]

[Code] ....

Output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like

* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)

View 1 Replies View Related

Joining Tables Through Multiple Criteria

Oct 14, 2013

I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.

Here is table a:

CREATE TABLE #A(
naics INT,
ust_code INT,
port INT,
all_qty_1_yr FLOAT,

[Code] ....

And here is table b:

CREATE TABLE #B(
naics INT,
ust_code INT,
port INT,
stat_month INT,
Cum_qty_1_mo FLOAT,

[Code] ....

I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’

/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]

[Code] ....

output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like

* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)

View 1 Replies View Related

Joining Tables With Multiple Criteria

Jun 9, 2014

I have a straight-forward select query to show work orders for a particular customer as below. I want to add a field value from another table, deltickitem diwhich contains contract records. I need to include the field di.weekchg to show the weekly hire rate, but the joined query must ensure that the both the contract number matches that in the original select and that the item number matches that in the actual select. Additionally, there is the problem that the item can appear more than once in the deltickitem table against a particular contract (if item has been off-hired and then re-hired on the same contract number) - in this case the query must select the record with the highest di.counter number, which I haven't worked out how to put in my query.

This is my basic code, but I keep ending up with duplicate work order lines in my result set.

Select wh.worknumber, wh.custnum, wh.contract, wh.sitename, wh.itemcode, wh.regnum, m.name, di.weekchg,
wh.date_created, wh.task_descr, wh.actual_labour_sale+wh.actual_parts_sale as [Repair Cost]
From worksorderhdr wh Left Join
inventory iv On iv.item = wh.itemcode inner Join
models m On m.id = iv.model_id left join deltickitem di on di.dticket = wh.contract
where wh.custnum = 'BARRATNE' and wh.rejected <> 1 and wh.charge_to_cust = 1
order by wh.date_created

View 9 Replies View Related

Counting And Grouping With Multiple Criteria

Jul 14, 2014

Consider the following dataset:

ID|MD|TYPE
1|JOHN|A
2|JOHN|B
3|JOHN|B
4|BOB|A
5|BOB|A
6|BOB|B
7|BOB|B
8|BOB|B

I need to count the number of IDs for each MD and each TYPE like this:

MD|A|B
JOHN|1|2
BOB|2|3

I only know how to count everything by MD like this:
SELECT MD, COUNT(ID) AS TOTAL
FROM MY_TABLE
GROUP BY MD
ORDER BY MD

The query above results in:

MD|TOTAL
JOHN|3
BOB|5

View 5 Replies View Related

Output Search Criteria For Multiple OR?

Oct 21, 2014

My selection criteria is as follows:

where content like '%EditLiveJava%'
or content like '% Sys__%' ESCAPE '_'
or content like '%<div class="row"/>%'
or content like '%<a href="" title=""%'
or content like '%cmsprod%'
or content like '%Error processing inline link%'
or content like '%see log for stack trace%'

I output the content field if the search is true but would like to also output which specific 'like' has been found.

Can I do this in the one pass or do I have to read the database separately for each condition?

View 3 Replies View Related

Select Based Upon Multiple Criteria

Sep 13, 2006

Hi

I would like to get records from a table and present a result set based upon the search fields

the search fields could be any of the following: PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3,

they could enter any combination of these however if they dont enter any of the above then the search should not retrieve any thing. the table colunms are listed below and asample data set is also shown below.

Currently the only way i think it can be done is by writing multiple queries with different queries to be executed based upon the search field that have been filled? can it be done in a stored prcedure? and can it be done using non-dynamic sql?

Name, PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3, RefImage


adam, 01, 1999, 099, 3yh333, 888, 989, 999, ref1999099.jpg
Brian, 01, 2005, 029, 3yh323, 828, 929, 929, ref1929099.jpg
sid, 04, 1989, 039, 3yh343, 838, 939, 939, ref1993399.jpg
jack, 06, 1996, 069, 3yh633, 868, 969, 969, ref1669099.jpg

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved