March 12, 2009 at 4:58 am
Table1 - personID, firstName, lastName
Table2 - personID, badgeID, validFrom, validUntil
I would need to create a query that would result in the folowing:
firstName, lastName, badgeID
whereas:
- only valid badgeID (understand the newest) is selected
- an application that populates data in tables incorrectly ends old records, so in Table2 exist the following
types of rows:
personID, badgeID, validFrom, validUntil
22, 111, 2009-01-01 00:00:00, 2009-03-12 23:59:59
22, 112, 2009-03-12 00:00:00, 2099-12-31 23:59:59
March 12, 2009 at 6:24 am
Try ...
SELECT FirstName, LastName, (SELECT TOP 1 BadgeID from Tabe2 AS T2 Where T2.PersonID = PersonID Order by validfrom desc) AS BadageID
FROM Table1
... Fixing small mistake on edit.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 13, 2009 at 4:13 am
Mohit, thank you for you reply. The solution suggested works fine, but I would need additionally help with little bit complicated query.
Table1 - BillID, PersonID, BillDate, BillAmount.
Table2 - personID, firstName, lastName
Table3 - personID, badgeID, validFrom, validUntil
I would need to create a query that would result in the folowing:
BillID, BillDate, firstName, lastName, badgeID
whereas:
- only valid badgeID (understand the newest) is selected - the valid badgeID depends on a bill date
- an application that populates data in tables incorrectly ends old records, so in Table2 exist the following
types of rows:
personID, badgeID, validFrom, validUntil
22, 111, 2009-01-01 00:00:00, 2009-03-12 23:59:59
22, 112, 2009-03-12 00:00:00, 2099-12-31 23:59:59
In this case it is needed to pass BillDate value from outer query into inner query (subquery). And if the subquery returns two values (because of application icorrect behaviour), it is needed to select just an older one.
Is it possible?
March 13, 2009 at 7:38 am
Is the error always off by 1 day? If it is you could do this:
SELECT
BillID,
BillDate,
firstName,
lastName,
badgeID
FROM
table1 AS bills JOIN
table2 AS persons ON
bills.PersonID = persons.PersonID JOIN
table3 AS badges ON
persons.personID = badges.personID AND
bills.billDate >= badges.ValidFrom AND
bills.billDate <= DateAdd(Day, -1, badges.ValidUntil)
It would be easier to provide a valid solution if you provided test data like described in the links in my signature line.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 13, 2009 at 9:11 am
Firstly, I would answer your question regarding time offset of results coming from my attempts. The problem was that my query returned duplicate rows with the same day, same person for a day in which a change of badge occurred for a particular person - first one for 'invalid' old badge, second one for 'valid' new badge.
I also tried a subquery in place of badgeID column, but it returned only an error message that subquery had more than one result, what is not allowed.
However a simple and really smart modification of condition as you suggested is that what I was exactly looking for. Thank you.
But if I would come back to the topic of this post, I would like to know also if exists a way how to pass a value from an outer query to an inner query, if the inner query is of 'in SELECT' type. In example, could my situation be solved by any technique that would pass BillDate value into a subquery that would return just one 'correct' value that would be filtered on basis of BillDate value.
March 13, 2009 at 9:15 am
Milan Sadil (3/13/2009)
But if I would come back to the topic of this post, I would like to know also if exists a way how to pass a value from an outer query to an inner query, if the inner query is of 'in SELECT' type. In example, could my situation be solved by any technique that would pass BillDate value into a subquery that would return just one 'correct' value that would be filtered on basis of BillDate value.
Look up APPLY in BOL, it does allow you to pass a value from an outer query to an inner query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 13, 2009 at 9:42 am
Milan Sadil (3/12/2009)
Table1 - personID, firstName, lastNameTable2 - personID, badgeID, validFrom, validUntil
I would need to create a query that would result in the folowing:
firstName, lastName, badgeID
whereas:
- only valid badgeID (understand the newest) is selected
- an application that populates data in tables incorrectly ends old records, so in Table2 exist the following
types of rows:
personID, badgeID, validFrom, validUntil
22, 111, 2009-01-01 00:00:00, 2009-03-12 23:59:59
22, 112, 2009-03-12 00:00:00, 2099-12-31 23:59:59
I'm sure this was asked, but I just wanted to be sure about it. You said that the application that updates the table incorrectly updates old records. Using the example above, does that mean the old record should have had a validuntil date of 2009-03-11 23:59:59? And, second, does this application always do this (in which case, comeone needs to fix it), or is this something that only occurs periodically?
March 13, 2009 at 10:28 am
Jack, the APPLY clause looks promising, I definitely will have look at this.
Lynn, you are right. Correct values for the datetime given should look like:
- either
22, 111, 2009-01-01 00:00:00, 2009-03-11 23:59:59
22, 112, 2009-03-12 00:00:00, 2099-12-31 23:59:59
- or
22, 111, 2009-01-01 00:00:00, 2009-03-12 08:50:50
22, 112, 2009-03-12 08:50:51, 2099-12-31 23:59:59
so that not even a second is common for both the old and new record.
I know that it occurs every time a badge is changed and that an application should be changed.
Thank you all for your help.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply