SQL，一段时间内的天数 [英] SQL, days in a time period

问题描述

旅途中的一组销售人员拥有一个SQL表，该表列出了他们离开的日期，每次旅行返回的日期以及ID.想确定每个人最近行驶的120天中有多少天，然后是120到240天，然后是240到360天.这比我想象的要难得多，有什么主意吗?

A group of sales guys on the road, have a SQL table that lists the date they left and the date they returned on each trip and their ID. Want to determine how many days out of the last 120 that each guy was on the road, then 120 to 240 then 240 to 360. It has been harder than I thought, any ideas? Do I need to do the logic in C# or can a query answer this?

推荐答案

因为您没有提供有关您所遇到的问题或问题的任何信息，我是否需要在C#中执行逻辑?目前已经完成.我将提供一些有关如何解决您的问题的想法和技巧.这只是解决此问题的一种可能方法，还有其他方法可以根据各自的优缺点来完成所有工作，但我将介绍如何使用标量值函数来完成此工作.我决定不要只为您写出整个查询，而无需您付出至少一点努力就可以向我们提供您到目前为止所做的一切，因此我们可以帮助您回答查询中可能遇到的特定问题.

您可以通过编写传递给Id，StartRange和EndRange的标量值函数(将其称为DaysOuts)开始，以返回该Range的天数.这将允许您查询业务员表的表并传递其ID和范围(例如，以DaysOut(Salesmen.Id，0，120)作为Range1，以DaysOut(Salesmen.Id，120，240)作为Range2，等等).

编写查询的关键是DateDiff函数. DateDiff(day，DateLeft，GetDate())将为您提供离开旅行的天数.因此，如果他们在6个月前离开旅行，那么DateDiff将返回〜180天.因此，您可以将其与StartRange和EndRange进行比较，以确定其是否在正确的范围内.然后，您可以根据DateLeft和DateReturned使用DateDiff求和它们过期的天数.

基本功能正常工作后，您可能需要考虑边缘条件.例如某人在130天前离开并在110天前返回时.由于其中的10天属于0到120范围，而其他10天属于120到240范围.这可能是查询中比较棘手的部分，因为它涉及查找DateLeft和DateReturned之间的实际落在Range内的日期.基本上，您需要确定DateLeft是否在EndRange之外，并且需要找出结束我们正在搜索的范围的日期，并使用该日期代替DateLeft.您需要对DateReturned做同样的事情.

Since you did not give any information about what you are having problems with or what you have currently done. I will give a few ideas and tips on how to solve your problem. This is only one possible way to complete this problem, there are other methods to do it all with their own pros and cons, but I will go over how to do it with a scalar-valued function. I decided against just writing out the entire query for you without you showing at least a little effort in providing us with what you have done so far, so we can help answer specific problems you might be having with your query.

You can start out by writing a scalar-valued function (lets call it DaysOuts) that you pass in a Id, StartRange, and EndRange that you will return the days out for that Range. This will allow you query the table of salesmen table and pass in their Id and Range (ex. DaysOut(Salesmen.Id, 0, 120) As Range1, DaysOut(Salesmen.Id, 120, 240) As Range2, etc.).

The key to writing the query is the DateDiff function. DateDiff(day, DateLeft, GetDate()) will get you the number of days ago they left for the trip. So if they left 6 months ago for a trip then DateDiff would return ~180 days. So you could compare that to your StartRange and EndRange to determine if that is within the correct range. You can then use DateDiff to Sum up the number of days they were out based on the DateLeft and DateReturned.

Once you get the basic function working you will probable need to take edge conditions into consideration. Such as when someone left 130 days ago and returned 110 days ago. Since 10 of those days will fall with the 0 to 120 range and the other 10 days would fall in the 120 to 240 range. This is probable the trickier part of the query since it involves finding the days between DateLeft and DateReturned that actually fall within the Range. Basically you need to determine if the DateLeft is outside the EndRange and you need to figure out the date that ends the range we are searching between and use that instead of the DateLeft. You will need to do the same thing for DateReturned.

这篇关于SQL，一段时间内的天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！