问题描述
我有以下SQL查询，我想使用此商品代码计算WTD，MTD和YTD销售数字，并且仅按商店代码分组 -
I have the below SQL Query and I want to Calculate the WTD, MTD and YTD sales figure using this and only group by the store codes -
declare @DateFrom datetime , @dateTo datetime
set @datefrom = getdate()-6
set @dateto= getdate()
select
st.store_code as Store,
p.product_code as Product,
PN.short_name as [Product Description],
c.description as Waist,
s.description as [Size(Length)],
rt.TRANSDATE as [Transaction Date],
rt.TRANSNUM as [Transaction number],
rt.operator_id as [Sales Associate ID#],
sta.name as [Sales Associate name],
dtp.QTY,
((dtp.qty*dtp.Price_Sold)+isnull(dtp.Tax_Amount1,0)+ISNULL(dtp.Tax_Amount2,0)) as [Price_Sold],
service.description as [Service Applied],
dts.PRICE_NET as [Service Amount],
@Datefrom as From_Date,
@DateTo as to_Date,
"YTD" = ?,
"MTD"=?,
"WTD"=?
from RETAIL_TRANSACTION (nolock) rt
inner join dt_product (nolock) dtp on rt.store_code_id = dtp.store_code_id
and rt.transtype = dtp.transtype
and rt.transnum = dtp.transnum
and rt.cais = dtp.cais
inner join retail_line (nolock) rl on rl.store_code_id=dtp.store_code_id
and rl.transnum=dtp.transnum
and rl.transtype=dtp.transtype
and rl.cais=dtp.cais
and rl.line_id=dtp.line_id
Left outer join dt_service (nolock) dts on dts.store_code_id=rl.store_code_id
and dts.transtype=rl.transtype
and dts.transnum=rl.transnum
and dts.cais=rl.cais
inner join service (nolock) on service.service_id=dts.service_id
inner join store (nolock) st on rt.store_code_id = st.store_code_id
inner join staff (nolock) sta on rt.operator_ID = sta.staff_id
inner join sku_conversion (nolock) sc on dtp.product_id = sc.product_id
and dtp.size_id = sc.size_id
and dtp.color_id = sc.color_id
inner join Product(nolock) P on P.product_id=dtp.product_id
inner join Product_name(nolock) PN on PN.product_id=P.product_id
inner join color(nolock) C on C.color_id=dtp.color_id
inner join size(nolock) s on s.size_id=dtp.size_id
where dts.service_id in ('1000045','1000044')
and rt.TS_ID between @Datefrom and @Dateto
我不想按选择语句中的所有字段进行分组，只需要按Transdate和store进行分组。
如何使用SSRS报告实现此目的。请给我一些想法..
I don't want to Group by all the fields in the select statement , only group by Transdate and store.
How to achieve this using SSRS Reporting . Please give me some idea..
推荐答案
declare @DateFrom datetime , @dateTo datetime
set @datefrom = dateadd(mm, -month({fn curdate()})+1, {fn curdate()}) /*down to January*/
set @datefrom = dateadd(dd, -Day(@datefrom ) + 1, @datefrom) /*down to Jan 1st*/
set @dateto= {fn curdate()}
因此在WHERE子句中使用它会带来YTD数据。现在你需要使用CASE语句为YTD，MTD和WTD制作一个SUM。
我不太了解你的字段名称，但是如果你按照这个模型你会没事的：
So using this in your WHERE clause brings YTD Data. Now you need to make a SUM with a CASE statement for YTD, MTD and WTD.
I don't know very well your field names, but if you follow this model you'll be fine:
SUM(YourTotalSales) AS YTD,
SUM(CASE WHEN SaleDate BETWEEN DateAdd(dd, -Day(@dateTo)+1, @dateTo) AND @dateTo
THEN YourTotalSales ELSE 0 END) AS MTD,
SUM(CASE WHEN SaleDate BETWEEN DateAdd(dd, -DATEPART(dw, @dateTo)+1, @dateTo) AND @dateTo THEN YourTotalSales ELSE 0 END) AS WTD
对于GROUP BY：by规则，如果SELECT子句中有一个字段不是聚合函数的一部分，那么它必须进入GROUP BY。
只是不要选择你不想分组的字段，并在GROUP BY子句中省略它们，你就可以了。
For the GROUP BY: by rule, if there is a field in the SELECT clause that is not part of an aggregate function, then it must go into the GROUP BY.
Just don't select the fields you don't want to group by and omit them from the GROUP BY clause and you'll do fine.
这篇关于如何使用SSRS报告计算WTD，MTD和YTD的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！