Need help in making the desired output in a query

dileepav
dileepav used Ask the Experts™
on
Hi Experts,

I need Help in making a query, These are the steps I do here

I am taking the data from one table and storing it in a different table, But I am not getting the expeted result, I am attaching the following:

(1) Table structure for S_TIS_INTEREST_RATES - Sparse Table
(2) Table structure for TISINT
(3) Sample data for S_TIS_INTEREST_RATES
(4) Sample data for TISINT
(5) Current code that I am using for this purpose.
(6) Expected O/P.
(7) Current O/P.

Current Stored Procedure I/P Param - 201104 means yyyymm

Can you please help me in getting the Expected O/P. Expect the current code all are available at the excel sheet (.xls file).

Thx
Dil. Current-Query.txt Book1.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Hmm.

Try replacing:
dateadd(day,-1,CONVERT(datetime, CONVERT(nchar(8),19000000 + (SELECT TOP 1 Date_of_Terms FROM Mabe4_Import.dbo.TISINT Child WHERE Child.Date_of_Terms > SIR.MRS_Date_Of_Terms and Child.Account_Number = SIR.Account_Number ORDER BY Date_of_Terms)), 112))

With:
dateadd(day,-1,CONVERT(datetime, CONVERT(nchar(8),19000000 + (SELECT MIN(Date_of_Terms) FROM Mabe4_Import.dbo.TISINT Child WHERE Child.Date_of_Terms > SIR.MRS_Date_Of_Terms and Child.Account_Number = SIR.Account_Number )), 112))

What you had should have worked, but maybe I am missing something else obvious. It would seem it might just be an oddity in the order by and/or selection of rows that match criteria. Check that  Child.Date_of_Terms > SIR.MRS_Date_Of_Terms shouldn't be  Child.Date_of_Terms > SIR.Date_Of_Terms for example.

Additionally, wouldn't this match to multiple rows:
FROM dbo.S_TIS_INTEREST_RATES SIR
      INNER JOIN MABE4_Import.dbo.TISINT TIS
            ON SIR.Account_Number = TIS.Account_Number
            AND SIR.MRS_Date_Of_Terms < TIS.Date_Of_Terms

In your sample, both rows before 9/30/2011 (1110930) would match to the above check, right?

Author

Commented:
Thx experts

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial