0

I am trying to count offer redemptions for every day over the last 30 days. I want to return a zero if there are no redeemed offers on that particular date for that offer type.

SELECT
    a.offertype,
    CONVERT(date,b.[date]) ,
    COUNT( a.offertype )
FROM [dbo].[Last30days] b
LEFT JOIN [dbo].[Offers] a ON CONVERT(date,b.[date]) = CONVERT(date,a.[RedeemedDate])
LEFT JOIN [dbo].[UniqueOffers] c ON a.offertype = c.OfferType
WHERE CONVERT(date,[RedeemedDate]) >= CONVERT(date,GETDATE()-30)
GROUP BY
    a.offertype,
    CONVERT(date,b.[date]) 
ORDER BY 
    a.offertype,
    CONVERT(date,b.[date])

Desired Result

  • Last30day table > has a date for each day for the last 30 days

  • UniqueOffers table > has every distinct offer in the offers table

  • offers > has the offer type and the date of redemption

Dale K
  • 25,246
  • 15
  • 42
  • 71
Chrudolpher
  • 1
  • 1
  • 1
    Please don't post [images](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) of data, sample data should be *consumable text* in your question, ideally as *create* and *insert* statements, or alternatively a [DB<>Fiddle](https://dbfiddle.uk/). See the [question guide](https://stackoverflow.com/help/how-to-ask). – Stu Mar 04 '22 at 22:14
  • 1
    Please show a few lines of each table, as text –  Mar 04 '22 at 22:16
  • Ideally you want to avoid any `convert`s in your joins and where clause because they can be quite detrimental for performance. – Dale K Mar 04 '22 at 22:19

0 Answers0