query-case? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query-case?

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2012-12-17 : 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, 
          
        [code] [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, 
        [code] [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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources