Query For Difference Between Two Fields
Sep 19, 2007Can anyone please help me to find the diffence between two fields.
Field1 - 16:12:27:123
Field2 - 16:12:36:750
I need to find the difference between field2 and field1??
ADVERTISEMENT
DB Engine :: Compare Two Tables And Log Difference In New Table With Fields?
Apr 21, 2015I want to compare two tables and log the difference in new table with the fields as (old value,new value, column name). The column name should be the changes value column.
View 10 Replies View RelatedNvarchar Fields With Time Data - Calculate Difference In Hours
Sep 19, 2014I have two nvarchar fields with time data 12:34:34 and the second one 12:34 I want to calculate the difference in Hours. The first field is called (OTIM) the second field is called (ReportedTime) if the name matters. I tried substring to trim the OTIM, I am unable to make it work.
View 3 Replies View RelatedDifference Bw SP And Normal Query
May 9, 2001Hi all,
I've been running a long query which takes almost 39 seconds in Query Analyzer. After creating a Stored Procedure (with the same query) I expected to run it faster bcoz I heared that SP has a cache, and its a faster technique. But I didnt gain any performance improvments.
Can somebody clear my confusion, what I'm doing wrong.
Thanks!
Union Query Difference
Oct 8, 2007Guys can you tell me whether there is any difference between the following queries.......... Both of them are resulting same number of records. Not sure whether the output is same or not. Just wanted to know if result output wise if there is no difference than performance wise there might be some. Kinldy educate me on the same.
SELECT * FROM FRProposalOutline FPO
INNER JOIN SC_Collection SCC
ON
FPO.Items= SCC.CollectionGID
UNION
SELECT * FROM FRProposalOutline FPO
INNER JOIN SC_Collection SCC
ON
FPO.PageBreakPositions= SCC.CollectionGID
SELECT * FROM FRProposalOutline FPO
INNER JOIN SC_Collection SCC
ON
FPO.Items= SCC.CollectionGID OR
FPO.PageBreakPositions= SCC.CollectionGID
Thanks,
Rahul Jha
Having Trouble With A Difference Query
May 28, 2008Hi, I have created a query (using SQL 2005) that will pull the people who have spent the most on tickets purchased:
Select P.Passenger_ID, Passenger_Name, Ticket_Price
From Passenger P, Ticket_Purchase T
Where P.Passenger_ID = T.Passenger_ID
Group By P.Passenger_ID, Passenger_Name, Ticket_Price
Having Ticket_Price >= All (Select Max(Ticket_Price)
From Ticket_Purchase
Group By Ticket_Price);
Passenger_ID Passenger_Name Ticket_Price
---------------------------------------
132812298 Nice,Richard 1750.00
234890032 Franco,Sylvia 1750.00
339209841 Kim,Jongouk 1750.00
385894857 Uribe,Gloria 1750.00
(4 row(s) affected)
I now want to be able to only choose the Passenger_ID's from above who are not listed in another table called Frequent_Flier, which should leave me with only 2 records not 4.
I am wondering if I add the below to the first query to eliminate those passengers in the Frequent_Flier table:
NOT IN (Select Passenger_ID
From Frequent_Flier);
When I add it to the Where clause I get an error. Should I be sub-querying that differently or is there a better way to do this.
Thanks for any help you can offer.
Date BETWEEN Query With A Difference?
Dec 12, 2007
Hi,
I have a BETWEEN query (at least I think that's what it will need), but with a difference.
Normally you would specific a field which was BETWEEN two set variables
ie. {fieldname} BETWEEN 1 AND 3
However I need mine the other way round.
I have a series of records which have a startdate and enddate held against them.
When a user submits a new record to the db, I need it to check that the starting and ending date range doesn't overlap any of the existing start-end date ranges that exist.
In order to do that I'm trying to build a query which takes in the incoming startdate variable and see if that is within any of the existing start-date-enddate dates ranges of the existing records, and then same for the incoming endate. I actually want the ones that are going to cause a problem to appear...
I;m sure there is a pretty easy way of coding this, but I'm struggling to get my head round it.
Anyone offer any advice?
Transact SQL :: Query On Running Value (difference)
Jun 18, 2015I have a table that will be loaded over night everyday and I need to write a query on running value difference ?
List of Columns (ID, Branch ,Group, Date, Value)
ID   Branch  Group  Date                 Value
1Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â CÂ Â Â Â 2015-06-01Â Â Â Â Â Â Â Â Â Â Â 10
2Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â 2015-06-02Â Â Â Â Â Â Â Â Â Â Â 15
3Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â 2015-06-03Â Â Â Â Â Â Â Â Â Â Â 25
4Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â 2015-06-04Â Â Â Â Â Â Â Â Â Â Â 20
5Â Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â 2015-06-01Â Â Â Â Â Â Â Â Â Â Â 20
6Â Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â 2015-06-02Â Â Â Â Â Â Â Â Â Â Â 25
7Â Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â 2015-06-03Â Â Â Â Â Â Â Â Â Â Â 10
8Â Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â 2015-06-04Â Â Â Â Â Â Â Â Â Â Â 20
I want the Output like below with a Running value difference in comparison to previous day.
ID   Branch  Group  Date         Value   Running Value
1Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â CÂ Â Â Â 2015-06-01Â Â Â Â Â Â Â Â Â Â Â 10Â Â Â Â Â Â Â Â 10
2Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â 2015-06-02Â Â Â Â Â Â Â Â Â Â Â 15Â Â Â Â Â Â Â Â Â 05
3Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â 2015-06-03Â Â Â Â Â Â Â Â Â Â Â 25Â Â Â Â Â Â Â Â 10
4Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â 2015-06-04Â Â Â Â Â Â Â Â Â Â Â 20Â Â Â Â Â Â Â Â -5
5Â Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â 2015-06-01Â Â Â Â Â Â Â Â Â Â Â 20Â Â Â Â Â Â Â Â 20
6Â Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â 2015-06-02Â Â Â Â Â Â Â Â Â Â Â 25Â Â Â Â Â Â Â Â 05
7Â Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â 2015-06-03Â Â Â Â Â Â Â Â Â Â Â 10Â Â Â Â Â Â Â Â -15
8Â Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â 2015-06-04Â Â Â Â Â Â Â Â Â Â Â 20Â Â Â Â Â Â Â Â 10
Basically I need to compare the previous day and show the difference. How can I do this in SQL 2008 r2?
Can Anyone Give Me A Layman's Explanation Of The Difference Between CURRENT_TIMESTAMP And GETDATE() (if There Is A Difference)?
Oct 24, 2007Question is in the subject.
Thanks in advance
-Jamie
Timing Difference Between Query Analyzer And Profiler
Aug 1, 2014We are trying to troubleshoot some website performance issues and found some queries taking 2 to 3 seconds when the request comes from the web, and captured by the Profiler.
The same queries, when run in the Query Analyzer take 0 seconds.
What could be the reasons for this difference, I mean why it takes 2 - 3 seconds shown by the Profiler, when it's 0 second in Analyzer?
Difference Between Enterprise Manager And Query Analyzer
Jan 24, 2007Hi All,What are the pros and cons between using Enterprise Manager or QueryAnalyzer for my queries.Currently I use Enterprise Manager because I prefer the interface andonly use Query Analayzer when queries time out in Enterprise Manager,but I'm sure there must be more to it.Regards,Ciarán
View 1 Replies View Related2000 To 2005 Query Processng Difference?
Mar 8, 2007I found an unusual problem between 2000 and 2005 I haven't been ableto decipher from any documentation.The query structure is as follows:select *fromtableA ajointableB b ON a.somekey = b.somekeywherea.type = 'A'and datediff(yyyy, b.someDateField, getdate()) betweena.lowboundary and a.highboundarySome basic facts about the elements and data. The low and high-boundary fields are varchar datatypes. In 2005 (regardless ofcompatibility type I run the database under), the query evaluates theBETWEEN and errors out due to the fact that it is evaluating theDATEDIFF as an integer and finds a non-integer entry in eitherlowboundary or highboundary. I understand and expect this behavior.Obviously, changing the result of the DATEDIFF function to varcharallows the operation to go forth.The odd thing is that there is no "a.type = 'A' " entry, thus thequery wouldn't return anything. In 2000, it seems as though theengine is evaluating the type = 'A' and short-circuiting and in 2005,it is trying to evaluate the entire query OR is there an implicitconversion occuring in 2000 and not in 2005?As I mentioned, the compatibility mode doesn't change how this reacts,but running this on a native 2000 server allows this to happen. Thisparticular code isn't the problem, it's what we might have to contendwith when we migrate this through. Sure, we're going to performregression testing, but I'm concerned about what we would miss.Thanks for any replies.
View 2 Replies View RelatedPerformance Difference Between Query Executed Through ASP.NET And SSMS
Sep 18, 2007I have also posted this in microsoft.public.sqlserver.programming.
I have a query which, depending on where I run it from, will either take 10 milliseconds or 10 seconds.
The query works perfectly when run in SQL Server Management Studio... in my database of around 70,000 items it returns the results in around 10ms. It uses all my indexes and indexed views correctly.
However when I run the identical query from my ASP.NET application, it takes around 10 seconds... 1000 times longer.
Looking at it in Sql Server Profiler I can't see any difference in the query, except from ASP.NET it needs 62531 reads and from SSMS it needs only 318 reads. If I copy the slow running ASP.NET query from the profiler into SSMS, then it runs quick again. The results returned are the same.
I have provided more details of the query below, but I guess my real question is: What is the best way to debug this? I'm not an expert with SQL Server, so any pointers on where I should start looking to find the difference in how the query is being executed would be a great help.
The query is of the form:
WITH RowPost AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY DateCreated DESC) AS Row,
ItemId,
Title,
....
FROM
Items_View WITH(NOEXPAND)
WHERE ItemX >= @minX AND ItemX <= @maxX AND ItemY >= @minY AND ItemY <= @maxY
)
SELECT
*,
(SELECT Count(*) FROM RowPost) AS [Count]
FROM RowPost
WHERE Row >= @minRow AND Row < @maxRow
Where Items_View is an indexed view, and WITH(NOEXPAND) is being used to force it to use the indexed view (this is optimal). The line beginning "SELECT Count(*)" is to get the total number of results (without having to run the inner query a second time).
This is running against SQL Server Developer Edition.
Query Within Query - Checking For Multiple Fields
Jun 9, 2008Hi,
I remember seeing a fancy query that checked for multiple fields in a table (I think using a select statement in the where clause but not sure), but can't remember how to do it... here is what I want to do (and maybe there is a much easier way). Thanks!
Table1
id item color
1 shoe red
2 shoe blue
3 coat green
4 coat black
Table2
item color
shoe red
coat green
I want everything in Table1 where item and color are not a match.
So my results should be:
2 shoe blue
4 coat black
I'm sorry if this is a dumb question... it's been that kind of a day!
Thanks!
Difference Between Select Query And SelectCommand In Data Adapters
May 27, 2008
Can any one tell me the difference between Select Query and
SelectCommand in data adapters?
Using SelectCommand:
string selectSQL = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City"; SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");SqlDataAdapter custDA = new SqlDataAdapter(); SqlCommand selectCMD = new SqlCommand(selectSQL, nwindConn);custDA.SelectCommand = selectCMD; // Add parameters and set values.selectCMD.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = "UK";selectCMD.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = "London"; DataSet custDS = new DataSet();custDA.Fill(custDS, "Customers"); Using Select Query:
SqlDataAdapter
da = new SqlDataAdapter("select P_UID,P_EMIAL,P_NAME from p_users where p_email=@p_email
and p_pwd=@p_pwd", con);
da.SelectCommand.Parameters.Add("@p_email", SqlDbType.VarChar, 50).Value
= etxt1.Text;
da.SelectCommand.Parameters.Add("@p_pwd", SqlDbType.VarChar, 50).Value = pwdtex.Text; Can you plz tell vat is the need for giving custDA.SelectCommand and adding parameters to that command instead of directly giving Select Query in Data Adapter? Similarly Update Command: custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID") custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")Dim myParm As SqlParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", _ SqlDbType.NChar, 5, "CustomerID")myParm.SourceVersion = DataRowVersion.Original[C#]custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"); SqlParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID");myParm.SourceVersion = DataRowVersion.Original; instead of above process, y cant v do above method in below procedure?
private void UpdateStudent(int
RollNo, string SName, string
Course, DateTime Doj, Decimal
FeePaid)
{
SqlConnection
conn = new SqlConnection("Data Source=UMASHANKAR\SQLSERVER2005;Initial
Catalog=employee;Persist Security Info=True;User ID=sa;pwd=sa1 ");
SqlCommand
cmd = new SqlCommand("UPDATE Student SET SName='" + SName + "',Course='" + Course + "',Doj='" + Doj + "',FeePaid='" + FeePaid + "'
WHERE RollNo=" + RollNo, conn);
try
{
if
(conn.State == ConnectionState.Closed)
conn.Open();
cmd.ExecuteNonQuery();
}
catch {
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
PLZ TELL ME
THE DIFFERENCE OR ANY ADVANTAGES OF UPDATECOMMAND PROCESS AND QUERY METHOD
IN this way I want difference OR ANY ADVANTAGES for InsertCommand, DeleteCommand also
Time Difference Sotored Proc &> Job &> Seperate Query
Dec 4, 2002I have seen in this forum that many people has posted like my query time taken problem. But I couldn't get clear solution for this.
Here is the my issue:-
I have written couple of batch query to bring data from another sql server.
I have used proper index and index hints for these query.
I have used SET NOCOUNT ON in stored procedure.
I am having the time difference for one of the blow qeury in various running process that executed in QA as single query , stored procedure for whole batch query and schedule this stored procedure in job
sotored Proc = 31 second > job =28 second > seperate query = 12 second.
one of 15 batch query
declare @last smalldatetime
set @last ='11/08/2002'
UPDATE ENTERPRISE_STATE
SET RESP_BUYER_FST_NAME = i.first_name,
RESP_BUYER_LST_NAME = i.last_name
FROM oesd.dbo.individual i WITH (NOLOCK index(XPKindividual))
INNER JOIN Leap.dbo.fnIndBuyer(@last) fn
ON fn.individual_num=i.individual_num
WHERE ENTERPRISE_NUM = fn.enterprise_num
Anbody can able to give proper reason why I am getting this time difference.
Thabnks,
Ravi
Difference Of Sqlserver Management Studio With Query Analyzer?
Nov 21, 2005what are the new features provided by SQL management studio
View 4 Replies View RelatedTransact SQL :: How To Get Difference To Two Datatime In Seconds Using Logparser Query
Sep 23, 2015In sql we have a built in function
DATEDIFF(second, startdate, enddate)
How can we achieve the same using Log parser query for date time in format (2015-09-22 10:25:30.5220)
Example:
startdate = 2015-09-22 10:25:30.5220.
Enddate = 2015-09-22 10:26:30.5220
After DateDiff I need to get 6 seconds.
Performance Difference: Query Window V. Stored Procedure
Oct 24, 2007Executing the stored procedure took 45 seconds. But copying the code to a query window and setting up the variables (instead of parameters), it took 7 seconds.
In the query window, most of the processing cost (86%) is right up front in a "Distinct Sort." But in exec stored procedure, the cost for this step is 11% and the significant costs are in later "Table Scans."
I don't know why SQL Server would choose different execution plans when the code is identical in each.
Any quick insights?
Many thanks.
Query Performance Difference Between Sql Server 2005 And 2000
Aug 1, 2007Hi,
I'm having an issue with a query I'm running on Sql Server 2005. It's a semi-complex query involving an in-line table function and several left outer joins which are joined on to the results of the function call. Two of the left outer joins are then qualified in a where clause of the form where table.Col is not null; the idea is that the final result set contains data that has no match in those two tables.
The problem revolves around a where clause in the function and the last left outer join (ie, one of the ones qualified with where not null). When I alter the where clause of the function to further restrict the result set the function returns, the query times shoots up from 1 second to roughly 2-3 minutes. Note that the time the function takes to complete is not affected. The difference in time is purely down to what the query does with the results the function provides. Also note that the change to the where clause provides a subset of the original data; it does not add any more data (it actually restricts the original resultset by roughly 1000 rows).
I can bring the query speed back down again by removing the last left outer join - this join takes one of the columns from the function, and joins it to a small table - 924 rows. So it appears that this particular join is the cause of the issue, but only when using the resultset generated from the modified function query.
Now, as the thread title alludes, Sql Server 2000 and 2005 handle this differently, or appear to. When I execute this same query on a Sql 2000 machine, there's no apparent time differences, and the data that is returned is as expected. Does anyone have any suggestions as to what might be causing this and how I can fix it? I could simply return the larger resultset and use managed code to filter out the rows I don't want; however, I would like to get to the bottom of this, especially if it's going to effect future queries.
Cheers,
Chris
SQL Server 2012 :: How To Subtract A Column From A Query Result And Add Difference To Another
Mar 20, 2015I have a query which provides the below result set:
1165 6
1,173.0013
9740 6
9820 13
2271 6
2287 13
10,952.006
11,029.0013
4,074.006
4,103.0013
I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.
1165 6 -8.00
1,173.0013-8.00
9740 6 -80
9820 13 -80
[code]...
SQL Server 2012 :: Query To Find The Difference In Values From Previous Month?
Dec 12, 2013I have my sql tables and query as shown below :
CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
[code]....
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year],
T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
Something like this :
+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----
SQL Server 2008 :: Query To Looking For Cases Of Period Difference In Legal Names
Jun 9, 2015How to write a Query for multiple legal names that have the same CARE Number (same address) with difference of one Legal Name having a period in the name versus the other legal name that doesn't.
For example: Looking for cases of two of the same legal name one set off by period
All Season Equipment Ltd.
All Season Equipment Ltd
West End Housing, Inc.
West End Housing, Inc
Wellings, Norman L.
Wellings, Norman L
North Texas Boats, LLC
North Texas Boats, L.L.C.
Oktibbeha County Cooperative (A.A.L.)
Oktibbeha County Cooperative (AAL)
S & R Turf & Irrigation Equipment, L.L.C
S & R Turf & Irrigation Equipment, L.L.C.
Burke Equipment Company; Burke Equipment-Seaford, Inc.; Newark Kubota, Inc.
Burke Equipment Company
Burke Equipment-Seaford, Inc.
Pleasant Valley Outdoor Power, L.L.C.
Pleasant Valley Outdoor Power, LLC
J & D Lawn and Tractor Sales, Inc.
J&D Lawn & Tractor Sales, Inc"
Default Date Format Difference In Enterprise Manager Vs. Query Analyzer
Mar 13, 2008Not sure why SQL Server displays different date formats for the same date in Enterprise Manager query window vs. SQL Query Analyzer.
When I see table rows through enterprise manager the date field looks like 3/18/2008 12:30:00 PM
Whereas when I run a query a query in Query Analyser/or through any other application the date is shown in a different format: 2008-03-18 12:30:00.000
Is there a setting in SQL Server that can remove the date format inconsistency?
Mon- Fri Bit Fields Query
Oct 25, 2006I am having trouble figuring out how to complete this any help is appreiciated.
I have Mon , Tues , Weds , Thurs , Fri as bit fields I need to write a query for a report to see if the value is true, and display M, T, W, Th, F in a column Days if they are scheduled that day
example:
Mon and Wed I got on the bus the checkboxes are set to True, I want one column in the report Days: to display M, W
Table Fields Name Query
Aug 9, 2004Hello all,
How can i write a sql statement that can give me the name of the fileds in a specified table.
Eg: if i have a table name "table1" , which has the fields "field1", "field2",........ How can i get these fields names using sql query.
any help.
How Do You Query To Get All Fields With A Null Value?
Jun 6, 2001how can i write a SQL query that will pull all records that are equal to NULL??
View 1 Replies View RelatedQuery Dependent On Three Fields?
Oct 13, 2014I am trying to do a select query like below...
SELECT INVENTORY_ITEM_TAB.ITEM_NO, INVENTORY_ITEM_TAB.DESCR, INVENTORY_ITEM_TAB.STATUS_FLG, INVENTORY_ITEM_TAB.PRICINGUOM,
INVENTORY_ITEM_TAB.PURCHUOM, INVENTORY_ITEM_TAB.ITEM_CATEGORY, INVENTORY_ITEM_TAB.ICINTERNALNOTES, INVENTORY_WHS.QTY_ON_HAND,
INVENTORY_WHS.QTY_ON_ORDER, INVENTORY_WHS.QTY_ALLOCATED
FROM INVENTORY_ITEM_TAB INNER JOIN
INVENTORY_WHS ON INVENTORY_ITEM_TAB.ITEM_NO = INVENTORY_WHS.ICWHSPCODE
WHERE (INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS') AND QTY_ALLOCATED, QTY_ON_HAND, Qty_On_ORDER <> 0
ORDER BY INVENTORY_ITEM_TAB.DESCR
But I don't want to select records if all of these fields have a 0 in them - QTY_ALLOCATED and QTY_ON_HAND and QTY_ON_ORDER.
how to do this type of query. If any of those fields doesn't have a 0 then I would want to return it. I just don't want them if all three of those fields have 0.
Search Query Through 3 Different Fields?
Dec 12, 2014I have 2 tables.
Clients
Contacts (multiple contacts for one client)
I'm trying to do a search that pulls a contacts where the search matches either the First Name, last Name or the clients name. If it does return clients, I'd like it to also return all the contacts associated with it.
I have two problems:
1. The query is not bringing up a lot of clients. In many cases a letter brings nothing back. Like G and H even though A and B return results.
2. If it finds a client it only returns one contact. I'd like it to return all contacts for the client.
Here's my query:
SELECT addressbook.clientid, clients.clientname, addressbook.addressid, addressbook.fname, addressbook.lname FROM clients, addressbook where clients.clientid = addressbook.addressid
AND (addressbook.fname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR addressbook.lname LIKE
'".strtoupper($_GET['txtsearch'])."%' OR clients.clientname LIKE
'" . strtoupper($_GET['txtsearch'])."%')
Joining Two Fields In A Query
Apr 26, 2006
I am trying to join two fields in a query in SQL 2000. For example.
Update myTable SET field_1 = @field_1_value , field_2 = @field_2_value, field_3 = @field_1_value + ' x ' + field_2_value
Is this even possible.
I want the user to input values for fields 1 and 2, then in the background combine the two and insert that value in field 3.
Thanks in advance,
Scotty_C
Query To Match Some Fields Out Of Many - HELP...
May 12, 2008I have a table with 6 fields, and I will have all 6 parameters passed in - is there any way to write a query to give me rows based on matching ANY combination of 4 fields out of the 6 parameters passed in ? This is driving me crazy... short of doing an OR statement for all the different combinations - I have no idea how to do this....
This is what I have so far -
SELECT @nodeMachineType = nodeMachineType,
@nodePKID = NodePKID,
@biosVar = Bios,
@computerNameVar = ComputerName,
@diskVolumeVar = DiskVolume,
@guidVar = Guid,
@macAddressVar = MacAddress,
@motherboardVar = motherboard
FROM Nodes_Active
WHERE
case when Bios = @bios then 1 else 0 end +
case when ComputerName = @computerName then 1 else 0 end +
case when DiskVolume = @diskVolume then 1 else 0 end +
case when guid = @guid then 1 else 0 end +
case when macAddress = @macAddress then 1 else 0 end +
case when MotherBoard = @motherboard then 1 else 0 end >= 4
Update Query That Only Updates Certain Fields
May 28, 2006I'm not a T-SQL expert so I'm calling on the pros. I want to design a stored procedure that updates only fields in a given record for which a non-null parameter is specified.
Example:
Create dbo.MySproc
@CustomerID int,
@CustomerFirstName varchar(50)=NULL,
@CustomerLastName(50)=NULL
AS
UPDATE Customers
SET
CustomerFirstName= @CustomerFirstName , //update only if non-null
CustomerLastName= @CustomerLastName //update only if non-null
WHERE CustomerID = @CustomerID
RETURN
So say when calling sproc if @CustomerFirstName is specified but @CustomerLastName is not, the field CustomerFirstName would be updated but field CustomerLastName would retain original data.
Thanx in advance!
Can't Query More Than 15 Fields In A Select Statement
Jul 14, 1999An ADODB error spawn everytime I query more than 15 fields in a specific SQL Table. My sql statement is something like this:
set rs=server.createobject("ADODB.Recordset")
rs.open "select * from mytbl",application("mycon"),1,3
And the error is something like this:
"you can't query more than the maximum field "