|
collie
Constraint Violating Yak Guru
349 Posts |
 Posted - 12/17/2012 : 13:28:15
|
Hi,
I am not sure what the best approach is to accomplish what I need.
1. If KEY in originalsheet is same as key in datasheet I need to add row same as original but values GrossAmount and TaxAmount must be in minus.
2. If there are 2 colums with same code in original such as TaxOnNewOrder ( code 6) and NewOrder ( code 06)then take values from these 2 columns and place them in another row.
3. If there is only one column with a code such as NewOrderDiv_Code ( code 37) -there is no other column with code 37 then add a new row with key 37 and no tax values and grossamount is what is under column NewOrderDiv_Code ( code 37)
I added columns [TaxOnNewOrder_Code] and [NewOrderDiv_Code] for simplicity sakes in order to get the code-the tables are imported from EXCEL.
CREATE TABLE [dbo].[DataSheet](
[Acc No] [nvarchar](255) NULL,
[key] [nvarchar](255) NULL,
[PayDate] [datetime] NULL,
[nvarchar](255) NULL,
[NewOrder_Code] [float] NULL,
[NewOrder ( code 6)] [float] NULL,
[TaxOnNewOrder_Code] [float] NULL,
[TaxOnNewOrder ( code 06)] [float] NULL,
[NewOrderDiv_Code ] [float] NULL,
[NewOrderDiv_Code ( code 37)] [float] NULL
) ON [PRIMARY]
GO
insert into [DataSheet]
select '0967889','06','2011-04-15 00:00:00.000','113011103',6,'324.15',6,'81.04',37,'83.53'
union
select '0967889','06','2011-04-15 00:00:00.000','009011109',6,'15.12',6,'81.04',37,'33.33'
union
select '0967889','06','2011-06-21 00:00:00.000','873061707',6,'324.15',6,'81.04',37,'83.53'
union
select '0967889','06','2011-12-25 00:00:00.000','893123403',6,'23.15',6,'80.00',37,'12.12'
CREATE TABLE [dbo].[originalsheet](
[AccountNo] [nvarchar](255) NULL,
[key] [nvarchar](255) NULL,
[PayDate] [datetime] NULL,
[nvarchar](255) NULL,
[GrossAmount] [money] NULL,
[TaxRate] [float] NULL,
[TaxAmount] [money] NULL
) ON [PRIMARY]
GO
insert into [originalsheet]
select '0967889','06','2011-04-15 00:00:00.000','113011103' ,'124.15' ,'25' ,'31.038'
union
select '0967889','06','2011-04-15 00:00:00.000','009011109' ,'622.96' ,'25' ,'155.74'
union
select '0967889','06','2011-06-21 00:00:00.000','873061707' ,'223' ,'25' ,'55.80'
union
select '0967889','06','2011-12-25 00:00:00.000','893123403' ,'407.68' ,'25' ,'101.92'
output
 |
|
query-case?
