当前位置： 文江博客话题详情

在 T-SQL 中查找一天结束时自上次交易（当天或前一天）以来的利润总和

发布于 2024-11-17 13:09:20 字数 2870 浏览 6 评论 0原文

我问过 四月份的一个问题和@Mikael帮助我解决了它。此后要求发生了变化，我无法弄清楚如何构建查询。 表结构为

SID Sdate                  Profit   Units 
1   7/26/2010 9:15:00 AM    -37.5   -1      
1   7/26/2010 12:00:00 PM   -125    -1      
1   7/26/2010 12:45:01 PM   -12.5   -1      
1   7/26/2010 12:45:02 PM   0       0       
1   7/26/2010 12:45:03 PM   -75     1       
1   7/26/2010 2:00:01 PM    -12.5   1       
1   7/26/2010 2:00:02 PM    0       0       
1   7/26/2010 2:00:03 PM    -125    -1      
1   7/26/2010 2:15:00 PM    -50     -1  
1   7/27/2010 9:15:00 AM    25      -1      
1   7/27/2010 12:00:00 PM   196     -1      
1   7/27/2010 2:15:00 PM    -12.5   -1 
1   7/28/2010 9:15:00 AM    425     -1      
1   7/28/2010 12:00:00 PM   -125    -1      
1   7/28/2010 2:15:00 PM    -12.5   -1 
1   7/29/2010 9:15:00 AM    -37.5   -1      
1   7/29/2010 12:00:00 PM   -90     -1      
1   7/29/2010 12:45:01 PM   -12.5   -1      
1   7/29/2010 12:45:02 PM   0       0       
1   7/29/2010 12:45:03 PM   -75     1       
1   7/29/2010 2:15:00 PM    23      1

计算解释如下：

SID Sdate                  Profit   Units   End-of-day-Profit   Comments
1   7/26/2010 9:15:00 AM    -37.5   -1      
1   7/26/2010 12:00:00 PM   -125    -1      
1   7/26/2010 12:45:01 PM   -12.5   -1      
1   7/26/2010 12:45:02 PM   0       0       
1   7/26/2010 12:45:03 PM   -75     1       
1   7/26/2010 2:00:01 PM    -12.5   1       
1   7/26/2010 2:00:02 PM    0       0       
1   7/26/2010 2:00:03 PM    -125    -1      
1   7/26/2010 2:15:00 PM    -50     -1  -175    SUM of profit(row 9 to row 11) going back to 7/26/2010  2:00:02 PM
1   7/27/2010 9:15:00 AM    25      -1      
1   7/27/2010 12:00:00 PM   196     -1      
1   7/27/2010 2:15:00 PM    -12.5   -1  33.5    SUM of profit ( row 9 to row 14) going back to 7/26/2010  2:00:02 PM
1   7/28/2010 9:15:00 AM    425     -1      
1   7/28/2010 12:00:00 PM   -125    -1      
1   7/28/2010 2:15:00 PM    -12.5   -1  321     SUM of profit(row9 to row 17) going back to 7/26/2010  2:00:02 PM
1   7/29/2010 9:15:00 AM    -37.5   -1      
1   7/29/2010 12:00:00 PM   -90     -1      
1   7/29/2010 12:45:01 PM   -12.5   -1      
1   7/29/2010 12:45:02 PM   0       0       
1   7/29/2010 12:45:03 PM   -75     1       
1   7/29/2010 2:15:00 PM    23      1   -52     SUM of profit(row 22 to row 23) going back to 7/29/2010  12:45:02 PM

结果

SID Sdate       Profit   Units
1   7/26/2010   -175    -1
1   7/27/2010   33.5    -1
1   7/28/2010   321     -1
1   7/29/2010   -52     1

为 每个 SID 每天下午 2:15 计算净利润列的总和。为简单起见，我包含了一个 SID。其想法是回顾当天或前几天利润 = 0 且单位 = 0 的行。找到该行后，将所有利润值相加。

我很感谢对此的任何帮助。谢谢。

原文

I had asked a question in April and @Mikael helped me solve it. The requirement has since changed and I am having trouble figuring it out how to build the query.
The table structure is

SID Sdate                  Profit   Units 
1   7/26/2010 9:15:00 AM    -37.5   -1      
1   7/26/2010 12:00:00 PM   -125    -1      
1   7/26/2010 12:45:01 PM   -12.5   -1      
1   7/26/2010 12:45:02 PM   0       0       
1   7/26/2010 12:45:03 PM   -75     1       
1   7/26/2010 2:00:01 PM    -12.5   1       
1   7/26/2010 2:00:02 PM    0       0       
1   7/26/2010 2:00:03 PM    -125    -1      
1   7/26/2010 2:15:00 PM    -50     -1  
1   7/27/2010 9:15:00 AM    25      -1      
1   7/27/2010 12:00:00 PM   196     -1      
1   7/27/2010 2:15:00 PM    -12.5   -1 
1   7/28/2010 9:15:00 AM    425     -1      
1   7/28/2010 12:00:00 PM   -125    -1      
1   7/28/2010 2:15:00 PM    -12.5   -1 
1   7/29/2010 9:15:00 AM    -37.5   -1      
1   7/29/2010 12:00:00 PM   -90     -1      
1   7/29/2010 12:45:01 PM   -12.5   -1      
1   7/29/2010 12:45:02 PM   0       0       
1   7/29/2010 12:45:03 PM   -75     1       
1   7/29/2010 2:15:00 PM    23      1

The calculation is explained as :

SID Sdate                  Profit   Units   End-of-day-Profit   Comments
1   7/26/2010 9:15:00 AM    -37.5   -1      
1   7/26/2010 12:00:00 PM   -125    -1      
1   7/26/2010 12:45:01 PM   -12.5   -1      
1   7/26/2010 12:45:02 PM   0       0       
1   7/26/2010 12:45:03 PM   -75     1       
1   7/26/2010 2:00:01 PM    -12.5   1       
1   7/26/2010 2:00:02 PM    0       0       
1   7/26/2010 2:00:03 PM    -125    -1      
1   7/26/2010 2:15:00 PM    -50     -1  -175    SUM of profit(row 9 to row 11) going back to 7/26/2010  2:00:02 PM
1   7/27/2010 9:15:00 AM    25      -1      
1   7/27/2010 12:00:00 PM   196     -1      
1   7/27/2010 2:15:00 PM    -12.5   -1  33.5    SUM of profit ( row 9 to row 14) going back to 7/26/2010  2:00:02 PM
1   7/28/2010 9:15:00 AM    425     -1      
1   7/28/2010 12:00:00 PM   -125    -1      
1   7/28/2010 2:15:00 PM    -12.5   -1  321     SUM of profit(row9 to row 17) going back to 7/26/2010  2:00:02 PM
1   7/29/2010 9:15:00 AM    -37.5   -1      
1   7/29/2010 12:00:00 PM   -90     -1      
1   7/29/2010 12:45:01 PM   -12.5   -1      
1   7/29/2010 12:45:02 PM   0       0       
1   7/29/2010 12:45:03 PM   -75     1       
1   7/29/2010 2:15:00 PM    23      1   -52     SUM of profit(row 22 to row 23) going back to 7/29/2010  12:45:02 PM

The result would be

SID Sdate       Profit   Units
1   7/26/2010   -175    -1
1   7/27/2010   33.5    -1
1   7/28/2010   321     -1
1   7/29/2010   -52     1

The sum of net profit column is taken at 2:15PM everyday for every SID. I included one SID for simplicity.The idea is to look back either current day or previous days for the row that has profit=0 and units=0. Once that row is found, then sum all profit values.

I appreciate any help on this.Thanks.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

在风中等你 2024-11-24 13:09:20

我无法访问数据库来测试它。但是填充一个表变量@t并尝试这个。

declare @t table (SID int, Sdate datetime, profit decimal(9,1), units int)

<populate it>

我猜想您想要每个 SID 的列表。我误解了。我想改变我的答案。

select SID, Sdata, (select sum(Profit) from @t where Sdate between s.start and s.Sdate and S.SID = SID) profit, Units from 
(select SID, Sdata, (select max(Sdata) from @t where profit = 0 and unit = 0 and Sdata < t.Sdate and SID = t.SID) start from @t t 
where DATEPART(MINUTE, t.Sdata) =15 AND DATEPART(HOUR, t.Sdata) = 14 AND DATEPART(SECOND, t.Sdata) = 0 ) s
ORDER BY SID, Sdate

I don't have access to a database to test it. But populate a table variable @t and try this.

declare @t table (SID int, Sdate datetime, profit decimal(9,1), units int)

<populate it>

I had guess you want a list for each SID. I misunderstood that. I like to change my answer to this.

select SID, Sdata, (select sum(Profit) from @t where Sdate between s.start and s.Sdate and S.SID = SID) profit, Units from 
(select SID, Sdata, (select max(Sdata) from @t where profit = 0 and unit = 0 and Sdata < t.Sdate and SID = t.SID) start from @t t 
where DATEPART(MINUTE, t.Sdata) =15 AND DATEPART(HOUR, t.Sdata) = 14 AND DATEPART(SECOND, t.Sdata) = 0 ) s
ORDER BY SID, Sdate
回复 原文
悍妇囚夫 2024-11-24 13:09:20

DDL 和示例数据：

CREATE TABLE atable (
  SID int,
  Sdate datetime,
  Profit money,
  Units int
);
INSERT INTO atable (SID, Sdate, Profit, Units)
SELECT 1, '7/26/2010 9:15:00 AM' , -37.5, -1   UNION ALL    
SELECT 1, '7/26/2010 12:00:00 PM', -125 , -1   UNION ALL    
SELECT 1, '7/26/2010 12:45:01 PM', -12.5, -1   UNION ALL    
SELECT 1, '7/26/2010 12:45:02 PM', 0    ,  0   UNION ALL     
SELECT 1, '7/26/2010 12:45:03 PM', -75  ,  1   UNION ALL     
SELECT 1, '7/26/2010 2:00:01 PM' , -12.5,  1   UNION ALL     
SELECT 1, '7/26/2010 2:00:02 PM' , 0    ,  0   UNION ALL     
SELECT 1, '7/26/2010 2:00:03 PM' , -125 , -1   UNION ALL    
SELECT 1, '7/26/2010 2:15:00 PM' , -50  , -1   UNION ALL
SELECT 1, '7/27/2010 9:15:00 AM' , 25   , -1   UNION ALL    
SELECT 1, '7/27/2010 12:00:00 PM', 196  , -1   UNION ALL    
SELECT 1, '7/27/2010 2:15:00 PM' , -12.5, -1   UNION ALL
SELECT 1, '7/28/2010 9:15:00 AM' , 425  , -1   UNION ALL    
SELECT 1, '7/28/2010 12:00:00 PM', -125 , -1   UNION ALL    
SELECT 1, '7/28/2010 2:15:00 PM' , -12.5, -1   UNION ALL
SELECT 1, '7/29/2010 9:15:00 AM' , -37.5, -1   UNION ALL    
SELECT 1, '7/29/2010 12:00:00 PM', -90  , -1   UNION ALL    
SELECT 1, '7/29/2010 12:45:01 PM', -12.5, -1   UNION ALL    
SELECT 1, '7/29/2010 12:45:02 PM', 0    ,  0   UNION ALL    
SELECT 1, '7/29/2010 12:45:03 PM', -75  ,  1   UNION ALL    
SELECT 1, '7/29/2010 2:15:00 PM' , 23   ,  1;

查询：

WITH ranges AS (
  SELECT
    t.SID,
    t.Sdate,
    t.Units,
    SdateStart = MAX(t0.Sdate)
  FROM atable t
    INNER JOIN atable t0
       ON t0.SID = t.SID AND t0.Sdate < t.Sdate
      AND t0.Profit = 0 AND t0.Units = 0
  WHERE DATEPART(HH, t.Sdate) = 14
    AND DATEPART(MI, t.Sdate) = 15
  GROUP BY
    t.SID,
    t.Sdate,
    t.Units
)
SElECT
  r.SID,
  r.Sdate,
  Profit = SUM(t.Profit),
  r.Units
FROM ranges r
  INNER JOIN atable t ON t.SID = r.SID
    AND t.Sdate BETWEEN r.SdateStart AND r.Sdate
GROUP BY
  r.SID,
  r.Sdate,
  r.Units;

输出：

SID         Sdate                   Profit                Units
----------- ----------------------- --------------------- -----------
1           2010-07-26 14:15:00.000 -175.00               -1
1           2010-07-27 14:15:00.000 33.50                 -1
1           2010-07-28 14:15:00.000 321.00                -1
1           2010-07-29 14:15:00.000 -52.00                1

DDL & sample data:

CREATE TABLE atable (
  SID int,
  Sdate datetime,
  Profit money,
  Units int
);
INSERT INTO atable (SID, Sdate, Profit, Units)
SELECT 1, '7/26/2010 9:15:00 AM' , -37.5, -1   UNION ALL    
SELECT 1, '7/26/2010 12:00:00 PM', -125 , -1   UNION ALL    
SELECT 1, '7/26/2010 12:45:01 PM', -12.5, -1   UNION ALL    
SELECT 1, '7/26/2010 12:45:02 PM', 0    ,  0   UNION ALL     
SELECT 1, '7/26/2010 12:45:03 PM', -75  ,  1   UNION ALL     
SELECT 1, '7/26/2010 2:00:01 PM' , -12.5,  1   UNION ALL     
SELECT 1, '7/26/2010 2:00:02 PM' , 0    ,  0   UNION ALL     
SELECT 1, '7/26/2010 2:00:03 PM' , -125 , -1   UNION ALL    
SELECT 1, '7/26/2010 2:15:00 PM' , -50  , -1   UNION ALL
SELECT 1, '7/27/2010 9:15:00 AM' , 25   , -1   UNION ALL    
SELECT 1, '7/27/2010 12:00:00 PM', 196  , -1   UNION ALL    
SELECT 1, '7/27/2010 2:15:00 PM' , -12.5, -1   UNION ALL
SELECT 1, '7/28/2010 9:15:00 AM' , 425  , -1   UNION ALL    
SELECT 1, '7/28/2010 12:00:00 PM', -125 , -1   UNION ALL    
SELECT 1, '7/28/2010 2:15:00 PM' , -12.5, -1   UNION ALL
SELECT 1, '7/29/2010 9:15:00 AM' , -37.5, -1   UNION ALL    
SELECT 1, '7/29/2010 12:00:00 PM', -90  , -1   UNION ALL    
SELECT 1, '7/29/2010 12:45:01 PM', -12.5, -1   UNION ALL    
SELECT 1, '7/29/2010 12:45:02 PM', 0    ,  0   UNION ALL    
SELECT 1, '7/29/2010 12:45:03 PM', -75  ,  1   UNION ALL    
SELECT 1, '7/29/2010 2:15:00 PM' , 23   ,  1;

The query:

WITH ranges AS (
  SELECT
    t.SID,
    t.Sdate,
    t.Units,
    SdateStart = MAX(t0.Sdate)
  FROM atable t
    INNER JOIN atable t0
       ON t0.SID = t.SID AND t0.Sdate < t.Sdate
      AND t0.Profit = 0 AND t0.Units = 0
  WHERE DATEPART(HH, t.Sdate) = 14
    AND DATEPART(MI, t.Sdate) = 15
  GROUP BY
    t.SID,
    t.Sdate,
    t.Units
)
SElECT
  r.SID,
  r.Sdate,
  Profit = SUM(t.Profit),
  r.Units
FROM ranges r
  INNER JOIN atable t ON t.SID = r.SID
    AND t.Sdate BETWEEN r.SdateStart AND r.Sdate
GROUP BY
  r.SID,
  r.Sdate,
  r.Units;

The output:

SID         Sdate                   Profit                Units
----------- ----------------------- --------------------- -----------
1           2010-07-26 14:15:00.000 -175.00               -1
1           2010-07-27 14:15:00.000 33.50                 -1
1           2010-07-28 14:15:00.000 321.00                -1
1           2010-07-29 14:15:00.000 -52.00                1
回复 原文
~没有更多了~

关于作者

归途

暂无简介

文章
评论
561 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

ch

文章 0 评论 0

孤千羽

文章 0 评论 0

耀眼的星火

文章 0 评论 0

qutao

文章 0 评论 0

Thomson_.oO

文章 0 评论 0

梨涡

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文