January 7, 2016 at 11:55 am
The objective is to determine, for each customer, the latest contract for each customer.
I am a bit at a loss on how to use the "more modern" features of T-SQL to achieve this.
The embarassing method I have used is a follows (at least I did not use cursors):
DECLARE @Customer_Contract TABLE
(
pk_Contract int not null primary key identity,
AccountId uniqueidentifier NULL,
AccountName varchar(20) NULL,
ContractStartDate date NULL,
ContractEndDate date NULL,
isLatestContract int NOT NULL DEFAULT 0
)
DECLARE @last_Customer_Contract TABLE
(
pk_LastContract int not null primary key identity,
AccountId uniqueidentifier NULL,
AccountName varchar(20) NULL,
ContractStartDate date NULL,
ContractEndDate date NULL
)
INSERT INTO @Customer_Contract
(
AccountId,
AccountName,
ContractStartDate,
ContractEndDate
)
VALUES
('{E2BC0C62-DB7D-4D7E-BEA0-F9D63AA7C354}', 'Alpha', '2010-01-01', '2010-12-31'),
('{E2BC0C62-DB7D-4D7E-BEA0-F9D63AA7C354}', 'Alpha', '2011-01-01', '2011-12-31'),
('{E2BC0C62-DB7D-4D7E-BEA0-F9D63AA7C354}', 'Alpha', '2012-01-01', '2012-12-31'),
('{E2BC0C62-DB7D-4D7E-BEA0-F9D63AA7C354}', 'Alpha', '2013-01-01', '2013-12-31'),
('{C856821D-8694-4400-A46E-B7B6A8362541}', 'Bravo', '2011-01-01', '2011-12-31'),
('{C856821D-8694-4400-A46E-B7B6A8362541}', 'Bravo', '2012-01-01', '2012-12-31'),
('{C91E905F-1051-44DE-91F2-4CB8B6C3635B}', 'Charlie', '2011-01-01', '2011-12-31'),
('{C91E905F-1051-44DE-91F2-4CB8B6C3635B}', 'Charlie', '2012-01-01', '2012-12-31'),
('{C91E905F-1051-44DE-91F2-4CB8B6C3635B}', 'Charlie', '2013-01-01', '2013-12-31'),
('{C91E905F-1051-44DE-91F2-4CB8B6C3635B}', 'Charlie', '2014-01-01', '2014-12-31')
INSERT INTO @last_Customer_Contract
(
AccountId,
AccountName,
ContractStartDate,
ContractEndDate
)
SELECT
AccountId,
AccountName,
MAX(ContractStartDate),
MAX(ContractEndDate)
FROM @Customer_Contract
GROUP BY AccountId, AccountName
UPDATE c
SET isLatestContract = 1
FROM @Customer_Contract c
INNER JOIN @last_Customer_Contract l
ON l.AccountId = c.AccountId
AND l.ContractStartDate = c.ContractStartDate
SELECT * FROM @Customer_Contract
The desired output is
[font="Courier New"]
NameStartLatest
Alpha2010-01-010
Alpha2011-01-010
Alpha2012-01-010
Alpha2013-01-011
Bravo2011-01-010
Bravo2012-01-011
Charlie2011-01-010
Charlie2012-01-010
Charlie2013-01-010
Charlie2014-01-011
[/font]
January 7, 2016 at 12:05 pm
How about something like the below:
SELECT Customer, ContractStart, ContractEnd,
CASE WHEN ContractEnd = MAX(ContractEnd) OVER (PARTITION BY Customer) THEN 1 ELSE 0 END
FROM YourTable;
The MAX() OVER (PARTITION BY) will return the maximum ContractEnd value of all rows for the same customer, without having to GROUP BY the actual query. (Before SQL Server 2008, you'd have to use a correlated subquery to do this)
January 7, 2016 at 12:13 pm
Thank you, half-way there. The main table still has to be * updated * with the result set, i.e. it is needed to identify, for each customer, which is its latest contract as the final state of the main table.
I tried creating a cte but could not use the partition inside it.
Maybe I'm running low on caffeine.
January 7, 2016 at 12:38 pm
j-1064772 (1/7/2016)
Thank you, half-way there. The main table still has to be * updated * with the result set, i.e. it is needed to identify, for each customer, which is its latest contract as the final state of the main table.I tried creating a cte but could not use the partition inside it.
Maybe I'm running low on caffeine.
Windowing functions can be used in CTEs. Can you post what you tried?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 7, 2016 at 12:42 pm
The problem was as follows:
UPDATE t1
SET IsLatestContract =
CASE
WHEN t1.AccountId = t2.AccountId
AND t1.ContractStartDate = MAX(t2.ContractStartDate) OVER (PARTITION BY t2.AccountName) THEN 1
ELSE 0
END
FROM @Customer_Contract t1
INNER JOIN @Customer_Contract t2
ON t2.AccountId = t1.AccountId
[font="Courier New"]Windowed functions can only appear in the SELECT or ORDER BY clauses.[/font]
My latest attempt is this:
UPDATE t1
SET IsLatestContract =
CASE
WHEN t1.AccountId = t2.AccountId
AND t1.ContractStartDate = (SELECT MAX(t2.ContractStartDate) OVER (PARTITION BY t2.AccountName) ) THEN 1
ELSE 0
END
FROM @Customer_Contract t1
INNER JOIN @Customer_Contract t2
ON t2.AccountId = t1.AccountId
But it is the first record for each customer that gets updated, not the last one. In fact, the records may not even be ordered by date in the table.
January 7, 2016 at 1:13 pm
Have a look at this:
with MostRecent as
(SELECT pk_Contract, rn = ROW_NUMBER() over (partition by AccountName order by ContractEndDate desc) FROM @Customer_Contract)
update cc
set isLatestContract = 1
from @Customer_Contract cc
join MostRecent on cc.pk_Contract = MostRecent.pk_Contract
where MostRecent.rn = 1
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 7, 2016 at 1:19 pm
Hit the nail right on the head. (Of course the "initiator" ; is required before the with:-))
Thank you !
January 7, 2016 at 2:35 pm
j-1064772 (1/7/2016)
Hit the nail right on the head. (Of course the "initiator" ; is required before the with:-))Thank you !
I hope that that 'initiator' comment was deliberately tongue-in-cheek! (As implied by the smiley.)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 8, 2016 at 7:53 am
Yes it was ... ;-). Your help is most appreciated. Contributions such as yours are what makes this forum great.
It took me 0,5 sec to realize that your solution did not "work" at first cut when I pasted it AFTER my code creating and populating the table. Started out with SQL 7, I just have not developed the reflex to add the semicolon terminator at the end of each statement - hey it works anyways - except for the WITH construct. 😎
That just got me started on the newfangled way of writing T-SQL such as:
SELECT
col1
,col2
;WITH
In short, placing a terminator at the beginning of the next argument, hence my "initiator" crack. T-SQL was supposed to allow querying a database using English-like sentences (COBOL, anyone ?) , where the comma is AFTER an item, NOT BEFORE the next one when you reach the end of a line. Instead of starting a new line with a comma.
I still do not see what was fixed that was broken by the old way of writing a SELECT statement. Maybe it made it easier to comment out a block of columns in a select statement without having to remove a trailing comma -- (you know, when you are trying to insert into a lot columns from the select of corresponding columns and you just do not have the exact number of columns in the INSERT list ad the SELECT list - divide and conquer and split up the lists until you find the missing column).
But that causes the same problem if you are disabling from the top because the first non disabled columns starts with a comma.
But the crown goes to my pet-peeve: turning an initiator keyword into a terminator as done by T-SQL statement generators (à la CRM):
FROM
Table1 INNER JOIN
Table2 ON Table1.x = Table2.x INNER JOIN
...
Try disabling the second table just for fun ... Guess what, you have to disable the last keyword on the previous line.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply