某些选定月份的天数总和使用sql查询 [英] sum of days from some selected months Using sql query

问题描述

我有一个像这样的表

i have a table like this

id          Attendate
1	5/25/2012 12:00:00 AM
2	6/25/2012 12:00:00 AM
3	9/25/2012 12:00:00 AM
4	5/25/2012 12:00:00 AM
5	7/25/2012 12:00:00 AM
6	8/25/2012 12:00:00 AM
7	9/25/2012 12:00:00 AM
8	5/25/2012 12:00:00 AM
9	6/25/2012 12:00:00 AM
10	8/25/2012 12:00:00 AM

我想要该表中月份(日历天)的天数总和

我想要输出:

I want the sum of the days in the months(Calendar days) from that table

I want the output:

153

实际上我只需要添加重复的月份一次；意味着我需要不同月份的日子；
例如:只有5个月，
5月5日
6月6日
7月7日
8月8日
9月9日

我需要这几个月的总和，

这表示表中提供的不同月份的日期

HI ,Actually I need to add The repeating months only once ;Means i need days of distinct month;
Eg:There is only 5 months,
5 may
6 June
7 July
8 August
9 September

I need the sum of these months ,

Which means the distinct month of dates provided in the table

推荐答案

尝试类似

SELECT DATEDIFF(
(select top 1 AttenDate from myTable orderby AttenDate Desc)
(select top 1 AttenDate from myTable orderby AttenDate Asc)
GETDATE()) AS NumberOfDays 

This should give you the difference between the maximum and minimum dates in your table.

You could optimize this query by using JOIN. You can try that out for yourself.

尝试:

SELECT SUM(DATEDIFF(DAY, DATEADD(DAY, 1 - DAY(Attendate), Attendate),
                         DATEADD(MONTH, 1, DATEADD(DAY, 1 - DAY(Attendate), Attendate))))
FROM MyTable

select datediff(day,(select top 1 Attendate from  table order by Attendate asc) ,(select top 1 Attendate from table  order by Attendate desc)) 

这篇关于某些选定月份的天数总和使用sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！