Hello Everyone,
I have this code and I want to pass the result of the total from LESH to another column Here is the code
Thank you for your reply,
Here is some example:
|
Total
|
Year
|
Department
|
Lesh
|
|
38.04
|
2020-01
|
Grocery
|
1
|
|
269.24
|
2020-03
|
Grocery
|
2
|
|
611.95
|
2020-04
|
Grocery
|
7
|
|
665.81
|
2020-05
|
Grocery
|
11
|
|
388.88
|
2020-06
|
Grocery
|
5
|
|
412.76
|
2020-07
|
Grocery
|
14
|
|
Total
|
|
|
14
|
This is how I get the results, and what I want is like this
|
Total
|
Year
|
Department
|
Lesh
|
|
38.04
|
2020-01
|
Grocery
|
14
|
|
269.24
|
2020-03
|
Grocery
|
|
|
611.95
|
2020-04
|
Grocery
|
|
|
665.81
|
2020-05
|
Grocery
|
|
|
388.88
|
2020-06
|
Grocery
|
|
|
412.76
|
2020-07
|
Grocery
|
|
;WITH CTE AS
(
SELECT
CAST(SUM(Price) AS VARCHAR(20)) As Total,
CONVERT(char(7), date, 120) As [Year],
Department
,COUNT(ISNULL(CONVERT(INT, CONVERT(VARCHAR(MAX),quantity)),0)) as Lesh
FROM dbo.Expenses
WHERE
CONVERT(char(7), date, 120) Between '2020-01' And '2020-07' AND Department = 'FixCost'
GROUP BY CONVERT(char(7), date, 120), Department
)
SELECT * FROM CTE
UNION ALL
SELECT 'Total','','',Total FROM
(
SELECT TOP 1 COUNT(CONVERT(char(7), date, 120)) Total
FROM dbo.Expenses
WHERE CONVERT(char(7), date, 120) Between '2020-01' And '2020-07' AND Department = 'FixCost'
GROUP BY CONVERT(char(7), date, 120)
ORDER BY COUNT(CONVERT(char(7), date, 120)) DESC
)t
Thank you for your patience, Elvis
