July 1, 2009 at 2:58 pm
Hi All,
Please help me on this. My original table has data like this:
cust_id date height weight
1 01/01/2009 5'2 110lbs
1 01/02/2009
1 01/03/2009
2 01/01/2008 5'2 110lbs
2 01/02/2009 5'5 115 lbs
2 01/03/2009
3 01/01/2006 5'2 110lbs
3 01/02/2007 5'5 115 lbs
3 01/03/2008
3 01/01/1909
Is it possible for me to run an update statement so that the records that have empty values will be updated with its latest data like this:
cust_id date heigh weight
1 01/01/2009 5'2 110lbs
1 01/02/2009 5'2 110lbs
1 01/03/2009 5'2 110lbs
2 01/01/2008 5'2 110lbs
2 01/02/2009 5'5 115 lbs
2 01/03/2009 5'5 115 lbs
3 01/01/2006 5'2 110lbs
3 01/02/2007 5'5 115 lbs
3 01/03/2008 5'5 115 lbs
3 01/01/2009 5'5 115 lbs
Thanks in advance!
Maijose
July 1, 2009 at 3:11 pm
DECLARE @Table TABLE (cust_id int, date datetime, height varchar(10), weight varchar(10))
INSERT INTO @Table
SELECT 1, '01/01/2009', '5''2', '110lbs' UNION ALL
SELECT 1, '01/02/2009', NULL, NULL UNION ALL
SELECT 1, '01/03/2009', NULL, NULL UNION ALL
SELECT 2, '01/01/2008', '5''2', '110lbs' UNION ALL
SELECT 2, '01/02/2009', '5''5', '115 lbs' UNION ALL
SELECT 2, '01/03/2009', NULL, NULL UNION ALL
SELECT 3, '01/01/2006', '5''2', '110lbs' UNION ALL
SELECT 3, '01/02/2007', '5''5', '115 lbs' UNION ALL
SELECT 3, '01/03/2008', NULL, NULL UNION ALL
SELECT 3, '01/01/1909', NULL, NULL
SELECT * FROM @Table
UPDATE t1
SET Height = t2.Height,
Weight = t2.Weight
FROM @Table t1
INNER JOIN (
SELECT t1.Cust_id,
t1.Height,
t1.Weight
FROM @Table t1
INNER JOIN (
SELECT cust_id, MAX(Date) as Maxdate
FROM @Table
WHERE COALESCE(Height,weight) IS NOT NULL
GROUP BY cust_id
) t2 ON t1.cust_id = t2.cust_id AND t1.date = t2.Maxdate
) t2
ON t1.cust_id = t2.cust_id
WHERE COALESCE(t1.Height,t1.Weight) IS NULL
SELECT * FROM @Table
July 1, 2009 at 3:50 pm
Well I am not a grt expert @ T-SQL. I can try to do with the of temp table.
I don't know what will be in the height and weight field (null or empty string) for where there is no data
-- Insert into temp tbl by group ID where date is max and height and weight is empty or null
-- then u have result set to join back. I have attach here code for u that i have tested. hope works for u. I usually use #temp table when there is heavy query to do.
I am using 2008 so ur insert on the test code will not workCREATE TABLE Test ( id int, dat date, ht varchar(5), wt varchar(10) )
DROP TABLE Test
insert into Test (id, dat, ht, wt)
Values
(1, '01/01/2009', '4', '110lbs'),
(1, '01/02/2009', null, null),
(1, '01/03/2009', null, ''),
(2, '01/01/2008', '6' ,'110lbs'),
(2, '01/02/2009', '5' ,'115 lbs'),
(2, '01/03/2009','', null ),
(3, '01/01/2006', '7' ,'110lbs'),
(3, '01/02/2007', '5' ,'115 lbs'),
(3, '01/03/2008',null, null ),
(3, '01/01/1909' , '', '' )
SELECT ID, max(Dat) as Dat
INTO #Test
FROM Test
where (ht is not NULL or ht '' )AND (wt IS not null or wt '')
group by ID
SELECT * FROM #TEST
update ttt
set wt = d.wt,
ht = d.ht
from test ttt
inner join
(select t.id, ht, wt from test t
inner join #test tt
on t.id = tt.id and
t.dat = tt.dat) AS D
On D.id = ttt.id
SELECT * FROM test
DROp table #test
July 1, 2009 at 4:35 pm
Thank you so much for your help. I'm sorry to overlook the data. Your query works perfectly for the data that I gave before. Sorry about that.
My data actually looks like this:
cust_id date height weight
101656 01/02/08
101656 01/08/08
101656 04/07/08 5' 5" 65.00
101656 04/09/08
101656 04/14/08
101656 04/29/08 5' 5" 65.00
101656 09/08/08
101656 09/12/08
101656 10/21/08
101656 10/28/08
101656 04/08/09 5' 4.961" 64.96
101656 04/09/09
101656 04/13/09
101656 05/05/09 5' 6.5" 66.50
101656 05/05/09
101656 05/05/09 5' 5.75" 65.75
101656 05/12/09
101656 05/27/09
And I want:
cust_id date height weight
101656 01/02/08
101656 01/08/08
101656 04/07/08 5' 5" 65.00
10165604/09/08 5' 5" 65.00
101656 04/14/08 5' 5" 65.00
101656 04/29/08 5' 5" 65.00
101656 09/08/08 5' 5" 65.00
101656 09/12/08 5' 5" 65.00
101656 10/21/08 5' 5" 65.00
101656 10/28/08 5' 5" 65.00
101656 04/08/09 5' 4.961" 64.96
101656 04/09/09 5' 4.961" 64.96
101656 04/13/09 5' 4.961" 64.96
101656 05/05/09 5' 6.5" 66.50
101656 05/05/09 5' 5.75" 65.75[/b]
101656 05/12/09 5' 5.75" 65.75
101656 05/27/09 5' 5.75" 65.75
So sorry about that! I couldn't figure out by relating to your syntax.
Maijoe
July 1, 2009 at 4:54 pm
The only way i think is, insert into #temp tbl and do in while loop............
July 1, 2009 at 5:25 pm
Should be possible with DENSE_RANK within a CROSS APPLY but...
First:
Your sample data contain:
101656 05/05/09 5' 6.5" 66.50
101656 05/05/09
101656 05/05/09 5' 5.75" 65.75
101656 05/12/09
101656 05/27/09
There is no uniqueness for date '05/05/09'. How shall this be handled? Does your table contain any IDENTITY or another incremental (primary) key?
Second:
Guy after more than 200 posts here on SSC you really should know how to provide sample data. It took me about 10 minutes to get your data into a SQL format. About two minutes later I saw that the data don't fit the requirement. I've been the third person who had to format your data...
Please give me an answer to my first question and help us to help you. You can find a link in my signature which makes it easy to provide formated sample data.
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
July 1, 2009 at 5:27 pm
Florian Reischl (7/1/2009)
Should be possible with DENSE_RANK within a CROSS APPLY but...First:
Your sample data contain:
101656 05/05/09 5' 6.5" 66.50
101656 05/05/09
101656 05/05/09 5' 5.75" 65.75
101656 05/12/09
101656 05/27/09
There is no uniqueness for date '05/05/09'. How shall this be handled? Does your table contain any IDENTITY or another incremental (primary) key?
Second:
Guy after more than 200 posts here on SSC you really should know how to provide sample data. It took me about 10 minutes to get your data into a SQL format. About two minutes later I saw that the data don't fit the requirement. I've been the third person who had to format your data...
Please give me an answer to my first question and help us to help you. You can find a link in my signature which makes it easy to provide formated sample data.
Flo
Agreed.
July 1, 2009 at 5:28 pm
3 questions:
Q1. Is it possible that for a given row the height column might be vaued but the weight column may be null, or vice versa? Or are the height and weight columns either both valued or both null? I.e. is the following possible?
cust_id date height weight
4 2006-01-01 5'5 110lbs
4 2007-01-01 NULL 120lbs
4 2008-01-01 NULL NULL
If the above situation is possible then it might be simpler to update the height and weight columns separately, i.e. update the null column values in 2 separate UPDATE statements.
Q2. Is is possible that for a given value of cust_id there can be a row with null height and weight columns whose date column is earlier that another row with the same value of cust_id whose height and weight columns are valued? I.e. is this possible:
cust_id date height weight
4 2006-01-01 5'5 110lbs
4 2007-01-01 NULL NULL
4 2008-01-01 5'7 120lbs
4 2009-01-01 NULL NULL
If it is possible, then I presume that the 2nd row should be updated with the values in the 1st row, and the 4th row should be updated with the values in the 3rd row.
Q3. Is the combination of the cust_id and date columns guaranteed to be unique, and if not, is there another primary key?
The following statements update the height and height columns assuming that the situations described in Q1 and Q2 are possible and that the combination of the cust_id and date columns is unique.
DECLARE @Table TABLE (cust_id int, date datetime, height varchar(10), weight varchar(10))
INSERT INTO @Table
SELECT 1, '20090101', '5''2', '110lbs' UNION ALL
SELECT 1, '20090102', NULL, NULL UNION ALL
SELECT 1, '20090103', NULL, NULL UNION ALL
SELECT 2, '20080101', '5''2', '110lbs' UNION ALL
SELECT 2, '20090102', '5''5', NULL UNION ALL
SELECT 2, '20090103', NULL, NULL UNION ALL
SELECT 3, '20060101', '5''2', '110lbs' UNION ALL
SELECT 3, '20070102', '5''5', '115 lbs' UNION ALL
SELECT 3, '20080103', NULL, NULL UNION ALL
SELECT 3, '20090101', NULL, NULL UNION ALL
SELECT 4, '20060601', '6''0', '130lbs' UNION ALL
SELECT 4, '20070602', NULL, NULL UNION ALL
SELECT 4, '20080603', '6''1', '135lbs' UNION ALL
SELECT 4, '20090601', NULL, NULL
SELECT cust_id, date, height, weight FROM @Table
/* First update height column */
;WITH cteHMAP (cust_id, source_date, target_date) AS (
SELECT T1.cust_id, MAX(T1.date), T2.date
FROM @Table T1 INNER JOIN @Table T2
ON (T1.cust_id = T2.cust_id AND T1.date < T2.date)
WHERE (T1.height IS NOT NULL AND T2.height IS NULL)
GROUP BY T1.cust_id, T2.date
),
cteHSRC (cust_id, date, height) AS (
SELECT SRC.cust_id, MAP.target_date, SRC.height
FROM @Table SRC INNER JOIN cteHMAP MAP
ON (SRC.cust_id = MAP.cust_id AND SRC.date = MAP.source_date)
)
UPDATE T SET height = S.height
FROM @Table T
INNER JOIN cteHSRC S ON (T.cust_id = S.cust_id AND T.date = S.date)
/* Next update weight column */
;WITH cteWMAP (cust_id, source_date, target_date) AS (
SELECT T1.cust_id, MAX(T1.date), T2.date
FROM @Table T1 INNER JOIN @Table T2
ON (T1.cust_id = T2.cust_id AND T1.date < T2.date)
WHERE (T1.weight IS NOT NULL AND T2.weight IS NULL)
GROUP BY T1.cust_id, T2.date
),
cteWSRC (cust_id, date, weight) AS (
SELECT SRC.cust_id, MAP.target_date, SRC.weight
FROM @Table SRC INNER JOIN cteWMAP MAP
ON (SRC.cust_id = MAP.cust_id AND SRC.date = MAP.source_date)
)
UPDATE T SET weight = S.weight
FROM @Table T
INNER JOIN cteWSRC S ON (T.cust_id = S.cust_id AND T.date = S.date)
SELECT cust_id, date, height, weight FROM @Table
July 1, 2009 at 11:30 pm
Yes, table does have identity column.
Data will always have both weight and height; otherwise, they will be null on both fields.
Maijoe
July 2, 2009 at 11:43 am
joemai (7/1/2009)
Yes, table does have identity column.Data will always have both weight and height; otherwise, they will be null on both fields.
Maijoe
Joe... With 122 visits, it's time you learned how to post data to get fully tested answers quicker. Please read and heed the article at the first link in my signature below.
--Jeff Moden
July 2, 2009 at 11:59 am
joemai (7/1/2009)
Yes, table does have identity column.Data will always have both weight and height; otherwise, they will be null on both fields.
Maijoe
Not totally sure what questions you are answering or not here.
Please identify each question asked by andrew and provide an detailed, yet concise answer. it will help us help you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply