August 5, 2013 at 9:31 am
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!
August 5, 2013 at 10:09 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2013 at 12:26 pm
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!
August 5, 2013 at 12:35 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 6:47 am
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,
August 6, 2013 at 7:29 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 7:59 am
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.
August 6, 2013 at 8:14 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 10:01 am
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
August 6, 2013 at 12:35 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 1:26 pm
I've gotten a little further
Step 1 query gets me this:
c_iD1 c_rep c_Date
11111 23 2010-09-06
11111 25 2010-10-06
In step 2 I need to look at different data from the same table and find records where c_iD1 = c_iD AND
my "last_date" is < c_Date in order to return:
c_iD1 c_rep1 c_Date c_rep last_dt
11111 23 2010-09-06 25 2010-09-03
11111 25 2010-10-06 28 2010-10-02
I did not include the column "last_dt" in the create #mytable code simply to keep the first step simple, but basically, it is selected from #mytable in step 2 and compared to c_Date from ther first query.
August 6, 2013 at 1:29 pm
Lrobinson 93181 (8/6/2013)
I've gotten a little furtherStep 1 query gets me this:
c_iD1 c_rep c_Date
11111 23 2010-09-06
11111 25 2010-10-06
In step 2 I need to look at different data from the same table and find records where c_iD1 = c_iD AND
my "last_date" is < c_Date in order to return:
c_iD1 c_rep1 c_Date c_rep last_dt
11111 23 2010-09-06 25 2010-09-03
11111 25 2010-10-06 28 2010-10-02
I did not include the column "last_dt" in the create #mytable code simply to keep the first step simple, but basically, it is selected from #mytable in step 2 and compared to c_Date from ther first query.
Well if you want my help you have to provide all of the info. Please post ddl with all the columns and the correct data that will match that output. I am willing to help but we just keep going around in circles.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply