Aggregation Aggravation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Aggregation Aggravation

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-23 : 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!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-23 : 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

666 Posts

Posted - 2005-08-23 : 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

11752 Posts

Posted - 2005-08-23 : 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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-23 : 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

666 Posts

Posted - 2005-08-23 : 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

666 Posts

Posted - 2005-08-23 : 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

3246 Posts

Posted - 2005-08-23 : 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

666 Posts

Posted - 2005-08-23 : 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

11752 Posts

Posted - 2005-08-23 : 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

666 Posts

Posted - 2005-08-23 : 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

3246 Posts

Posted - 2005-08-23 : 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

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-23 : 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!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-08-23 : 18:42:57

No, but it does represent the subquery immediately before the T.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 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

11752 Posts

Posted - 2005-08-24 : 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

22864 Posts

Posted - 2005-08-24 : 05:16:59

No doubt
Without alias it wont work

Madhivanan

Failing to plan is Planning to fail

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-24 : 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

11752 Posts

Posted - 2005-08-24 : 11:34:27

gnrsgrsilgshghiesl is as good as t

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-24 : 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!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-24 : 17:45:22

Let's set this on FIRE!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources