Good morning! For a report based on the [Дата заказа] column on a specific date @bdate, you need to find the number of orders for the current month, for the week and for the past 24 hours. Please help.

  DECLARE @bdate DATETIME = Cast('08.02.2017 00:00:00' AS DATETIME) SELECT @bdate as data ,[Заказ ] ,[Дата заказа] FROM table

    1 answer 1

     DECLARE @bdate DATETIME = Cast('08.02.2017 00:00:00' AS DATETIME) select sum(case when [Дата заказа] >= @bdate and [Дата заказа] < DATEADD(d,1,@bdate) then 1 end) day, sum(case when DATEPART(iso_week,[Дата заказа])=DATEPART(iso_week,@bdate) then 1 end) week, sum(case when DATEPART(m,[Дата заказа])=DATEPART(m,@bdate) then 1 end) month from datetest where [Дата заказа] between DATEADD(d, -7, DATEADD(m, DATEDIFF(m, 0, @bdate), 0)) and DATEADD(d, 7, DATEADD(m, DATEDIFF(m, 0, @bdate) + 1, 0))

    In the condition where we select records for the month, with a margin of + -7 days, in case the current week is outside the bounds of the current month. The condition in the first sum () is calculated on the counting of orders in the days specified by the parameter. If the "past" is a day earlier - correct the condition. Week is taken calendar, from Monday to Sunday, which includes the specified day.