我如何显示不同的一周？ [英] How do I show distinct week?

问题描述

我正在执行基于Running total概念的SQL代码。我的代码正在计算准确的运行总计，但我需要在每周完成后才显示运行总计而不是按日期。



我尝试过：



我的代码是

I am executing the SQL code based on "Running total" concept.My code is calculating accurate running total but i need to show the running total only after completion of every week not by dates.

What I have tried:

My code is

Select DISTINCT SD1.[Week], SD1.[Allocation], SD1.[Subsidiaries],SD1.[Month],SD1.[Date],(SD1.[Daily Sale]+IsNull(SUM(SD2.[Daily Sale]),0)) as 'Running Total'
from [dbo].[Solar data] SD1 left join [dbo].[Solar data] SD2 on SD1.[Subsidiaries]=SD2.[Subsidiaries]
and SD2.[Date]<SD1.[Date] And SD1.[Month]=SD2.[Month]
where SD1.[Subsidiaries]='WCL' group by SD1.[Allocation],SD1.[Month],SD1.[Subsidiaries],SD1.[Daily Sale],SD1.[Week],SD1.[Date]
order by SD1.[Date]

推荐答案

您可以使用分析查询（如下所示）：

select< other columns>，

案例当sale_date = max_week_date convert_to_string（week_sale）else''end week_sale

来自

（

select< ;其他列>，

总和（daily_sale）结束（按年划分，按销售日期订购周订单） week_sale，

max（sale_date）结束（按年，周划分）max_week_date

）a



自从你只想要一周的总数（而不是一周中每天的总运行总额），你甚至不需要编码'按sale_date排序'。

替换SQL-Server函数来计算年，周（corr到销售日期）并将数字转换为字符串。

You can use analytic query (something like this):
select <other columns>,
case when sale_date = max_week_date convert_to_string(week_sale) else '' end week_sale
from
(
select <other columns>,
sum(daily_sale) over (partition by year, week order by sale_date) week_sale,
max(sale_date) over (partition by year, week) max_week_date
) a

Since you want only the total for the week (and not the running total for every day in the week) you don't even need to code 'order by sale_date'.
Substitute the SQL-Server functions to compute the year, week (corr to sale date) and to convert number to string.

这篇关于我如何显示不同的一周？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！