我可以使用什么SQL从我的付款数据中检索计数？ [英] What SQL can I use to retrieve counts from my payments data?
问题描述
计数标准：每个学校按学校服务类型列出的学生人数（以唯一学生ID为索引）的真实计数。
我目前使用MS-Access。我需要能够提取与下面的示例数据类似数据的数据库的计数。每一行都是付款观察。
示例数据：
学校|学校服务类别| PaymentStartDate | PaymentEndDate |
001 ABC ED 01/02/2010 02/04/2012
001 ABC ED 01/02/2010 01/05/2010
001 ABC ED 04/02/2010 05/05 / 2010
001 DEF EZ 01/02/2010 02/04/2012
001 RR 02/02/2012 02/03/2012
002 ABC ED 02/02/2010 02/03 / 2011
002 ABC EZ 02/02/2010 06/03/2010
002 GHI ED 02/04/2011 02/04/2012
003 ABC ED 02/02/2011 02/03 / 2012
003 DEF ED 01/02/2010 08/03/2010
003 RR 02/02/2011 02/03/2011
004 RR 02/02/2011 02/03 / 2011
005 GHI ED 08/02/2010 02/04/2011
006 GHI ED 08/02/2010 08/02/2010
我想让我的计数看起来像：
年|学校| ED | EZ | RR |
01 2010 ABC 1 0 0
01 2010 DEF 1 1 0
01 2010 GHI 0 0 0
02 2010 ABC 2 1 0
03 2010 ABC 2 1 0
示例
欲望：我想根据服务类型确定学生的真实数量， 2010年1月
最大的障碍 - 重复
请查看学生ID 001 的观察结果。在2010年1月至2012年2月期间，学校 ABC 收到学生001的服务类型（ED）
我想要2010年1月在学校获得 ED 服务的学生人数 ABC 返回值为1，因为只有一个学生（学生001 ）接受 ED 那时的学校。
但是，当我使用交叉表按照原样检索我的计数时，它返回值2（对于两个学生）。原因是学生001 的付款＃1和付款＃2都符合我们2010年1月的年度标准。
付款1 符合条件，因为2010年1月位于01/02/2010 - 02/04/2012 *的付款日期范围内。
付款＃2 也符合条件，因为2010年1月的付款日期范围为01/02/2010 - 01/05/2010 。
付款＃3 不符合条件，因为2010年1月不在该行的日期范围内（04/02/2010 - 05 / 05/2010）。
*您可以从 PaymentStartDate 和 PaymentEndDate 。
我在这里准备了一个Excel版本的示例数据：链接到Excel文件
请记住：
-
PaymentStartDate和PaymentEndDate之间的时间在整个时间内极为可变，范围从0天到122天。
-
很多时候，有真正独特的付款观察结果，其中PaymentStartDate和PaymentEndDate之间的时间为0天（查看学生ID 006的付款行在上面的数据）。因此，删除不满足指定的在PaymentStartDate和PaymentEndDate之间的时间间隔标准的行不是一个选项，因为很多时候它们不是我想要计算的重复。
/ li>
-
是，对于某些服务类型，没有学校值。
一如往常，任何有用的建议如何解决这个重复的问题，在MS-Access中非常感谢。感谢您的时间。
编辑（02/10/2014）：更改了上面的计数输出，以反映我在我的帖子中提供的示例数据。我的道歉，不这样做之前。
这里有一种解决方法。对于名为[Payments]的表中的示例数据
付款行学生ID学校服务类型PaymentStartDate PaymentEndDate
----------- ---------- ------ ------------------ - ---------------- --------------
1 001 ABC ED 2010-01-02 2012-02-04
2 001 ABC ED 2010-01-02 2010-01-05
3 001 ABC ED 2010-04-02 2010-05-05
4 001 DEF EZ 2010-01-02 2012- 02-04
5 001 RR 2012-02-02 2012-02-03
6 002 ABC ED 2010-02-02 2011-02-03
7 002 ABC EZ 2010-02- 02 2010-06-03
8 002 GHI ED 2011-02-04 2012-02-04
9 003 ABC ED 2011-02-02 2012-02-03
10 003 DEF ED 2010-01-02 2010-08-03
11 003 RR 2011-02-02 2011-02-03
12 004 RR 2011-02-02 2011-02-03
13 005 GHI ED 2010-08-02 2011-02-04
14 006 GHI ED 2010-08-02 2010-08-02
如果我们在名为[PaymentsYearMonth]的Access中创建一个已保存的查询
SELECT
[Student ID]，
School，
[School Service Type]，
（Year（PaymentStartDate）* 100）+ Month（PaymentStartDate）AS StartYYYYMM，
（Year（PaymentEndDate）* 100）+ Month（PaymentEndDate）AS EndYYYYMM
FROM Payments
它会给我们
学生ID学校服务类型StartYYYYYMMY $ YYYYMM
- --------- ------ ------------------- ----------- ----- ----
001 ABC ED 201001 201202
001 ABC ED 201001 201001
001 ABC ED 201004 201005
001 DEF EZ 201001 201202
001 RR 201202 201202
002 ABC ED 201002 201102
002 ABC EZ 201002 201006
002 GHI ED 201102 201202
003 ABC ED 201102 201202
003 DEF ED 201001 201008
003 RR 201102 201102
004 RR 201102 201102
005 GHI ED 201008 201102
006 GHI ED 201008 201008
为了生成数据覆盖的Year / Month对，我们可以使用一个名为[MonthNumbers]的表。
MonthNumber
-----------
1
2
3
4
5
6
7
8
9
10
11
12
和一个名为[YearNumbers]的表，它涵盖了可能出现在数据中的每年。为了安全，可以包括从例如1901到2525的每年，但是为了说明，我们将使用
YearNumber
----------
2009
2010
2011
2012
2013
现在我们可以创建一个名为[MonthsToReport]的保存的查询，给出可能有结果的行
SELECT
yn.YearNumber，
mn.MonthNumber，
（yn.YearNumber * 100）+ mn.MonthNumber AS YYYYMM
FROM
YearNumbers AS yn，
MonthNumbers AS mn
WHERE（（yn.YearNumber * 100）+ mn.MonthNumber）> = DMin （StartYYYYMM，PaymentsYearMonth）
AND（（yn.YearNumber * 100）+ mn.MonthNumber）<= DMax（EndYYYYMM，PaymentsYearMonth）
按年份和月份排序，如下所示
YearNumber MonthNumber YYYYMM
---------- ----------- ------
2010 1 201001
2010 2 201002
2010 3 201003
2010 4 201004
2010 5 201005
2010 6 201006
2010 7 201007
2010 8 201008
2010 9 201009
2010 10 201010
2010 11 201011
2010 12 201012
2011 1 201101
2011 2 201102
2011 3 201103
2011 4 201104
2011 5 201105
2011 6 201106
2011 7 201107
2011 8 201108
2011 9 201109
2011 10 201110
2011 11 201111
2011 12 201112
2012 1 201201
2012 2 201202
现在创建一个查询，它提供年/月/学生/学校/类型的不同实例
SELECT DISTINCT
mtr.YearNumber，
mtr.MonthNumber，
pym。[Student ID]，
pym.School，
pym。服务类型]
FROM
MonthsToReport AS mtr
INNER JOIN
PaymentsYearMonth AS pym
ON mtr.YYYYMM> = pym.StartYYYYMM
AND mtr.YYYYMM< = pym.EndYYYYMM
...在聚合查询中换行以计数（现在唯一） [Student ID] values
SELECT
YearNumber，
MonthNumber，
School，
[School Service Type]，
COUNT（*）AS CountOfStudents
FROM
（
SELECT DISTINCT
mtr.YearNumber，
mtr.MonthNumber，
pym。[Student ID]，
pym.School，
pym。[School Service Type]
FROM
MonthsToReport AS mtr
INNER JOIN
PaymentsYearMonth AS pym
ON mtr.YYYYMM> = pym.StartYYYYMM
AND mtr.YYYYMM< = pym.EndYYYYMM
）AS distinctQuery
GROUP BY
YearNumber，
MonthNumber，
学校，
[学校服务类型]
然后使用作为交叉表查询中的FROM子查询
TRANSFORM Nz（First（CountOfStudents），0）AS npre>
SELECT
YearNumber，
MonthNumber，
School
FROM
（
SELECT
YearNumber，
MonthNumber，
School，
[School Service Type]，
COUNT（*）AS CountOfStudents
FROM
（
SELECT DISTINCT
mtr.YearNumber，
mtr.MonthNumber，
pym。[Student ID]，
pym.School，
pym 。[学校服务类型]
FROM
MonthsToReport AS mtr
INNER JOIN
PaymentsYearMonth AS pym
ON mtr.YYYYMM> = pym.StartYYYYMM
AND mtr .YYYYMM< = pym.EndYYYYMM
）AS distinctQuery
GROUP BY
YearNumber，
MonthNumber，
学校，
[学校服务类型]
）AS countQuery
GROUP BY
YearNumber，
MonthNumber，
School
PIVOT [学校服务类型]
返回
YearNumber MonthNumber学校ED EZ RR
---------- ----------- ------ - - -
2010 1 ABC 1 0 0
2010 1 DEF 1 1 0
2010 2 ABC 2 1 0
2010 2 DEF 1 1 0
2010 3 ABC 2 1 0
2010 3 DEF 1 1 0
2010 4 ABC 2 1 0
2010 4 DEF 1 1 0
2010 5 ABC 2 1 0
2010 5 DEF 1 1 0
2010 6 ABC 2 1 0
2010 6 DEF 1 1 0
2010 7 ABC 2 0 0
2010 7 DEF 1 1 0
2010 8 ABC 2 0 0
2010 8 DEF 1 1 0
2010 8 GHI 2 0 0
2010 9 ABC 2 0 0
2010 9 DEF 0 1 0
2010 9 GHI 1 0 0
2010 10 ABC 2 0 0
2010 10 DEF 0 1 0
2010 10 GHI 1 0 0
2010 11 ABC 2 0 0
2010 11 DEF 0 1 0
2010 11 GHI 1 0 0
2010 12 ABC 2 0 0
2010 12 DEF 0 1 0
2010 12 GHI 1 0 0
2011 1 ABC 2 0 0
2011 1 DEF 0 1 0
2011 1 GHI 1 0 0
2011 2 0 0 2
2011 2 ABC 3 0 0
2011 2 DEF 0 1 0
2011 2 GHI 2 0 0
2011 3 ABC 2 0 0
2011 3 DEF 0 1 0
2011 3 GHI 1 0 0
2011 4 ABC 2 0 0
2011 4 DEF 0 1 0
2011 4 GHI 1 0 0
2011 5 ABC 2 0 0
2011 5 DEF 0 1 0
2011 5 GHI 1 0 0
2011 6 ABC 2 0 0
2011 6 DEF 0 1 0
2011 6 GHI 1 0 0
2011 7 ABC 2 0 0
2011 7 DEF 0 1 0
2011 7 GHI 1 0 0
2011 8 ABC 2 0 0
2011 8 DEF 0 1 0
2011 8 GHI 1 0 0
2011 9 ABC 2 0 0
2011 9 DEF 0 1 0
2011 9 GHI 1 0 0
2011 10 ABC 2 0 0
2011 10 DEF 0 1 0
2011 10 GHI 1 0 0
2011 11 ABC 2 0 0
2011 11 DEF 0 1 0
2011 11 GHI 1 0 0
2011 12 ABC 2 0 0
2011 12 DEF 0 1 0
2011 12 GHI 1 0 0
2012 1 ABC 2 0 0
2012 1 DEF 0 1 0
2012 1 GHI 1 0 0
2012 2 0 0 1
2012 2 ABC 2 0 0
2012 2 DEF 0 1 0
2012 2 GHI 1 0 0
Count criteria: A "true" count, by month-year, of the number of students (indexed by a unique Student ID) at each School by School Service Type.
I am currently using MS-Access. I need to be able to extract counts for a database with similar data as the sample data below. Each row is a payment observation.
Sample data:
Student ID | School | School Service Type | PaymentStartDate | PaymentEndDate | 001 ABC ED 01/02/2010 02/04/2012 001 ABC ED 01/02/2010 01/05/2010 001 ABC ED 04/02/2010 05/05/2010 001 DEF EZ 01/02/2010 02/04/2012 001 RR 02/02/2012 02/03/2012 002 ABC ED 02/02/2010 02/03/2011 002 ABC EZ 02/02/2010 06/03/2010 002 GHI ED 02/04/2011 02/04/2012 003 ABC ED 02/02/2011 02/03/2012 003 DEF ED 01/02/2010 08/03/2010 003 RR 02/02/2011 02/03/2011 004 RR 02/02/2011 02/03/2011 005 GHI ED 08/02/2010 02/04/2011 006 GHI ED 08/02/2010 08/02/2010What I want my count to look like:
Month | Year | School | ED | EZ | RR | 01 2010 ABC 1 0 0 01 2010 DEF 1 1 0 01 2010 GHI 0 0 0 02 2010 ABC 2 1 0 03 2010 ABC 2 1 0Example
Desire: I want a true count of students, across schools, by service type, for
January 2010Biggest hurdle - Duplicates
Please take a look at the observations for
Student ID 001. Between January 2010 and February 2012, SchoolABCreceived 3 payments for the exact sameService Type (ED)for Student 001.I want my January 2010 count of students that received
EDservices at schoolABCto return a value of 1, since only one student (Student 001) received services forEDat that school for that time.However, when I conduct a crosstab to retrieve my counts with this data as it is, it returns a value of 2 (for two students). The reason being that both payment #1 and payment #2 of
Student 001meet my month-year criteria of January 2010.Payment #1 meets the criteria because January 2010 lies within the payment date range of 01/02/2010 - 02/04/2012*.
Payment #2 also meets the criteria because January 2010 lies within the payment date range of 01/02/2010 - 01/05/2010.
Payment #3 does not meet the criteria because January 2010 is not within the date range for that row (04/02/2010 - 05/05/2010).
*You get these dates from the
PaymentStartDateandPaymentEndDaterespectively.I prepared an Excel version of the sample data here:Link to Excel file
Please keep in mind that:
The time elapsed between the PaymentStartDate and PaymentEndDate is extremely variable across the board, and ranges from 0 days to 122 days.
Many times, there are truly unique payment observations where the time lapsed between PaymentStartDate and PaymentEndDate is 0 days (take a look at the payment row for Student ID 006 in the data above). Therefore, getting rid of rows that do not meet a specified "time lapsed between PaymentStartDate and PaymentEndDate" criteria is not an option, since many times they are not the duplicates I am trying to get rid of for my counts.
Yes, for certain Service Types, there is no School value.
As always, any helpful ideas of advice on how I can resolve this duplicates issue, and retrieve my true count values within MS-Access are greatly appreciated. Thank you for your time.
Edit (02/10/2014): Changed the count output above to reflect the sample data I provided in my post. My apologies for not doing so before.
Here's one way to tackle it. For sample data in a table named [Payments]
Payment Row Student ID School School Service Type PaymentStartDate PaymentEndDate
----------- ---------- ------ ------------------- ---------------- --------------
1 001 ABC ED 2010-01-02 2012-02-04
2 001 ABC ED 2010-01-02 2010-01-05
3 001 ABC ED 2010-04-02 2010-05-05
4 001 DEF EZ 2010-01-02 2012-02-04
5 001 RR 2012-02-02 2012-02-03
6 002 ABC ED 2010-02-02 2011-02-03
7 002 ABC EZ 2010-02-02 2010-06-03
8 002 GHI ED 2011-02-04 2012-02-04
9 003 ABC ED 2011-02-02 2012-02-03
10 003 DEF ED 2010-01-02 2010-08-03
11 003 RR 2011-02-02 2011-02-03
12 004 RR 2011-02-02 2011-02-03
13 005 GHI ED 2010-08-02 2011-02-04
14 006 GHI ED 2010-08-02 2010-08-02
if we create a saved query in Access named [PaymentsYearMonth]
SELECT
[Student ID],
School,
[School Service Type],
(Year(PaymentStartDate) * 100) + Month(PaymentStartDate) AS StartYYYYMM,
(Year(PaymentEndDate) * 100) + Month(PaymentEndDate) AS EndYYYYMM
FROM Payments
it will give us
Student ID School School Service Type StartYYYYMM EndYYYYMM
---------- ------ ------------------- ----------- ---------
001 ABC ED 201001 201202
001 ABC ED 201001 201001
001 ABC ED 201004 201005
001 DEF EZ 201001 201202
001 RR 201202 201202
002 ABC ED 201002 201102
002 ABC EZ 201002 201006
002 GHI ED 201102 201202
003 ABC ED 201102 201202
003 DEF ED 201001 201008
003 RR 201102 201102
004 RR 201102 201102
005 GHI ED 201008 201102
006 GHI ED 201008 201008
To generate the Year/Month pairs that the data covers, we can use a table named [MonthNumbers]
MonthNumber
-----------
1
2
3
4
5
6
7
8
9
10
11
12
and a table named [YearNumbers] which covers every year that might possibly appear in the data. For safety one might include every year from, say, 1901 to 2525, but for illustration we'll just use
YearNumber
----------
2009
2010
2011
2012
2013
Now we can create a saved query named [MonthsToReport] to give us the rows that might have results
SELECT
yn.YearNumber,
mn.MonthNumber,
(yn.YearNumber * 100) + mn.MonthNumber AS YYYYMM
FROM
YearNumbers AS yn,
MonthNumbers AS mn
WHERE ((yn.YearNumber * 100) + mn.MonthNumber)>=DMin("StartYYYYMM","PaymentsYearMonth")
AND ((yn.YearNumber * 100) + mn.MonthNumber)<=DMax("EndYYYYMM","PaymentsYearMonth")
Sorted by Year and Month that would look like
YearNumber MonthNumber YYYYMM
---------- ----------- ------
2010 1 201001
2010 2 201002
2010 3 201003
2010 4 201004
2010 5 201005
2010 6 201006
2010 7 201007
2010 8 201008
2010 9 201009
2010 10 201010
2010 11 201011
2010 12 201012
2011 1 201101
2011 2 201102
2011 3 201103
2011 4 201104
2011 5 201105
2011 6 201106
2011 7 201107
2011 8 201108
2011 9 201109
2011 10 201110
2011 11 201111
2011 12 201112
2012 1 201201
2012 2 201202
Now to create a query that gives us distinct instances of Year/Month/Student/School/Type
SELECT DISTINCT
mtr.YearNumber,
mtr.MonthNumber,
pym.[Student ID],
pym.School,
pym.[School Service Type]
FROM
MonthsToReport AS mtr
INNER JOIN
PaymentsYearMonth AS pym
ON mtr.YYYYMM>=pym.StartYYYYMM
AND mtr.YYYYMM<=pym.EndYYYYMM
...wrap that in an aggregation query to count up the (now unique) [Student ID] values
SELECT
YearNumber,
MonthNumber,
School,
[School Service Type],
COUNT(*) AS CountOfStudents
FROM
(
SELECT DISTINCT
mtr.YearNumber,
mtr.MonthNumber,
pym.[Student ID],
pym.School,
pym.[School Service Type]
FROM
MonthsToReport AS mtr
INNER JOIN
PaymentsYearMonth AS pym
ON mtr.YYYYMM>=pym.StartYYYYMM
AND mtr.YYYYMM<=pym.EndYYYYMM
) AS distinctQuery
GROUP BY
YearNumber,
MonthNumber,
School,
[School Service Type]
and then use that as the FROM subquery in our crosstab query
TRANSFORM Nz(First(CountOfStudents),0) AS n
SELECT
YearNumber,
MonthNumber,
School
FROM
(
SELECT
YearNumber,
MonthNumber,
School,
[School Service Type],
COUNT(*) AS CountOfStudents
FROM
(
SELECT DISTINCT
mtr.YearNumber,
mtr.MonthNumber,
pym.[Student ID],
pym.School,
pym.[School Service Type]
FROM
MonthsToReport AS mtr
INNER JOIN
PaymentsYearMonth AS pym
ON mtr.YYYYMM>=pym.StartYYYYMM
AND mtr.YYYYMM<=pym.EndYYYYMM
) AS distinctQuery
GROUP BY
YearNumber,
MonthNumber,
School,
[School Service Type]
) AS countQuery
GROUP BY
YearNumber,
MonthNumber,
School
PIVOT [School Service Type]
returning
YearNumber MonthNumber School ED EZ RR
---------- ----------- ------ -- -- --
2010 1 ABC 1 0 0
2010 1 DEF 1 1 0
2010 2 ABC 2 1 0
2010 2 DEF 1 1 0
2010 3 ABC 2 1 0
2010 3 DEF 1 1 0
2010 4 ABC 2 1 0
2010 4 DEF 1 1 0
2010 5 ABC 2 1 0
2010 5 DEF 1 1 0
2010 6 ABC 2 1 0
2010 6 DEF 1 1 0
2010 7 ABC 2 0 0
2010 7 DEF 1 1 0
2010 8 ABC 2 0 0
2010 8 DEF 1 1 0
2010 8 GHI 2 0 0
2010 9 ABC 2 0 0
2010 9 DEF 0 1 0
2010 9 GHI 1 0 0
2010 10 ABC 2 0 0
2010 10 DEF 0 1 0
2010 10 GHI 1 0 0
2010 11 ABC 2 0 0
2010 11 DEF 0 1 0
2010 11 GHI 1 0 0
2010 12 ABC 2 0 0
2010 12 DEF 0 1 0
2010 12 GHI 1 0 0
2011 1 ABC 2 0 0
2011 1 DEF 0 1 0
2011 1 GHI 1 0 0
2011 2 0 0 2
2011 2 ABC 3 0 0
2011 2 DEF 0 1 0
2011 2 GHI 2 0 0
2011 3 ABC 2 0 0
2011 3 DEF 0 1 0
2011 3 GHI 1 0 0
2011 4 ABC 2 0 0
2011 4 DEF 0 1 0
2011 4 GHI 1 0 0
2011 5 ABC 2 0 0
2011 5 DEF 0 1 0
2011 5 GHI 1 0 0
2011 6 ABC 2 0 0
2011 6 DEF 0 1 0
2011 6 GHI 1 0 0
2011 7 ABC 2 0 0
2011 7 DEF 0 1 0
2011 7 GHI 1 0 0
2011 8 ABC 2 0 0
2011 8 DEF 0 1 0
2011 8 GHI 1 0 0
2011 9 ABC 2 0 0
2011 9 DEF 0 1 0
2011 9 GHI 1 0 0
2011 10 ABC 2 0 0
2011 10 DEF 0 1 0
2011 10 GHI 1 0 0
2011 11 ABC 2 0 0
2011 11 DEF 0 1 0
2011 11 GHI 1 0 0
2011 12 ABC 2 0 0
2011 12 DEF 0 1 0
2011 12 GHI 1 0 0
2012 1 ABC 2 0 0
2012 1 DEF 0 1 0
2012 1 GHI 1 0 0
2012 2 0 0 1
2012 2 ABC 2 0 0
2012 2 DEF 0 1 0
2012 2 GHI 1 0 0
这篇关于我可以使用什么SQL从我的付款数据中检索计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
