| Author |
 Topic  |
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
 Posted - 08/23/2005 : 12:28:44
|
Whenever I want to perform major calculations on tables, I create test data and try them out there (like any normal IT Pro). Here I'm trying to calculate years, months and days based on starting and ending dates----but the dates aren't exclusively within the realm of the 20th or 21st centuries.
The best test I could think of was using the list of U.S. Presidents. However, I keep running into an aggregation error and the dates don't come out in correct year. month, and day formats.
I do not want to re-invent the wheel, so does anyone have a similar algorithm or way to do this?
Here's my test code:
DROP TABLE [Fowler].[dbo].[POTUS_A]
DROP TABLE [Fowler].[dbo].[POTUS_B]
CREATE TABLE [Fowler].[dbo].[POTUS_A]
(
seq int NULL ,
p_name nvarchar (10) NULL,
p_born nvarchar (08) NULL,
p_term_beg nvarchar (08) NULL,
p_term_end nvarchar (08) NULL,
p_died nvarchar (08) NULL,
p_age nvarchar (08) NULL
)
GO
INSERT POTUS_A VALUES('1','WASHINGTON', '17320222','17890430','17970304','17991214')
INSERT POTUS_A VALUES('2','ADAMS', '17351030','17970304','18010304','18260704')
INSERT POTUS_A VALUES('3','JEFFERSON', '17430413','18010304','18090304','18260704')
INSERT POTUS_A VALUES('4','MADISON', '17510316','18090304','18170304','18360628')
INSERT POTUS_A VALUES('5','MONROE', '17580428','18170304','18250304','18310704')
INSERT POTUS_A VALUES('6','ADAMS', '17670711','18250304','18290304','18480223')
INSERT POTUS_A VALUES('7','JACKSON', '17670315','18290304','18370304','18450608')
INSERT POTUS_A VALUES('8','VAN BUREN', '17821205','18370304','18410304','18620724')
INSERT POTUS_A VALUES('9','HARRISON', '17730209','18410304','18410404','18410404')
INSERT POTUS_A VALUES('10','TYLER', '17900329','18410404','18450304','18620118')
INSERT POTUS_A VALUES('11','POLK', '17951102','18450304','18490304','18490615')
INSERT POTUS_A VALUES('12','TAYLOR', '17841124','18490304','18500709','18500709')
INSERT POTUS_A VALUES('13','FILLMORE', '18000107','18500709','18530304','18740308')
INSERT POTUS_A VALUES('14','PIERCE', '18041123','18530304','18570304','18691008')
INSERT POTUS_A VALUES('15','BUCHANAN', '17910423','18570304','18610304','18680601')
INSERT POTUS_A VALUES('16','LINCOLN', '18090212','18610304','18650415','18650415')
INSERT POTUS_A VALUES('17','JOHNSON', '18081229','18650415','18690304','18750731')
INSERT POTUS_A VALUES('18','GRANT', '18220427','18690304','18770304','18850723')
INSERT POTUS_A VALUES('19','HAYES', '18221004','18770304','18810304','18930117')
INSERT POTUS_A VALUES('20','GARFIELD', '18311119','18810304','18810919','18810919')
INSERT POTUS_A VALUES('21','ARTHUR', '18291005','18810919','18850304','18861118')
INSERT POTUS_A VALUES('22','CLEVELAND', '18370318','18850304','18890304','19080624')
INSERT POTUS_A VALUES('23','HARRISON', '18330820','18890304','18930304','19010313')
INSERT POTUS_A VALUES('24','CLEVELAND', '18370318','18930304','18970304','19080624')
INSERT POTUS_A VALUES('25','MCKINLEY', '18430129','18970304','19010914','19010914')
INSERT POTUS_A VALUES('26','ROOSEVELT', '18591027','19010914','19090304','19190106')
INSERT POTUS_A VALUES('27','TAFT', '18570915','19090304','19130304','19300308')
INSERT POTUS_A VALUES('28','WILSON', '18561228','19130304','19210304','19240203')
INSERT POTUS_A VALUES('29','HARDING', '18651102','19210304','19230802','19230802')
INSERT POTUS_A VALUES('30','COOLIDGE', '18720704','19230802','19290304','19330105')
INSERT POTUS_A VALUES('31','HOOVER', '18740810','19290304','19330304','19641020')
INSERT POTUS_A VALUES('32','ROOSEVELT', '18820130','19330304','19450412','19450412')
INSERT POTUS_A VALUES('33','TRUMAN', '18840508','19450412','19530120','19721226')
INSERT POTUS_A VALUES('34','EISENHOWER','18901014','19530120','19610120','19690328')
INSERT POTUS_A VALUES('35','KENNEDY', '19170529','19610120','19631122','19631122')
INSERT POTUS_A VALUES('36','JOHNSON', '19080827','19631122','19690120','19730122')
INSERT POTUS_A VALUES('37','NIXON', '19130109','19690120','19740809','19940422')
INSERT POTUS_A VALUES('38','FORD', '19130714','19740809','19770120',' ')
INSERT POTUS_A VALUES('39','CARTER', '19241001','19770120','19810120',' ')
INSERT POTUS_A VALUES('40','REAGAN', '19110206','19810120','19890120','20040605')
INSERT POTUS_A VALUES('41','BUSH', '19240612','19890120','19930120',' ')
INSERT POTUS_A VALUES('42','CLINTON', '19460819','19930120','20010120',' ')
INSERT POTUS_A VALUES('43','BUSH', '19460706','20010120','20090120',' ')
INSERT POTUS_A VALUES('44','FOWLER', '19570704','20090120','20170120',' ')
GO
SELECT seq, p_name, p_born, p_term_beg, p_term_end, p_died, ' ' as p_age
INTO [Fowler].[dbo].[POTUS_b]
FROM [Fowler].[dbo].[POTUS_a]
GO
UPDATE [Fowler].[dbo].[POTUS_b]
SET p_age =
CASE
WHEN p_died > 0 THEN sum(p_died-p_born)
ELSE NULL
END
GO
SELECT * FROM [Fowler].[dbo].[POTUS_b] GO
Here's my error:
Server: Msg 157, Level 15, State 1, Line 5
An aggregate may not appear in the set list of an UPDATE statement.
Any assistance is appreciated.
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL! |
Edited by - Xerxes on 08/23/2005 13:17:53
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 08/23/2005 : 12:49:26
|
ok... you were missing a few pieces: The 'Into's and the last column in the values list
Is this the idea?
DROP TABLE #POTUS_A
DROP TABLE #POTUS_B
CREATE TABLE #POTUS_A
(
seq int NULL ,
p_name nvarchar (10) NULL,
p_born nvarchar (08) NULL,
p_term_beg nvarchar (08) NULL,
p_term_end nvarchar (08) NULL,
p_died nvarchar (08) NULL,
p_age nvarchar (08) NULL
)
INSERT INTO #POTUS_A VALUES('1','WASHINGTON', '17320222','17890430','17970304','17991214','')
INSERT INTO #POTUS_A VALUES('2','ADAMS', '17351030','17970304','18010304','18260704','')
INSERT INTO #POTUS_A VALUES('3','JEFFERSON', '17430413','18010304','18090304','18260704','')
INSERT INTO #POTUS_A VALUES('4','MADISON', '17510316','18090304','18170304','18360628','')
INSERT INTO #POTUS_A VALUES('5','MONROE', '17580428','18170304','18250304','18310704','')
INSERT INTO #POTUS_A VALUES('6','ADAMS', '17670711','18250304','18290304','18480223','')
INSERT INTO #POTUS_A VALUES('7','JACKSON', '17670315','18290304','18370304','18450608','')
INSERT INTO #POTUS_A VALUES('8','VAN BUREN', '17821205','18370304','18410304','18620724','')
INSERT INTO #POTUS_A VALUES('9','HARRISON', '17730209','18410304','18410404','18410404','')
INSERT INTO #POTUS_A VALUES('10','TYLER', '17900329','18410404','18450304','18620118','')
INSERT INTO #POTUS_A VALUES('11','POLK', '17951102','18450304','18490304','18490615','')
INSERT INTO #POTUS_A VALUES('12','TAYLOR', '17841124','18490304','18500709','18500709','')
INSERT INTO #POTUS_A VALUES('13','FILLMORE', '18000107','18500709','18530304','18740308','')
INSERT INTO #POTUS_A VALUES('14','PIERCE', '18041123','18530304','18570304','18691008','')
INSERT INTO #POTUS_A VALUES('15','BUCHANAN', '17910423','18570304','18610304','18680601','')
INSERT INTO #POTUS_A VALUES('16','LINCOLN', '18090212','18610304','18650415','18650415','')
INSERT INTO #POTUS_A VALUES('17','JOHNSON', '18081229','18650415','18690304','18750731','')
INSERT INTO #POTUS_A VALUES('18','GRANT', '18220427','18690304','18770304','18850723','')
INSERT INTO #POTUS_A VALUES('19','HAYES', '18221004','18770304','18810304','18930117','')
INSERT INTO #POTUS_A VALUES('20','GARFIELD', '18311119','18810304','18810919','18810919','')
INSERT INTO #POTUS_A VALUES('21','ARTHUR', '18291005','18810919','18850304','18861118','')
INSERT INTO #POTUS_A VALUES('22','CLEVELAND', '18370318','18850304','18890304','19080624','')
INSERT INTO #POTUS_A VALUES('23','HARRISON', '18330820','18890304','18930304','19010313','')
INSERT INTO #POTUS_A VALUES('24','CLEVELAND', '18370318','18930304','18970304','19080624','')
INSERT INTO #POTUS_A VALUES('25','MCKINLEY', '18430129','18970304','19010914','19010914','')
INSERT INTO #POTUS_A VALUES('26','ROOSEVELT', '18591027','19010914','19090304','19190106','')
INSERT INTO #POTUS_A VALUES('27','TAFT', '18570915','19090304','19130304','19300308','')
INSERT INTO #POTUS_A VALUES('28','WILSON', '18561228','19130304','19210304','19240203','')
INSERT INTO #POTUS_A VALUES('29','HARDING', '18651102','19210304','19230802','19230802','')
INSERT INTO #POTUS_A VALUES('30','COOLIDGE', '18720704','19230802','19290304','19330105','')
INSERT INTO #POTUS_A VALUES('31','HOOVER', '18740810','19290304','19330304','19641020','')
INSERT INTO #POTUS_A VALUES('32','ROOSEVELT', '18820130','19330304','19450412','19450412','')
INSERT INTO #POTUS_A VALUES('33','TRUMAN', '18840508','19450412','19530120','19721226','')
INSERT INTO #POTUS_A VALUES('34','EISENHOWER','18901014','19530120','19610120','19690328','')
INSERT INTO #POTUS_A VALUES('35','KENNEDY', '19170529','19610120','19631122','19631122','')
INSERT INTO #POTUS_A VALUES('36','JOHNSON', '19080827','19631122','19690120','19730122','')
INSERT INTO #POTUS_A VALUES('37','NIXON', '19130109','19690120','19740809','19940422','')
INSERT INTO #POTUS_A VALUES('38','FORD', '19130714','19740809','19770120',' ','')
INSERT INTO #POTUS_A VALUES('39','CARTER', '19241001','19770120','19810120',' ','')
INSERT INTO #POTUS_A VALUES('40','REAGAN', '19110206','19810120','19890120','20040605','')
INSERT INTO #POTUS_A VALUES('41','BUSH', '19240612','19890120','19930120',' ','')
INSERT INTO #POTUS_A VALUES('42','CLINTON', '19460819','19930120','20010120',' ','')
INSERT INTO #POTUS_A VALUES('43','BUSH', '19460706','20010120','20090120',' ','')
INSERT INTO #POTUS_A VALUES('44','FOWLER', '19570704','20090120','20170120',' ','')
SELECT seq, p_name, p_born, p_term_beg, p_term_end, p_died, 0 as p_age
INTO #POTUS_B
FROM #POTUS_A
UPDATE #POTUS_B
SET p_age =
CASE
WHEN p_died <> ' ' THEN isnull(convert(varchar,(convert(int,p_died) - convert(int,p_born))/10000),'')
ELSE ''
END
SELECT * FROM #POTUS_B
Corey
Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."  |
 |
|
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
Posted - 08/23/2005 : 13:01:07
|
Corey,
Thanks! OK, my head is spinning from reading the isnull(convert(varchar,(convert(int,p_died) - convert(int,p_born))/10000),''.
But I only got years....I need to know how many months and days they lived, too!
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL! |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 08/23/2005 : 13:25:33
|
does this work for you?
select p_born, p_died,
year(p_died1) - year(p_born1) as years,
abs(month(p_died1) - month(p_born1)) as months,
abs(day(p_died1) - day(p_born1)) as days
from (
SELECT p_born, p_died,
convert(datetime, convert(varchar(4), left(p_died, 4)+1000) + right(p_died, 4)) as p_died1,
convert(datetime, convert(varchar(4), left(p_born, 4)+1000) + right(p_born, 4)) as p_born1
FROM #POTUS_B
where p_died <> ' '
) t
Go with the flow & have fun! Else fight the flow |
|
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 08/23/2005 : 13:45:56
|
oh... danggit!
how bout this (Age: yymmdd)
DROP TABLE #POTUS_A
DROP TABLE #POTUS_B
CREATE TABLE #POTUS_A
(
seq int NULL ,
p_name nvarchar (10) NULL,
p_born nvarchar (08) NULL,
p_term_beg nvarchar (08) NULL,
p_term_end nvarchar (08) NULL,
p_died nvarchar (08) NULL,
p_age nvarchar (08) NULL
)
INSERT INTO #POTUS_A VALUES('1','WASHINGTON', '17320222','17890430','17970304','17991214','')
INSERT INTO #POTUS_A VALUES('2','ADAMS', '17351030','17970304','18010304','18260704','')
INSERT INTO #POTUS_A VALUES('3','JEFFERSON', '17430413','18010304','18090304','18260704','')
INSERT INTO #POTUS_A VALUES('4','MADISON', '17510316','18090304','18170304','18360628','')
INSERT INTO #POTUS_A VALUES('5','MONROE', '17580428','18170304','18250304','18310704','')
INSERT INTO #POTUS_A VALUES('6','ADAMS', '17670711','18250304','18290304','18480223','')
INSERT INTO #POTUS_A VALUES('7','JACKSON', '17670315','18290304','18370304','18450608','')
INSERT INTO #POTUS_A VALUES('8','VAN BUREN', '17821205','18370304','18410304','18620724','')
INSERT INTO #POTUS_A VALUES('9','HARRISON', '17730209','18410304','18410404','18410404','')
INSERT INTO #POTUS_A VALUES('10','TYLER', '17900329','18410404','18450304','18620118','')
INSERT INTO #POTUS_A VALUES('11','POLK', '17951102','18450304','18490304','18490615','')
INSERT INTO #POTUS_A VALUES('12','TAYLOR', '17841124','18490304','18500709','18500709','')
INSERT INTO #POTUS_A VALUES('13','FILLMORE', '18000107','18500709','18530304','18740308','')
INSERT INTO #POTUS_A VALUES('14','PIERCE', '18041123','18530304','18570304','18691008','')
INSERT INTO #POTUS_A VALUES('15','BUCHANAN', '17910423','18570304','18610304','18680601','')
INSERT INTO #POTUS_A VALUES('16','LINCOLN', '18090212','18610304','18650415','18650415','')
INSERT INTO #POTUS_A VALUES('17','JOHNSON', '18081229','18650415','18690304','18750731','')
INSERT INTO #POTUS_A VALUES('18','GRANT', '18220427','18690304','18770304','18850723','')
INSERT INTO #POTUS_A VALUES('19','HAYES', '18221004','18770304','18810304','18930117','')
INSERT INTO #POTUS_A VALUES('20','GARFIELD', '18311119','18810304','18810919','18810919','')
INSERT INTO #POTUS_A VALUES('21','ARTHUR', '18291005','18810919','18850304','18861118','')
INSERT INTO #POTUS_A VALUES('22','CLEVELAND', '18370318','18850304','18890304','19080624','')
INSERT INTO #POTUS_A VALUES('23','HARRISON', '18330820','18890304','18930304','19010313','')
INSERT INTO #POTUS_A VALUES('24','CLEVELAND', '18370318','18930304','18970304','19080624','')
INSERT INTO #POTUS_A VALUES('25','MCKINLEY', '18430129','18970304','19010914','19010914','')
INSERT INTO #POTUS_A VALUES('26','ROOSEVELT', '18591027','19010914','19090304','19190106','')
INSERT INTO #POTUS_A VALUES('27','TAFT', '18570915','19090304','19130304','19300308','')
INSERT INTO #POTUS_A VALUES('28','WILSON', '18561228','19130304','19210304','19240203','')
INSERT INTO #POTUS_A VALUES('29','HARDING', '18651102','19210304','19230802','19230802','')
INSERT INTO #POTUS_A VALUES('30','COOLIDGE', '18720704','19230802','19290304','19330105','')
INSERT INTO #POTUS_A VALUES('31','HOOVER', '18740810','19290304','19330304','19641020','')
INSERT INTO #POTUS_A VALUES('32','ROOSEVELT', '18820130','19330304','19450412','19450412','')
INSERT INTO #POTUS_A VALUES('33','TRUMAN', '18840508','19450412','19530120','19721226','')
INSERT INTO #POTUS_A VALUES('34','EISENHOWER','18901014','19530120','19610120','19690328','')
INSERT INTO #POTUS_A VALUES('35','KENNEDY', '19170529','19610120','19631122','19631122','')
INSERT INTO #POTUS_A VALUES('36','JOHNSON', '19080827','19631122','19690120','19730122','')
INSERT INTO #POTUS_A VALUES('37','NIXON', '19130109','19690120','19740809','19940422','')
INSERT INTO #POTUS_A VALUES('38','FORD', '19130714','19740809','19770120',' ','')
INSERT INTO #POTUS_A VALUES('39','CARTER', '19241001','19770120','19810120',' ','')
INSERT INTO #POTUS_A VALUES('40','REAGAN', '19110206','19810120','19890120','20040605','')
INSERT INTO #POTUS_A VALUES('41','BUSH', '19240612','19890120','19930120',' ','')
INSERT INTO #POTUS_A VALUES('42','CLINTON', '19460819','19930120','20010120',' ','')
INSERT INTO #POTUS_A VALUES('43','BUSH', '19460706','20010120','20090120',' ','')
INSERT INTO #POTUS_A VALUES('44','FOWLER', '19570704','20090120','20170120',' ','')
SELECT seq, p_name, p_born, p_term_beg, p_term_end, p_died, 0 as p_age
INTO #POTUS_B
FROM #POTUS_A
UPDATE #POTUS_B
SET p_age =
CASE
WHEN p_died <> ' ' THEN
isnull(convert(varchar,(convert(int,p_died) - convert(int,p_born))/10000),'') +
right('00'+isnull(convert(varchar,((convert(int,p_died) - convert(int,p_born))/100%100+12)%100%12),''),2) +
case when ((convert(int,p_died)/100%100+12)%100%12)-1 in (0,2,4,6,7,9,11) then
right('00'+isnull(convert(varchar,(convert(int,p_died)%100 - convert(int,p_born%100)+31)%31),''),2)
when ((convert(int,p_died)/100%100+12)%100%12)-1 in (1) then
right('00'+isnull(convert(varchar,(convert(int,p_died)%100 - convert(int,p_born%100)+28)%28),''),2)
else right('00'+isnull(convert(varchar,(convert(int,p_died)%100 - convert(int,p_born%100)+30)%30),''),2) end
ELSE ''
END
SELECT * FROM #POTUS_B
Corey
Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now." |
|
|
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
Posted - 08/23/2005 : 14:34:05
|
quote:
Originally posted by spirit1
does this work for you?
select p_born, p_died,
year(p_died1) - year(p_born1) as years,
abs(month(p_died1) - month(p_born1)) as months,
abs(day(p_died1) - day(p_born1)) as days
from (
SELECT p_born, p_died,
convert(datetime, convert(varchar(4), left(p_died, 4)+1000) + right(p_died, 4)) as p_died1,
convert(datetime, convert(varchar(4), left(p_born, 4)+1000) + right(p_born, 4)) as p_born1
FROM #POTUS_B
where p_died <> ' '
) t
Go with the flow & have fun! Else fight the flow
Mladen....what's with the 't' at the end?
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL! |
|
|
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
Posted - 08/23/2005 : 14:36:38
|
quote:
Originally posted by Seventhnight
oh... danggit!
how bout this (Age: yymmdd)
DROP TABLE #POTUS_A
DROP TABLE #POTUS_B
CREATE TABLE #POTUS_A
(
seq int NULL ,
p_name nvarchar (10) NULL,
p_born nvarchar (08) NULL,
p_term_beg nvarchar (08) NULL,
p_term_end nvarchar (08) NULL,
p_died nvarchar (08) NULL,
p_age nvarchar (08) NULL
)
INSERT INTO #POTUS_A VALUES('1','WASHINGTON', '17320222','17890430','17970304','17991214','')
INSERT INTO #POTUS_A VALUES('2','ADAMS', '17351030','17970304','18010304','18260704','')
INSERT INTO #POTUS_A VALUES('3','JEFFERSON', '17430413','18010304','18090304','18260704','')
INSERT INTO #POTUS_A VALUES('4','MADISON', '17510316','18090304','18170304','18360628','')
INSERT INTO #POTUS_A VALUES('5','MONROE', '17580428','18170304','18250304','18310704','')
INSERT INTO #POTUS_A VALUES('6','ADAMS', '17670711','18250304','18290304','18480223','')
INSERT INTO #POTUS_A VALUES('7','JACKSON', '17670315','18290304','18370304','18450608','')
INSERT INTO #POTUS_A VALUES('8','VAN BUREN', '17821205','18370304','18410304','18620724','')
INSERT INTO #POTUS_A VALUES('9','HARRISON', '17730209','18410304','18410404','18410404','')
INSERT INTO #POTUS_A VALUES('10','TYLER', '17900329','18410404','18450304','18620118','')
INSERT INTO #POTUS_A VALUES('11','POLK', '17951102','18450304','18490304','18490615','')
INSERT INTO #POTUS_A VALUES('12','TAYLOR', '17841124','18490304','18500709','18500709','')
INSERT INTO #POTUS_A VALUES('13','FILLMORE', '18000107','18500709','18530304','18740308','')
INSERT INTO #POTUS_A VALUES('14','PIERCE', '18041123','18530304','18570304','18691008','')
INSERT INTO #POTUS_A VALUES('15','BUCHANAN', '17910423','18570304','18610304','18680601','')
INSERT INTO #POTUS_A VALUES('16','LINCOLN', '18090212','18610304','18650415','18650415','')
INSERT INTO #POTUS_A VALUES('17','JOHNSON', '18081229','18650415','18690304','18750731','')
INSERT INTO #POTUS_A VALUES('18','GRANT', '18220427','18690304','18770304','18850723','')
INSERT INTO #POTUS_A VALUES('19','HAYES', '18221004','18770304','18810304','18930117','')
INSERT INTO #POTUS_A VALUES('20','GARFIELD', '18311119','18810304','18810919','18810919','')
INSERT INTO #POTUS_A VALUES('21','ARTHUR', '18291005','18810919','18850304','18861118','')
INSERT INTO #POTUS_A VALUES('22','CLEVELAND', '18370318','18850304','18890304','19080624','')
INSERT INTO #POTUS_A VALUES('23','HARRISON', '18330820','18890304','18930304','19010313','')
INSERT INTO #POTUS_A VALUES('24','CLEVELAND', '18370318','18930304','18970304','19080624','')
INSERT INTO #POTUS_A VALUES('25','MCKINLEY', '18430129','18970304','19010914','19010914','')
INSERT INTO #POTUS_A VALUES('26','ROOSEVELT', '18591027','19010914','19090304','19190106','')
INSERT INTO #POTUS_A VALUES('27','TAFT', '18570915','19090304','19130304','19300308','')
INSERT INTO #POTUS_A VALUES('28','WILSON', '18561228','19130304','19210304','19240203','')
INSERT INTO #POTUS_A VALUES('29','HARDING', '18651102','19210304','19230802','19230802','')
INSERT INTO #POTUS_A VALUES('30','COOLIDGE', '18720704','19230802','19290304','19330105','')
INSERT INTO #POTUS_A VALUES('31','HOOVER', '18740810','19290304','19330304','19641020','')
INSERT INTO #POTUS_A VALUES('32','ROOSEVELT', '18820130','19330304','19450412','19450412','')
INSERT INTO #POTUS_A VALUES('33','TRUMAN', '18840508','19450412','19530120','19721226','')
INSERT INTO #POTUS_A VALUES('34','EISENHOWER','18901014','19530120','19610120','19690328','')
INSERT INTO #POTUS_A VALUES('35','KENNEDY', '19170529','19610120','19631122','19631122','')
INSERT INTO #POTUS_A VALUES('36','JOHNSON', '19080827','19631122','19690120','19730122','')
INSERT INTO #POTUS_A VALUES('37','NIXON', '19130109','19690120','19740809','19940422','')
INSERT INTO #POTUS_A VALUES('38','FORD', '19130714','19740809','19770120',' ','')
INSERT INTO #POTUS_A VALUES('39','CARTER', '19241001','19770120','19810120',' ','')
INSERT INTO #POTUS_A VALUES('40','REAGAN', '19110206','19810120','19890120','20040605','')
INSERT INTO #POTUS_A VALUES('41','BUSH', '19240612','19890120','19930120',' ','')
INSERT INTO #POTUS_A VALUES('42','CLINTON', '19460819','19930120','20010120',' ','')
INSERT INTO #POTUS_A VALUES('43','BUSH', '19460706','20010120','20090120',' ','')
INSERT INTO #POTUS_A VALUES('44','FOWLER', '19570704','20090120','20170120',' ','')
SELECT seq, p_name, p_born, p_term_beg, p_term_end, p_died, 0 as p_age
INTO #POTUS_B
FROM #POTUS_A
UPDATE #POTUS_B
SET p_age =
CASE
WHEN p_died <> ' ' THEN
isnull(convert(varchar,(convert(int,p_died) - convert(int,p_born))/10000),'') +
right('00'+isnull(convert(varchar,((convert(int,p_died) - convert(int,p_born))/100%100+12)%100%12),''),2) +
case when ((convert(int,p_died)/100%100+12)%100%12)-1 in (0,2,4,6,7,9,11) then
right('00'+isnull(convert(varchar,(convert(int,p_died)%100 - convert(int,p_born%100)+31)%31),''),2)
when ((convert(int,p_died)/100%100+12)%100%12)-1 in (1) then
right('00'+isnull(convert(varchar,(convert(int,p_died)%100 - convert(int,p_born%100)+28)%28),''),2)
else right('00'+isnull(convert(varchar,(convert(int,p_died)%100 - convert(int,p_born%100)+30)%30),''),2) end
ELSE ''
END
SELECT * FROM #POTUS_B
Corey
Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."
Corey... thanks a bunch!
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL! |
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 08/23/2005 : 14:43:14
|
quote:
Originally posted by Xerxes
Mladen....what's with the 't' at the end?
He's just aliasing the subquery in the FROM statement. A handy habit to get into because in almost every other situation, it is required.
---------------------------
EmeraldCityDomains.com |
|
|
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
Posted - 08/23/2005 : 15:22:46
|
quote:
Originally posted by AjarnMark
quote:
Originally posted by Xerxes
Mladen....what's with the 't' at the end?
He's just aliasing the subquery in the FROM statement. A handy habit to get into because in almost every other situation, it is required.
---------------------------
EmeraldCityDomains.com
Aliasing?
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL! |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 08/23/2005 : 15:36:46
|
select col1 as someOtherName
from Table1
someOtherName is an alias of the column.
same goes for subqueries.
Go with the flow & have fun! Else fight the flow |
|
|
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
Posted - 08/23/2005 : 16:09:42
|
That's what the 't' is for? How does the 't' augment that?
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL! |
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 08/23/2005 : 16:23:47
|
The t is an alias for the subquery taking the place of a table. It's like doing
SELECT *
FROM authors as a
JOIN titleauthors as ta ON a.au_id = ta.au_id
JOIN titles t ON au.title_id = t.title_ID -- NOTE that the AS keyword is optional.
In his example, he never has to use the alias anywhere else in the code, but like I said, it's a good habit to get into when you are making a table out of a subquery because most of the time you HAVE TO have an alias for the subquery as table to be usable.
---------------------------
EmeraldCityDomains.com |
Edited by - AjarnMark on 08/23/2005 16:25:16 |
|
|
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
Posted - 08/23/2005 : 17:24:21
|
Uh, I think we're talking about fish and bicycles here:
I'm referring to this:
select p_born, p_died,
year(p_died1) - year(p_born1) as years,
abs(month(p_died1) - month(p_born1)) as months,
abs(day(p_died1) - day(p_born1)) as days
from (
SELECT p_born, p_died,
convert(datetime, convert(varchar(4), left(p_died, 4)+1000) + right(p_died, 4)) as p_died1,
convert(datetime, convert(varchar(4), left(p_born, 4)+1000) + right(p_born, 4)) as p_born1
FROM #POTUS_B
where p_died <> ' '
) t <<== this t doesn't represent a specific table
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL! |
Edited by - Xerxes on 08/23/2005 18:40:25 |
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 08/23/2005 : 18:42:57
|
| No, but it does represent the subquery immediately before the T. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 08/24/2005 : 01:17:18
|
>>) t <<== this t doesn't represent a specific table
There is no need that it should represent a specific table that was created already.
A query or subquery (even with many select statements with Union All) can be considered as table by giving alias name
Madhivanan
Failing to plan is Planning to fail |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 08/24/2005 : 05:12:17
|
well i'm surprised that this works for anyone without the alias.
if i don't alias it i get the error
Incorrect syntax near ')'.
Go with the flow & have fun! Else fight the flow |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 08/24/2005 : 05:16:59
|
No doubt
Without alias it wont work 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
Posted - 08/24/2005 : 11:21:05
|
quote:
Originally posted by robvolk
No, but it does represent the subquery immediately before the T.
So.....I could use any character, not just a 't'?
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL! |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 08/24/2005 : 11:34:27
|
gnrsgrsilgshghiesl is as good as t
Go with the flow & have fun! Else fight the flow |
|
|
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
Posted - 08/24/2005 : 14:14:36
|
Is gnrsgrsilgshghiesl some Slavic word for 'whatever'? And just how is that pronounced?
I get tongue-tied right after the 3rd 'g'.
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL! |
Edited by - Xerxes on 08/24/2005 14:16:53 |
|
|
|
Xerxes
Aged Yak Warrior
USA
665 Posts |
Posted - 08/24/2005 : 17:45:22
|
Let's set this on FIRE!
Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL! |
|
|
|
Topic |
|
|
|
Aggregation Aggravation
