November 11, 2015 at 8:46 pm
Hi Guys how to add a Year to date Aggregate to this query to return a column of MTD and YTD respectively?
I am using SQL Server 2012 and this is a report developed in VS
This query is returning the total cost, the revenue .... Please look at the excel report attached.
Thanks
SELECT
Y.CpnyID,
RTRIM(Y.CustID) + ' ' + RTRIM(Y.Name) AS Client,
Y.project AS Project,
Y.contract,
Y.project_desc AS ProjectDescription,
Y.pjt_entity,
Y.pjt_entity_desc AS [Cost Code],
dbo.fsReverseNameWithTilde(Y.emp_name) AS Manager,
Y.start_date AS [Start Date],
Y.end_date AS [End Date],
ROUND(SUM(Y.[Contract Value]), 0) AS BudgetRevenue,
ROUND(SUM(Y.[Original Cost]), 0) AS [Original Cost],
ISNULL(MAX(CRA.CostRevisionAmount), 0) AS CostRevisionAmount,
ISNULL(MAX(CRARev.RevenueRevisionAmount), 0) AS REVRevisionAmount,
ROUND(SUM(Y.[JTD Costs]), 0) AS [JTD Costs],
ROUND(SUM(Y.[Contract Value]), 0) - ROUND(SUM(Y.[Billed Amt]), 0) AS Backlog,
ROUND(SUM(Y.RTD), 0) AS RTD,
ROUND(SUM(Y.[Contract Value]), 0) - ROUND(SUM(Y.RTD), 0) AS [RTD Backlog],
CASE
WHEN SUM([Billed Amt]) != 0
THEN Round((SUM([Billed Amt]) - SUM([JTD Costs])) / SUM([Billed Amt]), 2)
ELSE 0 END AS [Rev GPM%],
CASE WHEN SUM([Contract Value]) != 0 AND SUM([Original Cost]) != 0
THEN Round((SUM([Contract Value]) - SUM([Original Cost])) / SUM([Contract Value]), 2)
ELSE 0
END AS [Bdgt GPM%], ROUND(MAX(Y.Commitments), 0) AS Commitments
FROM
(
SELECT
CpnyId,
CustId,
Name,
project,
contract,
pjt_entity,
pjt_entity_desc,
project_desc,
start_date,
end_date,
emp_name,
CASE WHEN column_nbr = 6 AND acct_type = 'RV'
THEN SUM(eac_amount)
ELSE 0
END AS [Contract Value],
CASE WHEN acct_type = 'RV' AND column_nbr = 7
THEN SUM(act_amount)
ELSE 0
END AS [Billed Amt],
CASE WHEN acct_type = 'EX' AND column_nbr = 5
THEN SUM(eac_amount)
ELSE 0
END AS [Original Cost],
CASE WHEN acct_type = 'EX' AND column_nbr = 4
THEN SUM(act_amount)
ELSE 0
END AS [JTD Costs],
CASE WHEN acct_type = 'RV' AND column_nbr = 3
THEN SUM(act_amount)
ELSE 0
END AS RTD, MAX(com_amount) AS Commitments
FROM
(
SELECT DISTINCT
pjptdsum.project,
C.CustId,
C.Name,
pjproj.contract,
pjpent.pjt_entity_desc,
pjptdsum.pjt_entity,
pjptdsum.acct,
pjrepcol.column_nbr,
pjproj.project_desc,
pjactsum.fsyear_num,
pjactsum.amount_bf,
pjptdsum.act_amount,
pjproj.gl_subacct,
pjproj.manager1,
pjproj.status_pa,
PJE.emp_name,
pjacct.acct_type,
pjptdsum.eac_amount,
pjacct.sort_num,
pjproj.CpnyId,
pjproj.manager2,
pjproj.start_date,
pjproj.end_date,
pjptdsum.com_amount
FROM
PJPTDSUM AS pjptdsum WITH (nolock)
INNER JOIN
PJPENT AS pjpent ON pjptdsum.pjt_entity = pjpent.pjt_entity AND pjptdsum.project = pjpent.project
INNER JOIN
PJREPCOL AS pjrepcol WITH (nolock) ON pjptdsum.acct = pjrepcol.acct
INNER JOIN
PJPROJ AS pjproj WITH (nolock) ON pjptdsum.project = pjproj.project
INNER JOIN
Customer AS C ON pjproj.customer = C.CustId
INNER JOIN
PJACCT AS pjacct WITH (nolock) ON pjptdsum.acct = pjacct.acct
LEFT OUTER JOIN
PJACTSUM AS pjactsum WITH (nolock) ON pjptdsum.acct = pjactsum.acct AND pjptdsum.pjt_entity = pjactsum.pjt_entity AND pjptdsum.project = pjactsum.project
LEFT OUTER JOIN
PJEMPLOY AS PJE WITH (nolock) ON PJE.employee = pjproj.manager1
WHERE
(pjproj.status_pa = 'A') AND (pjactsum.fsyear_num = CASE WHEN Month(DateAdd(mm, - 1, GetDate())) = 12 THEN Year(GetDate()) - 1 ELSE Year(GetDate()) END
OR pjactsum.fsyear_num IS NULL) AND (pjrepcol.column_nbr IN ('3', '4', '5', '6', '7')) AND (LEFT(pjptdsum.pjt_entity, 1) <> 0)
) AS X
GROUP BYCpnyId,
CustId,
Name,
contract,
project,
project_desc,
pjt_entity,
pjt_entity,
pjt_entity_desc,
sort_num, acct,
column_nbr,
fsyear_num,
acct_type,
start_date,
end_date,
emp_name
) AS Y
LEFT OUTER JOIN
(
SELECT
A.project,
A.pjt_entity,
ISNULL(SUM(A.Amount), 0) AS CostRevisionAmount
FROM
PJREVCAT AS A WITH (nolock)
INNER JOIN
PJREVHDR AS B WITH (nolock) ON B.Project = A.project AND B.RevId = A.RevId
INNER JOIN
PJPROJ AS PJ WITH (nolock) ON PJ.project = A.project AND B.Change_Order_Num <> ''
INNER JOIN
PJACCT AS PJACCT WITH (nolock) ON PJACCT.acct = A.Acct
WHERE
(A.Amount <> 0) AND (PJ.status_pa = 'A') AND (PJACCT.acct_type = 'EX')
GROUP BYA.project,
PJ.project_desc,
A.pjt_entity,
PJACCT.acct_type
) AS CRA ON CRA.project = Y.project AND CRA.pjt_entity = Y.pjt_entity
LEFT OUTER JOIN
(
SELECT
A.project,
A.pjt_entity,
ISNULL(SUM(A.Amount), 0) AS RevenueRevisionAmount
FROM
PJREVCAT AS A WITH (nolock) INNER JOIN
PJREVHDR AS B WITH (nolock) ON B.Project = A.project AND B.RevId = A.RevId
INNER JOIN
PJPROJ AS PJ WITH (nolock) ON PJ.project = A.project AND B.Change_Order_Num <> ''
INNER JOIN
PJACCT AS PJACCT WITH (nolock) ON PJACCT.acct = A.Acct
WHERE
(A.Amount <> 0) AND (PJ.status_pa = 'A') AND (PJACCT.acct_type = 'RV')
GROUP BYA.project,
PJ.project_desc,
A.pjt_entity,
PJACCT.acct_type
)AS CRARev ON CRARev.project = Y.project AND CRARev.pjt_entity = Y.pjt_entity
WHERE
(Y.project LIKE RTRIM(@Project) + '%')
AND (RTRIM(Y.Name) LIKE @CustomerName + '%')
AND (RTRIM(Y.CpnyId) = @CpnyID)
AND (RTRIM(dbo.fsReverseNameWithTilde(Y.emp_name)) LIKE @ManagerName + '%')
November 11, 2015 at 10:29 pm
What version of SQL Server are you using? You can create a running sum using a windowing function ... something like this:
SELECT Protocol
, [Year]
, Weekno
, [Weekly Count]
, SUM([Weekly Count]) OVER (PARTITION BY Protocol
ORDER BY [Year], WeekNo
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS RunTotalCount
FROM dbo.vw_enrollCounts
WHERE WeekNo IS NOT NULL;
If you're using an earlier version of SQL Server, Windowing functions are not available and you'd have to do it a different way. (Jeff Moden's Quirky Update method?)
November 12, 2015 at 6:48 am
using SQL Server 2012
November 12, 2015 at 9:00 am
look up windowing functions in BOL. Maybe read Itzik Ben-Gan's book on it. He has a bunch of examples, and explains how to go about doing things like this. Basically, it's a running total (using a windowing function) that is partitioned by Year.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply