Complicated Insert SQL with Calculations
-
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
- This topic was modified 2 years, 10 months ago by mansoorabid.
-
First, the Excel view is nice to see, but really for someone to help answer, you should have a CREATE TABLE and INSERT statement to set things up.
Second, build the insert as a SELECT first. If you can get the SELECT working, you can run INSERT .. SELECT.
For this, I'd tackle this as multiple inserts, which will help test your logic. For the duplicates, since you have different logic, I'd use a query to extract those out and then calculate the insert for the duplicate separate from the insert for the original row.
For your calculation, you need general logic. Not logic for a specific row. If you have something for a specific row, then that's a separate insert, IMHO.
-
A very similar question of yours has already been answered here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I have additional colums with Caluculations, plz chk
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
-
It's not clear what you mean. If you need to just insert a few rows, then write insert statements to do that. You haven't provided much logic that would work for multiple rows. You could do :
INSERT INTO [Table2] VALUES ('Name1','aa','usa',111,91,1,11,101,'ABC')
INSERT INTO [Table2] VALUES ('Name2','bb','uk',222,92,2,22,102,'ABC')
INSERT INTO [Table2] VALUES ('Name3','xx','Ind',330,108.9,3,33,103,'ABC')
INSERT INTO [Table2] VALUES ('Name3','xx','Ind',3,0,0,0,999,'ABC')Just build the insert statements. If you want to build those from a SELECT, you could do that, but if you are trying to ensure you have some sort of ordering based on the identity, that's a mistake. Really, you ought to SET IDENTITY_INSERT OFF and then insert the values you need.
-
THE PREVIOUS CODE I HAD POSTED PRODUCED THE WRONG VALUES FOR THE 1st ROW OF FEDERALTAX OF THE "XX" ROWS. I HAD COPIED YOUR FORMULA WITHOUT REALIZING THERE WAS A "PRE-FORMULA" INVOLVED. THE FORMULA HAS BEEN UPDATED AND THE CODE NOW WORKS AS EXPECTED.
Thank you for the readily consumable test data.If you actually want blanks in the output then this will generate the output to populate Table2. Since blanks will be convert to "0" in numeric columns, the columns that contain blanks will need to be changed to a character based datatype. I'll leave that chore and the actual INSERT INTO dbo.Table2 up to you. You have to have some of the fun! 😀
WITH cte AS
(--==== This is nearly identical to what Scott Pletcher had posted on the other thread
-- The "N" column is the additional secret sauce for the outer query.
SELECT N=1,* FROM dbo.Table1 UNION ALL
SELECT N=2,* FROM dbo.Table1 WHERE DeptName = 'XX'
)--==== The rest is all just brute force according to the calculation rules.
-- Notice that I changed EmpNo to "stay with" the EmpName and will be duplicated on "xx" rows.
-- That seemed to be more logical to me than what you had in your final output.
-- If that's not what you actually want, then change it to a ROW_NUMBER() OVER (ORDER BY EmpNo,N)
SELECT EmpNo
,EmpName
,DeptName
,[Location] = CASE WHEN N = 1 THEN TaxCode ELSE '' END
,AmountPaid = CASE
WHEN N = 1 AND DeptName <> 'xx' THEN AmountPaid
WHEN N = 1 AND DeptName = 'xx' THEN AmountPaid-CountyTax
WHEN N = 2 THEN LAG(CountyTax,1,NULL) OVER (PARTITION BY EmpNo ORDER BY N) --A bit of 2012 "magic"
END
,FederalTax = CASE
WHEN N = 1 AND DeptName <> 'xx' THEN CONVERT(VARCHAR(13),FederalTax)
WHEN N = 1 AND DeptName = 'xx' THEN CONVERT(VARCHAR(13),CONVERT(DECIMAL(13,1),(AmountPaid-CountyTax)*PropertyRatio/100.0))
WHEN N = 2 THEN ''
END
,CountyTax = CASE WHEN N = 1 THEN CONVERT(VARCHAR(13),CountyTax) ELSE '' END
,PropertyRatio = CASE WHEN N = 1 THEN CONVERT(VARCHAR(13),PropertyRatio) ELSE '' END
,Deductable = CASE WHEN N = 1 THEN Deductable ELSE '999' END
,TaxCode = CASE WHEN N = 1 THEN TaxCode ELSE 'PAY' END
FROM cte
ORDER BY EmpNo,N
;Here's the output from the code above:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
So, enquiring minds want to know... did that latest code help?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Did you know that by definition, a table must have a key? We cannot have NOT NULLs, so what you post can never be a table. You also have more NOT NULLs in this one table than I've had accounting systems for major corporations. People who work with punchcards often make everything NOT NULL because it looks like a punch card with no holes in it. SQL programmers use default values and constraints on the columns of their tables.
You also have no idea what normalization is. Departments and employees are two very different kinds of entities. Each would have its own table and they would be related by job assignments. But when you're trying to do display formatting in a query, beginners will often make this mistake.
I also see the use of integer data types just like we would with punchcards, which could only hold character strings and have the decimal places were assigned by the program reading them. I'm going to guess your tax amounts are actually decimal values in a currency.
I don't have any specs from you, so here's my guess of corrections to your attempt at DDL. I also would like to remind you that tables represent sets so they have specific names, you're still using the old convention of numbering your card readers or tape drive numbers instead of giving them a meaningful name.
CREATE TABLE Employee_Taxes
(emp_nbr CHAR(10) NOT NULL PRIMARY KEY REFERENCES Personnel,
something_paid_amt DECIMAL(10,4) NOT NULL,
federal_tax_amt DECIMAL(10,4) NOT NULL,
county_tax_amt DECIMAL(10,4) NOT NULL,
property_ratio DECIMAL(10,4) NOT NULL,
deductable_tax_amt DECIMAL(10,4) NOT NULL,
tax_code NCHAR(10) NOT NULL CHECK (tax_code IN (....));
The last several years, you've been able to do an insert with a table constructor. But you're still inserting one row at a time. Just as if you are reading a magnetic tape or a punch card.
But more fundamentally than that, you missed the whole purpose of a database. Even before we had SQL, we had databases to remove redundancy. The filesystems are trying to imitate with SQL, the same day I was spread out over multiple files. When different people wanted to use the same data, you copy it onto a new magnetic tape. Logically the same as your second table!
If you really need to modify the data, then put it in a VIEW. In SQL, a virtual table is just as much a table as a materialized base table. There is no need to copy that data to another tape.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
"It Depends", Joe. Why recalculate something perhaps thousands of times per day if the answer isn't going to change that day? It's called "caching" and it's a time honored method of taking a load off the server and maybe even distributing the "answer" to the webservers themselves, which would also relieve the system of a shedload of I/O. The technique is also one of the primary reasons for why "data warehouses" exist.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply