How Write this query

Posted by Cpatil1000 under Sql Server on 11/1/2017 | Points: 10 | Views : 1247 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi,

I want do solve this query. in this query our staff is working for car parking
office time is 8.00 am to till next day 7.59 am. So our manager want to
check how many cars are parking this time with start date to end data

parking time is 8.00 am to till next day 7.59 am (24 hours is taking one day)
and start date with 01/10/2017 - 31/10/2017

e.g. 01/10/2017 - 8.00 am to till next day 7.59 am within next
day 02/10/2017( it is calculating one day for 01/10/2017)

02/10/2017 - 8.00 am to till next day 7.59 am within
next day 03/10/2017( it is calculating one day for 02/10/2017)

Data :
=====

date time car no parcking fees
01/10/2017 8.00 am mh-0412-22 25
01/10/2017 8.10 am mh-0312-40 40

02/10/2017 8.00 am mh-0412-22 25
02/10/2017 8.10 am mh-0312-40 40

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Ajay2707 on: 11/16/2017 [Member] Starter | Points: 25

0	Hi Cpatil, While working with datetime, when you compare the value must have time value, other wise sqlserver ignore the time and give compare based on only dates. This is the solution of your query : DECLARE @T TABLE (id int identity(1,1), CarParkingStartTime datetime, CarParkingEndTime datetime , CarNo varchar(50)) Insert into @T values ( '01/09/2017 14:10:00', '01/10/2017 2:10:00', '1' ), ( '01/10/2017 8:10:00', '02/10/2017 6:10:00', '2' ), ( '02/10/2017 14:10:00', '02/10/2017 20:10:00', '1' ), ( '02/10/2017 8:10:00', '03/10/2017 6:10:00', '2' ), ( '02/10/2017 23:10:00', '03/10/2017 2:10:00', '1' ), ( '01/10/2017 18:10:00', '02/10/2017 10:10:00', '3' ), ( '03/10/2017 14:10:00', '04/10/2017 20:10:00', '1' ) select * from @t order by carno, CarParkingStartTime DECLARE @Date datetime = '02/10/2017' , @datestart datetime , @dateend datetime /The below query give wrong answer/ --select * from @t where @date between CarParkingStartTime and CarParkingEndTime order by carno, CarParkingStartTime Select @datestart = cast (@date + ' 08:00:000' as datetime), @dateend = cast ( dateadd(d, 1, @date ) + ' 07:59:000' as datetime) select @datestart , @dateend, * from @t where (CarParkingStartTime between @datestart and @dateend )or (CarParkingEndTime between @datestart and @dateend) order by carno, CarParkingStartTime Cpatil1000, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Cpatil,

While working with datetime, when you compare the value must have time value, other wise sqlserver ignore the time and give compare based on only dates.

This is the solution of your query :
DECLARE @T TABLE (id int identity(1,1), CarParkingStartTime datetime, CarParkingEndTime datetime , CarNo varchar(50))

Insert into @T values
( '01/09/2017 14:10:00', '01/10/2017 2:10:00', '1' ),
( '01/10/2017 8:10:00', '02/10/2017 6:10:00', '2' ),
( '02/10/2017 14:10:00', '02/10/2017 20:10:00', '1' ),
( '02/10/2017 8:10:00', '03/10/2017 6:10:00', '2' ),
( '02/10/2017 23:10:00', '03/10/2017 2:10:00', '1' ),
( '01/10/2017 18:10:00', '02/10/2017 10:10:00', '3' ),
( '03/10/2017 14:10:00', '04/10/2017 20:10:00', '1' )

select * from @t order by carno, CarParkingStartTime

DECLARE @Date datetime = '02/10/2017' , @datestart datetime , @dateend datetime

/*The below query give wrong answer*/
--select * from @t where @date between CarParkingStartTime and CarParkingEndTime order by carno, CarParkingStartTime

Select @datestart = cast (@date + ' 08:00:000' as datetime), @dateend = cast ( dateadd(d, 1, @date ) + ' 07:59:000' as datetime)

select @datestart , @dateend, *
from
@t
where
(CarParkingStartTime between @datestart and @dateend )or
(CarParkingEndTime between @datestart and @dateend)
order by
carno, CarParkingStartTime

Cpatil1000, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts