如何在给定的日期间隔内获得平均工人数

问题描述

你好同胞CPians，



我想找到两个特定日期之间的平均工人数。我有以下查询，它给了我想要的天工人数。



例如：以下给出了2014年1月16日的工人数。

Hello fellow CPians,

I am trying to find average worker count between two given dates. I have the following query which gives me the desired days worker count.

For ex: The following gives me the worker count on 16.01.2014.

SELECT T.TOTALPERSCOUNT
FROM (
SELECT NVL(SUBSTR(p0.persid, 1, 2),'00') AS COMPANY,
    COUNT(*) AS TOPLAM
    FROM   pers0 p0, pers2 p2, role r
    WHERE p0.persid = p2.persid
        AND p2.client= r.client
        AND p2.persid= r.persid
        AND r.validfrom = (select max(validfrom) from role where client = p0.client and persid = p0.persid)
        AND p0.startdate < '16.01.2014'
        AND p0.leavedate >= '16.01.2014'
        AND p2.validfrom < '16.01.2014'
        AND p2.validuntil >= TO_DATE('16.01.2014', 'DD.MM.YYYY') -1
        AND p2.socinsurstat <> 7
    GROUP BY ROLLUP (SUBSTR (p0.persid, 1, 2))
    ORDER BY SUBSTR (p0.persid, 1, 2)
) T
WHERE T.COMPANY = '00'

我想得到一个2014年1月10日至2014年2月15日之间的平均工人人数。我怎样才能做到这一点？



这是第二个查询，它给出了这些给定日期之间的日期。

I want to get an average worker count between dates 10.01.2014 and 15.02.2014. How can I accomplish this?

And here is the second query which gives me the dates between these given dates.

select to_date('10.01.2014','dd.MM.yyyy') + rownum -1 as DATEINTV
      from all_objects
     where rownum <=
to_date('15.02.2014','dd.MM.yyyy')-to_date('10.01.2014','dd.MM.yyyy')+1

我想我需要加入这些表吗？或许不是？也许是一种不同的方法。



我将在C＃项目中使用此查询的结果，但我不想每天循环，如果间隔是一年。而不是发出365请求，应该有更好的方法在数据库端完成这个过程。



有人可以帮我解决这个问题。在此先感谢。

I think some how I need to join these tables? Or maybe not?Maybe a different approach.

I will be using the result of this query in a C# project but I don't want to loop day by day if the interval is a year. Instead of making a 365 requests there should be a better effective way accomplishing this process in the database side.

Can someone help me sort this out. Thanks in advance.

推荐答案

这篇关于如何在给定的日期间隔内获得平均工人数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！