sub query by using max function and where conditio

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sub query by using max function and where conditio

Author

Topic

soofihussain
Starting Member

11 Posts

Posted - 2015-01-12 : 06:18:02

Table A
Item Effective Date Std Cost Amount
HT000005 2005-12-31 23:00:00.000 12744.3548
HT000005 2010-01-01 07:38:43.000 14859.5128
HT000005 2010-01-01 11:39:03.000 14857.7208
HT000005 2010-05-22 05:27:52.000 14847.1867
HT000005 2010-09-05 07:02:49.000 14897.8551
HT000005 2010-09-15 11:58:41.000 14902.2955

Table B
Production Order Item Closing Date
LR3000100 HT000005 2010-08-29

Result will be

Production Order Item Closing Date Std Cost Amount
LR3000100 HT000005 2010-08-29 14847.1867

Condition: In Table B Closing date less than or equal to Effective Date of Table A should pick the value of maximum of std Cost Amount

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-12 : 06:57:01

[code]
-- *** Test Data ***
CREATE TABLE #a
(
Item varchar(10) NOT NULL
,EffectiveDate datetime NOT NULL
,StdCostAmount money NOT NULL
);
INSERT INTO #a
SELECT 'HT000005', '20051231 23:00:00.000', 12744.3548
UNION ALL SELECT 'HT000005', '20100101 07:38:43.000', 14859.5128
UNION ALL SELECT 'HT000005', '20100101 11:39:03.000', 14857.7208
UNION ALL SELECT 'HT000005', '20100522 05:27:52.000', 14847.1867
UNION ALL SELECT 'HT000005', '20100905 07:02:49.000', 14897.8551
UNION ALL SELECT 'HT000005', '20100915 11:58:41.000', 14902.2955;
CREATE TABLE #b
(
Production varchar(20) NOT NULL
,ProductionOrder varchar(20) NOT NULL
,ClosingDate datetime NOT NULL
)
INSERT INTO #b
SELECT 'LR3000100', 'HT000005', '20100829';
-- *** End Test Data ***

SELECT *
FROM #b B
CROSS APPLY
(
SELECT TOP 1 StdCostAmount
FROM #a A
WHERE A.Item = B.ProductionOrder
AND A.EffectiveDate <= B.ClosingDate
ORDER BY A.EffectiveDate DESC
) D;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources