Insert with Calculation

[mansoorabid]

I want to insert into select from Table1 to Table2

If the DeptName is xx, the record should be inserted twice with no entry for the

location, FederalTax, CountyTax and PropertyRatio

Table1 has no constraints

Table2 has EmpNo with Identity seed

Below are the calculations for

Amount paid for EmpNo3 is Amount paid-CountTax (333-3=330)

Amount Paid for EmpNo4 is the CountyTax 3

FederalTax for EmpNo3 is the AmountPaid * PropertyRatio/100 (330*3/100=108.9)

if the DeptName is xx then deductable for duplicated recored is 999

if the DeptName is xx then deductable for duplicated recored is XXX

CREATE TABLE [dbo].[Table1](

[EmpNo] [int] NULL,

[EmpName] [nchar](10) NULL,

[DeptName] [nchar](10) NULL,

[Location] [nchar](10) NULL,

[AmountPaid] [int] NULL,

[FederalTax] [int] NULL,

[CountyTax] [int] NULL,

[PropertyRatio] [int] NULL,

[Deductable] [int] NULL,

[TaxCode] [nchar](10) NULL

) ON [PRIMARY]

GO

INSERT INTO [Table1] VALUES (1,'Name1','aa','usa',111,91,1,11,101,'ABC')

INSERT INTO [Table1] VALUES (2,'Name2','bb','uk',222,92,2,22,102,'ABC')

INSERT INTO [Table1] VALUES (3,'Name3','xx','Ind',333,93,3,33,103,'ABC')

INSERT INTO [Table1] VALUES (4,'Name4','cc','Ksa',444,94,4,44,104,'ABC')

INSERT INTO [Table1] VALUES (5,'Name5','dd','Ger',555,95,5,55,105,'ABC')

INSERT INTO [Table1] VALUES (6,'Name6','ee','usa',666,96,6,66,106,'ABC')

INSERT INTO [Table1] VALUES (7,'Name7','xx','Ksa',777,97,7,77,107,'ABC')

INSERT INTO [Table1] VALUES (8,'Name8','ff','Ger',888,98,8,88,108,'ABC')

INSERT INTO [Table1] VALUES (9,'Name9','gg','Uk',999,99,9,99,109,'ABC')

INSERT INTO [Table1] VALUES (10,'Name10','xx','usa',1110,100,10,110,110,'ABC')

GO

CREATE TABLE [dbo].[Table2](

[EmpNo] [int] IDENTITY(1,1) NOT NULL,

[EmpName] [nchar](10) NULL,

[DeptName] [nchar](10) NULL,

[Location] [nchar](10) NULL,

[AmountPaid] [int] NULL,

[FederalTax] [int] NULL,

[CountyTax] [int] NULL,

[PropertyRatio] [int] NULL,

[Deductable] [int] NULL,

[TaxCode] [nchar](10) NULL

) ON [PRIMARY]

GO

 

[Skydiver]

Show us how you resolved your other thread and then we can help you build upon that to add in your new requirements.