1. Select A Field Twice With Different (special) Conditions
Sep 12, 2007
Hi guys,
My challenge is really 3 problems in 1.
I have Table1 and Table2 which are inner joined with an ID.
Each record in Table1 may have up to two corresponding values in Table2, which are Type and Name.
req1- I want to select Table2.Name twice (or more), with each selection conditioned on a specific Type.
req2- I also want all information pertaining to the same ID to be returned in each row
req3- I want all IDs to be returned, even if Table2.Name are empty for Type1 and/or Type2.
I have a Vehicle/Driver Inspection Database and I'm trying to create a Traffic Violation Point System, its a system when drivers commit certain traffic offences they will incur driving-offence points.
System: The points for violations that all occurred within the last 12 months of ONE ANOTHER are added together to calculate point total. If the accumulated points is reached 14 points driver is suspended the points will be REMOVED or minus 14 points after the suspension has been served.
Example:
Date of Offence - Number of Points Aug. 6, 2013 - 6 Feb. 4, 2014 - 4 Apr. 25, 2014 - 4 - 2 May 8, 2014 - 2
Explanation:
On April 25, 2014 two offences incurred and from (Aug 6, 2013 - Apr. 25 2014) 14 points have accumulated (6 + 4 + 4) and suspension is carried out then 14 points is removed. The 2nd offence on Apr. 25, 2014 will be carried forward and added to May 8, 2014 points (total 4 points).
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.
I have a data flow task in which i have multiple OLEDB sources going to a one OLEDB destination via UNION component. I also have a derived column component sitting behind the union, because there are some columns, which exist in the destination table, but are not coming from source, but I am responsible of providing values for those fields. The question is that some of those values are conditional, for eg. Lets say we have a column called RecStatusCode in the derived column component. The value for that column depends on a condition/criteria, so I need to query a db and based on the value of another field, I set the value of the recstatus code. How do i accomplish that? I am thinking expressions, but I need clear direction in steps in how to set a value of a derived column based on a criteria(sql statement). Thanks.
Hi All, This is my problem. I need the out put of a sql select statement to be "true" or "false" depending on the actual columns value is positive or negative. Does any one how to do this. Thanks in advance, -VJ
I can't figure this out for the life of me. Wanted to know if it's possible to select certain date conditions in a query, then later reference those conditions and to only select the max of them.
I need to do this dynamically as I do not know what the max value is. I've provided an example below:
Select var1 From table1 where ( (Date1 = '11/30/2005') OR (Date1 = '12/31/2005') ) and Date1 = (Max of previously selected values e.g. '12/31/2005')
What I can't figure out is how to dynamically retrieve the max of 11/31/2005 and 12/31/2005. Any ideas are greatly appreciated.
Select c.Plan, c.ClaimNumber from tbFLags c inner join tbMembers m On c.Claim = m.HistoryNum where c.Plan = 'J318' and c.Paymon = c.Rmon and c.Rmon = '2008-03-01'
Now I want to add these into this statement, what should be done.
Members meeting any of the 3 sets of criteria should not be selected
1) tbFlags.Hosp='1'
2) tbFlags.RD='1' OR tbCMSFlags.RAType in ('D', 'I2')
Have a nvarchar column in ms sql that, based on user input, may or may not have a carriage return and a line break together (CHR(13)&CHR(10)), just a carriage return (CHR(13)), just a line break (CHR(10)). In using that column in ssrs, I need to find if any of the above exist and replace them with the string "x0Dx0A", "x0D", or "x0A" respectively.
System.Web.Services.Protocols.SoapException: The Value expression for the textrun ‘Info.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'Replacement' of 'Public Function Replace(Expression As String, Find As String,
How can i insert a if-else condition in a select statement or is there a way to specify a condition within a select statement? Any reply is really appreciated..thank you..
SQL Version: 2008 (not r2) Problem: My Select statement seems to be unaffected by some of the conditions in the WHERE Clause. For instance
JCCD.Mth >= cutoffs.FiscalYear_FirstMonth (value '20130101') AND JCCD.Mth <= @WIPMonthCurrent (value '20130101')AND LTRIM(RTRIM(JCCD.Job)) = '71-' (see output and code below) SQL Code: declare @WIPMonthCurrent date = '20130101' SELECT      JCCD.JCCo, JCCD.Job, JCCD.Mth, JCCD.Source, sum(JCCD.ActualCost) AS CostToDate
Im starting a project that will be used to reserve rooms in a building. My problem right now is writing an sql statement that will return a dataset with the proper results. I have three tables setup below. I need a dataset that returns rooms that are closed on a certain date and a set that returns open rooms on a particular date.I cant seem to figure out that sql statement... Any suggestions will be greatly appreciated. tblRooms pkRoomID fkReservationID tblDates pkDateID fkReservationID tblReservation pkReservationID fkRoomID fkDateID
This is an exact matching record and straight forward
Is it possible to identify the record using T SQL query based on the following scenarios
1) return the record - If all the three where conditions match 2) if record not found check and return the record where 2 columns values in the where condition match
-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values
SELECT Productname FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1
Select DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName, s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType, ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc, ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode, ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,
[code]....
however, I only want the lastest a.dbPatApptTime and only when a.dbPFStatus = 1 and a.ClientRef = 'EPS'
So the stand alone sql could be....
Select Top(1) dbPatApptTime as LastVisitDate, dbSchTypeDesc as LastVisitDesc from appointments where dbPFStatus = 1 and clientref = 'EPS' order by dbPatApptTime desc
I'm just not sure how to incorporate that into my sql or whether there is a better way,
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?
I've got a tricky one. I'm trying to do a SELECT statement to return all of our email fields that include the Newline character, or Char(13). I've tried all of the following with no result:
WHERE Email like "%Char(13)%" WHERE charindex(Char(13), @Email) > 0 WHERE patindex(@NewLine, @Email) > 0
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?
I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks
I would like to query a table for a max value of one field for a distinct combination of two other fields. Let's call these fields RowID, ObjectID, and ObjectType. RowID is an auto-increment field, so for each distinct combination of ObjectID and ObjectType, there will be many values of RowID. To visualize an example:
Of these rows, I would only want 4, 5, and 6 (max values for distinct combination of ObjectID and ObjectType).
I hope I explained this clearly. I would imagine I'd need to use some form of nested query, but nothing I have tried so far has worked. I am using SQL 2005.
Hi, I've got a question! In a database I have a field interest-target with values like 8,15,115,3 and 18,13,15,6 and 51,6,7,118 etc. Now I like to select from these fields the value 8. I tried the following: SELECT * FROM TABLE WHERE FIELD LIKE '%8,%' OR FIELD LIKE '%,8,%' OR FIELD LIKE '%,8%' I will the also get the value 18 wich I don't like :-( Any solutions here? Thanks! Roel
Is there a way in SQL Server to query for a character that is in a field? ... I have this old table before there was form validation, and I want to get the addresses out of the column that actually have an @.
I want to do something like this.
SELECT email FROM xyz WHERE email contains '@' AND email contains '.'
Hi, Can someone tell me if it is possible to do an SQL insert with a select (to copy specific records) query and specify the value for a specific field to insert in the new records instead of using the value in the field in the select statement. If so can you provide me with a simple example. Cheers Mark :)
I've a select SELECT Field1, Field2, Field3 FROM Tablethe problem is that I can have filled Field1 OR Field2 ... I would like to create a new column in the result for see only the filled field if Field1 is = null or = ' ' I want to see Field2 if Field2 is = null or = ' ' I want to see Field1
OK, i'm sure this is an easy one, but can't seem to figure it out. Basically need to search against two database columns (fields) to see if either contain the work "test". Here is an example of the statement but of course dosn't work correctly: SELECT * FROM tbl_db WHERE skills LIKE 'test' AND title LIKE 'test' Please note the AND is incorrect. What i need to accomplish: if one record contains 'test' in its skills field then SELECT it. if one record contains 'test' in the title field the SELECT it. I need to make sure that is 'test' is in both skills and title fields then only display once. Thanks, Jake
