So I have a sql statement that breaks sales out by a few groups. This is then grouped by several fields. I need to add one more field to this statement that is percentage of total of last year.

Here is the SQL that I have working now.

This is the sql that with out other fields or group by gives me the total sales for last year.

I would like to add another calculated field that is the Last FY Sales / Last FY Total I tried to do it in a derived table and just pass the total value but I could not get it to work.

Here is the SQL that I have working now.

```
SELECT OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep,
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LAST FY Sales',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END)AS 'YTD Sales',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END) AS 'LYTD Sales',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END) - SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END)as 'YTD VAR',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LFM Sales',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LYFM Sales',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)-SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)as 'LFM VAR YOY'
FROM OINV INNER JOIN
INV1 ON OINV.DocEntry = INV1.DocEntry INNER JOIN
INV12 ON OINV.DocEntry = INV12.DocEntry INNER JOIN
OCRD ON OINV.CardCode = OCRD.CardCode INNER JOIN
CRD1 ON OCRD.CardCode = CRD1.CardCode AND INV1.ShipToCode = CRD1.Address
GROUP BY OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep
```

This is the sql that with out other fields or group by gives me the total sales for last year.

```
SELECT SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 THEN INV1.Quantity * INV1.Price ELSE 0 END) AS 'Last FY Total'
FROM OINV INNER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry
```

I would like to add another calculated field that is the Last FY Sales / Last FY Total I tried to do it in a derived table and just pass the total value but I could not get it to work.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

```
SELECT OINV.CardName,
INV12.CityS,
INV12.StateS,
CRD1.U_SIF_OSRep,
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END) AS [LAST FY Sales],
(SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END) * 100) / [Last FY Total] AS [Last FY Sales %],
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END) AS [YTD Sales],
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END) AS [LYTD Sales],
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END) - SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END)as [YTD VAR],
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS [LFM Sales],
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS [LYFM Sales],
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)-SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)as [LFM VAR YOY]
FROM OINV
INNER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry
INNER JOIN INV12 ON OINV.DocEntry = INV12.DocEntry
INNER JOIN OCRD ON OINV.CardCode = OCRD.CardCode
INNER JOIN CRD1 ON OCRD.CardCode = CRD1.CardCode AND INV1.ShipToCode = CRD1.Address
INNER JOIN (SELECT DocEntry,
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 THEN INV1.Quantity * INV1.Price ELSE 0 END) AS 'Last FY Total'
FROM OINV) LFYT
ON LFYT.DocEntry = OINV.DocEntry
GROUP BY OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep, [Last FY Total]
```

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialYou can have either nested derived tables or two CTEs prior to the outer query if it is necessary.

your solution just gave me fields that had no headers with the field names in the fields. I have modified based on dsacker's comments and got it to work with the place holder.

```
Select [CardName], [CityS], [StateS], [U_SIF_OSRep],[LAST FY Sales], [YTD Sales], [LYTD Sales], [YTD VAR], [LFM Sales], [LYFM Sales], [LFM VAR YOY], 1 + 2 As 'Percntage'
From
(
-- your existing code starts here
SELECT OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep,
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LAST FY Sales',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END)AS 'YTD Sales',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END) AS 'LYTD Sales',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) THEN INV1.Quantity*INV1.Price Else 0 END) - SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) - 1 AND OINV.DocDate <= DATEADD(year, -1, GETDATE()) THEN INV1.Quantity*INV1.Price Else 0 END)as 'YTD VAR',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LFM Sales',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)AS 'LYFM Sales',
SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate()) and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)-SUM(CASE WHEN YEAR(OINV.DocDate) = YEAR(getdate())-1 and month(oinv.docdate) = month(getdate())-1 THEN INV1.Quantity*INV1.Price Else 0 END)as 'LFM VAR YOY'
FROM OINV INNER JOIN
INV1 ON OINV.DocEntry = INV1.DocEntry INNER JOIN
INV12 ON OINV.DocEntry = INV12.DocEntry INNER JOIN
OCRD ON OINV.CardCode = OCRD.CardCode INNER JOIN
CRD1 ON OCRD.CardCode = CRD1.CardCode AND INV1.ShipToCode = CRD1.Address
GROUP BY OINV.CardName, INV12.CityS, INV12.StateS, CRD1.U_SIF_OSRep
-- your existing code endss here
) As t
```

Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

Please run this to see if it produces what you have now before adding the actual % column.

Open in new window

Mike