November 22, 2012 at 10:10 am
My data is the log of changes to 3 specific records for an AppID. The AppCatID describes the 3 different types of AppID records (4, 5 or 6).
What I need to return is the single most recent change to each of the 3 AppCatID records for the one AppID (#184).
The data:
LogKeyID is the key of the log table
CreateDate is the datetime the log record was created
OrgKeyID is the key of the original table that holds the 3 records
AppID is the master ID that relates the 3 records together
AppCatID is the category of the record (4, 5 or 6 in this example but could be any number)
DisplayOrder is the display order
Text = text recorded for the record.
LogKeyID; CreateDate; OrgKeyID; AppID; AppCatID; DisplayOrder; Text
307; 2012-11-19 16:34:24.260; 322; 184; 4; 1; orginal text for rec 1
308; 2012-11-19 16:34:24.260; 323; 184; 5; 2; orginal text for rec 2
309; 2012-11-19 16:34:24.260; 324; 184; 6; 3; orginal text for rec 3
310; 2012-11-20 10:50:34.063; 322; 184; 4; 1; change 1 to rec 1
311; 2012-11-20 10:50:43.470; 323; 184; 5; 2; change 1 to rec 2
312; 2012-11-20 10:50:48.110; 324; 184; 6; 3; change 1 to rec 3
313; 2012-11-22 09:28:06.220; 323; 184; 5; 2; change 2 to rec 2
314; 2012-11-22 09:30:05.627; 324; 184; 6; 3; change 2 to rec 3
315; 2012-11-22 11:20:42.750; 322; 184; 4; 1; change 2 to rec 1
316; 2012-11-22 11:21:19.470; 322; 184; 4; 1; change 3 to rec 1
Desired result set in this order (by display order):
316; 2012-11-22 11:21:19.470; 322; 184; 4; 1; change 3 to rec 1
313; 2012-11-22 09:28:06.220; 323; 184; 5; 2; change 2 to rec 2
314; 2012-11-22 09:30:05.627; 324; 184; 6; 3; change 2 to rec 3
Thanks Very much
Gord
November 22, 2012 at 11:27 am
You want something like this:
-- Setup test table
create table #TestData (
LogKeyID int,
CreateDate datetime,
OrgKeyID int,
AppID int,
AppCatID int,
DisplayOrder int,
TextData varchar(64));
go
-- Insert test data
insert into #TestData(
LogKeyID,CreateDate,OrgKeyID,AppID,AppCatID,DisplayOrder,TextData
)
VALUES
(307,'2012-11-19 16:34:24.260',322,184,4,1,'orginal text for rec 1'),
(308,'2012-11-19 16:34:24.260',323,184,5,2,'orginal text for rec 2'),
(309,'2012-11-19 16:34:24.260',324,184,6,3,'orginal text for rec 3'),
(310,'2012-11-20 10:50:34.063',322,184,4,1,'change 1 to rec 1'),
(311,'2012-11-20 10:50:43.470',323,184,5,2,'change 1 to rec 2'),
(312,'2012-11-20 10:50:48.110',324,184,6,3,'change 1 to rec 3'),
(313,'2012-11-22 09:28:06.220',323,184,5,2,'change 2 to rec 2'),
(314,'2012-11-22 09:30:05.627',324,184,6,3,'change 2 to rec 3'),
(315,'2012-11-22 11:20:42.750',322,184,4,1,'change 2 to rec 1'),
(316,'2012-11-22 11:21:19.470',322,184,4,1,'change 3 to rec 1');
go
-- Run example code
with BaseData as (
select
LogKeyID,
CreateDate,
OrgKeyID,
AppID,
AppCatID,
DisplayOrder,
TextData,
rn = row_number() over (partition by AppID, AppCatID order by CreateDate desc)
from
#TestData
)
select
LogKeyID,
CreateDate,
OrgKeyID,
AppID,
AppCatID,
DisplayOrder,
TextData
from
BaseData
where
rn = 1
order by
DisplayOrder;
go
-- clean up test data
drop table #TestData;
go
November 22, 2012 at 12:27 pm
Sweet! Thanks works great!:-D
November 22, 2012 at 9:08 pm
jonesg 74626 (11/22/2012)
Sweet! Thanks works great!:-D
Glad it works.
Also, did you see how I setup the data for the query? That is how you should post your table DDL and sample data. Don't rely on the good naturedness of the volunteers on this site to do that work for you, especially as your questions get more complex.
The more you do for us, the more we will do to help you.
December 14, 2012 at 7:58 am
One more question, new dataset - similar problem... it is possible to extend this solution and now INSERT this query into another table?
Take the query (simplifed):
With BaseTable AS (Select A, B, C FROM SomeTable WHERE A=1)
Select A, B, D
FROM BaseTable INNER JOIN
JoinTable ON JoinTable.A = BaseTable.A
and bulk insert the resulting records into a table:
INSERT INTO NewTable (A, B, D)
SELECT...???
Thanks
December 14, 2012 at 8:37 am
jonesg 74626 (12/14/2012)
One more question, new dataset - similar problem... it is possible to extend this solution and now INSERT this query into another table?Take the query (simplifed):
With BaseTable AS (Select A, B, C FROM SomeTable WHERE A=1)
Select A, B, D
FROM BaseTable INNER JOIN
JoinTable ON JoinTable.A = BaseTable.A
and bulk insert the resulting records into a table:
INSERT INTO NewTable (A, B, D)
SELECT...???
Thanks
Yes you can just use your select as the insert.
INSERT INTO NewTable (A, B, D)
SELECT A, B, D
from BaseTable
_______________________________________________________________
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/
December 14, 2012 at 8:46 am
BaseTable is not what needs to be inserted... The query joins BaseTable to JoinTable, it's the result of this query that needs to be inserted.
December 14, 2012 at 8:55 am
jonesg 74626 (12/14/2012)
BaseTable is not what needs to be inserted... The query joins BaseTable to JoinTable, it's the result of this query that needs to be inserted.
So make the query whatever it needs to be. The point is that you can use a select statement as the value for an insert.
Insert YourTable
Select A, B, D
FROM BaseTable INNER JOIN
JoinTable ON JoinTable.A = BaseTable.A
_______________________________________________________________
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/
December 14, 2012 at 9:12 am
I've used INSERT INTO...SELECT many times but as "SSCoach" answered above on Nov 22 the query solution to my problem is complex. This is the SQL Query I need to insert into a new table. The following 2 examples do not have the correct syntax (will not compile), I'm looking for the right syntax 🙂 :
INSERT INTO NewTable(A, B, D)
With BaseTable AS (Select A, B, C FROM SomeTable WHERE A=1)
Select A, B, D
FROM BaseTable INNER JOIN
JoinTable ON JoinTable.A = BaseTable.A
------------- OR ----------------------
With BaseTable AS (Select A, B, C FROM SomeTable WHERE A=1)
INSERT INTO NewTable(A, B, D)
Select A, B, D
FROM BaseTable INNER JOIN
JoinTable ON JoinTable.A = BaseTable.A
December 14, 2012 at 9:24 am
jonesg 74626 (12/14/2012)
I've used INSERT INTO...SELECT many times but as "SSCoach" answered above on Nov 22 the query solution to my problem is complex. This is the SQL Query I need to insert into a new table. The following 2 examples do not have the correct syntax (will not compile), I'm looking for the right syntax 🙂 :INSERT INTO NewTable(A, B, D)
With BaseTable AS (Select A, B, C FROM SomeTable WHERE A=1)
Select A, B, D
FROM BaseTable INNER JOIN
JoinTable ON JoinTable.A = BaseTable.A
------------- OR ----------------------
With BaseTable AS (Select A, B, C FROM SomeTable WHERE A=1)
INSERT INTO NewTable(A, B, D)
Select A, B, D
FROM BaseTable INNER JOIN
JoinTable ON JoinTable.A = BaseTable.A
The first query above is certainly not going to work. You can't use a cte inside of another statement like that.
In your second query you are referencing column D in your select statement but that column doesn't exist in your cte.
If you are getting an error it would be easier to help you figure out the problem if you tell me the error. Keep in mind that with a cte the previous statement MUST be terminated with a semicolon. To deal with this a lot of times people will just start their cte with a semicolon.
;With BaseTable AS (Select A, B, C FROM SomeTable WHERE A=1)
INSERT INTO NewTable(A, B, D)
Select A, B, C
FROM BaseTable INNER JOIN
JoinTable ON JoinTable.A = BaseTable.A
_______________________________________________________________
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/
December 14, 2012 at 9:40 am
That was it ! ";"
I was getting an error:
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Which I thought was just another useless syntax error... my mistake! :blush:
Thanks for sticking with me.
December 14, 2012 at 9:45 am
No problem. I have been bitten by that one myself. Glad you got it working.
_______________________________________________________________
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