TPH에서 행을 Null 값과 병합하고 종속 필드를 계산하는 방법

dapper entity-framework sql sql-server sql-server-2012

문제

다음과 같이 정규화되지 않은 테이블이있는 경우 :

FinanceList (NetSales,Expenses,Receivables,...etc,Discriminator)

그리고 나는 두 가지 방법에 따라 연도를 분류하는 다른 테이블 ( PeriodType ) PeriodType 집니다.

  • Quarter (4 기간)

    1- Period1 1 월에서 3 월까지

    2- Period2 2 4 월에서 6 월까지

    3- Period3 3 7 월에서 9 월까지

    4- Period4 4 10 월에서 12 월까지

  • Yearly (1 기간) 1 월에서 12 월까지

여기에 이미지 설명을 입력하십시오.


샘플 데이터 :

Year   per TypeId NetSales    Expenses  Receivables CompanyId
2016    2   2   164547.0000   NULL      NULL        3001
2016    2   2   NULL          NULL      50601.0000  3001
2016    2   2   NULL          550.4110  NULL        3001
2016    3   2   222764.0000   NULL      NULL        3001
2015    3   2   264843.0000   NULL      NULL        3001
2015    2   2   NULL          NULL      42049.0000  3001
2015    1   3   NULL          NULL      32431.0000  3001
2015    2   2   NULL          614.6200  NULL        3001
2015    2   2   187112.0000   NULL      NULL        3001
2014    1   3   NULL          NULL      28033.0000  3001
2016    3   2   502757.0000   NULL      NULL        3002
2016    3   2   NULL          NULL      56407.0000  3002
2016    2   2   429821.0000   NULL      NULL        3002
2016    2   2   NULL          516.0000  NULL        3002
2016    2   2   NULL          NULL      70724.0000  3002
2015    2   2   NULL          6092.0000 NULL        3002
2015    2   2   NULL          NULL      96377.0000  3002
2015    2   2   598416.0000   NULL      NULL        3002
2015    3   2   677026.0000   NULL      NULL        3002
2015    3   2   NULL          NULL      NULL        3002
2015    1   3   NULL          NULL      92406.0000  3002
2014    1   3   NULL          NULL      84243.0000  3002

이제 나는 두 가지 문제에 직면한다 :

  • 왜냐하면 TPH (많은 nulls가 있습니다)가 있으므로 한 줄에 각 회사의 동일한 ( year,period,periodTypeId )을 가진 모든 행을 병합하고 싶습니다.

EX : 세 개의 행 대신 각 테이블 (유형)에 하나씩 :

  2016  2   2   164547.0000   NULL      NULL        3001
  2016  2   2   NULL          NULL      50601.0000  3001
  2016  2   2   NULL          550.4110  NULL        3001

  2015  2   2   NULL          NULL      42049.0000  3001
  2015  2   2   NULL          614.6200  NULL        3001
  2015  2   2   187112.0000   NULL      NULL        3001

나는 이처럼 한 줄을 원한다.

  2016  2   2   164547.0000   550.4110  50601.0000    3001
  2015  2   2   187112.0000   614.6200  42049.0000    3001
  • 이렇게 계산 된 방정식을 원한다면

(The accumulated number of days in the quarter ( 90 or 180 0r 270 or 360 ) / ( NetSales / (( Receivables in the specific quarter Receivables in the previous yearly year + Receivables in the previous yearly year ) / 2))

그래서 예를 들어 만약이 period2 of quarter 동안 period2 of quarter 에서 ( 3001 ) 회사에 대해이 방정식을 계산하려면 다음과 같이됩니다 :

180/( 164547.0000/((50601.0000 +32431.0000)/2)) =45 (for year 2016)
180/( 187112.0000/((42049.0000 +28033.0000)/2)) =34 (for year 2015)

내 검색어 :

SELECT a.[Year],d.period,d.PeriodTypeId, a.NetSales,a.Expenses,a.Receivables,
c.CompanyId
FROM FinanceList a INNER JOIN Company c 
ON a.CompanyId = c.CompanyId
INNER JOIN ListPeriod d 
ON d.FinanceListId = a.FinanceListId 
WHERE a.[Year] IN (2016,2015) AND d.PeriodTypeId IN(2,3) --The User Enter only Two Years ,PeriodTypeId ==>2 means quarter ,3 means yearly
ORDER BY c.CompanyId, a.[Year] DESC

내가 원하는 결과 최종 결과 각 회사에 대한 두 가지 기록 :

 Year   per TypeId   NetSales     Expenses  Receivables CompanyId   equation
 2016   2   2      164547.0000    550.4110  50601.0000    3001      45
 2015   2   2      187112.0000    614.6200  42049.0000    3001      34

수락 된 답변

나는 이것이 집계 함수에 의해 실현 될 수 있다고 생각한다. 공통 표 식을 사용하여 검색된 기간과 년 및 이전 연도를 선택하십시오. 결과와 계산을 위해 반환 된 데이터를 처리합니다. 참고 : 다음 쿼리는 구문적으로 만 테스트되었습니다.

WITH data AS (
  SELECT a.[Year], 
         d.period, 
         d.PeriodTypeId, 
         SUM(a.NetSales) AS NetSales, 
         SUM(a.Expenses) AS Expenses, 
         SUM(a.Receivables) AS Receivables, 
         c.CompanyId
    FROM FinanceList a 
         INNER JOIN Company c 
                 ON a.CompanyId = c.CompanyId
         INNER JOIN ListPeriod d 
                 ON d.FinanceListId = a.FinanceListId 
   WHERE a.[Year] IN (2016, 2015) AND d.PeriodTypeId IN(2, 3)
      OR a.[Year] IN (2016 - 1, 2015 - 1) AND d.PeriodTypeId = 3 -- previous years
   GROUP BY a.[Year], d.period, d.PeriodTypeId, c.CompanyId
)
SELECT [Year], 
       period, 
       PeriodTypeId, 
       NetSales, 
       Expenses, 
       Receivables, 
       CompanyId,
       CASE
         WHEN PeriodTypeId = 2
         THEN (period * 90) / (NetSales / ((Receivables + (SELECT Receivables
                                                             FROM data T2
                                                            WHERE T2.[Year]       = T1.[Year] - 1
                                                              AND T2.period       = 1
                                                              AND T2.PeriodTypeId = 3
                                                              AND T2.CompanyId    = T1.CompanyId) /* Receivables previous year */) / 2))
         ELSE NULL
       END AS equation
  FROM data T1
 WHERE [Year] IN (2016, 2015) AND PeriodTypeId IN(2, 3)
 ORDER BY CompanyId, [Year] DESC


아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow