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 :
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
(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
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