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