September 8, 2016 at 10:45 pm
Hi Friends pleasde some one help me,
CREATE TABLE [dbo].[#diff](
[model_key] [varchar](25) NULL,
[brand] [varchar](25) NULL,
[model] [varchar](25) NULL,
[trim] [varchar](25) NULL,
[door_count] [int] NULL,
[created_date] [date] NULL,
[stat] [varchar](100) NULL
) ON [PRIMARY]
insert into [#diff]([model_key],[brand],[model],[trim],[door_count],[created_date]) values ('A11','Audi','A1','RS 7','5','2016-09-06')
insert into [#diff] ([model_key],[brand],[model],[trim],[door_count],[created_date]) values ('A11','Audi','A1','RS 8','4','2016-08-06')
insert into [#diff] ([model_key],[brand],[model],[trim],[door_count],[created_date]) values ('A11','Audi','A1','RS 8','3','2016-07-06')
insert into [#diff] ([model_key],[brand],[model],[trim],[door_count],[created_date]) values ('A12','Audi','A1','RS 8','3','2016-07-06')
insert into [#diff] ([model_key],[brand],[model],[trim],[door_count],[created_date]) values ('A12','Audi','A1','RS 8','5','2016-09-06')
insert into [#diff] ([model_key],[brand],[model],[trim],[door_count],[created_date]) values ('A12','Audi','A1','RS 2','5','2016-05-06')
select * from #diff
input :
model_keybrandmodeltrimdoor_countcreated_datestat
A11AudiA1RS 752016-09-06NULL
A11AudiA1RS 842016-08-06NULL
A11AudiA1RS 832016-07-06NULL
A12AudiA1RS 832016-07-06NULL
A12AudiA1RS 852016-09-06NULL
A12AudiA1RS 252016-05-06NULL
Expected out put :
model_keybrandmodeltrimdoor_countcreated_datestat
A11AudiA1RS 752016-09-06Trim changed , doorcount changed
A11AudiA1RS 842016-08-06NULL
A11AudiA1RS 832016-07-06NULL
A12AudiA1RS 832016-07-06NULL
A12AudiA1RS 852016-09-06Doorcount changed
A12AudiA1RS 252016-05-06NULL
i need to compare same( model_key and bran_column and model ) against (trim and door_count) only for maximum of date and 2nd maximum of date :
For more concept explanation below :
eg case 1:
model_keybrandmodeltrimdoor_countcreated_datestat
A11AudiA1RS 752016-09-06NULL --> same key,brand,model
A11AudiA1RS 842016-08-06NULL --> same key,brand,model
output shuold be updated in stat column max(date) record column like Trim changed, door_count changed
eg :
model_keybrandmodeltrimdoor_countcreated_datestat
A11AudiA1RS 752016-09-06Trim changed, door_count changed
eg case 2:
model_keybrandmodeltrimdoor_countcreated_datestat
A11AudiA1RS 752016-09-06NULL --> same key,brand,model
A11AudiA1RS 852016-08-06NULL --> same key,brand,model
output shuold be updated in stat column max(date) record column like Trim changed
eg :
model_keybrandmodeltrimdoor_countcreated_datestat
A11AudiA1RS 752016-09-06Trim changed
September 9, 2016 at 1:25 am
This should help..
; WITH cte_diff AS
(
SELECT ROW_NUMBER() OVER( PARTITION BY model_key, brand, model ORDER BY created_date DESC ) AS RN,
LEAD( trim ) OVER( PARTITION BY model_key, brand, model ORDER BY created_date DESC ) AS prev_trim,
LEAD( door_count ) OVER( PARTITION BY model_key, brand, model ORDER BY created_date DESC ) AS prev_door_count, *
FROM #diff
)
UPDATE cte_diff
SET stat = CASE
WHEN trim != prev_trim AND door_count != prev_door_count THEN 'trim changed, door_count changed'
WHEN trim != prev_trim THEN 'trim changed'
WHEN door_count != prev_door_count THEN 'door_count changed'
ELSE NULL
END
WHERE RN = 1;
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 9, 2016 at 4:01 am
Hello sir ,
thank u very much for your valuable time. i am using SQL server 2008. it will showing
****** 'LEAD' is not a recognized built-in function name. ***** .
please can u suggest me is there any other posibilities ..?
September 9, 2016 at 4:29 am
Anandkumar-SQL_Developer (9/9/2016)
Hello sir ,thank u very much for your valuable time. i am using SQL server 2008. it will showing
****** 'LEAD' is not a recognized built-in function name. ***** .
please can u suggest me is there any other posibilities ..?
Do you realise that you posted in a SQL Server 2012 forum? Makes it confusing for others who try to help you (as has happened here).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 9, 2016 at 4:40 am
Dear Phil Parkin ,
first I have tried to colleagues SQL 2012 version only.
but their is display an error message like " *** The Parallel Data Warehouse (PDW) features are not enabled *** " .
after that i was tried to my system 2008 after tried only i realized Lead in not a build in function in 2008.
September 9, 2016 at 6:15 am
Anandkumar-SQL_Developer (9/9/2016)
Dear Phil Parkin ,first I have tried to colleagues SQL 2012 version only.
but their is display an error message like " *** The Parallel Data Warehouse (PDW) features are not enabled *** " .
after that i was tried to my system 2008 after tried only i realized Lead in not a build in function in 2008.
OK, but if you are developing on your colleagues' machines, what are your colleagues doing? 🙂
If you are developing on 2008, why risk wasting the time of yours and others by posting in a later forum?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 9, 2016 at 6:24 am
What version of SQL is the production system running?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 9, 2016 at 6:55 am
The below code should work on any version starting from 2005.
; WITH cte_diff AS
(
SELECT ROW_NUMBER() OVER( PARTITION BY model_key, brand, model ORDER BY created_date DESC ) AS RN, *
FROM #diff
)
UPDATE main
SET main.stat =
CASE
WHEN main.trim != tmp.trim AND main.door_count != tmp.door_count THEN 'trim changed, door_count changed'
WHEN main.trim != tmp.trim THEN 'trim changed'
WHEN main.door_count != tmp.door_count THEN 'door_count changed'
ELSE NULL
END
FROM cte_diff AS main
INNER JOIN cte_diff AS tmp
ON tmp.model_key = main.model_key
AND tmp.brand = main.brand
AND tmp.model = main.model
WHERE main.RN = 1 AND tmp.RN = 2;
As other members have mentioned in the forum, please check the SQL Server Version you have in Production before starting any development work
Posting in inappropriate forums creates confusion and would lead to wastage of efforts including yours
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 9, 2016 at 9:13 am
Dear Kingston Dhasian ,
really your coding is really fantastic. (i was developed coding in another method like Equellent of lead function functionality in 2008).
but compare to my coding.., your coding is really much more efficient and more effective.
Thank you very much genius.
your coding thought me better idea to me. i will keep this idea in my mind. once again thanks alot.
September 11, 2016 at 10:45 pm
Anandkumar-SQL_Developer (9/9/2016)
Dear Kingston Dhasian ,really your coding is really fantastic. (i was developed coding in another method like Equellent of lead function functionality in 2008).
but compare to my coding.., your coding is really much more efficient and more effective.
Thank you very much genius.
your coding thought me better idea to me. i will keep this idea in my mind. once again thanks alot.
Glad I could help you out.
Even you deserve some credit for posting DDL of the tables involved along with some sample data.
That helped me in providing you solutions faster.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply