May 2, 2016 at 12:24 pm
Thanks in advance to all who take the time and read this and offer help. I appreciate it!
On to my dilemma, and your mission, should you choose to except, is to help me figure out how to get the most recent CASH_DRWR_DATE from this query result:
CUST_NUMBERCASH_DRWR_DATECONTR_DATE
1000272014-04-11 00:00:00.0002014-05-04 00:00:00.000
1000272014-05-05 17:17:46.0002014-07-04 00:00:00.000
1000272014-05-05 17:18:02.0002014-05-04 00:00:00.000
1000272014-05-08 17:21:38.0002014-06-04 00:00:00.000
1000272014-06-12 09:52:15.0002014-07-04 00:00:00.000
1000272014-07-15 07:54:04.0002014-07-04 00:00:00.000
1000272014-07-18 09:39:09.0002014-08-04 00:00:00.000
1000272014-08-14 18:04:03.0002014-09-04 00:00:00.000
1000272014-09-15 07:27:05.0002014-09-04 00:00:00.000
1000272014-09-18 17:08:04.0002014-10-04 00:00:00.000
1000272014-10-15 19:20:17.0002014-10-04 00:00:00.000
1000272014-10-23 13:26:48.0002014-11-04 00:00:00.000
1000272014-11-15 17:10:11.0002014-11-04 00:00:00.000
1000272014-12-15 08:07:09.0002014-11-04 00:00:00.000
1000272014-12-24 11:55:48.0002014-11-04 00:00:00.000
1000272014-12-24 14:12:55.0002014-12-04 00:00:00.000
1000272015-01-15 17:02:11.0002014-12-04 00:00:00.000
Notice that on 2014-05-05 we have two CONTR_DATE (both are different). In this case a transaction was rolled back reverting to the older CONTR_DATE. I want to return by date, 2014-05-05, the most recent CASH_DRWR_DATE for each day. This would yield the correct CONTR_DATE for each day when there are more than one entry. I hope that makes sense.
Again thanks for any and all help!
Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC
'...if they take my stapler then I'll set the building on fire...'
May 2, 2016 at 12:48 pm
Hi Fred,
First I want to say thanks for the dataset to work with
However
You should probably understand that you will get a lot more help if you set up the ENTIRE environment for people to cut and paste and get you an answer........programmers and dbas are generally lazy but they like puzzles and if you make it easy to start then they may answer your question.
Now because I am trying to become a better contributer here at SSC I will set this up for you and give you my answer.
Create Table CUST_CASH_CONTR(Cust_Number INT, CASH_DRWR_DATE DATETIME, CONTR_DATE DATE)
INSERT INTO CUST_CASH_CONTR (Cust_Number,CASH_DRWR_DATE,CONTR_DATE)
VALUES
(100027,'2014-04-11 00:00:00.000','2014-05-04 00:00:00.000'),
(100027,'2014-05-05 17:17:46.000','2014-07-04 00:00:00.000'),
(100027,'2014-05-05 17:18:02.000','2014-05-04 00:00:00.000'),
(100027,'2014-05-08 17:21:38.000','2014-06-04 00:00:00.000'),
(100027,'2014-06-12 09:52:15.000','2014-07-04 00:00:00.000'),
(100027,'2014-07-15 07:54:04.000','2014-07-04 00:00:00.000'),
(100027,'2014-07-18 09:39:09.000','2014-08-04 00:00:00.000'),
(100027,'2014-08-14 18:04:03.000','2014-09-04 00:00:00.000'),
(100027,'2014-09-15 07:27:05.000','2014-09-04 00:00:00.000'),
(100027,'2014-09-18 17:08:04.000','2014-10-04 00:00:00.000'),
(100027,'2014-10-15 19:20:17.000','2014-10-04 00:00:00.000'),
(100027,'2014-10-23 13:26:48.000','2014-11-04 00:00:00.000'),
(100027,'2014-11-15 17:10:11.000','2014-11-04 00:00:00.000'),
(100027,'2014-12-15 08:07:09.000','2014-11-04 00:00:00.000'),
(100027,'2014-12-24 11:55:48.000','2014-11-04 00:00:00.000'),
(100027,'2014-12-24 14:12:55.000','2014-12-04 00:00:00.000'),
(100027,'2015-01-15 17:02:11.000','2014-12-04 00:00:00.000')
SELECT Cust_Number,MAX(CASH_DRWR_DATE) as Most_Recent_Cash_Date,CONTR_DATE
FROM CUST_CASH_CONTR
GROUP BY Cust_Number,CONTR_DATE
ORDER BY CONTR_DATE
DROP TABLE CUST_CASH_CONTR
This results in
Cust_NumberMost_Recent_Cash_DateCONTR_DATE
1000272014-05-05 17:18:02.0002014-05-04
1000272014-05-08 17:21:38.0002014-06-04
1000272014-07-15 07:54:04.0002014-07-04
1000272014-07-18 09:39:09.0002014-08-04
1000272014-09-15 07:27:05.0002014-09-04
1000272014-10-15 19:20:17.0002014-10-04
1000272014-12-24 11:55:48.0002014-11-04
1000272015-01-15 17:02:11.0002014-12-04
May 2, 2016 at 12:56 pm
Smendle,
You rock! I stared at this all morning and just couldn't see it. I should have, but just couldn't. I will make sure the next time I add more to the environment! I truly appreciate you help in this!!
Fred
Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC
'...if they take my stapler then I'll set the building on fire...'
May 2, 2016 at 12:59 pm
I understood this a little different from Smendle.
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER( PARTITION BY CUST_NUMBER, CAST(CASH_DRWR_DATE AS date) ORDER BY CONTR_DATE DESC) AS rn
FROM TestDates
)
SELECT CUST_NUMBER,
CASH_DRWR_DATE,
CONTR_DATE
FROM CTE
WHERE rn = 1
ORDER BY CASH_DRWR_DATE;
You should follow his advice on how to post sample data. For more details on that, check the links in my signature.
If you don't fully understand the solutions, feel free to ask any questions you might have.
May 2, 2016 at 1:03 pm
Quick suggestion (similar to what's already posted)
😎
;WITH SAMPLE_DATA (CUST_NUMBER,CASH_DRWR_DATE,CONTR_DATE) AS
(SELECT
CUST_NUMBER
,CONVERT(DATETIME,CASH_DRWR_DATE,121)
,CONVERT(DATETIME,CONTR_DATE,121)
FROM (VALUES
(100027,'2014-04-11 00:00:00.000','2014-05-04 00:00:00.000')
,(100027,'2014-05-05 17:17:46.000','2014-07-04 00:00:00.000')
,(100027,'2014-05-05 17:18:02.000','2014-05-04 00:00:00.000')
,(100027,'2014-05-08 17:21:38.000','2014-06-04 00:00:00.000')
,(100027,'2014-06-12 09:52:15.000','2014-07-04 00:00:00.000')
,(100027,'2014-07-15 07:54:04.000','2014-07-04 00:00:00.000')
,(100027,'2014-07-18 09:39:09.000','2014-08-04 00:00:00.000')
,(100027,'2014-08-14 18:04:03.000','2014-09-04 00:00:00.000')
,(100027,'2014-09-15 07:27:05.000','2014-09-04 00:00:00.000')
,(100027,'2014-09-18 17:08:04.000','2014-10-04 00:00:00.000')
,(100027,'2014-10-15 19:20:17.000','2014-10-04 00:00:00.000')
,(100027,'2014-10-23 13:26:48.000','2014-11-04 00:00:00.000')
,(100027,'2014-11-15 17:10:11.000','2014-11-04 00:00:00.000')
,(100027,'2014-12-15 08:07:09.000','2014-11-04 00:00:00.000')
,(100027,'2014-12-24 11:55:48.000','2014-11-04 00:00:00.000')
,(100027,'2014-12-24 14:12:55.000','2014-12-04 00:00:00.000')
,(100027,'2015-01-15 17:02:11.000','2014-12-04 00:00:00.000')
) AS X(CUST_NUMBER,CASH_DRWR_DATE,CONTR_DATE))
SELECT
SD.CUST_NUMBER
,MAX(SD.CASH_DRWR_DATE) AS CASH_DRWR_DATE
,CONVERT(DATE,SD.CONTR_DATE,0) AS CONTR_DATE
FROM SAMPLE_DATA SD
GROUP BY SD.CUST_NUMBER
,CONVERT(DATE,SD.CONTR_DATE,0);
Results
CUST_NUMBER CASH_DRWR_DATE CONTR_DATE
----------- ----------------------- ----------
100027 2014-05-05 17:18:02.000 2014-05-04
100027 2014-05-08 17:21:38.000 2014-06-04
100027 2014-07-15 07:54:04.000 2014-07-04
100027 2014-07-18 09:39:09.000 2014-08-04
100027 2014-09-15 07:27:05.000 2014-09-04
100027 2014-10-15 19:20:17.000 2014-10-04
100027 2014-12-24 11:55:48.000 2014-11-04
100027 2015-01-15 17:02:11.000 2014-12-04
May 2, 2016 at 1:16 pm
Thanks Luis!
I read your links. Never occurred to me to get that detailed. But I can definitively do that in the future. Thanks for the advice.
Quick question, how would I use the CTE to populate a temp table if I was pulling the data from an existing table.
Thanks again!
Fred
Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC
'...if they take my stapler then I'll set the building on fire...'
May 2, 2016 at 1:23 pm
That VALUES trick is really handy! Absolutely going to steal that!
May 2, 2016 at 2:12 pm
You just add an INSERT INTO between the CTE and the final SELECT.
WITH CTE AS(
SELECT Something
FROM Somewhere
)
INSERT INTO Destination
SELECT *
FROM CTE;
May 2, 2016 at 3:24 pm
Thanks again Luis! I really appreciate all of you help and everyone else as well!: -)
Frederick (Fred) J. Stemp, Jr.
Database Administrator / Database Developer
Dealer Funding, LLC
'...if they take my stapler then I'll set the building on fire...'
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply