November 21, 2014 at 2:21 pm
I am still learning SQL and hope someone could help
I have Table A with id, date and type plus a lot our fields they are not import for my query. This table has a row for each day of the year. It is possible to have the same id, date and but different types. So I could have multiple roes for the date and id.
I have Table B with the start date, end date, type and id
I need to query Table A and check to see if it has the correct type based on the Table B start and end date.
In the example below Table A type would not be correct, because Type was Non-Public for 01/05/14.
example:
Table A
ID: 111
Date: 01/05/14
Type: Public
Table B
ID 111
Start Date: 01/01/13
End Date: 12/31/13
Type: Public
Table B
ID 111
Start Date: 01/01/14
End Date: 11/21/14
Type: Non-Public
November 21, 2014 at 2:48 pm
Hi and welcome to the forums. You didn't provide a lot of details here but I think something like this might be close.
select [YourColumnsHere]
from TableA a
join TableB b on a.Date >= b.StartDate and a.Date < b.EndDate and a.Type <> b.Type
FWIW, I hope your real columns are named better and you have some normalization on things like Type. At the very least you should provide some kind of info in your names. Type is meaningless but something like ProductType of whatever gives a lot more clarity.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 24, 2014 at 9:24 am
Great, thanks for your help. Yes, the field names are much better than indicated.
November 24, 2014 at 9:43 am
rki1966 (11/24/2014)
Great, thanks for your help. Yes, the field names are much better than indicated.
Cool. Glad that worked for you and thanks for letting me know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy