You must Sign In to post a response.
• # sql to check the cars parked between starttime and endtime

I want to solve this query. Our staff is working for car parking office time is 8.00 am to till next day 7.59 am and our manager want to check how many cars are parked between start date and 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

• Hi,

Use the below SQL Script to get the how many cars are parked in a day

`DECLARE @car TABLE (pdate char(20), carno char(10), fees INT)INSERT INTO @car(pdate,carno,fees)VALUES('01/13/2017 8:00AM', 'mh-0412-22', 25)INSERT INTO @car(pdate,carno,fees)VALUES('01/13/2017 8:10AM', 'mh-0312-40', 25)INSERT INTO @car(pdate,carno,fees)VALUES('01/14/2017 7:58AM', 'mh-0323-40', 25)INSERT INTO @car(pdate,carno,fees)VALUES('01/14/2017 8:00AM', 'mh-0412-22', 25)INSERT INTO @car(pdate,carno,fees)VALUES('01/14/2017 8:10AM', 'mh-0312-40', 25)SELECT CASE WHEN cast(pdate AS datetime)>= DATEADD(HOUR,8,CONVERT(VARCHAR(10), cast(pdate AS datetime),110)) THEN cast(pdate AS date) WHEN cast(pdate AS datetime)<=DATEADD(MINUTE,-1,DATEADD(HOUR,8,CONVERT(VARCHAR(10), cast(pdate AS datetime)+1,110))) THEN DATEADD(DAY,-1,cast(pdate AS date)) END AS [Date], count(Carno) TotalCars, sum(fees) AS feesFROM @carGROUP BY CASE WHEN cast(pdate AS datetime)>= DATEADD(HOUR,8,CONVERT(VARCHAR(10), cast(pdate AS datetime),110)) THEN cast(pdate AS date) WHEN cast(pdate AS datetime)<=DATEADD(MINUTE,-1,DATEADD(HOUR,8,CONVERT(VARCHAR(10), cast(pdate AS datetime)+1,110))) THEN DATEADD(DAY,-1,cast(pdate AS date)) END`

Please mark this as Answer, if this helps

Regards
Siva