开发者

How to show all days regardless of activity

开发者 https://www.devze.com 2023-01-17 22:15 出处:网络
I have two tables - quotes and bookings I am selecting \"quote date\" to show the day of the week but if a booking is made on a day without a quote, then there is no \"quote date\" and therefore this

I have two tables - quotes and bookings

I am selecting "quote date" to show the day of the week but if a booking is made on a day without a quote, then there is no "quote date" and therefore this row is never displayed.

How can I created a column that is just every day for the past 30,60 or 90 days then shows the number of quotes f开发者_如何学JAVAor that given day and the number of bookings?

Current SQL Using QuoteDate:

SELECT 
  cast(q.created as date) AS QuoteDate,
  count(q.id) AS Quotes,
  count(b.quote_id) AS Bookings
FROM quotes q 
LEFT OUTER JOIN bookings b ON b.quote_id=q.id
WHERE year(q.created)='2010'
AND month(q.created)='09'
GROUP BY cast(q.created as date)
ORDER BY cast(q.created as date) DESC

These results will not display bookings on days where no quotes were made...

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号