Date Function (Possibility)

Question

Date Function (Possibility)

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

SQLPain Hall of Fame Points: 3522 More actions · Answer 1

there are some records in exported column those are NOT Null and it is also a DATETIME data type. If you look at the attached excel Loan '123123' is actually displayed in my result set although there is only 1 row where Exported column is NULL, so this record should not have been displayed in the first place at all, when I am running the below code loan '123123' shows up.

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(DraftDate) , MAX(DraftDate)) <=3

I believe if we could figure out why loan '123123' is showing in our result set than I think we would have our answer.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

SQLPain (10/27/2015)
there are some records in exported column those are NOT Null and it is also a DATETIME data type. If you look at the attached excel Loan '123123' is actually displayed in my result set although there is only 1 row where Exported column is NULL, so this record should not have been displayed in the first place at all, when I am running the below code loan '123123' shows up.
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(DraftDate) , MAX(DraftDate)) <=3
I believe if we could figure out why loan '123123' is showing in our result set than I think we would have our answer.

Please can I make the following suggestion (again)...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

..and please include ALL columns that re to be used in the filters....ie "Exported"

I have neither the time nor the inclination to start building scripts based on a spreadsheet. Sorry.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

SQLPain Hall of Fame Points: 3522 More actions · Answer 3

Alright here you go:

Another sample Data:

Lets analyze the where(filter clause) first, one record(row) of loan 1004 should be eliminated because it has a record where Coloum Exported is NOT NULL

Loan 1005 should be eliminated because the DraftDate < getdate()

Loan 1003, one record(row) should be eliminated because RequestedDate < getdate()

CREATE TABLE TEST_TABLE_Dupe_Pay

(

LID INT,

RegPay Money,

DraftDt DATETIME,

RequestedDt DATETIME,

Exported DATETIME

)

INSERT INTO TEST_TABLE_Dupe_Pay(LID, RegPay, DraftDt, RequestedDt, Exported)

Values (1001, '500', '2015-10-28 00:00:00.000', '2015-10-27 05:52:02.950', NULL)

INSERT INTO TEST_TABLE_Dupe_Pay(LID, RegPay, DraftDt, RequestedDt, Exported)

Values (1001, '500', '2015-11-28 00:00:00.000', '2015-10-27 08:09:14.037', NULL)

INSERT INTO TEST_TABLE_Dupe_Pay(LID, RegPay, DraftDt, RequestedDt, Exported)

Values (1002, '1000', '2015-11-01 00:00:00.000', '2015-10-27 09:55:09.487', NULL)