April 14, 2014 at 8:34 am
Hi, I have two views: ytd and ytdper
I'd like to combine the two views into one but I'm not sure how to do it. The difference between the two views is the val calculation is based on a different glpernum where clause as in "SELECT (glpernum-100) / 100 * 100" for ytd and "SELECT glpernum / 100 * 100" for ytdper
ytd view code:
WITH MyCTE AS (SELECT TOP (1) nomcode AS Retained
FROM dbo.ctlfil
WHERE (recid = 24)
ORDER BY Retained)
SELECT dbo.nomfil.nomcode, ISNULL(calc.val, 0) AS ytd, calc.costcentre
FROM dbo.nomfil LEFT OUTER JOIN
(SELECT CASE WHEN nomfil_1.type IN (3, 4) THEN nomtrn.nomcode ELSE MyCTE_2.Retained END AS nomcode,
SUM(dbo.nomtrn.val * (1 - 2 * dbo.nomtrn.crddbt)) AS val, dbo.nomtrn.costcentre
FROM dbo.nomtrn INNER JOIN
dbo.nomfil AS nomfil_1 ON dbo.nomtrn.nomcode = nomfil_1.nomcode CROSS JOIN
MyCTE AS MyCTE_2
WHERE (dbo.nomtrn.period <
(SELECT (glpernum - 100) / 100 * 100 AS Expr1
FROM dbo.perfil))
GROUP BY CASE WHEN nomfil_1.type IN (3, 4) THEN nomtrn.nomcode ELSE MyCTE_2.Retained END, dbo.nomtrn.costcentre) AS calc ON
calc.nomcode = dbo.nomfil.nomcode
ytdper code:
WITH MyCTE AS (SELECT TOP (1) nomcode AS Retained
FROM dbo.ctlfil
WHERE (recid = 24)
ORDER BY Retained)
SELECT dbo.nomfil.nomcode, ISNULL(calc.val, 0) AS ytdper, calc.costcentre
FROM dbo.nomfil LEFT OUTER JOIN
(SELECT CASE WHEN nomfil_1.type IN (3, 4) THEN nomtrn.nomcode ELSE MyCTE_1.Retained END AS nomcode,
SUM(dbo.nomtrn.val * (1 - 2 * dbo.nomtrn.crddbt)) AS val, dbo.nomtrn.costcentre
FROM dbo.nomtrn INNER JOIN
dbo.nomfil AS nomfil_1 ON dbo.nomtrn.nomcode = nomfil_1.nomcode CROSS JOIN
MyCTE AS MyCTE_1
WHERE (dbo.nomtrn.period <
(SELECT glpernum / 100 * 100 AS Expr1
FROM dbo.perfil))
GROUP BY CASE WHEN nomfil_1.type IN (3, 4) THEN nomtrn.nomcode ELSE MyCTE_1.Retained END, dbo.nomtrn.costcentre) AS calc ON
calc.nomcode = dbo.nomfil.nomcode
April 15, 2014 at 7:30 am
Posting scripts for table structure and script for some sample data will get a better response.
In general, it sounds like if you have a where statement that grabs YTD info, then 2 case statements, one that sums YTD, one for current Month, each as a column, that is what you are looking for.
April 15, 2014 at 7:35 am
Just a side note - I come from an SSAS cube background.
So these end up as dimensions.
Simplifies doing this quite a bit.
Especially when you combine with other dimensions, as you can see many intersections very quickly and easily, and at many levels.
Company, Department, Work Center.
Yesterday, MTD, YTD, Prior YTD, ect.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply