November 21, 2011 at 9:58 am
I want to select all data from this file:
SELECT perForename, perSurname, perPreferredName, perSex, perCountry, perEmail, CONVERT(varchar(10), perDateOfBirth, 103) AS DOB,
FLOOR(DATEDIFF(day, perDateOfBirth, GETDATE()) / 365.25) AS Age, perStreet, perTown, perCounty, PerPostcode, perPhone, perMobile,
perSkype
FROM tblPersonalData
ORDER BY perSurname
and just the top record from the next file:
SELECT TOP (1) MatchID, LoginID
FROM Match
and link them on Match.LoginID = tblPersonalData.perLogin
How do I do this?
November 22, 2011 at 6:53 am
Come on you useless bunch of bum bandits. Answer the freaking question.
November 22, 2011 at 7:51 am
You do realize that your rudeness and impatience has just branded you as someone that none of the regulars will ever want to help, right?
In fact, that second comment right there has probably gotten you blocked by a number of people.
For future reference, if you want an answer to your posts, 1) Be Polite, and 2) post information people can work with, 3) Be Polite and Patient. Most of us have day jobs and don't live on the forum just to please you.
Here are a few links you need to read.
November 22, 2011 at 7:54 am
I'm available for hire if that is possible for you.
I can take a lot of rudeness for 1000$+tx PREPAID per day.
November 22, 2011 at 7:59 am
Brandie Tarvin (11/22/2011)
You do realize that your rudeness and impatience has just branded you as someone that none of the regulars will ever want to help, right?In fact, that second comment right there has probably gotten you blocked by a number of people.
For future reference, if you want an answer to your posts, 1) Be Polite, and 2) post information people can work with, 3) Be Polite and Patient. Most of us have day jobs and don't live on the forum just to please you.
Here are a few links you need to read.
Have you no sense of humour?
November 22, 2011 at 8:02 am
Yes we do, but a smiley face goes a long way to make that clear on a forum where we don't see your face nor tone of voice.
November 22, 2011 at 8:03 am
Ninja's_RGR'us (11/22/2011)
Yes we do, but a smiley face goes a long way to make that clear on a forum where we don't see your face nor tone of voice.
how much crap will you tolerate for $10 dollars paypal? 😛
Lowell
November 22, 2011 at 8:12 am
Lowell (11/22/2011)
Ninja's_RGR'us (11/22/2011)
Yes we do, but a smiley face goes a long way to make that clear on a forum where we don't see your face nor tone of voice.how much crap will you tolerate for $10 dollars paypal? 😛
Accept the funds, pay 5$ in fees and wait 7 days to get the $ in my account, then have to account for it in my taxes and pay 40% of what's left.
You know what, just forget it, keep your stinking money :-D.
November 22, 2011 at 8:16 am
stephen.staple (11/22/2011)
Come on you useless bunch of bum bandits. Answer the freaking question.
Use a WHERE clause, you %$£$%%$$ $%$££ %^^&%$£ 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 22, 2011 at 8:18 am
stephen.staple (11/22/2011)
Come on you useless bunch of bum bandits. Answer the freaking question.
The main reason that you were not getting any replies for your question, it that we don't have enough information from you to answer!
For starters, it seems that your DDL script has fallen off your post as has your readily consumable sample data. Or perhaps you were unaware of the benefits of providing these things?
When you have time, read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Here's a random guess based on the nonsense you posted.
SELECT perForename,perSurname,perPreferredName,perSex,perCountry,perEmail,
--Generally, it's a better idea to format data in the presentation layer,
--rather than the database layer. So I'd suggest that you pass this as
--a DATETIME, then format in the application.
CONVERT(VARCHAR(10), perDateOfBirth, 103) AS DOB,
--This is bad. There are 365.242199 days in a year, so change the
--formula to FLOOR(DATEDIFF(day, perDateOfBirth, GETDATE()) / 365.242199)
FLOOR(DATEDIFF(day, perDateOfBirth, GETDATE()) / 365.25) AS Age,
perStreet,perTown,perCounty,PerPostcode,perPhone,perMobile,perSkype
FROM tblPersonalData a
INNER JOIN (SELECT
--This is bad, no ORDER BY means that
--TOP 1 is not guarantee'd to always
--return the same value
TOP (1) MatchID, LoginID
FROM Match) b ON LoginID = perLogin
Good luck.
November 22, 2011 at 9:37 am
I'm not clear on what you mean by selecting from files. Do you mean tables? Or are you trying to extract data from Excel or text files or something like that?
As far as getting data from one table and then related data from another table, you'll want to look up Cross/Outer Apply in Books Online or MSDN.com. (Same material in each, pick based on your preference.)
As for the "humor" in the second post, I truncated my humor table years ago, and lost the backup. In other words, I don't consider it humor to be rude, insulting, arrogant, et al, and then try to cover your *** by making the lame excuse "it was just a joke" or "can't you take a joke" or whatever. Try professionalism instead of lame attempts to make yourself "right". You'll get a lot more milleage out of that.
I suspect the query should look like this:
SELECT perForename,
perSurname,
perPreferredName,
perSex,
perCountry,
perEmail,
CONVERT(VARCHAR(10), perDateOfBirth, 103) AS DOB,
FLOOR(DATEDIFF(day, perDateOfBirth, GETDATE()) / 365.25) AS Age,
perStreet,
perTown,
perCounty,
PerPostcode,
perPhone,
perMobile,
perSkype
FROM tblPersonalData
OUTER APPLY (SELECT TOP (1)
MatchID
FROM MATCH
WHERE Match.LoginID = tblPersonalData.perLogin) AS TopMatch
ORDER BY perSurname ;
Some notes:
Top(1) doesn't guarantee any sort of order. You'll need to provide the query with an Order By that makes it follow any rules you have on that, or you'll need to make sure to document that which match it picks is essentially random.
Your age calculation is known to be erroneous. It has problems with leap years, etc. There are better calculations that can be used in T-SQL for this. Google "T-SQL calculate age" and you'll find some good ones.
I picked Outer Apply in case there are rows in the outer table that don't have a match in the inner table, and you still want those outer rows. If that's not correct, and a row in Match is required for the business-purpose of this query, you'll want to change it to Cross Apply.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 23, 2011 at 7:16 am
Thank you GSquared. I actually ended up with a very similar solution to yours.
First I defined a function:
create FUNCTION [dbo].[fn_GetTopMatch1](@LoginID AS int)
RETURNS TABLE
AS
RETURN
--
SELECT TOP (1) MatchID, VacancyID
FROM Match
WHERE (LoginID = @LoginID)
Then I used CROSS APPLY:
SELECTperForename,
perSurname,
perPreferredName,
perSex,
perCountry,
perEmail,
perStreet,
perTown,
perCounty,
PerPostcode,
perPhone,
perMobile,
CONVERT(varchar(10), perDateOfBirth, 103) AS DOB,
FLOOR(DATEDIFF(day, perDateOfBirth, GETDATE()) / 365.25) AS Age,
perSkype,
matchTable.MatchID
FROM tblPersonalData
cross apply
dbo.fn_GetTopMatch1(perLoginID) as matchTable
WHERE(Archived = 0)
ORDER BYperSurname
November 23, 2011 at 7:39 am
stephen.staple (11/23/2011)
Thank you GSquared. I actually ended up with a very similar solution to yours.First I defined a function:
create FUNCTION [dbo].[fn_GetTopMatch1](@LoginID AS int)RETURNS TABLE
AS
RETURN
--
SELECT TOP (1) MatchID, VacancyID
FROM Match
WHERE (LoginID = @LoginID)
.....
With no ORDER BY, the results from TOP are unpredictable. TOP by brightness? Gravity? You'd be far better off using G2's code as it stands.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 23, 2011 at 7:40 am
stephen.staple (11/23/2011)
Thank you GSquared. I actually ended up with a very similar solution to yours.First I defined a function:
create FUNCTION [dbo].[fn_GetTopMatch1](@LoginID AS int)RETURNS TABLE
AS
RETURN
--
SELECT TOP (1) MatchID, VacancyID
FROM Match
WHERE (LoginID = @LoginID)
Then I used CROSS APPLY:
SELECTperForename,perSurname,
perPreferredName,
perSex,
perCountry,
perEmail,
perStreet,
perTown,
perCounty,
PerPostcode,
perPhone,
perMobile,
CONVERT(varchar(10), perDateOfBirth, 103) AS DOB,
FLOOR(DATEDIFF(day, perDateOfBirth, GETDATE()) / 365.25) AS Age,
perSkype,
matchTable.MatchID
FROM tblPersonalData
cross apply
dbo.fn_GetTopMatch1(perLoginID) as matchTable
WHERE(Archived = 0)
ORDER BYperSurname
After the complaint that you didn't get an answer fast enough, I expected you to follow the advice you were given.
Both GSquared and myself pointed out a couple of problems with your code.
1) Your TOP 1 has no ORDER BY. When you have no ORDER BY then the ordering of the data returned is not guaranteed which means that the TOP 1 of the data set is not certain. This means that your SELECT TOP 1 FROM Match WHERE (LoginID = @LoginID) is essentially returning any single row that matches the WHERE clause.
2) To work out age you are dividing the age in days by 365.25, this is bad maths. There are 365.242199 days in a year (google agrees) so instead you need to divide by that; e.g. FLOOR(DATEDIFF(day, perDateOfBirth, GETDATE()) / 365.242199)
November 23, 2011 at 7:51 am
I dug through my scripts library, and found this for age calculation:
-- Age Calculation (most accurate known version; does NOT take into account Leapling laws)
ISNULL(CAST((CAST(CONVERT(VARCHAR(25), GETDATE(), 112) AS INT)
- CAST(CONVERT(VARCHAR(25), CAST(NULLIF(DateOfBirth, '') AS DATETIME), 112) AS INT))
/ 10000 AS VARCHAR(3)), '') AS Age,
I don't remember who I got it from, but it was part of a series of performance and accuracy tests on age calculations, and this one was best for accuracy and performed quite well enough.
You'll want to use that instead of the current math you have.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply