SQL Query

[IndyGuy]

I am a novice and need some help. What I want to accomplish is this...

If I have 3 people coming into my shop on the same day and all three need a rental car, but I only have one rental car so I need to tell if the car booking is overlapped.

I was trying something like this, not sure if this is the best way to go.


declare @FromDate as datetime = '05-01-2012',
@ToDate as datetime = '05-31-2012'

select (select COUNT(c1.[FixDate]) from core c1) as 'NbrofCars',
c.[Program Date] as 'Prog Date',
convert(varchar, c.[Program Time], 108) as 'FixTime'
FROM Event e
INNER JOIN core c ON c.EventId = e.EventId
where convert(varchar, c.StartDateTime, 101) between @FromDate and @ToDate

 

[wbodger]

Can you give us your data structure with some sample data and your expected outcome?

wb

 

[IndyGuy]

If I were expecting 3 cars on March 12th, and 2 on March 15th. If there was only one per day I don't need to see that. I would expect the results to look something like this.

NbrofCars ProgDate FixTime
3 3-12-12 9:00
3 3-12-12 10:00
3 3-12-12 11:00
2 3-15-12 8:00
2 3-15-12 12:00

 

[wbodger]

But that doesn't really tell me what your data source looks like. Is there more than one table? What are the fields in the table(s) you are using?