SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get last transaction from multiple groups of records


Get last transaction from multiple groups of records

Author
Message
Lrobinson 93181
Lrobinson 93181
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 140
I need to pull the last transaction that occurred before a specific type of transaction, using the transaction dates.
It should return 2 records, the Last_Dt for c_Dt 2010-09-06 and the Last_Dt for c_Dt 2010-12-22, but is returning all the records for both groups:

c_id c_Dt Last_Dt c_rep
123 2010-09-06 2010-09-06 25
456 2010-09-06 2010-08-06 23
789 2010-09-06 2010-07-06 25
123 2011-12-06 2011-09-06 25
456 2011-12-06 2011-08-06 23
789 2011-12-06 2011-07-06 25

This is the code:

SELECT DISTINCT a.c_id, b.c_Dt, MAX(d.c_dt) AS LastDt, a.c_rep
FROM tbl_c_master AS a INNER JOIN
tbl_s_det AS d ON a.c _id = d.c_id
INNER JOIN
(SELECT c_id, c_rep, MIN(DISTINCT c_dt) AS CpDt
FROM vw_C_Pd
GROUP BY c_id, s_code, c_rep
HAVING (s_code = '01') OR
(s_code = '10') OR
(s_code LIKE '14') OR (s_code = '24') OR
(s_code = '20')) AS b a.c_id = b.c_id
GROUP BY a.c_id, b.c_Dt, a.c_rep, d.c_dt
HAVING (a.c_id = b.c_id) AND (MAX(d.c_dt) < b. CpDt)
ORDER BY a.c_id, b.c_Dt

I thought MAX would do it, but it made no difference. I also tried with a CTE but it came up empty (no records returned).

Any suggestions greatly appreciated!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61669 Visits: 17954
Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lrobinson 93181
Lrobinson 93181
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 140
Hi, I hope this is what you wanted to see:

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
c_ID INT IDENTITY(10,1) PRIMARY KEY CLUSTERED,
c_dt DATETIME,
last_dt DATETIME,
c_rep INT,
)

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INSERT INTO #mytable
(c_iD, c_dt, last_dt, c_rep )
SELECT '11111','2010-09-06 12:00AM','2010-09-04 12:00AM','25' UNION ALL
SELECT '11111','2010-09-06 12:00AM','2010-08-04 12:00AM','25' UNION ALL
SELECT '11111','2010-09-06 12:00AM','2010-07-04 12:00AM','23' UNION ALL
SELECT '11111','2010-09-06 12:00AM','2010-06-04 12:00AM','25' UNION ALL
SELECT '11111','2010-09-06 12:00AM','2010-05-04 12:00AM','23' UNION ALL
SELECT '11111','2010-10-06 12:00AM','2010-10-04 12:00AM','25' UNION ALL
SELECT '11111','2010-10-06 12:00AM','2010-09-04 12:00AM','25' UNION ALL
SELECT '11111','2010-10-06 12:00AM','2010-08-04 12:00AM','25' UNION ALL
SELECT '11111','2010-10-06 12:00AM','2010-07-04 12:00AM','23'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF



First, I need to pull the c_id, c_dt, and s_code for specific s_codes by the last c_dt for those specific codes. Once I have those, I want to pull the matching c_ids for any transactions that occured prior to each distinct c_dt in in my initial query, and select only the last c_id date from that group of records, so that my result is:

'11111','2010-09-06 12:00AM','2010-09-04 12:00AM','25'
'11111','2010-10-06 12:00AM','2010-10-04 12:00AM','25'

Many thanks!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61669 Visits: 17954
Your inserts didn't work because you have specified a value for each row but the values are all the same. I modified that so it will work.


IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
c_ID INT IDENTITY(10,1) PRIMARY KEY CLUSTERED,
c_dt DATETIME,
last_dt DATETIME,
c_rep INT,
)

--===== Insert the test data into the test table
INSERT INTO #mytable
(c_dt, last_dt, c_rep )
SELECT '2010-09-06 12:00AM','2010-09-04 12:00AM','25' UNION ALL
SELECT '2010-09-06 12:00AM','2010-08-04 12:00AM','25' UNION ALL
SELECT '2010-09-06 12:00AM','2010-07-04 12:00AM','23' UNION ALL
SELECT '2010-09-06 12:00AM','2010-06-04 12:00AM','25' UNION ALL
SELECT '2010-09-06 12:00AM','2010-05-04 12:00AM','23' UNION ALL
SELECT '2010-10-06 12:00AM','2010-10-04 12:00AM','25' UNION ALL
SELECT '2010-10-06 12:00AM','2010-09-04 12:00AM','25' UNION ALL
SELECT '2010-10-06 12:00AM','2010-08-04 12:00AM','25' UNION ALL
SELECT '2010-10-06 12:00AM','2010-07-04 12:00AM','23'

select * from #mytable



Now for the actual problem.


First, I need to pull the c_id, c_dt, and s_code for specific s_codes by the last c_dt for those specific codes. Once I have those, I want to pull the matching c_ids for any transactions that occured prior to each distinct c_dt in in my initial query, and select only the last c_id date from that group of records, so that my result is:

'11111','2010-09-06 12:00AM','2010-09-04 12:00AM','25'
'11111','2010-10-06 12:00AM','2010-10-04 12:00AM','25'

Many thanks!


From that description I have absolutely no idea what the logic is supposed to be here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lrobinson 93181
Lrobinson 93181
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 140
I have provided the code and an example of the expected result set...so I am not sure what doesn't make sense? Maybe a step-by-step would help...it took me several conversations with the user to finally get it.

1. Pull the records from each group with the latest c_dt
This should return these two records:
'11111','2010-09-06 12:00AM','25'
'11111','2010-10-06 12:00AM','23'
Where the C_Id is the same, but the c_rep is different.

2. Nest the first query, to pull matching C_id's for final query, and then pull the latest "last_dt" that is earlier than the c_dt from the first set of records:
'11111','2010-09-06 12:00AM','2010-09-04 12:00AM','25'
'11111','2010-10-06 12:00AM','2010-10-04 12:00AM','23'

Basically, I want to pull the last transactions for a specific group, using c_dt, then find another type of "last transaction" that occurred prior to that last transaction. I hope this helps.

Thanks,
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61669 Visits: 17954
Lrobinson 93181 (8/6/2013)
I have provided the code and an example of the expected result set...so I am not sure what doesn't make sense? Maybe a step-by-step would help...it took me several conversations with the user to finally get it.

1. Pull the records from each group with the latest c_dt
This should return these two records:
'11111','2010-09-06 12:00AM','25'
'11111','2010-10-06 12:00AM','23'
Where the C_Id is the same, but the c_rep is different.

2. Nest the first query, to pull matching C_id's for final query, and then pull the latest "last_dt" that is earlier than the c_dt from the first set of records:
'11111','2010-09-06 12:00AM','2010-09-04 12:00AM','25'
'11111','2010-10-06 12:00AM','2010-10-04 12:00AM','23'

Basically, I want to pull the last transactions for a specific group, using c_dt, then find another type of "last transaction" that occurred prior to that last transaction. I hope this helps.

Thanks,


I think part of the issue is that you originally defined c_ID as a primary key but all the rows in your sample data had the same value. That means it either needs to be a identity OR not the primary key. I am now thinking that it should not be the primary key?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lrobinson 93181
Lrobinson 93181
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 140
Correct, it is not the PK. I should have adjusted the code to reflect that, but neglected to when I copy/pasted and just inserted my own example data.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61669 Visits: 17954
Lrobinson 93181 (8/6/2013)
I have provided the code and an example of the expected result set...so I am not sure what doesn't make sense? Maybe a step-by-step would help...it took me several conversations with the user to finally get it.

1. Pull the records from each group with the latest c_dt
This should return these two records:
'11111','2010-09-06 12:00AM','25'
'11111','2010-10-06 12:00AM','23'
Where the C_Id is the same, but the c_rep is different.



This doesn't seem to make sense to me. By latest I assume you mean the most recent or MAX(c_dt)? In your sample data the max and min for both group are the same.

select c_id, min(c_dt) as MinDate, Max(c_dt) as MaxDate, c_rep
from #mytable
group by c_ID, c_rep

I can't even begin to think about the second part because I don't understand your first part. :-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lrobinson 93181
Lrobinson 93181
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 140
Ok, I had to refer back to my actual code and you're right; Step 1 should be asking for a MIN DISTINCT c_dt. I've revised the mytable example code, and provided the query that returns the results I'm looking for in step 1. That should help us get to Step 2, which is where I am stuck!


--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
c_ID INT IDENTITY(10,1),
c_dt DATETIME,
c_rep INT,
)

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INSERT INTO #mytable
(c_iD, c_rep, c_dt )
SELECT '11111','23','2010-09-06 12:00AM' UNION ALL
SELECT '11111','23','2010-09-07 12:00AM' UNION ALL
SELECT '11111','23','2010-09-08 12:00AM' UNION ALL
SELECT '11111','23','2010-09-09 12:00AM' UNION ALL
SELECT '11111','23','2010-09-10 12:00AM' UNION ALL
SELECT '11111','25','2010-10-06 12:00AM' UNION ALL
SELECT '11111','25','2010-10-07 12:00AM' UNION ALL
SELECT '11111','25','2010-10-08 12:00AM' UNION ALL
SELECT '11111','25','2010-10-09 12:00AM'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF

SELECT c_iD ,c_rep, MIN(DISTINCT c_dt) AS c_Date
FROM #mytable
GROUP BY c_ID, c_rep
ORDER BY c_ID, c_rep
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61669 Visits: 17954
OK trying once again to piece this together.

Step #1 is easy enough. I think we got that. Your description of Step #2 doesn't make sense here.


2. Nest the first query, to pull matching C_id's for final query, and then pull the latest "last_dt" that is earlier than the c_dt from the first set of records:


We don't have a column "last_dt" in the table. AND since step #1 gets the earliest value of c_dt there is no value that will be less than that. Can you post what the expected output is based on your sample data?

Also, you don't need MIN(Distinct c_dt). The distinct is not useful here because you are getting the lowest value. :-P MIN(c_dt) is perfectly fine.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search