按周分组SQL并获取本周开始日期

I have a FiscalCalendar in MS SQL which has FiscalWeek, Week, FiscalMonth, Month, datekey etc. I have joined this table to another table. I am running a query to get the sales data for different categories and grouping them by FiscalWeek. However, in the table, I want to display the first day of the week associated with it. When I add Date to the select and groupby cause, my granularity changes (which I know is wrong)

我在MS SQL中有一个FiscalCalendar,它有FiscalWeek,Week,FiscalMonth,Month,datekey等。我已将此表连接到另一个表。我正在运行查询以获取不同类别的销售数据并按FiscalWeek对它们进行分组。但是,在表格中,我想显示与之相关的一周的第一天。当我将日期添加到select和groupby原因时,我的粒度发生了变化(我知道这是错误的)

I want something like this:

我想要这样的东西:

Instead I am getting this when I put date in the select and the group by clause

相反,当我在select和group by子句中输入日期时,我得到了这个

Here is my Query:

这是我的查询:

select cal.FiscalYear, cal.FiscalWeek, CONVERT(VARCHAR(10), cal.Date, 111) AS Date, sum(sd.Used) as Actual, sd.ShipGrp

from ods.adhoc.MktFiscalCalendar as cal,
Details as sd

where cal.daykey=sd.sys1dt
and cal.FiscalYear in ('2018')

Group by cal.FiscalYear, cal.FiscalWeek, Date, sd.ShipGrp

2 个解决方案

#1

I think you can do this in a windows function. Please try something like below. Note that we remove the date from the group by clause and use the MIN() aggregate function in the SELECT clause. Partitioning by week, will change the result for each week - be sure to order it or the results can look deceiving.

我想你可以在windows函数中做到这一点。请尝试下面的内容。请注意,我们从group by子句中删除日期,并在SELECT子句中使用MIN()聚合函数。按周分区,将改变每周的结果 - 一定要订购它或结果看起来很欺骗。

SELECT cal.FiscalYear
       , cal.FiscalWeek
       , MIN(cal.Date)
           OVER (
             PARTITION BY cal.FiscalWeek) AS Date
       , sum(sd.Used)                     AS Actual
       , sd.ShipGrp
FROM   ods.adhoc.MktFiscalCalendar AS cal,
       Details AS sd
WHERE  cal.daykey = sd.sys1dt
       AND cal.FiscalYear IN ( '2018' )
GROUP  BY cal.FiscalYear
          , cal.FiscalWeek
          , sd.ShipGrp 

#2

SELECT cal.FiscalYear
       , cal.FiscalWeek
       , (select min(Date)
        from MktFiscalCalendar
        where FiscalWeek = cal.FiscalWeek
        and FiscalYear = cal.FiscalYear) AS Date,
       , sum(sd.Used)                     AS Actual
       , sd.ShipGrp
FROM   MktFiscalCalendar AS cal,
       Details AS sd
WHERE  cal.daykey = sd.sys1dt
       AND cal.FiscalYear IN ( '2018' )
GROUP  BY cal.FiscalYear
          , cal.FiscalWeek
          , sd.ShipGrp