February 7, 2012 at 9:51 am
i have three tables orders, orders_delivered, orders_delivered_sta
and the data in the three tables look like
table orders
orders_id
10
11
12
13
table orders_delivered
orders_delivered_id orders_id
10 1000
10 1001
11 1002
12 1003
12 1004
13 1005
13 1006
13 1007
table orders_delivered_sta
orders_delivered_sta_id orders_delivered_id date now_ind
1 1000 02/11/2011 0
2 1000 01/10/2006 0
3 1000 09/13/2011 0
4 1001 01/19/2010 0
5 1001 02/21/2011 0
6 1002 02/11/2009 0
7 1002 08/27/2010 0
8 1003 07/15/2012 0
9 1004 03/09/2007 0
10 1010 10/01/2010 0
11 1011 03/27/2011 0
12 1012 07/25/2010 0
13 1013 09/18/2004 0
so i need to update orders_delivered_sta table such that now_ind should be 1 for the max date of one orders_delivered_id
like for one orders_delivered_id 1000 the max date is 09/13/2011 for this set of orders_delivered_id and date (1000,09/13/2011) the now_ind should be 1 and if the column orders_delivered_id has one and only one id then that should be changed to 1
there is some data in orders_delivered_sta table which are not in orders and orders_delivered tables those need not to be changed. the orders_delivered_id which are in oreders_delivered table only needs to change
so the desired output should look like
table orders_delivered_sta
orders_delivered_sta_id orders_delivered_id date now_ind
1 1000 02/11/2011 0
2 1000 01/10/2006 0
3 1000 09/13/2011 1
4 1001 01/19/2010 0
5 1001 02/21/2011 1
6 1002 02/11/2009 0
7 1002 08/27/2010 1
8 1003 07/15/2012 1
9 1004 03/09/2007 1
10 1010 10/01/2010 0
11 1011 03/27/2011 0
12 1012 07/25/2010 0
13 1013 09/18/2004 0
create table orders
(
order_id int primary key
)
insert into orders select 10
insert into orders select 11
insert into orders select 12
insert into orders select 13
create table orders_delivered
(
orders_delivered_id int primary key,
orders_id int FOREIGN KEY(orders_id)REFERENCES orders (orders_id)
)
insert into orders_delivered select 1000,10
insert into orders_delivered select 1001,10
insert into orders_delivered select 1002,11
insert into orders_delivered select 1003,12
insert into orders_delivered select 1004,12
insert into orders_delivered select 1005,13
insert into orders_delivered select 1006,13
insert into orders_delivered select 1007,13
create table orders_delivered_sta
(
orders_delivered_sta_id int primary key,
orders_delivered_id int FOREIGN KEY(orders_delivered_id)REFERENCES orders_delivered (orders_delivered_id),
date char(10),
now_ind int
)
insert into orders_delivered_sta select 1,1000,'02/11/2011', 0
insert into orders_delivered_sta select 2,1000,'01/10/2006', 0
insert into orders_delivered_sta select 3,1000,'09/13/2011', 0
insert into orders_delivered_sta select 4,1001,'01/19/2010', 0
insert into orders_delivered_sta select 5,1001,'02/21/2011', 0
insert into orders_delivered_sta select 6,1002,'02/11/2009', 0
insert into orders_delivered_sta select 7,1002,'08/27/2010', 0
insert into orders_delivered_sta select 8,1003,'07/15/2012', 0
insert into orders_delivered_sta select 9,1004,'03/09/2007', 0
insert into orders_delivered_sta select 10,1010,'10/01/2010', 0
insert into orders_delivered_sta select 11,1011,'03/27/2011', 0
insert into orders_delivered_sta select 12,1012,'07/25/2010', 0
insert into orders_delivered_sta select 13,1013,'09/18/2004', 0
February 7, 2012 at 9:59 am
You can achieve this easily by using the RANK() or ROW_NUMBER () with a grouping on OrderID.
February 7, 2012 at 10:01 am
tq...but if u hav a chance, can u help me out wth a sample query...
February 7, 2012 at 10:18 am
try something like this.
I have intentionally not written the update code. I guess you can figure that out on your own 😎
create table #orders_delivered_sta
(
#orders_delivered_sta_id int primary key,
orders_delivered_id int FOREIGN KEY(orders_delivered_id)REFERENCES orders_delivered (orders_delivered_id),
date char(10),
now_ind int
)
insert into #orders_delivered_sta select 1,1000,'02/11/2011', 0
insert into #orders_delivered_sta select 2,1000,'01/10/2006', 0
insert into #orders_delivered_sta select 3,1000,'09/13/2011', 0
insert into #orders_delivered_sta select 4,1001,'01/19/2010', 0
insert into #orders_delivered_sta select 5,1001,'02/21/2011', 0
insert into #orders_delivered_sta select 6,1002,'02/11/2009', 0
insert into #orders_delivered_sta select 7,1002,'08/27/2010', 0
insert into #orders_delivered_sta select 8,1003,'07/15/2012', 0
insert into #orders_delivered_sta select 9,1004,'03/09/2007', 0
SELECT * FROM #orders_delivered_sta
;WITH CTE1 AS(
SELECT [#orders_delivered_sta_id],orders_delivered_id, date,
RANK() over(partition by orders_delivered_id order by date asc) 'Rank1'
FROM #orders_delivered_sta
)
select orders_delivered_id, date, MAX(RANK1) from CTE1 group by orders_delivered_id, date, Rank1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply