SQL 最后一天在 moth select 中

发布于 2024-08-26 00:07:22 字数 417 浏览 4 评论 0原文

我有下表，基本上是每日交易的摘要，并且每晚加载。

+++++++++++++++++++++++
+ DateCreated + Sale  +
+++++++++++++++++++++++
+ 20100101    + 1000  +
+ 20100131    + 2000  +
+ 20100210    + 2000  +
+ 20100331    + 4000  +
+++++++++++++++++++++++

我需要按月显示销售情况，但仅显示每个月的最后一天。

例如，

JAN    2000
FEB       0
MAR    4000

我可能可以在我的选择中使用 CASE 来完成此操作，但这有效吗？这是 SQL Server 2000。

原文

I have the following table which is basically the summary of daily transactions and is loaded nightly.

+++++++++++++++++++++++
+ DateCreated + Sale  +
+++++++++++++++++++++++
+ 20100101    + 1000  +
+ 20100131    + 2000  +
+ 20100210    + 2000  +
+ 20100331    + 4000  +
+++++++++++++++++++++++

I need to display the sale by month, but only for the last day in each month.

JAN    2000
FEB       0
MAR    4000

I could probably accomplish this with CASE in my select, but is this efficient? This is SQL Server 2000.

分享到QQ

分享到微博

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

发布评论

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

(り薆情海 2024-09-02 00:07:22

我的问答解决方案是构造一个日期，即下个月的第一天，然后减去一天。

回复收藏 0 原文

柒夜笙歌凉 2024-09-02 00:07:22

你可以这样做，但它不会显示丢失数据的 0：

select YEAR(DateCreated) as Year, MONTH(DateCreated) as Month, Sale
from MyTable
where DAY(DateCreated + 1) = 1

You could do something like this, but it will not show 0 for missing data:

select YEAR(DateCreated) as Year, MONTH(DateCreated) as Month, Sale
from MyTable
where DAY(DateCreated + 1) = 1

回复收藏 0 原文

凡尘雨 2024-09-02 00:07:22

我有一个日历表，只是添加了一个“IsLastDayOfMonth”列，设置每个月的最后一天。然后将其加入到我的查询中，仅按“IsLastDayOfMonth”进行过滤，并且有效。

select a.datecreated,a.sale
         from salesummary a
 inner join calendar c
on a.datecreated = c.fulldatekey
 and c.IsLastDayOfMonth = 1
group by a.datecreated,a.sale

I had a calendar table, just added a "IsLastDayOfMonth" column, set the last day of each month. Then joined that to my query, filtering by "IsLastDayOfMonth" only and that worked.

select a.datecreated,a.sale
         from salesummary a
 inner join calendar c
on a.datecreated = c.fulldatekey
 and c.IsLastDayOfMonth = 1
group by a.datecreated,a.sale

回复收藏 0 原文

~没有更多了~