Date Function (Possibility)

  • 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

  • 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

  • Can anyone help me out here please, I would really appreciate it.

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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".

  • 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.

  • I have edited my CREATE TEST TABLE DraftDT and RequestedDt to DATETIME if that helps.

  • 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".

  • 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

  • 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

  • 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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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