January 21, 2010 at 9:51 pm
Hello,
I have a table with the column of DepartmentId, ParentId, I/C, Mobile etc
the I/C, Mobile column have the cost of that department. The parent and child departments are identified by the column DepartmentId, ParentId
eg;
DepartmentId --ParentId ----Name----- I/C --- Mobile
10 -------------- 0 --------Foods------12.00---01.00
11---------------10--------Production--0.12---01.20
12---------------11--------spices------1.12---00.12
Here Foods is the parent and production is the child then spices is the child to production.
Each department having summary of its own in I/C, Mobile.
I want the spices's summary result should be added to the production's summary ie 1.12 +0.12 and 01.20 + 00.12,
and then the production summary is added to the Foods summary.
How can i achieve this in sql.
The child level is N number.
Thanks in advance.
January 21, 2010 at 10:25 pm
You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 21, 2010 at 10:46 pm
Hello,
Here i add code for the sample data .
CREATE TABLE [dbo].[TestingReportTableORG](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[DepartmentId] [bigint] NULL,
[ParentId] [bigint] NULL,
[WorkPlace] [varchar](255) NULL,
[decimal](18, 2) NULL,
[Mobile] [decimal](18, 2) NULL,
[Free] [decimal](18, 2) NULL,
[IDD] [decimal](18, 2) NULL,
[Local] [decimal](18, 2) NULL,
[Operator] [decimal](18, 2) NULL,
[InfoCall] [decimal](18, 2) NULL,
[13Call] [decimal](18, 2) NULL,
[STD] [decimal](18, 2) NULL,
[Others] [decimal](18, 2) NULL,
[Total] [decimal](18, 2) NULL,
[IsTotal] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[TestingReportTableORG] ON
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (1, 0, 0, N'Unassigned', CAST(31.00 AS Decimal(18, 2)), CAST(11.75 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.36 AS Decimal(18, 2)), CAST(16.30 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.12 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(61.53 AS Decimal(18, 2)), 0)
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (2, 10, 0, N'SuzlanFoods', CAST(3.00 AS Decimal(18, 2)), CAST(126.68 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.30 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.92 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.22 AS Decimal(18, 2)), CAST(140.12 AS Decimal(18, 2)), 1)
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (3, 11, 10, N'Production', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (4, 12, 10, N'Package', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (5, 13, 10, N'Marketing', CAST(3.00 AS Decimal(18, 2)), CAST(22.01 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.12 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(26.13 AS Decimal(18, 2)), 0)
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (6, 14, 11, N'Spices', CAST(0.00 AS Decimal(18, 2)), CAST(3.80 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3.30 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(7.10 AS Decimal(18, 2)), 0)
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (7, 15, 11, N'Masala', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (8, 16, 12, N'Box', CAST(0.00 AS Decimal(18, 2)), CAST(50.56 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2.22 AS Decimal(18, 2)), CAST(52.78 AS Decimal(18, 2)), 0)
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (9, 17, 12, N'Packets', CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), CAST(1.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (10, 1343, 0, N'SuzlanParcel', CAST(0.00 AS Decimal(18, 2)), CAST(29.19 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(101.28 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(22.16 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(2.24 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(154.87 AS Decimal(18, 2)), 1)
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (11, 1344, 1343, N'Spliting', CAST(0.00 AS Decimal(18, 2)), CAST(10.80 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(11.08 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.12 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(23.00 AS Decimal(18, 2)), 0)
INSERT [dbo].[TestingReportTableORG] ([Id], [DepartmentId], [ParentId], [WorkPlace], , [Mobile], [Free], [IDD], [Local], [Operator], [InfoCall], [13Call], [STD], [Others], [Total], [IsTotal]) VALUES (12, 1345, 1343, N'Grouping', CAST(0.00 AS Decimal(18, 2)), CAST(18.39 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(101.28 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(11.08 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(1.12 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(131.87 AS Decimal(18, 2)), 0)
SET IDENTITY_INSERT [dbo].[TestingReportTableORG] OFF
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply