You must Sign In to post a response.
  • Category: SQL Server

    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
  • #769417

    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 fees
    FROM @car
    GROUP 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


  • Sign In to post your comments