How add the data by using ParentId and DepartmentId relation

• WayneS

  SSC Guru

  Points: 95461

  More actions

  January 21, 2010 at 10:25 pm

  #1107086

  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

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• mohanaraj

  SSC-Addicted

  Points: 406

  More actions

  January 21, 2010 at 10:46 pm

  #1107093

  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