相关日期时间和聚合功能查询 [英] Related On Date time and Aggregate Fuction Query

问题描述

大家好，



我的桌子如下所示。

Hello guys,

I'm having a table as Below.

Pmaster
PID Pname
1   SB
2   LA
3   Rd
4   FD

Tmaster(Table name)
with Column Name
PID,Txn TYpe,Txn Amount,Date of Transaction

注 -

TXN TYPE =交易类型（可能是Sb，la等）

Txn金额=交易金额



所以我的问题是：我想列出具有最大的平均交易数量的产品（考虑过去6个月的数据）？

NOTE-
TXN TYPE=transaction type(might be Sb,la etc)
Txn Amount=Transaction AMount

So my question is: i want list the product having the maximum montly, average number of transactions (consider the last 6 months of data) ?

推荐答案

请阅读我对该问题的评论。我不确定你想要什么，但我的建议是创建存储程序 [ ^ ]（SP）是这样的：

PLease, read my comment to the question. I'm not sure what you want, but my advice is to create stored procedure[^](SP) like this:

CREATE PROCEDURE GetAmountRangeDates
    @dateFrom DATETIME,
    @dateTo DATETIME
AS
BEGIN
    SELECT PM.PID, PM.Pname, TM.TYpe, SUM(TM.Amount) AS Amount
    FROM Pmaster AS PM LEFT JOIN Tmaster AS TM O PM.PID=TM.PID
    WHERE TM.[Date of Transaction] BETWEEN @dateFrom AND @dateTo
    GROUP BY  PM.PID, PM.Pname, TM.TYpe
END

以上SP获得2个输入参数： @dateFrom 和 @dateTo 。使用两者来定义日期范围。



如何在代码中使用sp？

如何：执行返回行的存储过程 [ ^ ]

如何使用Visual Basic .NET在ASP.NET中调用SQL Server存储过程 [ ^ ]

Above SP gets 2 input parameters: @dateFrom and @dateTo. Use both to define date range.

How to use sp in code?
How to: Execute a Stored Procedure that Returns Rows[^]
How to call SQL Server stored procedures in ASP.NET by using Visual Basic .NET [^]

这篇关于相关日期时间和聚合功能查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！