Tricky SELECT
Jul 18, 2007
I have a LastName field which holds this data
LastName
Johnson|Vasques
Adams|Fox|Johnson
Vasques|Smith
Now let’s say I have a SELECT Stored Procedure which takes 1 parameter @LastName.
The @LastName can be something like this: “Fox|Smith�.
I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).
In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith .
Thank you.
View 13 Replies
ADVERTISEMENT
Jul 18, 2007
I have a LastName field which holds this dataLastNameJohnson|VasquesAdams|Fox|JohnsonVasques|Smith Now let’s say I have a SELECT Stored Procedure which takes 1 parameter @LastName.The @LastName can be something like this: “Fox|Smith�.I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith . Thank you.
View 14 Replies
View Related
May 13, 2003
Hi, I'm tring to break my head finding a solution of how to return the following result:
I have the source table -> S_TAB with some data like
COL1 COL2 COL3
ABC DTT COL
ANC DRT COL
ANC DRT COL
......
......
what I need is come up with a single select statement (a view) to get the following output:
select <something> as RID, COL1, COL2, COL3 from S_TAB
RID COL1 COL2 COL3
1 ABC DTT COL
2 ANC DRT COL
3 ANC DRT COL
4 .....
5 .....
Any Idea will be appreciated
Dim
View 2 Replies
View Related
May 22, 2008
I don't even know if this is possible, but I need to find a way to do the following:
I have a select statement that returns the the Top (x) scores from a table called Rounds. The number of rows (x) will vary based on another calculation that I have, in this example I used 3.
SELECT TOP (3) Scores
FROM Rounds AS Rounds_1
WHERE (UserID = 'testuser')
I need to take the 3 values from this example, and calculate the AVERAGE. How do I do that?
Thank you.
View 2 Replies
View Related
Nov 26, 2005
In a system holding data from questionnaires, I have two defined tables: one holding info on the respondent (r) and the other holding answer data (ad)
The layout of the respondent table:
r_idint
weekint
The layout of the answer data table:
ad_id int
r_idint
qvarchar(10)
a_valint
a_text varchar(50)
Each row in the ad table matches data for one question on the questionnaire.
Lets say a
<sql>
select *
from respondent r, answer_data ad
where r.r_id = ad.r_id
</sql>
returns the following data:
<result>
r_id, week, ad_id, r_id, q, a_val, a_text
1, 40, 1, 1, '1', 1, 'Destination 1'
1, 40, 2, 1, '2', 1, 'Bad'
1, 40, 3, 1, '3', 3, 'Good'
2, 40, 4, 2, '1', 2, 'Destination 2'
2, 40, 5, 2, '2', 2, 'Acceptable'
2, 40, 6, 2, '3', 4, 'Excellent'
3, 41, 7, 3, '1', 1, 'Destination 1'
3, 41, 8, 3, '2', 4, 'Excellent'
3, 41, 9, 3, '3', 4, 'Excellent'
</result>
Extracting the mean value of answers by week is easily done using the following select:
<sql>
select week, q, avg(cast(a_val as float)) mean
from respondent r, answer_data ad
where r.r_id = ad.r_id
and q > '1'
group by q, week
order by q, week
</sql>
This would give a result like:
<result>
week, q, mean
40, '2', 1.5
40, '3', 4.0
41, '2', 3.5
41, '3', 4.0
</result>
Now the tricky part - a result by destination (ad.q = '1') has been requested by the customer.
Doing a
<sql>
select q, avg(cast(a_val as float)) mean
from respondent r, answer_data ad
where r.r_id = ad.r_id
and q > '1'
and r.r_id in (
select r.r_id
from respondent r, answer_data ad
where r.r_id = ad.r_id and q = '1'
)
group by q
order by q
</sql>
returns the requested data:
<result>
q, mean
'2', 2.3333333333333335
'3', 3.6666666666666665
</result>
Only, it lacks info on the destination. What I need is something like this:
<result>
dest, q, mean
'Destination 1', '2', 2.5
'Destination 1', '3', 3.5
'Destination 2', '2', 2.0
'Destination 2', '3', 4.0
</result>
How can I achieve that?
Thanks,
Jacob Dall
View 2 Replies
View Related
Jul 20, 2005
Dear GroupI wonder whether you can push me in a direction on how to design thefollowing statement. I'm looking for a SELECT with some tricky ORDERBY.The database table looks like this:MenuID TabText SubTabID TabOrderID------- ----------- ----------- -----------1 Main 0 12 Cars 0 23 Boats 0 34 Planes 0 45 Pick-Ups 2 16 Campers 2 2The result should look like this:MainCarsPick-UpsCampersBoatsPlanesNotice that 'Pick-Ups' and 'Campers' are a subcategory of 'Cars' andmust appear in the result directly following 'Cars'.In more detail:'Main', 'Cars', 'Boats' and 'Planes' are top-level categories and'Pick-Ups' and 'Campers' are subcategories of 'Cars'. The SubTabIDvalue of an item identifies to what top-level category a subcategorybelongs.The TabOrderID specifies in what order the items should be sorted,e.g. 'Pick-Ups' comes first and 'Campers' second.Thanks very much for your help & efforts!Martin
View 3 Replies
View Related
Apr 7, 2008
I have stumbled upon a sql question I cannot answer. I am looking for the following SELECT sql statement:
Basically I need a way to get "5 days ago from today". BUT, the trick is that there is a table called tblnoworkday with contains weekends and holidays and those dates cannot count. So basically what I am really trying to get is "5 Business days ago from today".
So it would basically do this for an query input date of '4/9/08'. If the table is called TblNoWorkday and contains the following records:
...
2008-04-06 00:00:00.000
2008-04-05 00:00:00.000
...
This is the last 5 business days then: (not in the table)
4/9/08
4/8/08
4/7/08
****weekend****
4/4/08
4/3/08
The query should return just 4/3/08. The problem is 4/3/08 doesn't exist in the database since the database only contains the no work days.
Any ideas on how to do this in a simple query without having to create another table with the valid working dates?
Thanks Before Hand,
Adiel
View 10 Replies
View Related
Nov 8, 2004
I have 2 tables joined together by the IDs, People and the pets they
own
PEOPLE
ID NAME
1 JohnSMith
2 JaneDoe
PETS
ID PET
1 Dog
2 Cat
2 Hamster
2 Hamster
2 Fish
I have create another where the PETS are in one column separated by
semi-colons and removing the dups
NEW TABLE
ID NAME ALLPETS
1 JohnSmith Dog
2 JaneDoe Cat;Hamster;Fish
What is the best way to do it? The only way I can think of is to run
an update where it checks to see if the value already exists
THanks!
View 1 Replies
View Related
Mar 18, 2008
Hello,
I have four diffrent tabels:
bo_ Class, bo_Competition, bo_Result, bo_Licence
And list all Licence where bo_Class.classRankingNbr >0
ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale
------------ --------- --------------- ------------- ----------- --------------- ----------
M70891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H
M050887PON01 Pontus Svensson U23 SM 2007 - Herrar 2 1 H
M181188MAR01 Marcus Edlund U23 SM 2007 - Herrar 3 1 H
M190291JOH01 Johan Helldén U23 SM 2007 - Herrar 4 1 H
M180360GER01 Gert Lindholm Herrar klass A 1 2 H
M041062MIC01 Micael Hamberg Herrar klass A 2 2 H
K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D
K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D
K050785CAR01 Carin Johansson U23 SM 2007- Damer 3 1 D
If bo_Class.ClassRowNbr for an Class is 1 get out place 1 from that competition,
and if an ClassRowNbr is 2 get out top2 from that competition.
And so on.
From that list i want an SubQuery list where all licens order by where
bo_Result.ResultPlace = bo_Class.ClassRowNbr.
In text form:
ResultLicNbr FirstName SurName ClassName ResultPlace ClassRankingNbr ClassRankingFemaleMale
------------ --------- --------------- ------------- ----------- --------------- ----------------------
M170891DEN03 Dennis Vrabac U23 SM 2007 - Herrar 1 1 H
M180360GER01 Gert Lindholm Herrar klass A 1 2 H
M041062MIC01 Micael Hamberg Herrar klass A 2 2 H
K191286SOP01 Sophia Bergvall U23 SM 2007- Damer 1 1 D
K030889REB01 Rebecka Larsen U23 SM 2007- Damer 2 1 D
K180793LIN01 Linnéa Hamberg Damer Klass A 1 1 D
Hope someone can take the time and help me out.
Best regards
Gert Lindholm
View 6 Replies
View Related
Oct 18, 2005
One statistic questiong from an db Resultat.
SELECT m.Namn + ' ' + m.ENamn AS Spelare, SUM(r.Serier) AS Ser, SUM(r.Poang) AS Po, ROUND(SUM(r.Resultat) / SUM(r.Serier * 1.0), 2, 1)AS Snitt, ROUND(SUM(r.Poang * 1.0) / SUM(r.Serier), 2) AS [P Snitt], ROUND(SUM(r.Miss * 1.0 / r.Serier), 2, 1) AS Miss, SUM(r.Miss) AS [Sa Miss], MAX(r.Resultat) AS Bästa, MIN(r.Resultat) AS Sämsta
FROM Resultat r INNER JOIN
Medlemmar m ON r.Spelare = m.ID
WHERE (r.Omgang IN (SELECT DISTINCT TOP 3 Omgang
FROM Resultat
ORDER BY Omgang DESC))
GROUP BY m.Namn + ' ' + m.ENamn
ORDER BY 4 DESC
What i want to do is sort out Min(r.Resultat) where serier is mor than 3, Not WHERE (r.Resultat) >3. More like in Access "Min(IIf(r.serier=4,r.resultat,Null)) AS [Sämsta]" But that i cant do in SQL
Have also tryed with "WHERE (SELECT MIN(r.Serier) FROM Resultat
Resultat r INNER JOIN
medlemmar m ON r.Spelare = m.ID"
Get lowest result same on all players.
Best reg
Gerten
View 2 Replies
View Related
Feb 7, 2006
Hello allI've got this tricky situation that I would like to solve in SQL, butdon't know how to do. This is the table:Id = 3, VId = 2, Time1 = 10:00, Time2 = 14:00Id = 4, VId = 2, Time1 = 16:00, Time2 = 17:00Id = 5, VId = 2, Time1 = 18:00, Time2 = 19:00Id = 6, VId = 2, Time1 = 20:00, Time2 = 21:00Id = 7, VId = 3, Time1 = 11:00, Time2 = 13:00Id = 8, VId = 3, Time1 = 15:00, Time2 = 16:00Id = 9, VId = 3, Time1 = 18:00, Time2 = 20:00GetRows @Time='15:30' will return row with Id=4GetRows @Time='16:30' will return row with Id=4 and row=9Logic behind this:Return row n where Time2 of Id=(n-1) < @Time < Time 1 of Id=(n) and sameVId.Ie. if @Time = '15:30' then Time2 of Id = 3 is lower than @Time, andTime1 of Id = 4 is higher than @Time => return row with Id = 4.This got a bit messy but if someone could decipher this and possiblygive an answer I'd be very glad.regardsJohnny
View 5 Replies
View Related
Jan 28, 2008
I have an integer in the database that was saved in reverse byte order (BigEndian). Due to some backward compatibility issues (long story) I can't just convert the number to the normal format and save it that way in the database.
Instead, when I read the number in my program, I just reverse its bytes and display the proper value, and translate the number back when it has to be saved back to the database.
Now, the problem is that there are some views that pull this numbers directly from the database and display it.
My question is: can this number be converted from BigEndian to LittleEndian similarly to how I do in my program using T-SQL?
Thanks.
View 4 Replies
View Related
Dec 13, 2007
This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it.
I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).
View 2 Replies
View Related
Mar 21, 2004
I have a table as follows:
Fixtures(ID, HomeTeam, AwayTeam, WeekNumber)
Each team plays alternately at home then away throughout the course of a season.
I want perform 2 seperate queries on this table.
Query 1:
I want to select a particular teams opposition for the entire season.
Query 2:
I want to select a particular teams opposition for a particular weekNumber.
Thanks
View 3 Replies
View Related
Oct 6, 2004
I have a stored procedure called TC3_GetAllJobOrders which takes 8 parameters as filter values and dynamically builds a statement to filter the data. If you pass in null values for the filters, then the data is not filtered.
I want to write another SP called TC3_GetNewestJobOrders which takes 9 parameters. The first 8 parameters are the same as TC3_GetAllJobOrders and the 9th parameter is numRecs which defines the number of records to return. The procedure should call TC3_GetAllJobOrders, sort the data by date and then return the top numRecs. However, I can't figure out the best way to write this stored procedure because it references another one.
I am trying to stay away from dynamic SQL if at all possible. But I am thinking I will have to use dynamic SQL because I don't think the number of records to be returned (as defined using the TOP keyword) can be parameterized. However, I was trying to write a dynamic SQL statement so that the end statement looked something like:
SELECT TOP 10 * FROM ( EXECUTE TC3_GetAllJobOrders ... ) ORDER BY createdOn DESC
However, I guess having the EXECUTE in parens like that is no good and SQL Server doesn't like it. What is the best/correct way to do it?
View 4 Replies
View Related
Mar 10, 2005
Hello,
I have a SQL statement which is working OK:
SELECT diakod,diatexter,skada FROM (SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl = @Kod) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod2)) AS SQ GROUP BY diakod,diatexter,skada HAVING COUNT(*) > 1
I need to have a third selector, I have used the following code :
SELECT diakod,diatexter,skada FROM (SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl = @Kod) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod2) UNION ALL SELECT DISTINCT diakod,diatexter,skada FROM Tra_ddl WHERE ( dia_ddl =@Kod3)) AS SQ GROUP BY diakod,diatexter,skada HAVING COUNT(*) > 1
This is giving me to many answers, does anyone have any good suggestions to improve it?
View 1 Replies
View Related
Mar 7, 2000
I have 2 tables which are related to each other, each having a Foreign Keys of the other table . When I delete company table, it gives me an error that I'm violating a FK constraint of the table owner. When I try to delete employee, it gives the same error.
CREATE TABLE OWNER {
employee_id PK
company_id - this is a FK of COMPANY
}
CREATE TABLE COMPANY{
company_id PK
owner_id - this is a FK of OWNER
}
Must I drop the constraints before I can delete? I don't want to do that because I don't want so many other tables are dependent on those tables.
Joyce
View 2 Replies
View Related
Feb 9, 2001
I'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...
the problem is:
The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.
I want to find the records where Keys values 1, 2 has the same Item value
(and if they don't I don't want to find any post at all, of course)
something like this:
SELECT * FROM table1
WHERE ???
ex of table1:
Key | Item
----------
1 | 1
1 | 2
2 | 2
3 | 1
please help...
View 3 Replies
View Related
Aug 31, 1999
I have a table that keeps track of account access errors. When there are three access errors in one day, the account is locked out. How can i construct a query to select any accounts that have three access errors on the same date. The pertinent fields would be ACCOUNTNUMBER AND ERRORDATE.
View 1 Replies
View Related
Aug 29, 2001
I have a feeling it is very easy to do what I want. But I don't know how.
I have 2 queries that return 2 results sets. I'd like to have just 1 query
that returns 1 result set that contains all the data of the 2 results sets.
Example.
Query 1 returns
Item Expected
--------------------
Lion 2
Tiger 2
Bear 2
Query 2 returns
Item Actual
-------------------
Lion 1
Bear 1
What I want is 1 query that will combine the results
Item Expected Actual
-------------------------------
Lion 2 1
Tiger 2 0
Bear 2 1
I tried using a unions between the 2 queries but that doesn't work.
I am pulling my hair out. I have been struggling with this for several
days now. Any help would be greatly appreciated.
Thanks
Josh
View 3 Replies
View Related
Feb 9, 2001
I'm developing a c++application with connections to a database, and got a little problem with the construction of a specific SQL Query. I was hoping that some of you guys maybe could help me out...
the problem is:
The table, table1, has two cols: Key and Item wich contains numbers only. Both are set to primary keys.
I want to find the records where Keys values 1, 2 has the same Item value
(and if they don't I don't want to find any post at all, of course)
something like this:
SELECT * FROM table1
WHERE ???
ex of table1:
Key | Item
----------
1 | 1
1 | 2
2 | 2
3 | 1
please help...
View 2 Replies
View Related
Nov 16, 2005
Hi,
I need to return a number of records at specifik days, i do it with this query;
SELECT LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) AS Days, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10))
AS Numbers_total, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) AS Numbers_In
FROM tb_SecurityLog
WHERE (CONVERT(varchar, CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
(SL_PolicyName LIKE N'%')
GROUP BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)
ORDER BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)
i also need to have a criteria at that second COUNT and if the criteria is not met that row should not be counted, is this possible at all?
//Mr
View 6 Replies
View Related
Feb 22, 2007
I have a data table like this:
MachineIDProductSales
-------------------------------
1Magazine$20.00
1Drink$30.00
2Drink$30.00
3Magazine$30.00
3Drink$40.00
4Magazine$30.00
5Food$40.00
5Drink$30.00
6Drink$40.00
One of the reports the user needs to see looks like this:
ProductNumber of MachinesTotal Sales
Magazine/Drink2$120.00
Drink2$70.00
Magazine1$30.00
Food/Drink1$70.00
To clarify:
There are two magazine/drink machines (ID 1 and 3)
There are two drink only machines (ID 2 and 6)
There is one magazine only machine (ID 4)
There is one food and drink machine (ID 5)
How do I do this query?
Ideally, I wouldn't limit the number of products in a given machine, but I can do so if necessary.
I'm using SQL Server 2000 so I can't use the newer PIVOT/UNPIVOT functions in SQL Server 2005.
Here is some setup T-SQL code:
CREATE TABLE SalesData
(
MachineIDINTEGER,
ProductNameVARCHAR(50),
SalesMONEY
)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Magazine', 20)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (2, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Magazine', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Drink', 40)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (4, 'Magazine', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Food', 40)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Drink', 30)
INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (6, 'Drink', 40)
Of course, this is a much simplified version of the real business problem I'm facing. Any help is greatly appreciated. Thanks!
View 5 Replies
View Related
Aug 10, 2007
Hello
I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).
Example:
(Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <--- Would pass
(Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <--- Would pass
(Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <--- Would not pass
How can I accomplish that?
Thanks a lot.
View 2 Replies
View Related
Jul 26, 2006
Hello all,
I'm very new to SQL, but find myself with this problem that i've been working on for a while, but I just can't figure out how to work through it.
I've got a database of appointments for vehicles. This database holds start time, month, day, year, end day month, year... etc.
I want to make a query that allows users to select a begining year, month day etc. and ending month year etc.
so i've got this query (I'm using PHP, so the $...'s are just $_Post variables.
so here's my query:
$query = "SELECT * FROM `appointments` WHERE start_year BETWEEN $start_year AND $end_year AND start_month >= $start_month AND end_month <= $end_month AND start_day >= $start_day ORDER BY start_year, start_month, start_day, start_time ASC";
When I try to run this query, if the start month is january, and the end month is january (regardless of year), it returns nothing (because nothing is between january and january, and it's not factoring in the year change. How can I factor in the year?)
Any input is greatly appreciated, I hope you understand where I'm running into trouble.
Thanks again!
-Robert
View 2 Replies
View Related
Oct 7, 2007
Hi guys,
Im trying to devise a statement with will return a true/false value based on if a field in a related table is null or not.
That is I have two tables: ServiceRepairOrders (Parent Table), LoanPhones (Child Table) related by the field "IMEI_ESN", which is a 15 character string.
When returning a "LoanPhone" record, i need one field "InStore", to return true/false based on if one or more related "CompletionDate" fields in ServiceRepairOrders is null or not.
At the moment I have the following:
SELECT LoanPhones.IMEI_ESN, LoanPhones.BoxNumber, LoanPhones.Make, LoanPhones.Model, [ServiceRepairOrders].[CompletionDate] Is Null AS InStore
FROM LoanPhones INNER JOIN ServiceRepairOrders ON LoanPhones.IMEI_ESN = ServiceRepairOrders.LoanPhoneIMEI_ESN;
But i need to ensure its a "one or more" relationship. That is, if all related service repair orders are complete (i.e. a completion date is supplied - not null), then the IsStore should be "True", otherwise false.
View 5 Replies
View Related
Dec 3, 2007
IN sql server 2005, you can rename a db table as sp_tbl1 by right clicking the table in object explorer and rename it to sp_tbl1,
The task is if you want to again change back the name from sp_tbl1 to tbl1 then sql server does not alow you to do that.
Is there any other way using dbcc commands or anything else to get that renamed back??
Thanks,
Kumar
View 2 Replies
View Related
Aug 19, 2005
Most questions of exam 70-228 have a selection of answers that all seemcorrect, but in reality, the right answer is the one that best solves thequestion.There a few trick questions like how to allow someone access toSQL Server using the Guest account over the Internet, and i solved suchtype of tricky questions before in ucertify. I feel i could have donebetter. Good luck to all who are preparing to take this exam.
View 2 Replies
View Related
Feb 22, 2006
Ok so I'm gonna attempt to explain my problem as easy as I can. I havea database of contacts (they are actually vendors). I am writing aprocedure that will query the vendors and generate a tab delimited filethat will be used an accounting program (MYOB). In the vendors databaseI am exporting from, I have a field for "First Name", a field for "LastName" and a field (looked up by ID) for "Company Name". Now here is theproblem. In the usual case there will be a company name, a first nameand a last name for example:Company Name: ACME CorporationFirst Name: JohnLast Name: SmithHOWEVER, sometimes the vendor is not part of a company and the users ofthe database have just been entering in the vendors name as the name ofthe company. So it would end up looking like this:Company Name: John SmithFirst Name: JohnLast Name: SmithSo the problem is that the accounting program that I am importing thesecontacts into uses a weird schema. Instead of separate fields for firstname, last name and company, they have a combined field for "CompanyName/Last Name" and then a separate field for first name. So if thefirst name field is blank then we know this contact is a company andnot an individual. As soon as we provide a first name, the recordbecomes an individual as opposed to a company.SO the MYOB import procedure is expecting two fields. One called"[Co./Last Name]" and one called "[First Name]". So when I query mycurrent vendor database, I need to figure out if the company namecontains both the first and last name of the vendor and if so thenreturn only the vendors "last name" for the "[Co./Last Name]" field.However if the company name is completely different then the first andlast name of the vendor, then I need to return the company name forthe "[Co./Last Name]" field and make the "[First Name]" field blank.Here is what I thought would work but it doesn't:SELECTCASEWHEN cu.vchrFirstName LIKE '%' + co.vchrCompanyName+ '%'AND cu.vchrLastName LIKE '%' + co.vchrCompanyName+ '%'THEN cu.vchrLastNameELSE co.vchrCompanyNameEND AS [Co./Last Name],CASEWHEN cu.vchrFirstName LIKE '%' + co.vchrCompanyName+ '%'AND cu.vchrLastName LIKE '%' + co.vchrCompanyName+ '%'THEN cu.vchrFirstNameELSE ''END AS [First Name],I don't get an error when I use this code, but it just always fails andends up using the company name no matter what even though I know forsure that the first and last names ARE in the company name.Sorry for my long winded explanation.THANKS
View 5 Replies
View Related
Jul 20, 2005
I have 2 tables joined together by the IDs, People and the pets theyownPEOPLEID NAME1 JohnSMith2 JaneDoePETSID PET1 Dog2 Cat2 Hamster2 Hamster2 FishI have create another where the PETS are in one column separated bysemi-colons and removing the dupsNEW TABLEID NAME ALLPETS1 JohnSmith Dog2 JaneDoe Cat;Hamster;FishWhat is the best way to do it? The only way I can think of is to runan update where it checks to see if the value already existsTHanks!
View 4 Replies
View Related
Aug 3, 2006
I'm self-taught at SQL, so this may be an easy one for others, but I can't even figure out how to search for an answer.
I need to put together a query as a datasource for a chart showing the firm's top ten clients by revenue AND the top ten clients by hours worked. It's easy to do either query separately, but the problem comes in when the two are combined. Then top ten by revenue doesn't always include all the top ten by hours clients, and vice versa [at the moment, I'm running a top twenty for each, then hand-compiling the top ten in Excel--oy!].
How can I write a query that will guarantee to include the top ten of both revenue and hours lists?
Thanks,
elinde
View 6 Replies
View Related
Aug 27, 2007
I have a quick question about an INSERT statement I've got that relies on a JOIN to populate rows in a table.
Here's the query in question:
SELECT
PE.PayeeId, --PAYEEID
PAA.PartyAddressId, --PARTYADDRESSID
DS.DistributionId, --DISTRIBUTIONID
EN.EntitlementId, --ENTITLEMENTID
401, --DELIVERYTYPEITEMENUMID
--@ACCOUNTPAYMENTID, --ACCOUNTPAYMENTID
NULL, --PARENTPAYMENTID
--@PAYMENTAMOUNT, --PAYMENTAMOUNT
GETDATE(), --PAYMENTDATE
501, --PAYMENTSTATUSENUMITMEID
GETDATE(), --PAYMENTSTATUSDATE
NULL, --RELEASERUNID
NULL, --RELEASEDATE
NULL, --ACCOUNTTRANSACTIONLOGID
601, --ACCOUNTSTATUSENUMITEMID
GETDATE(), --ACCOUNTSTATUSDATE
NULL, --ACCOUNTPAIDAMOUNT
0, --RECONCILEDIND
0, --UNDELIVERABLEIND
0, --REISSUEDIND
NULL, --REISSUENOTE
GETDATE(), --CREATEDATE
REPLACE(SYSTEM_USER, 'DOMAIN', ''), --CREATEID
GETDATE(), --MODIFIEDDATE
REPLACE(SYSTEM_USER, 'DOMAIN', '') --MODIFIEDID
FROM Distribution AS DS INNER JOIN
Entitlement AS EN ON DS.DistributionId = EN.DistributionId INNER JOIN
PartyAddress AS PAA inner JOIN
Party ON PAA.PartyId = Party.PartyId INNER JOIN
Payee AS PE ON Party.PartyId = PE.PartyId ON EN.clm_no = Party.clm_no INNER JOIN
clm ON EN.clm_no = clm.clm_no AND PE.clm_no = clm.clm_no
WHERE (clm.clm_no = 11178)
The portion I'm having an issue with is the PartyAddress piece. A PARTY may or may not have a corresponding PARTYADDRESS record. Regardless of whether or not they have one, I still want to insert the rest of the data - not including the PartyAddressID.
But when the query is run, if a Party doesn't have a PArtyAddressID, nothing is returned, although the rest of the data does exist. I know the isssue is the
PartyAddress AS PAA inner JOIN
Party ON PAA.PartyId = Party.PartyId INNER JOIN
query but am out of ideas...
Any help would be appreciated.
Thanks!!
View 3 Replies
View Related
Jul 27, 2006
Hello all,
I'm
very new to SQL, but find myself with this problem that i've been
working on for a while, but I just can't figure out how to work through
it.
I've got a database of appointments for vehicles. This database holds start time, month, day, year, end day month, year... etc.
I want to make a query that allows users to select a begining year, month day etc. and ending month year etc.
so i've got this query (I'm using PHP, so the $...'s are just $_Post variables.
so here's my query:
$query
= "SELECT * FROM `appointments` WHERE start_year BETWEEN $start_year
AND $end_year AND start_month >= $start_month AND end_month <=
$end_month AND start_day >= $start_day ORDER BY start_year,
start_month, start_day, start_time ASC";
When I try to run this
query, if the start month is january, and the end month is january
(regardless of year), it returns nothing (because nothing is between
january and january, and it's not factoring in the year change. How can
I factor in the year?)
Any input is greatly appreciated, I hope you understand where I'm running into trouble.
Thanks again!
-Robert
P.S. I accidentally posted this question in another forum, but i don't think I chose the right one... I hope this forum is right for this type of question. If not, I'm sorry
View 3 Replies
View Related
