获取两个日期之间的记录 [英] Get records count between two dates I

问题描述

嗨专家，



我有listofholidays表

带列

RowId holidayname fromdate和todate



如果员工从日期开始申请休假 - 迄今为止。在此范围内，如果任何假期回报根据条件从日期=到日期和从日期开始！=到目前为止



我怎样才能达到效果？



我尝试了什么：



我写了两个查询

从假日列表中选择count（*）作为count1，其中@fromdate和@todate之间的日期为/>


从listofholidays中选择count（*）作为count2，其中todate

@fromdate和@todate之间



我将这两个计数相加但在表格中从日期和todate等于

它给出了双重计数

Hi Experts,

I have listofholidays table
With columns
RowId holidayname fromdate and todate

If an employee apply for a leave from date - to date.In this range if any holiday return that count based on conditions fromdate=to date and fromdate != to date

How can I achieve the result??

What I have tried:

I wrote two queries
Select count(*) as count1 from listof holidays where fromdate between @fromdate and @todate

Select count(*) as count2 from listofholidays where todate
Between @fromdate and @todate

I am summing these two counts but in table fromdate and todate equal
Its giving double count

推荐答案

0。这些日期似乎是varchar类型，如果是这样，你应该将它们更改为日期类型。

1.看看你的例子，似乎每个假期只持续一天，如果是这样，为什么需要两个约会 - fromdate和todate？在这种情况下，您只需要一个日期字段，例如date_of_holiday，然后获取其记录的数量（date_of_holiday介于fromdate和todate之间）并且您已完成。

2.如果假期超过一天，请考虑以下情况：

2.1（leave_start_date和leave_end_date之间的holiday_from_date）AND（holiday_to_date> leave_end_date）或

2.2（leave_start_date和leave_end_date之间的holiday_to_date）AND（holiday_from_date< leave_start_date）或

2.3（leave_start_date和leave_end_date之间的holiday_from_date）AND（leave_start_date和leave_end_date之间的holiday_to_date）

0. Those dates seem to be of varchar type, if so, you should change them to date type.
1. Looking at your example, it seems that each holiday only lasts one day, if so, why need two dates - fromdate and todate? In such a case, you only need a date field say date_of_holiday, then get a count of records whose (date_of_holiday between fromdate and todate) and you are done.
2. If there are holidays that span more that one day, then consider these scenarios:
2.1 (holiday_from_date between leave_start_date and leave_end_date) AND (holiday_to_date > leave_end_date) OR
2.2 (holiday_to_date between leave_start_date and leave_end_date) AND (holiday_from_date < leave_start_date) OR
2.3 (holiday_from_date between leave_start_date and leave_end_date) AND (holiday_to_date between leave_start_date and leave_end_date)

您是否正在寻找重叠拟议假期的假期计数？

Are you looking for a count of holidays which overlap the proposed leave?

SELECT
    COUNT(*) As OverlappingCount
FROM
    ListOfHolidays
WHERE
    FromDate <= @ToDate
And
    ToDate >= @FromDate
;

这篇关于获取两个日期之间的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！