How to compute dependent fields and combine rows with null values in TPH

Question

How to compute dependent fields and combine rows with null values in TPH

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

Question

If I have a De-normalized table like this :

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

And I have another table (PeriodType) which i categorize the year according to two methods :

Quarter (4 periods)

1-Period1 From Jan to Mar

2-Period2 From Apr to Jun

3-Period3 From Jul to Sep

4-Period4 From Oct to Dec
Yearly (1 period) From Jan to Dec

SAMPLE DATA :

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

Now i face two problems :

Because I follow TPH (many nulls are there) so I want to merge all rows that have the same(year,period,periodTypeId) for each company in one row .

EX :Instead of three rows, one for each table(type):

  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

I want one row like this :

  2016  2   2   164547.0000   550.4110  50601.0000    3001
  2015  2   2   187112.0000   614.6200  42049.0000    3001

If I want to get an equation which calculated like this

(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 )/2))

So For Example If want to calc this equation for (3001) Company in the period2 of quarter it will be like this :

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

My query :

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

I want Final result like this Two records for each Company:

 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

Anyname Donotcare

Related Questions

View more on Stack Overflow

fbcomps · Accepted Answer · 11/20/2016 7:34:38 PM

I think this can be realized by aggregation functions. Use a Common Table Expression to select the searched periods and years and its previous years. Process the returned data for the result and the calculations. Note: I have the following query only syntactically tested.

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

How to compute dependent fields and combine rows with null values in TPH

Question

Fastest Entity Framework Extensions

Accepted Answer

Related Questions

Fastest Entity Framework Extensions

Performance

Expression Evaluator

Related