SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 query-case?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

collie
Constraint Violating Yak Guru

399 Posts

Posted - 12/17/2012 :  13:28:15  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000