Insert with Calculation

mansoorabid

New member
Joined
Dec 20, 2021
Messages
2
Programming Experience
1-3
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




C#:
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
 
Last edited by a moderator:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
4,431
Location
Chesapeake, VA
Programming Experience
10+
Show us how you resolved your other thread and then we can help you build upon that to add in your new requirements.
 
Top Bottom