Selection of Records - date data. DATEDIFF

SELECT show_name, show_address FROM show -----------------------------------------------

show_name show_address Dubbo 23 Wingewarra St, Dubbo Young 13 Cherry Lane, Young Castle Hill Showground Rd, Castle Hill Royal Easter PO Box 13, GPO Sydney Dubbo 17 Fitzroy St, Dubbo

Hi I'm trying to select Dubbo at 'Wingewarra St' and Castel Hill and list the number of days between them

Dubbo at 'Wingewarra St' was held on 05/ July / 1995 and Castle Hill was held on 04/ May/ 1996 and the result should be 304 days

and here is the show table:

SELECT *

FROM show --------------------------------------------------------- show_name show_held show_address Dubbo 1995-07-05 23 Wingewarra St, Dubbo Young 1995-09-13 13 Cherry Lane, Young Castle Hill 1996-05-04 Showground Rd, Castle Hill Royal Easter 1996-04-21 PO Box 13, GPO Sydney Dubbo 1996-07-01 17 Fitzroy St, Dubbo


and Here is the code that I wrote:

SELECT		DATENAME (DAY, show_held.CastleHill) - DATENAME (DAY, show_held.Dubbo) AS number_of_days_between_show
FROM		show
WHERE		show.show_name = 'Dubbo' AND show.show_address LIKE 'Wingewarra St'

-----------------------------------------------------
Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "show_held.CastleHill" could not be bound.
Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "show_held.Dubbo" could not be bound.

I have no idea how to only select the day for Castle Hill and subtract the day of Dubbo that was held at Wingewarra St

Please help thank you



  • Edited by H.Kim 4 hours 8 minutes ago
September 5th, 2015 12:18am

Try this:

SELECT		DATEDIFF(day,min(show_held),max(show_held)) AS number_of_days_between_show
FROM		show
WHERE		(show_name = 'Dubbo' and show_address like '23  Wingewarra St%') or show_name = 'Castle Hill' 

Free Windows Admin Tool Kit Click here and download it now
September 5th, 2015 9:24am

The result was '0'

I guess the show_names, Dubbo and Castle Hill are not assigned correctly

why does your code give me 0?

and how do I select Dubbo that is held at 'Wingewarra' and Castle Hill from show_name

thank you


  • Edited by H.Kim 4 hours 3 minutes ago
September 5th, 2015 7:32pm

Try

SELECT DATEDIFF(day,sh.show_held),sh1.show_held) AS number_of_days_between_shows,

sh.show_name, sh.show_address, sh1.show_name as secondShow, sh1.show_address as SecondShowAddress FROM show sh, show sh1 WHERE (sh.show_name = 'Dubbo' and sh.show_address like '23 Wingewarra St%')

and sh1.show_name = 'Castle Hill'


Free Windows Admin Tool Kit Click here and download it now
September 5th, 2015 11:44pm

The result was '0'

I guess the show_names, Dubbo and Castle Hill are not assigned correctly

why does your code give me 0?

and how do I select Dubbo that is held at 'Wingewarra' and Castle Hill from show_name

thank you



declare @show table (show_name nvarchar(20),     show_held  date,   show_address nvarchar(50))

insert into @show values
('Dubbo'	   ,   '1995-07-05' ,   '23  Wingewarra St, Dubbo'),
('Young'	   ,   '1995-09-13' ,   '13  Cherry Lane, Young'),
('Castle Hill' ,  '1996-05-04'  , 'Showground Rd, Castle Hill'),
('Royal Easter',  '1996-04-21'  ,  'PO Box 13, GPO Sydney'),
('Dubbo'	   ,   '1996-07-01' ,   '17 Fitzroy St, Dubbo')


SELECT		DATEDIFF(day,min(show_held),max(show_held)) AS number_of_days_between_show
FROM		@show
WHERE		(show_name = 'Dubbo' and show_address like '23  Wingewarra St%') or show_name = 'Castle Hill' 
It returns 304.
September 6th, 2015 12:13am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics