July 31, 2009 at 5:17 am
Hi,
We have 2 tables. One containing data and one containing date period values. There is no relationship between the two tables.
Basically, the date period values are the end of a financial month. This is something as follows:
period_end_date,period_month,period_year
26-Jul-2009,12,2009
30-Aug-2009,1,2010
27-Sep-2009,2,2010
In the datatable, we have a list of items and dates they were issued. For example:
item_name,date_issued
"some item",23-Jul-2009
"some item 2", 24-Aug-2010
Now I want to find the period end date for "some item" based on the date it was issued. To do this I would like to JOIN onto the period end info table as I want all the information I have displayed.
The period end should be the first record found after or equal to (>=) the date_issued. I thought I had it but it appears to bring out multiple records now...
I did try
SELECT ii.*
FROM item_info ii
JOIN[period_end_dates] ped ON 1 = 1
WHERE ped.[period_end_date] =
(
SELECT MIN( [per_end_date] )
FROM [period_end_dates]
WHERE [period_end_date] >= ii.[date_issued]
)
Any ideas?
Andez
July 31, 2009 at 5:57 am
The code is correct what I posted oops. Was something else causing the problem
July 31, 2009 at 6:05 am
Here is the script with the dummy data for anyone else who might need it.
CREATE TABLE #period (period_end DATETIME, period_month INT, period_year INT)
INSERT INTO #period VALUES ('07/26/09', 12, 2009)
INSERT INTO #period VALUES ('08/30/09', 1, 2010)
INSERT INTO #period VALUES ('09/27/09', 2, 2010)
CREATE TABLE #items (item_name VARCHAR(20), date_issued DATETIME)
INSERT INTO #items VALUES ('some item', '07/23/09')
INSERT INTO #items VALUES ('some item2', '08/24/09')
SELECT ii.*, ped.period_end, ped.period_month, ped.period_year
FROM #items ii
JOIN [#period] ped ON 1 = 1
WHERE ped.[period_end] =
(
SELECT MIN( [period_end] )
FROM [#period]
WHERE [period_end] >= ii.[date_issued]
)
DROP TABLE #period
DROP TABLE #items
What are the results you are expecting?
August 3, 2009 at 8:24 pm
is another way to it without the cross join (on 1=1 in the ON clause of the inner join):
SELECT
ii.item_name,
ii.date_issued,
ped.period_end_date
FROM
item_info ii
INNER JOIN period_end_dates ped
ON (ped.period_end_date = ( select
min(ped2.period_end_date)
from
period_end_dates ped2
where
ped2.period_end_date > ii.date_issued
));
Viewing 4 posts - 1 through 3 (of 3 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