Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- C.Name Client ,
- SecONdary_Document.Primary_Document_ID ,
- SecONdary_Document.Secondary_Document_Id ,
- Primary_Document.State + ',' + GB_Counties.CountyName State ,
- Grantor.Name Grantor ,
- Loan_Number ,
- CONVERT(VARCHAR(10), Primary_Document.PIF_Date, 101) PIF_Date ,
- ( SELECT CASE WHEN dbo.SECONDARY_DOCUMENT.Status_ID = 21
- THEN CONVERT(VARCHAR(10), dbo.SECONDARY_DOCUMENT.Updated_Dt, 101)
- ELSE ( SELECT TOP 1
- CONVERT(VARCHAR(10), dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Created_Dt, 101)
- FROM dbo.SECONDARY_DOCUMENT_STATUS_HISTORY
- WHERE dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Secondary_Document_ID = SecONdary_Document.Secondary_Document_Id
- AND SECONDARY_DOCUMENT_STATUS_HISTORY.Status_ID = 21
- ORDER BY dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Created_Dt DESC
- )
- END
- ) AS Date_Sent_to_Recorder ,
- Satis_TimeFrame ,
- CASE WHEN PIF_Date IS NULL
- OR Date_Sent_to_Recorder IS NULL THEN NULL
- ELSE DATEDIFF(DAY, PIF_Date,
- Date_Sent_to_Recorder)
- END TotalDays ,
- CASE WHEN PIF_Date IS NULL
- OR Date_Sent_to_Recorder IS NULL THEN NULL
- ELSE CASE WHEN DATEDIFF(DAY, PIF_Date,
- ISNULL(Date_Sent_to_Recorder,
- GETDATE())) > Satis_TimeFrame
- THEN 'N'
- ELSE 'Y'
- END
- END InCompliance ,
- Loan_Name ,
- Deal_Name ,
- Deal.Deal_Id ,
- Loan.Loan_Id
- FROM Primary_Document
- INNER JOIN SecONdary_Document ON SecONdary_Document.Primary_Document_ID = Primary_Document.Primary_Document_ID
- INNER JOIN Status ON Status.Status_Id = SecONdary_Document.Status_Id
- INNER JOIN GB_Counties ON GB_Counties.CountyId = Primary_Document.County_Id
- INNER JOIN Loan ON Loan.Loan_Id = Primary_Document.Loan_Id
- DECLARE @Date_Sent_to_Recorder varchar(10)
- SELECT C.Name Client ,
- SecONdary_Document.Primary_Document_ID ,
- SecONdary_Document.Secondary_Document_Id ,
- Primary_Document.State + ',' + GB_Counties.CountyName State ,
- Grantor.Name Grantor ,
- Loan_Number ,
- CONVERT(VARCHAR(10), Primary_Document.PIF_Date, 101) PIF_Date ,
- --<START>
- --3021,RRaghuvansi,If current status is 21 in SECONDARY_DOCUMENT then take Updated_Dt else take Created_Dt in SECONDARY_DOCUMENT_STATUS_HISTORY with status Out For Recorder
- --CONVERT(VARCHAR(20), Recording_Date, 101) AS Recording_Date ,
- @Date_Sent_to_Recorder=[dbo].[GET_RecordingDate](SecONdary_Document.Secondary_Document_Id), --<END>
- Satis_TimeFrame ,
- Loan_Name ,
- Deal_Name ,
- Deal.Deal_Id ,
- Loan.Loan_Id
- FROM Primary_Document
- Declare @Sample table(id1 int, id2 int, value varchar(20))
- insert into @sample values (1,1, 'this')
- insert into @sample values(2,2, 'that')
- insert into @sample values(3,2, 'the other')
- Select t1.ID1, t1.Value, t2.RevVal,
- case
- when t2.RevVal = 'siht' then 1 else 0
- end as RevIsThisBackwards
- from @sample t1
- inner join (Select id1, reverse(value) as RevVal from @sample) t2
- on t1.ID1 = t2.ID1
- id VALUES Rev Rev Value is this backwards
- 1 this siht 1
- 2 that taht 0
- 3 the other rehto eht 0
- INNER JOIN Loan ON Loan.Loan_Id = Primary_Document.Loan_Id
- INNER JOIN (Select SomPKField, CASE WHEN dbo.SECONDARY_DOCUMENT.Status_ID = 21
- THEN CONVERT(VARCHAR(10), dbo.SECONDARY_DOCUMENT.Updated_Dt, 101)
- ELSE ( SELECT TOP 1
- CONVERT(VARCHAR(10), dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Created_Dt, 101)
- FROM dbo.SECONDARY_DOCUMENT_STATUS_HISTORY
- WHERE
- dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Secondary_Document_ID = SecONdary_Document.Secondary_Document_Id
- AND SECONDARY_DOCUMENT_STATUS_HISTORY.Status_ID = 21
- ORDER BY dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Created_Dt DESC
- )
- END as Date_Sent_to_Recorder
- ) as Sub1
- on SomeTable.SomePKField = Sub1.SomePKField
- **I edit your edit in this edit.**
- SELECT C.Name Client ,
- SecONdary_Document.Primary_Document_ID ,
- SecONdary_Document.Secondary_Document_Id ,
- Primary_Document.State + ','
- + GB_Counties.CountyName State ,
- Grantor.Name Grantor ,
- Loan_Number ,
- CONVERT(VARCHAR(10), Primary_Document.PIF_Date, 101) PIF_Date ,
- [dbo].[GET_RecordingDate]
- (SecONdary_Document.Secondary_Document_Id)
- As Date_Sent_To_Recorder
- Satis_TimeFrame ,
- Loan_Name ,
- Deal_Name ,
- Deal.Deal_Id ,
- Loan.Loan_Id
- FROM Primary_Document
- create table #temp
- ( Primary_Document_ID int,
- Date_Sent_To_Recorder datetime )
- insert #temp
- select Primary_Document_ID,
- CASE
- WHEN dbo.SECONDARY_DOCUMENT.Status_ID = 21
- THEN CONVERT(VARCHAR(10), dbo.SECONDARY_DOCUMENT.Updated_Dt, 101)
- ELSE
- (SELECT TOP 1
- CONVERT(VARCHAR(10), dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Created_Dt, 101)
- FROM dbo.SECONDARY_DOCUMENT_STATUS_HISTORY
- WHERE dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Secondary_Document_ID = SecONdary_Document.Secondary_Document_Id
- AND SECONDARY_DOCUMENT_STATUS_HISTORY.Status_ID = 21
- ORDER BY dbo.SECONDARY_DOCUMENT_STATUS_HISTORY.Created_Dt DESC)
- END
- from SecONdary_Document
- -- main select, joining to #temp
- SELECT SD.Primary_Document_ID,
- SD.Secondary_Document_Id,
- CASE WHEN SD.Status_ID = 21
- THEN CONVERT(VARCHAR(10), SD.Updated_Dt, 101)
- ELSE CONVERT(VARCHAR(10), SDSH.Created_Dt, 101)
- END Date_Sent_to_Recorder
- FROM SECONDARY_DOCUMENT SD
- INNER JOIN dbo.SECONDARY_DOCUMENT_STATUS_HISTORY SDSH
- ON SDSH.Secondary_Document_ID = SD.Secondary_Document_Id
- AND SDSH.Status_ID = 21
- INNER JOIN (SELECT Secondary_Document_ID, max(Created_Dt) Created_Dt
- FROM dbo.SECONDARY_DOCUMENT_STATUS_HISTORY
- WHERE Status_ID = 21
- GROUP BY Secondary_Document_ID) SDSH2
- ON SDSH.Secondary_Document_ID = SDSH2.Secondary_Document_Id
- AND SDSH.Created_Dt = SDSH2.Created_Dt
- SELECT C.Name Client,
- SecONdary_Document.Primary_Document_ID,
- SecONdary_Document.Secondary_Document_Id,
- Primary_Document.State + ',' + GB_Counties.CountyName State,
- Grantor.Name Grantor,
- Loan_Number,
- CONVERT(VARCHAR(10), Primary_Document.PIF_Date, 101) PIF_Date,
- SecONdary_Document.Date_Sent_to_Recorder,
- Satis_TimeFrame,
- CASE WHEN PIF_Date IS NULL
- OR SecONdary_Document.Date_Sent_to_Recorder IS NULL
- THEN NULL
- ELSE DATEDIFF(DAY, PIF_Date,
- SecONdary_Document.Date_Sent_to_Recorder)
- END TotalDays,
- CASE WHEN PIF_Date IS NULL
- OR SecONdary_Document.Date_Sent_to_Recorder IS NULL THEN NULL
- ELSE CASE WHEN DATEDIFF(DAY, PIF_Date,
- ISNULL(SecONdary_Document.Date_Sent_to_Recorder
- ,GETDATE())) > Satis_TimeFrame
- THEN 'N'
- ELSE 'Y'
- END
- END InCompliance,
- Loan_Name,
- Deal_Name,
- Deal.Deal_Id,
- Loan.Loan_Id
- FROM Primary_Document
- INNER JOIN (SELECT SD.Primary_Document_ID,
- SD.Secondary_Document_Id,
- CASE WHEN SD.Status_ID = 21
- THEN CONVERT(VARCHAR(10), SD.Updated_Dt, 101)
- ELSE CONVERT(VARCHAR(10), SDSH.Created_Dt, 101)
- END Date_Sent_to_Recorder
- FROM SECONDARY_DOCUMENT SD
- INNER JOIN dbo.SECONDARY_DOCUMENT_STATUS_HISTORY SDSH
- ON SDSH.Secondary_Document_ID = SD.Secondary_Document_Id
- AND SDSH.Status_ID = 21
- INNER JOIN (SELECT Secondary_Document_ID, max(Created_Dt) Created_Dt
- FROM dbo.SECONDARY_DOCUMENT_STATUS_HISTORY
- WHERE Status_ID = 21
- GROUP BY Secondary_Document_ID) SDSH2
- ON SDSH.Secondary_Document_ID = SDSH2.Secondary_Document_Id
- AND SDSH.Created_Dt = SDSH2.Created_Dt) SecONdary_Document
- ON SecONdary_Document.Primary_Document_ID
- = Primary_Document.Primary_Document_ID
- INNER JOIN Status ON Status.Status_Id = SecONdary_Document.Status_Id
- INNER JOIN GB_Counties ON GB_Counties.CountyId = Primary_Document.County_Id
- INNER JOIN Loan ON Loan.Loan_Id = Primary_Document.Loan_Id
Add Comment
Please, Sign In to add comment