How to merge rows with null values in TPH and calculate dependant fields

Question

How to merge rows with null values in TPH and calculate dependant fields

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

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 merge rows with null values in TPH and calculate dependant fields

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related