SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Aggregation Aggravation
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 08/23/2005 :  12:28:44  Show Profile  Reply with Quote
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  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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."
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 08/23/2005 :  13:01:07  Show Profile  Reply with Quote
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!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/23/2005 :  13:25:33  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/23/2005 :  13:45:56  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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."
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 08/23/2005 :  14:34:05  Show Profile  Reply with Quote
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!
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 08/23/2005 :  14:36:38  Show Profile  Reply with Quote
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!
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 08/23/2005 :  14:43:14  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 08/23/2005 :  15:22:46  Show Profile  Reply with Quote
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!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/23/2005 :  15:36:46  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 08/23/2005 :  16:09:42  Show Profile  Reply with Quote
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!
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 08/23/2005 :  16:23:47  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 08/23/2005 :  17:24:21  Show Profile  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15663 Posts

Posted - 08/23/2005 :  18:42:57  Show Profile  Visit robvolk's Homepage  Reply with Quote
No, but it does represent the subquery immediately before the T.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/24/2005 :  01:17:18  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>) 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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/24/2005 :  05:12:17  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/24/2005 :  05:16:59  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
No doubt
Without alias it wont work

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 08/24/2005 :  11:21:05  Show Profile  Reply with Quote
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!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/24/2005 :  11:34:27  Show Profile  Visit spirit1's Homepage  Reply with Quote
gnrsgrsilgshghiesl is as good as t

Go with the flow & have fun! Else fight the flow
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 08/24/2005 :  14:14:36  Show Profile  Reply with Quote
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
Go to Top of Page

Xerxes
Aged Yak Warrior

USA
665 Posts

Posted - 08/24/2005 :  17:45:22  Show Profile  Reply with Quote
Let's set this on FIRE!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000