Hi! I have a table Tbl1 has to columns:
A B
_________
Ibm Me
Sony Me
Me Bob
Me Frank
I'd like to select all rows where B=ME and A=Me Thanks for the help
I have a table that has a DateTime column which uses a DataTimedatatype. How do I retrieve a range of records based on the month andyear using ms sql?Eugene Anthony*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedI need to list all the records in Table2 which don't have matching field values in Table1.
This the the exact opposite of what I need:
SELECT DISTINCT
Field1,
Field2,
Field3,
Field4,
Field5
FROM
[Table1]
WHERE EXISTS(
SELECT DISTINCT
FieldA,
FieldB,
FieldC,
FieldD,
FieldE
FROM
[Table2]
)
The above seems to give me all records in Table1 in which the five fields match the five fields specified in Table2. What does not show up is the test record I put in Table2 which is not in Table1.
What I need, however, is the exact opposite.
I tried the above using NOT EXISTS but I get no records at all.
How do do this?
So let's say I have a table Orders with columns: Order# and ReceiptDate. Order#'s may be duplicated (Could have same Order# with different ReceiptDate). I want to select Order#'s that go back 6 months from the last ReceiptDate for each Order#.
I can't just do something like:
SELECT *
FROM Orders
WHERE ReceiptDate >= add_months(date,-6)
because there could be Order#'s whose last ReceiptDate was earlier than 6 months ago. I want to capture all of the instances of each Order# going back 6 months from each last ReceiptDate relative to each Order#.
We have three fields in a table: firstname, Surname & Organisation. Firstname & surname will always be filled but in most cases organisation is NULL.
Part of what these fields will be used for is a mailshot. If there is no organisation data then the mailshot will open with 'Dear Firstname Surname, ' but if the organisation is present they would like 'Dear Organisation'.
Is it possible to create a select state that checks the organisation field, and if a value is present return that value else return the firstname, surname combination? I have tried various things but I cannot get it to work.
I know the ideal situation would be to do this type of condition check at scripting level (PHP, ASP, Visual Basic) but my bosses would like to try it at SQL level.
The situation is that we have resources (trucks) that perform shifts. Shifts consists of actions. A resource can perform multiple shifts.
For every resource we want to find the record that:
- Is 'younger' than the last realized action.
- Has actionkind pickup, deliver or clean
I have constructed a solution with CTE and row_number but I was curious if there would be other alternatives. The fact that I'm joining a CTE onto itself and subject the outcome to a partition makes me think there are sharper ways.
Note that the action id in the data below is also sorted but in practice this need not be the case. The sorting key is prevalent.
output of the query is
id_action id_resource actionKindCode
4665 4 clean
34540 96 pickup
24000 901 clean
declare @mytable table (
id_action int,
id_shift int,
[code]....
ID (PK), userID (FK), from [Datetime], to[datetime], type [int] , value [nvarchar]
Conditions
I want to select the column "to" which is a datetime-column for all users within the table.
One user can have multiple registered rows in "mytable", so I want to select the row that contains the newest "from" date.The newest "from"-column for a specific user is where I am struggling... Because if the "to"-column of any of the registered rows by the current user is equal to the newest "from -1 day" column for the user, I want to select the second newest from-date.
Code:
//Handwritten
DECLARE @from DATETIME //Current Selected datetime [the newest of the current user doing the query]
DECLARE @id INT //userID
DECLARE @tbl TABLE (from datetime, userid int)
[code]....
But for user ID 201 [all users with Type = 1], I need to run through some more code, to figure out which date we want... Which date do I want? The "from"-date that is the newest, but if "to" is just 1 day before "from", I want to select the "from" at that row instead... Example with numbers [integers] instead of Date:
Code:
From - To
5 8
10 15
16 20
I want to select "10" in the from column as the maximum "from"-integer, and not 16. Because in the "To" column we have a match in the "from" column on the condition "To + 1" [15+ 1 == 16], so then I want the "from" value where "To" equals 15, which is 10. The top row I do not want, because the difference between From and To [10 - 8 is larger than 1]...
This means that the Image shown... I am first selecting the "1", then I do some checks, if the "to" for the user is 1 day earlier than the MAX(from) I grab a new "from" value...
Code:
SELECT MIN (t1.from) FROM table as t1
WHERE
t1.from IN
(
SELECT t2.to+ 1 FROM table as t2
WHERE t2.userID = @id
)
I have an issue accumulating a value based on certain conditions:
I need to sum up the days column as you can see table below. The sum is based on the StartNewFlag.
When the flag is 1, I need to start a new sum from zero, else I need to accumulate to the previous rows.
CREATE TABLE #foo
(
ItemID int,
StartDate datetime,
EndDate datetime,
StartNewFlag bit,
[Code] ....
My Results must look like one of the following tables (either one would work, however the second one is preferred)
Result 1:
ItemID StartDate EndDate StartNewFlag Days TotalDays
1 2015-07-01 00:00:00.000 2015-07-05 00:00:00.000 1 4 5
1 2015-07-08 00:00:00.000 2015-07-09 00:00:00.000 0 1 5
1 2015-07-11 00:00:00.000 2015-07-15 00:00:00.000 1 4 11
1 2015-07-18 00:00:00.000 2015-07-21 00:00:00.000 0 3 11
1 2015-07-22 00:00:00.000 2015-07-26 00:00:00.000 0 4 11
OR
Result 2:
ItemID EndDate TotalDays
1 2015-07-09 00:00:00.000 5
1 2015-07-26 00:00:00.000 11
I need to update the status of a client when they make a payment of a certine amount. My problem is this, the two pieces of information needed to do this are comming from two tables. For example;
@ClientID Int,
@PmtAmt Money
IF @PmtAmt >= tblSettings.TopAmt THEN
Update tblClients
SET
ClientStatus='High'
WHERE ClientID=@ClientID
ELSE
Update tblClients
SET
ClientStatus='Medium'
WHERE ClientID=@ClientID
ENDIF
How do I do this in a stored procedure? I need to select the TopAmt from the table tblSettings and then update the table tblClients.
Hi,
I have the following table in MsAccess
EmployeesA
empId integer,
empName varchar(60),
empAge integer,
empStatus char(1) - can be N,D or S - New, Deleted or Shifted
and the following in Sql2005
EmployeesB
Id smallint,
Name varchar(60),
Age int,
Status char(1) - Bydefault 'N'
I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.
If empStatus = N in EmployeesA, then insert a new record in EmployeesB
If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'
If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.
How do I do it for each table each row in EmployeesA using a foreach file loop?
Thanks,
lolsron
I have conducted a thorough search in the forums and cannot quite find my answer. I have a date field called open_date. If the open_date is more than 30 days old, I need to count it. I have started with the following code:
SELECT 'Older_Than_30Days' =
CASE
WHEN open.date >= 30 THEN '1'
ELSE '0"
END
My problem is the WHEN.
Hi all sql experts,
I have a table that has columns 1)ProductName and 2)qualification.
The reporting scenario is i have to display the highest qualification among all qualifications for the particular product name:
Suppose 3 qualifications(B,P,D)
D__lowest
B__Medium
P__highest
So i need P always if P is there among the group.
The different conditions would be
1)If the ProductName has only only type of qualification then display that.
2)If both B and D, then display only B.
3)If both P and D, then display only p.
4) If p,B and D then display only P.
The current table would be like:
ProductName Qualification Col3 Col4
P1 B
P1 P
P1 D
P2 B
P2 D
P3 P
P3 D
P4 D
P5 B
P5 B
P5 B
The expected Result:
ProductName Qualification Col3 Col4
P1 P
P2 B
P3 P
P4 D
P5 B
Any sort of help would be highly appreciated
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.
SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.
Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?
Thanks,
Marc
Product version : SQL Reporting Service 2005 with SQL SP2
It's a report with 3 sub-reports in it, i want to display only one of the 3 sub-reports at a time depending on 1 or 2 parameters received by the parent report. These parameters are verified by an expression into the visibility tab of each sub-report. The two parameters are 2 lists with possibles values between 1 and 2 (not query based). I have verified these values and they are correctly received by report depending on the selection of each list.
Ex.: SubReport1.expression = IIf(Parameters!Regroupement.Value = 1, True, False)
SubReport2.expression =IIf(Parameters!Regroupement.Value = 2 AND Parameters!SautPage.Value = 1, True, False)
SubReport3.expression =IIf(Parameters!Regroupement.Value = 2 AND Parameters!SautPage.Value = 2, True, False)
So here are the posibilities :
when Regroupement.Value = 1 --> SubReport1 will be shown
when Regroupement.Value = 2 AND SautPage.Value = 1 --> SubReport2 will be shown
when Regroupement.Value = 2 AND SautPage.Value = 2 --> SubReport3 will be shown
Now why that doesn't work ? I always see the same report and it's not the right one displayed even i change the selection of my 2 lists for the parameters value.
I want to calculate the target based on Flag value if Flag value is "Y" ....than MAX(Customer Target) else MAX(SLA target).Flag column contains "Y" , "N" and some blank values . Flag, Customer Target and SLA target are the columns in Table1. I have used the below formulas
Target:=IF('Table1'[ Flag]= "Y",MAX('Table1'[Customer Target]),MAX('Table1'[SLA Target]))
Target:=IFERROR(IF('Table1'[Flag]= "Y",MAX('Table1'[Customer Target]),MAX('KPI'[SLA Target])),BLANK())
I have an issue in trying to format rows base on conditions. Below is a replication of the tables and the select statement.
CREATE TABLE #CompareVal
(CompareValID INT Not Null
, ValName NVARCHAR(75) Null
, Vehicle INT Null
, Driver INT Null
);
GO
[Code] ....
First issue, James and Jane does not have a driver available and that should show "No Driver available"I am to compare values in VehicleAvailable and DriverAvailable to the first row - (Group Value Standard row) so that when a value is less than the value in first row, it should be Gold, if equal to, Blue and if greater than then, Red.
The first row is to be Black. In other for me to be able to compare, I added columns like so:
SELECT #CompanyName
, [Description]
, ISNULL(CAST(VehicleAvalible AS NVARCHAR(30)),'N/A') AS VehicleAvalible
, ISNULL(CAST(DriverAvailable AS NVARCHAR(30)),'No Driver available') AS DriverAvailable
, 0 AS TotalVehicles
, 0 AS TotalDrivers
[Code] ...
And my expression for "VehicleAvailable" column is :
=Switch(Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value = Fields!TotalVehicles.Value, "Black"
, Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value < Fields!TotalVehicles.Value, "Black"
, Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value > Fields!TotalVehicles.Value, "Black"
[Code]....
This doesn't work as I am comparing integer against text value. How do I format to get result like the below image?
The data I have is as follows -
ID1 ID2 Date Action
100 500 09/08/14 Open
100 500 09/24/14 Close
101 510 07/10/15 Open
101 510 07/19/15 Close
The output I want in a single result set is -
ID1 ID2 Open_Date Close_Date
100 500 09/08/14 09/24/14
101 510 07/10/15 07/19/15
Any way to do this in T-SQL .
TABLE DEFINITION
Column NameComments
CustomerIDINT
SequenceNoINT
CommentsVARCHAR(MAX)
CUSTOMER TABLE
CustomerIDSequenceNoComments
11ABC D
12CDE
13ABC
14ABC D
15CDE
16abc
27ABC DEF
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST
OUTPUT
CustomerIDSequenceNoComments
13ABC
14ABC D
15CDE
16abc
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST
Records should be filtered by
1.Display only Unique Comments from Customer Table for all the customers,
2.If Comments are same then display the row which has maximum SequenceNo
I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.
Here is the scenario:
I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).
I would like to extract Key_member where key_member_type="PM" and active as of tb1(month_end_date).
Is this possible using DAX ?
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
Okay, let's see if I can explain this one. I am summing multiple lines of data from a labor detail table, by status. Using this query
SELECT EM.Lastname, LD.WBS1, LD.WBS2, P.Longname, SUM(LD.Held) AS HELDLABOR, SUM(LD.TBWRittenOff) as TBWrittenOffLabor, SUM(LD.WrittenOff) AS WRITTENOFFLABOR
FROM PR P INNER JOIN
(SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff,
SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff
FROM LD
WHERE BillSTatus IN ('x','h', 'w')
GROUP BY WBS1, WBS2) LD ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 INNER JOIN
EM ON p.ProjMgr = EM.Employee
WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r'
GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname
ORDER BY EM.Lastname, LD.WBS1
I get these results...
LastnameWBS1WBS2LongnameHELDLABORTBWrittenOffLaborWRITTENOFFLABOR
Boulet0001039.000100S.r. 41 & Del Prado Shopping Center/miscellaneous civil engineering18408.6309923.47
Boulet0001039.000102S.r. 41 & Del Prado Shopping Center/rezoning process008790
Boulet0001039.000106S. R. 41 & Del Prado Shopping center / const plan rev for environ planting2200.6800
Boulet0001039.000107S.r. 41 & Del Prado Shopping Center/cpd rezoning9335.4600
Okay, so now, of coarse, I want to change everything. I only want to return rows if there is a value > 0 in either Held Labor or TBWrittenOffLabor. Otherwise, no row return.
Here's what I tried, but it didn't work out because it still returns rows, it just zero's out the values for written off labor.
SELECT EM.Lastname, LD.WBS1, LD.WBS2, P.Longname, SUM(LD.Held) AS HELDLABOR, SUM(LD.TBWRittenOff) as TBWrittenOffLabor,
SUM(CASE WHEN LD.HELD > '0' THEN LD.WrittenOff ELSE '0' END) AS WRITTENOFFLABOR
FROM PR P INNER JOIN
(SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff,
SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff
FROM LD
WHERE BillSTatus IN ('x','h', 'w')
GROUP BY WBS1, WBS2) LD ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 INNER JOIN
EM ON p.ProjMgr = EM.Employee
WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r'
GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname
ORDER BY EM.Lastname, LD.WBS1
Here's my current SQL:
SELECT
RN_TEST_ID AS 'Test ID',
MAX(RN_EXECUTION_DATE) AS 'Last Execution Date',
MAX(RN_EXECUTION_TIME) AS 'Execution Time',
RN_DURATION AS 'Run Duration'
FROM RUN
[code]....
Here's a sample of data returned:
Test IDLast Execution DateExecution TimeRun Duration
86722/9/2015 0:0012:08:16180
86822/9/2015 0:0011:29:06181
86842/9/2015 0:0008:29:17119
105252/3/2015 0:0019:03:4089
105252/3/2015 0:0019:10:13305
106682/3/2015 0:0018:55:43103
106682/6/2015 0:0018:10:50123
114572/3/2015 0:0011:40:0726
What I need are two things:
1. The query should only return one record for each test id
2. The record returned should be the most recent. By most recent I mean the RN_EXECUTION_DATE and RN_EXECUTION_TIME of the returned row should be the most recent in time.
For example, in the sample data there are multiple rows with the same test id (for example 10668 and 10525. The 10525 is even more problematic since its execution date is the same for both rows returned - the execution times differ. Again, I want one record per test id and that record should be the most recent in time.
Hi All
I want to select a certainnumber of rows
select custno, amt, balance from customer where custno='customerno'
when showcust='r' then select rows where amt<balance
when showcust='c' then amt>balance etc
if showcust='' then show everything
Any insight will be greatly apprecaited.
Thanks
Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ... There will be entries where there can be duplicate customerFirst and customerLast names. I would like to just return a single entry of the duplicate names and all associated row information. IE: [customerID], [customerFirst], [customerLast], [ email], [program] 01 Bill Smith bill.smith@hotmail.com ymca 02 Bill Smith bill.smith@hotmail.com Sports 03 jon doe jon.doe@hotmail.com AAA 04 jon doe jon.doe@hotmail.com Ebay 05 Paul Sprite paul.sprite@hotmail.com Rec Desired Returned result: 01 Bill Smith bill.smith@hotmail.com ymca 03 jon doe jon.doe@hotmail.com AAA
05 Paul Sprite paul.sprite@hotmail.com Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); dAdapter.Fill(pocDS, "Data Set"); However this is throwing up an error when i build the app: An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException:
An expression of non-boolean type specified in a context where a
condition is expected, near ')'.
Source Error:
Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison 1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry
OK. I have this query, works on another box fine.
SELECT *
FROM bookkeep RIGHT OUTER JOIN
acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum
WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S')
on my sql box, if i run it, i get no data.
i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works
OR
if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works.
my problem is the basic query is hard coded and i really can't change it.
is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need the best way to accomplish this:
Select h.worknumber, h.itemcode, h.descr, h.task_descr, h.qty, h.itemised,
h.serialnum, h.manufacturer, h.model_id, h.depot, h.date_in, h.date_approved,
h.est_complete_date, h.actual_complete_date, h.meterstart, h.meterstop,
h.custnum, h.name cust_name, h.addr1, h.addr2, h.town, h.county, h.postcode,
h.country_id, h.contact, h.sitename, h.siteaddr1, h.siteaddr2, h.sitetown,
[Code] ....
Each work order should only be returned once, and with the following additional criteria:
1. i.meter - this should return only the lowest number from that file.
2. sm.next_calendar_date - this should return only the most recent date out of those selected for the certificates on this piece of equipment
3. wh.meterstop as [Last Service Hours],
wh.date_created as [Last Service] - this should return the number from wh.meterstop at the most recent wh.date_created for that piece of equipment.
This is a simple one, and I know that it has to be fairly common, but I just can't figure out an elegant way to do it. I have a table with the following fields:
OrderID (FK, not unique)
InstallationDate (Datetime)
CreateDtTm (Datetime)
There is no PK or Unique ID on this table, though an combo of OrderID and CreateDtTm would ostensibly be a unique identifier.
For each OrderID, I need to pull the InstallationDate that was created most recently (based on CreateDtTm). Here's what I've got so far, and it works, but man is it ugly:
SELECT a.OrderID, InstallationDate
FROM ScheduleDateLog a
INNER JOIN
(SELECT OrderID, max(convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)) as TopRecord
FROM ScheduleDateLog GROUP BY OrderID) as b
ON convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)=b.TopRecord
AND a.OrderID = b.OrderID
Hi,
I was wondering how you perform a select statement based on a specific date that will show all the records no matter which times belong to the specific date.
I have been having trouble with this one when using a single date, I think this is because of the time property as no records are displayed.
Thanks for any help.
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
Can someone please help me here?
Thanks,
Romakanta
Table1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.
--Version 1:
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE
UserRoles.Active = 'TRUE'
-- Version 2
SELECT
*
FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId
AND UserRoles.Active = 'TRUE')
So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.
So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?
Thanks for whatever information you can provide.
Hi,
i have a table like below
table:-
id col2 col3 col4 col5 col6
1 2 3 4 5 6
2 5 8 4 7 6
3 4 8 2 6 9
4 2 5 8 6 3
5 6 9 5 5 9
i want to write a stored procedure where i pass column names a parameters and i want to get result based on that
For ex:-
if i pass the parameters as
col3 and col5 where id =1 then i should the result as
id col3 col4 col5
1 3 4 5
and if i pass input as col2and col6 where id =3, the result should be
id col2 col3 col4 col5 col6
3 4 8 2 6 9
can anyone help on this??