Need help with the Query please.

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with the Query please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-27 : 14:12:17

[code]I've got two tables (#EI and #LoanHist) and I am try to compare date if the values are the same don't return otherwise return. They seem to be simple and
I got stuck. Any helps would greatly appreciate.

I am using SQL 2008.

IF OBJECT_ID('Tempdb.dbo.#EI', 'u') IS NOT NULL
DROP TABLE #EI
GO

CREATE TABLE #EI
(
LoanNum VARCHAR(8) NOT NULL,
REODate Date NULL,
DilRecorded DATE NULL
)
GO

INSERT #EI VALUES ('123456', NULL, '2010-11-27')
GO

IF OBJECT_ID('Tempdb.dbo.#LoanHist', 'u') IS NOT NULL
DROP TABLE #LoanHist
GO
CREATE TABLE #LoanHist
(
LoanNum VARCHAR(8) NOT NULL,
REOHistoryId INT IDENTITY (1,1) NOT NULL,
UpdateBy VARCHAR(10) NULL,
InsertDt DATE NULL
)
GO

INSERT #LoanHist (LoanNum, UpdateBy, InsertDt)
VALUES ('120606', 'User1', '2010-12-27'), ('120606', 'User1', '2010-12-22'), ('120606', 'User1', '2010-12-23');

SELECT *
FROM #EI;
GO

LoanNum REODate DilRecorded
-------- ---------- -----------
123456 NULL 2010-11-27

SELECT *
FROM #LoanHist;
GO

LoanNum REOHistoryId UpdateBy InsertDt
-------- ------------ ---------- ----------
120606 1 User1 2010-12-27

120606 2 User1 2010-12-22 * return
120606 3 User1 2010-12-23 *

-- Testing... Not working yet.

SELECT COALESCE (NullIf(a.REODate,''), NullIf(a.DilRecorded, '')) AS 'REODate',
b.*
FROM #EI AS a
JOIN #LoanHist AS b
ON a.LoanNum = b.LoanNum
AND CAST(COALESCE (NullIf(a.REODate,''), NullIf(a.DilRecorded, '')) AS DATE) <> CAST(b.InsertDt AS DATE);
GO

-- Business Rules: Compare either REODate or DilRecorded date from #EI table with #LoanHist..InsertDt and return those rows NOT the same as value in #EI..DiRecorded column.
-- Desired Results:

LoanNum REOHistoryId UpdateBy InsertDt
-------- ------------ ---------- ----------
120606 2 User1 2010-12-22
120606 3 User1 2010-12-23[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-27 : 15:08:40

SELECT --[Reodate] = isnull(a.reodate,lh.DilRecorded)
lh.LoanNum
,lh.REOhistoryID
,lh.UpdateBy
,lh.insertDt
FROM #LoanHist lh
CROSS APPLY(select * from #eI where lh.insertdt not in ( isnull(reodate,''),isnull(dilrecorded,''))) a

Jim

Everyday I learn something that somebody else already knew

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-27 : 17:03:37

The business rule has changed and I only need to compare 2 columns within the same table. Please see the desired results below.

Thank you.

DROP TABLE dbo.TestTable;
go
CREATE TABLE [dbo].[TestTable](
[LoanNum] [varchar](10) NOT NULL,
[REOHistoryId] [int] IDENTITY(1,1) NOT NULL,
[REODate] [datetime] NULL,
[DilRecorded] [datetime] NULL,
[InsertBy] [varchar](50) NULL,
[InsertDt] [datetime] NULL
) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[TestTable] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[TestTable]([LoanNum], [REOHistoryId], [REODate], [DilRecorded], [InsertBy], [InsertDt])
SELECT N'120606', 2, NULL, '20101112 00:00:00.000', N'DBATeam', '20101227 08:54:44.153' UNION ALL
SELECT N'120606', 944, '20101213 00:00:00.000', NULL, N'DBATeam', '20101227 12:59:48.287' UNION ALL
SELECT N'120606', 945, '20101215 00:00:00.000', NULL, N'DBATeam', '20101227 13:00:12.607' UNION ALL
SELECT N'120606', 946, '20101220 00:00:00.000', NULL, N'DBATeam', '20101227 13:00:23.100' UNION ALL
SELECT N'120606', 947, '20101221 00:00:00.000', NULL, N'DBATeam', '20101227 13:00:29.157' UNION ALL
SELECT N'120606', 948, '20101227 00:00:00.000', NULL, N'DBATeam', '20101227 13:00:42.983'
COMMIT;
RAISERROR (N'[dbo].[TestTable]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

SET IDENTITY_INSERT [dbo].[TestTable] OFF;

SELECT *
FROM TestTable
GO

LoanNum REOHistoryId REODate DilRecorded InsertBy InsertDt
---------- ------------ ----------------------- ----------------------- -------------------------------------------------- -----------------------
120606 2 NULL 2010-11-12 00:00:00.000 DBATeam 2010-12-27 08:54:44.153
120606 944 2010-12-13 00:00:00.000 NULL DBATeam 2010-12-27 12:59:48.287
120606 945 2010-12-15 00:00:00.000 NULL DBATeam 2010-12-27 13:00:12.607
120606 946 2010-12-20 00:00:00.000 NULL DBATeam 2010-12-27 13:00:23.100
120606 947 2010-12-21 00:00:00.000 NULL DBATeam 2010-12-27 13:00:29.157
120606 948 2010-12-27 00:00:00.000 NULL DBATeam 2010-12-27 13:00:42.983

SELECT *
FROM TestTable AS a
WHERE NOT EXISTS ( SELECT 1
FROM TestTable AS b
WHERE b.LoanNum = a.LoanNum
AND COALESCE ( b.REODate, b.DilRecorded) = a.InsertDt)
GO

I try to compare REODate or DilRecorded date same values in the InsertDt column then Don't return but it keep returing the last row and I can't see what wrong.
Can you see what the error is?. Thank you.

-- Desire results:
LoanNum REOHistoryId REODate DilRecorded InsertBy InsertDt
---------- ------------ ----------------------- ----------------------- -------------------------------------------------- -----------------------
120606 2 NULL 2010-11-12 00:00:00.000 DBATeam 2010-12-27 08:54:44.153
120606 944 2010-12-13 00:00:00.000 NULL abc 2010-12-27 12:59:48.287
120606 945 2010-12-15 00:00:00.000 NULL abc 2010-12-27 13:00:12.607
120606 946 2010-12-20 00:00:00.000 NULL abc 2010-12-27 13:00:23.100
120606 947 2010-12-21 00:00:00.000 NULL abc 2010-12-27 13:00:29.157

quote:
Originally posted by jimf

SELECT --[Reodate] = isnull(a.reodate,lh.DilRecorded)
lh.LoanNum
,lh.REOhistoryID
,lh.UpdateBy
,lh.insertDt
FROM #LoanHist lh
CROSS APPLY(select * from #eI where lh.insertdt not in ( isnull(reodate,''),isnull(dilrecorded,''))) a

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-27 : 18:02:53

I'm not sure that makes any sense. I doesn't seem like you are going to get any matches because none of the dates (ReoDate or DilRecord) are going to match the InsertDt.. Do you want to ignore the time portion of the date time?

If you want to explain what you are trying to accomplish at a higher level we might be able to help. It seems like you are having a tough time with populating a history table or do some sort of delta load, which both are pretty easy if you set things up properly.

I'll venture a guess without actually knowing your requirements.

SELECT A.*
FROM TestTable AS A
LEFT OUTER JOIN
    (
        SELECT LoanNum, REOHistoryId
        FROM TestTable AS a
        WHERE CAST(COALESCE(REODate, DilRecorded) AS DATE) = CAST(InsertDt AS DATE)
    ) AS B
    ON A.LoanNum = B.LoanNum
    AND A.ReoHistoryID = B.ReoHistoryID
WHERE B.LoanNum IS NULL

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-12-27 : 21:41:08

Try this to see if it works for you ....

declare @temp table(LoanNum varchar(10) NOT NULL,
REOHistoryId int NOT NULL,
REODate datetime NULL,
DilRecorded datetime NULL,
InsertBy varchar(50) NULL,
InsertDt datetime NULL
)

INSERT INTO @temp(LoanNum, REOHistoryId, REODate, DilRecorded, InsertBy, InsertDt)
SELECT N'120606', 2, NULL, '20101112', N'DBATeam', '20101227' UNION ALL
SELECT N'120606', 944, '20101213', NULL, N'DBATeam', '20101227' UNION ALL
SELECT N'120606', 945, '20101215', NULL, N'DBATeam', '20101227' UNION ALL
SELECT N'120606', 946, '20101220', NULL, N'DBATeam', '20101227' UNION ALL
SELECT N'120606', 947, '20101221', NULL, N'DBATeam', '20101227' UNION ALL
SELECT N'120606', 948, '20101227', NULL, N'DBATeam', '20101227' UNION ALL
SELECT N'120606', 949, NULL, NULL, N'DBATeam', NULL
select * from @temp

select * from
(select *, case when InsertDt = REODate or InsertDt = DilRecorded then 0 else 1 end as tempColumn from @temp t1) temp
where tempColumn = 1

--If you want in your comparation, null equals null, add or (InsertDt is null and (InsertDt is null or InsertDt is null)) to the when
--If your columns include time, convert them before compare

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-28 : 11:32:52

Thanks namman for your query. I already figure out.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-28 : 12:27:05

quote:
Originally posted by NguyenL71

Thanks namman for your query. I already figure out.

Care to post your solution so that other might learn?

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-30 : 11:37:17

Here is the query.

-- If the record does not meet the criteria then set to 0 and do not insert into REOHistory table.
SET @Exist = ISNULL((SELECT 1
FROM KondaurData AS a
JOIN ExitInfo AS b
ON a.LoanNum = b.LoanNum
WHERE ( a.PoolNum = 'Hermosa 031810' )
AND ( a.SecuritizedType = 1)
AND ( b.LoanNum = @LoanNum)), 0);

--PRINT @Exist

SELECT @PrevREODate = b.REODate
,@PreDilRecorded = b.DilRecorded
FROM KondaurData AS a
JOIN ExitInfo AS b
ON a.LoanNum = b.LoanNum
WHERE ( a.PoolNum = 'Hermosa 031810' )
AND ( a.SecuritizedType = 1 )
AND ( b.LoanNum = @LoanNum )

IF (
CONVERT(CHAR(8), @PrevREODate, 112) = CONVERT(CHAR(8), @REODate, 112)
OR CONVERT(CHAR(8), @PreDilRecorded, 112) = CONVERT(CHAR(8), @DilRecorded, 112)
)
BEGIN
RETURN (0)
END

BEGIN TRY

IF (@Exist = 1)
BEGIN
MERGE INTO REOHistory AS t
USING (VALUES ( @LoanNum , @REODate, @DilRecorded, @LoginId )) AS s (LoanNum, REODate, DilRecorded, LoginId)
ON t.LoanNum = s.LoanNum
AND (t.REODate = s.REODate) --OR (t.REODate IS NULL AND s.REODate IS NULL))
AND (t.DilRecorded = s.DilRecorded) -- OR (t.DilRecorded IS NULL AND s.DilRecorded IS NULL))

WHEN NOT MATCHED BY TARGET
THEN INSERT (LoanNum, REODate, DilRecorded, InsertBy, InsertDt)
VALUES (s.LoanNum, s.REODate, s.DilRecorded, @LoginId, CURRENT_TIMESTAMP);
END

END TRY

BEGIN CATCH

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)

END CATCH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources