Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get last transaction from multiple groups of records Expand / Collapse
Author
Message
Posted Monday, August 5, 2013 9:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, 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!
Post #1480969
Posted Monday, August 5, 2013 10:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 13,028, Visits: 11,838
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 Moden's 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)
Post #1480988
Posted Monday, August 5, 2013 12:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, 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!
Post #1481035
Posted Monday, August 5, 2013 12:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 13,028, Visits: 11,838
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 Moden's 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)
Post #1481041
Posted Tuesday, August 6, 2013 6:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, 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,
Post #1481294
Posted Tuesday, August 6, 2013 7:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 13,028, Visits: 11,838
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 Moden's 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)
Post #1481317
Posted Tuesday, August 6, 2013 7:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, 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.

Post #1481336
Posted Tuesday, August 6, 2013 8:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 13,028, Visits: 11,838
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 Moden's 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)
Post #1481352
Posted Tuesday, August 6, 2013 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, 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
Post #1481421
Posted Tuesday, August 6, 2013 12:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 13,028, Visits: 11,838
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. 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 Moden's 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)
Post #1481496
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse