October 12, 2010 at 10:15 am
hi,
I 've scenario like this. I want to retrieve data like
VIN LastRODate LastbutoneRODate
-------------------------------------
xxxx sep 31 2010 aug 1 2010
how can i write the query?
pls help
October 12, 2010 at 10:18 am
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
With 290 points, you should know this by now.
Is what you are showing the expected results, or the data to query from?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 12, 2010 at 10:32 am
Are you looking at last date and then last date minus 1 day or even minus 1 business day?
If not you could always do "select top 2 distinct order by desc"
October 12, 2010 at 10:40 am
Ninja's_RGR'us (10/12/2010)
Are you looking at last date and then last date minus 1 day or even minus 1 business day?If not you could always do "select top 2 distinct order by desc"
I was thinking this myself, but his sample data has dates almost 2 months apart - and one of those is an invalid date. Which leads to the select top (2) process.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 12, 2010 at 10:51 am
WayneS (10/12/2010)
Ninja's_RGR'us (10/12/2010)
Are you looking at last date and then last date minus 1 day or even minus 1 business day?If not you could always do "select top 2 distinct order by desc"
I was thinking this myself, but his sample data has dates almost 2 months apart - and one of those is an invalid date. Which leads to the select top (2) process.
Good catch.... and / or : a simple typo in the question.
Hence we need more info :w00t:.
October 12, 2010 at 11:11 am
I have service table
Dealerid Vin transactiondt
123 xxxx sep 30 2010
123 xxxx Aug 1 2010
123 xxxx May 23 2010
456 yyyy 0ct 1 2010
456 yyyy sep 28 2010
456 yyyy june 28 2010
789 zzzz july 19 2010
789 zzzz june 1 2009
789 zzzz aug 28 2010
I want to get the 2 most recent transactions
for each vin
Output
dealerid vin transactiondt last2Transactiondt
123 xxxx sep 30 2010 Aug 1 2010
456 yyyy 0ct 1 2010 sep 28 2010
789 zzzz aug 28 2010 july 19 2010
I wrote query like this:
with lastrecord (VIN, transactiondt)
as
(
select vin, max(transactiondt) as LastRODate
from ilxauto.Service
group by VIN
),
last2record (VIN, transactiondt)
as
(
select top 1 t.vin, max(t.transactiondt) as last2date
from ILxauto.service t
inner join lastrecord l on t.VIN = l.VIN and t.transactiondt < l.LastRODate
group by t.VIN
)
select
v.v1_vin,v.Dealer_num,l1.lastROdate, l2.last2date
from vin v
left join lastrecord l1 on v.V1_VIN = l1.VIN
left join last2record l2 on v.V1_VIN = l2.VIN
it gave error like this:
Msg 207, Level 16, State 1, Line 14
Invalid column name 'LastRODate'
October 12, 2010 at 11:41 am
Since you posted in a sql 2008 forum, what are the data types for the date? Are they datetime, date, datetime2, etc.
Also, is there a PK constraint or unique constraint on the table to prohibit > 1 entry per DealerID/VIN/transactiondt?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 12, 2010 at 11:58 am
Does this do what you're looking for?
-- See how this starts off with a table and data in it?
-- If you had provided us the data in this format,
-- it would have made things easier for all of the
-- volunteers on this site to help you out.
DECLARE @test-2 TABLE (Dealerid int, Vin varchar(4), transactiondt date);
INSERT INTO @test-2
SELECT 123, 'xxxx', 'sep 30 2010' UNION ALL
SELECT 123, 'xxxx', 'Aug 1 2010' UNION ALL
SELECT 123, 'xxxx', 'May 23 2010' UNION ALL
SELECT 456, 'yyyy', 'Oct 1 2010' UNION ALL
SELECT 456, 'yyyy', 'sep 28 2010' UNION ALL
SELECT 456, 'yyyy', 'june 28 2010' UNION ALL
SELECT 789, 'zzzz', 'july 19 2010' UNION ALL
SELECT 789, 'zzzz', 'june 1 2009' UNION ALL
SELECT 789, 'zzzz', 'aug 28 2010' ;
WITH CTE AS
(
-- rank each transaction date, starting with the most recent.
-- use dense_rank to avoid gaps.
SELECT *,
DR = DENSE_RANK() OVER (PARTITION BY Dealerid, VIN ORDER BY transactiondt DESC)
FROM @test-2
)
SELECT Dealerid,
Vin,
LastRODate = MAX(CASE WHEN DR = 1 THEN transactiondt ELSE NULL END),
LastRObutoneDate = MAX(CASE WHEN DR = 2 THEN transactiondt ELSE NULL END)
FROM CTE
WHERE DR < 3
GROUP BY Dealerid, Vin;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 12, 2010 at 12:58 pm
Nevermind. I figured it out
October 12, 2010 at 1:02 pm
Thanks, wayne.
October 12, 2010 at 1:02 pm
He explicitly named the column transactiondt in the first CTE, but then specified an AS LastRODate in the select..and then tried to call it by the LastROdate, so it wasn't working. You just needed to call it using Transactiondt right?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply