October 20, 2015 at 11:47 am
Hello everyone, is it possible that result set only return me the Loan ID (LID) where my Draft Date (DraftDt) is within 3 days to each other, for example in my result set I want only LID = '1002' as they are within 3 days close to each other(11/1, 11/2, 11/3) I do not want LID = '1001' as the DraftDT is 1 month apart from each other.
Assumption here is that draft dates within 3 days are duplicate entries and over 3 days are not.
Sample Code:
CREATE TABLE TEST_TABLE_Dupe_Pay
(
LID INT,
RegPay Money,
DraftDt DATETIME,
RequestedDt DATETIME
)
INSERT INTO TEST_TABLE_Dupe_Pay(LID, RegPay, DraftDt, RequestedDt)
Values (1001, '500', '2015-10-28 00:00:00.000', '2015-10-27 05:52:02.950')
INSERT INTO TEST_TABLE_Dupe_Pay(LID, RegPay, DraftDt, RequestedDt)
Values (1001, '500', '2015-11-28 00:00:00.000', '2015-10-27 08:09:14.037')
INSERT INTO TEST_TABLE_Dupe_Pay(LID, RegPay, DraftDt, RequestedDt)
Values (1002, '1000', '2015-11-01 00:00:00.000', '2015-10-27 09:55:09.487')
INSERT INTO TEST_TABLE_Dupe_Pay (LID, RegPay, DraftDt, RequestedDt)
Values (1002, '1000', '2015-11-02 00:00:00.000', '2015-10-27 08:48:27.093')
INSERT INTO TEST_TABLE_Dupe_Pay (LID, RegPay, DraftDt, RequestedDt)
Values (1002, '1000', '2015-11-03 00:00:00.000', '2015-10-27 08:51:56.153')
INSERT INTO TEST_TABLE_Dupe_Pay (LID, RegPay, DraftDt, RequestedDt)
Values (1002, '1000', '2015-11-01 00:00:00.000', '2015-10-27 10:11:39.460')
SELECT * FROM TEST_TABLE_Dupe_Pay
ORDER BY LID, DraftDt
SELECT LID FROM TEST_TABLE_Dupe_Pay
WHERE DraftDt > CONVERT(DATE, GETDATE())
AND RequestedDt >= CONVERT(DATE, GETDATE())
GROUP BY LID
HAVING COUNT (*) >1
--DROP TABLE TEST_TABLE_Dupe_Pay
October 20, 2015 at 3:22 pm
I am working on two techniques but both of them are not giving me the required result>
1)
SELECT DISTINCT LID FROM TEST_TABLE_Dupe_Pay AS A
CROSS APPLY (SELECT TOP 1 B.DraftDt,
DATEDIFF(DAY, B.DraftDt, A.DraftDt)
FROM TEST_TABLE_Dupe_Pay AS B
WHERE A.LID = B.LID
AND DATEDIFF(DAY, B.DraftDt, A.DraftDt) <= 3
AND B.DraftDt <> A.DraftDt
ORDER BY B.DraftDt ASC)
AB (FirstDraftDate, DifferenceInDays);
2)
;WITH Dupe_Pay As (
SELECT LID
,DraftDt
,[Instance] = ROW_NUMBER() OVER (PARTITION BY LID ORDER BY DraftDt ASC)
FROM TEST_TABLE_Dupe_Pay
)
SELECT
A.LID
,A.DraftDt
,B.DraftDt AS FirstDraftdate
,TimeDiffInDays = DATEDIFF(Day, B.DraftDt, A.DraftDt)
FROM TEST_TABLE_Dupe_Pay A
INNER JOIN (SELECT LID, DraftDt FROM TEST_TABLE_Dupe_Pay
WHERE [Instance] = 1 ) B
ON A.LID = B.LID
AND DATEDIFF(DAY, B.DraftDt, A.DraftDt) > 3
WHERE A.instance <> 1
October 23, 2015 at 7:50 am
Can anyone help me out here please, I would really appreciate it.
October 23, 2015 at 8:00 am
SQLPain (10/20/2015)
Hello everyone, is it possible that result set only return me the Loan ID (LID) where my Draft Date (DraftDt) is within 3 days to each other, for example in my result set I want only LID = '1002' as they are within 3 days close to each other(11/1, 11/2, 11/3) I do not want LID = '1001' as the DraftDT is 1 month apart from each other.Assumption here is that draft dates within 3 days are duplicate entries and over 3 days are not.
please confirm the results you are expecting....do you only want one row with one column (LID) where LID = 1002...or did you mean something else?
also....are your dates stored as datetime or actually as varchar as you posted?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 23, 2015 at 8:06 am
Yes In this case I only want 1 row stating '1002' under the column 'LID'. In the sample data the datatype for DraftDt and RequestedDt is varchar but in real data it is datetime so we have to use the convert function.
Thanks
October 23, 2015 at 8:19 am
try this
SELECT LID
FROM TEST_TABLE_Dupe_Pay
GROUP BY LID
HAVING DATEDIFF(d, MIN(DraftDt) , MAX(DraftDt)) <=3
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 23, 2015 at 8:36 am
Oh Wow, and I was using the CTE's and cross joins , This works and looks so clean.
This actually happened to be a job that whenever there is a duplicate entry, another Job kicks (XYZ) kicks in which is just sending out emails to the responsible person.
The code currently been used is as follows, my question would be after I use your suggested having clause (HAVING DATEDIFF(d, MIN(DraftDt) , MAX(DraftDt)) <=3) which happens to be working fine can I use the same below if statement?
if @@ROWCOUNT > 0
begin
USE msdb
EXEC sp_start_job @job_name = 'XYZ'
end
October 26, 2015 at 3:57 pm
When I tried the following on the Test Data table it worked,
SELECT LID
FROM TEST_TABLE_Dupe_Pay
GROUP BY LID
HAVING DATEDIFF(d, MIN(DraftDt) , MAX(DraftDt)) <=3
When I tried on the real data it did not, It is listing all the records, seems like under the WHERE clause CONVERT is causing some issues. The datatype for DraftDate and RequestedDate is DATETIME. Can somebody please look
SELECT LoanID As [Loan ID]
FROM APayments
Where DraftDate > CONVERT(DATE, GETDATE())
AND DateRequested >= CONVERT(DATE, GETDATE())
AND Exported IS NULL
GROUP BY LoanID
HAVING DATEDIFF(DAY, MIN(CONVERT(DATE, GETDATE())) , MAX(CONVERT(DATE, GETDATE()))) <=3
October 26, 2015 at 4:13 pm
The MIN and MAX need to operate on the dates in the table, not on GETDATE(), therefore more like this:
SELECT LoanID As [Loan ID]
FROM APayments
Where DraftDate > DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND DateRequested >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND Exported IS NULL
GROUP BY LoanID
HAVING DATEDIFF(DAY, MIN(DateRequested) , MAX(DraftDate)) <=3
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 27, 2015 at 9:28 am
Thanks Scott, I got your point and it makes sense. but somehow its still not giving me the desired result. If I run your above statement without the GROUPBY and HAVING clause it is still giving me the exact same result. It is displaying all the records and not just the repetitive ones. I want the LoanID that repeats itself having draftdate between 3 days.
My DraftDate is in DATETIME (2010-12-08 00:00:00.000)
DateRequested is in DATETIME (2010-12-08 07:02:53.827)
Not sure if completely removing DATEADD clause from the where statement would actually help.
October 27, 2015 at 9:46 am
I have edited my CREATE TEST TABLE DraftDT and RequestedDt to DATETIME if that helps.
October 27, 2015 at 9:57 am
SQLPain (10/27/2015)
Thanks Scott, I got your point and it makes sense. but somehow its still not giving me the desired result. If I run your above statement without the GROUPBY and HAVING clause it is still giving me the exact same result. It is displaying all the records and not just the repetitive ones. I want the LoanID that repeats itself having draftdate between 3 days.My DraftDate is in DATETIME (2010-12-08 00:00:00.000)
DateRequested is in DATETIME (2010-12-08 07:02:53.827)
Not sure if completely removing DATEADD clause from the where statement would actually help.
You still need the GROUP BY and HAVING, but the HAVING needs to reference the date columns in the table, not GETDATE().
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 27, 2015 at 10:24 am
SQLPain (10/27/2015)
I have edited my CREATE TEST TABLE DraftDT and RequestedDt to DATETIME if that helps.
a suggestion...as you appear to be using Getdate() as a filter. then please provide sample scripts that include specific rows that you would wish to filter out based on Getdate().......and please provide as a new script....editing earlier scripts just confuses the thread
edit...and please include ALL columns that re to be used in the filters....ie "Exported"
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 27, 2015 at 10:30 am
Thanks Scott, I am using the GroupBy and having clause but using them is not making any difference as results are coming out to be exact same. Funny thing is I even changed my sample data into datetime format as I mentioned earlier and used the following code and it is working perfectly fine, I am not sure why it is not working in the real data.
It is driving me crazy now.
SELECT LID
FROM TEST_TABLE_Dupe_Pay
Where DraftDt > DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND RequestedDt >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
GROUP BY LID
HAVING DATEDIFF(d, MIN(DraftDt) , MAX(DraftDt)) <=3
October 27, 2015 at 10:38 am
SQLPain (10/27/2015)
Thanks Scott, I am using the GroupBy and having clause but using them is not making any difference as results are coming out to be exact same. Funny thing is I even changed my sample data into datetime format as I mentioned earlier and used the following code and it is working perfectly fine, I am not sure why it is not working in the real data.It is driving me crazy now.
SELECT LID
FROM TEST_TABLE_Dupe_Pay
Where DraftDt > DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND RequestedDt >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
GROUP BY LID
HAVING DATEDIFF(d, MIN(DraftDt) , MAX(DraftDt)) <=3
Your sample data is not representative of your real data.
Add some of the data - which fails - from your real data to your sample data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply