My project contains below TABLES and sample data for it ::
CREATE TABLE [DBO].[TBL_Budget] (
BudgetId INT IDENTITY(1, 1) NOT NULL
,BudgetName NVARCHAR(200) NOT NULL
,BudgetType INT NOT NULL
,BudgetAmount INT
,CONSTRAINT PK_Budget PRIMARY KEY (BudgetId)
)
BudgetId BudgetType BudgetName BudgetAmount
1 101 B1 5,00,000
2 201 B2 10,00,000
CREATE TABLE [DBO].[TBL_School] (
SchoolId INT IDENTITY(1, 1) NOT NULL
,SchoolName NVARCHAR(200) NOT NULL
,CONSTRAINT PK_School PRIMARY KEY (SchoolId)
)
SchoolId SchoolName
1 NJ.EBS
2 LA.EBS
CREATE TABLE [DBO].[TBL_Mapped] (
MappedId INT IDENTITY(1, 1) NOT NULL
,MappedType INT NOT NULL --- {1st quarter, 2nd quarter, 3rd quarter}
,MappedAmount INT
,BudgetId INT NOT NULL
,SchoolId INT NOT NULL
,CONSTRAINT PK_Mapped PRIMARY KEY (MappedId)
)
MappedId MappedType MappedAmount BudgetId SchoolId
1 1 1,00,000 1 1
2 1 3,00,000 1 2
3 2 6,00,000 2 2
4 2 2,00,000 2 1
5 2 1,00,000 1 1
I have to make a report such that how much amount from each budget is given to schools in quarterly basis.
Please see the attached Report Format data, I can't provide here because the data does not fit into the window.
How to get this report format data? I have tried with this query but not getting the desired result. Also MS SQL Server does not allow to group by in the columns which are not contained in function. What will be the query to get the desired result?
SELECT
A.BudgetId
,A.BudgetName
,A.BudgetAmount
,B.MappedType
,SUM(B.MappedAmount) [TotalMappedAmount]
FROM [DBO].[TBL_Budget] A
INNER JOIN [DBO].[TBL_Mapped] B ON A.BudgetId = B.BudgetId
GROUP BY
A.BudgetId
,B.MappedType