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 Sunday, September 06, 2015 2:56 AM
September 5th, 2015 12:18am

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 Sunday, September 06, 2015 3:01 AM
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2015 11:31pm

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'

September 6th, 2015 3:43am

I think there is a typo in this line: DATEDIFF(day,sh.show_held),sh1.show_held)

and the result didn't show anything..

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

thank you! this did work but why do I have to declare a show table and insert into show values when I already have a show table?
September 7th, 2015 9:35am

thank you! this did work but why do I have to declare a show table and insert into show values when I already have a show table?

Don't declare anything.

Just replace @show by show as follows:

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 7th, 2015 9:44am

You need to use your own table. If both my and Bermi queries don't return results in means the show name is not Dubbo or show_address is something different. Please re-check your data.
September 7th, 2015 2:18pm

the result is still 0 without this:

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')

 and I still don't know why. it only works with the code above

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 1:35am

Bermi's query did work but only when I declare @show table and so on but without @show table, it doesn't work

and I've tried to figure out what is wrong in your query but I still don't understand it


  • Edited by H.Kim 1 hour 20 minutes ago
September 8th, 2015 1:39am

Bermi's query did work but only when I declare @show table and so on but without @show table, it doesn't work

and I've tried to figure out what is wrong in your query but I still don't understand it


Could you post the result of the below query?

SELECT		*
FROM		show
WHERE		(show_name = 'Dubbo' and show_address like '23  Wingewarra St%') or show_name = 'Castle Hill' 
I suspect that the show_held dates are the same.

Free Windows Admin Tool Kit Click here and download it now
September 8th, 2015 3:15am

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

Other recent topics Other recent topics