Qual SQL posso usar para recuperar contagens dos meus dados de pagamentos?
Critérios de contagem: Uma contagem "verdadeira", por mês-ano, do número de alunos (indexados por uma ID de aluno exclusiva) em cada escola por tipo de serviço escolar.
Atualmente, estou usando o MS-Access. Eu preciso ser capaz de extrair contagens para um banco de dados com dados semelhantes aos dados de exemplo abaixo. Cada linha é uma observação de pagamento.
Dados de amostra:
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/2010
Como eu quero que minha contagem seja:
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 0
Exemplo
Desejo: quero uma contagem verdadeira de alunos, nas escolas, por tipo de serviço, porJanuary 2010
Maior obstáculo - duplicatas
Por favor, dê uma olhada nas observações paraStudent ID 001
. Entre janeiro de 2010 e fevereiro de 2012, a EscolaABC
recebeu 3 pagamentos exatamente pelo mesmoService Type (ED)
para o aluno 001.
Quero minha contagem de janeiro de 2010 de alunos que receberamED
serviços na escolaABC
para retornar um valor de 1, pois apenas um aluno (Student 001
) receberam serviços paraED
naquela escola para esse tempo.
No entanto, quando conduzo uma tabela de referência cruzada para recuperar minhas contagens com esses dados, ele retorna um valor de 2 (para dois alunos). A razão é que o pagamento nº 1 e o pagamento nº 2 deStudent 001
atendem aos meus critérios de mês e ano de janeiro de 2010.
Pagamento nº 1 atende aos critérios, pois janeiro de 2010 está dentro do período de pagamento de 01/02/2010 a 02/02/2012 *.
Pagamento # 2 também atende aos critérios, pois janeiro de 2010 está dentro do período de pagamento de 01/02/2010 a 01/05/2010.
Pagamento nº 3 não atende aos critérios porque janeiro de 2010 não está no período dessa linha (04/02/2010 - 05/05/2010).
* Você obtém essas datas noPaymentStartDate
ePaymentEndDate
respectivamente.
Preparei uma versão do Excel dos dados de amostra aqui:Link para arquivo do Excel
Lembre-se de que:
O tempo decorrido entre PaymentStartDate e PaymentEndDate é extremamente variável em todos os aspectos e varia de 0 a 122 dias.
Muitas vezes, existem observações de pagamento verdadeiramente únicas, nas quais o tempo decorrido entre PaymentStartDate e PaymentEndDate é de 0 dias (consulte a linha de pagamento da ID do aluno 006 nos dados acima). Portanto, livrar-se de linhas que não atendem aos critérios "tempo decorrido especificado entre PaymentStartDate e PaymentEndDate" não é uma opção, pois muitas vezes elas não são as duplicatas das quais estou tentando me livrar das minhas contagens.
Sim, para certos tipos de serviço, não há valor escolar.
Como sempre, todas as idéias úteis de conselhos sobre como resolver esse problema de duplicatas e recuperar meus verdadeiros valores de contagem no MS-Access são muito apreciadas. Obrigado pelo seu tempo.
Editar (10/02/2014): a contagem da saída foi alterada acima para refletir os dados de amostra que forneci na minha postagem. Peço desculpas por não fazer isso antes.