Power By 专否社区

Update column in SQL Server 2008 - SSIS

Go To StackoverFlow.com

0

If the Customer Transaction type is not mentioned in the database
If the model year is less than or equal to 2 years than the transaction type should be updated as a Warranty.

Remaining 60 % of the customer data should be updated as Customer pay and the 40% of the customer data should be updated as Warranty randomly on each dealer.

I have a table for Model year table of this structure:

SlNo  VehicleNo  ModelYear
----  ---------  ---------
1     AAAD1234   2012
2     VVV023333  2008
3     CRT456     2011
4     MTER6666   2010

Is it possible to achieve this using SSIS??

I have tried a query. Please help fix it

select 
  vehicleNo, Modelyear,
  case 
      when DATEDIFF(year, ModelYear, GETDATE()) <= 2 then 'Warranty' END,
  case 
      when COUNT(modelyear) * 100 / (select COUNT(*) from VehicleModel) > 2 then '100%' end,
  case 
      when COUNT(modelyear) * 40 / (select COUNT(*) from VehicleModel) > 2  then '40%' end
from 
    vehiclemodel
group by 
    vehicleNo, Modelyear

Output

vehicleNo  Modelyear  (No column name)  (No column name)  (No column name) 
---------  ---------  ----------------  ----------------  ----------------
AAAD1234   2008       NULL              100%              40% 
VVV023333  2010       Warranty          100%              40% 
CRT456     2011       Warranty          100%              40%
MTER6666   2012       Warranty          100%              40%
2012-04-04 07:20
by Gallop
what output do you expect - Arion 2012-04-04 07:42
0 down vote favorite share [g+] share [fb] share [tw]

If the Customer Transaction type is not mentioned in the database If the model year is less than or equal to 2 years than the transaction type should be updated as a Warranty.

Remaining 60 % of the customer data should be updated as Customer pay and the 40% of the customer data should be updated as Warranty randomly on each dealer. Thank - Gallop 2012-04-04 08:25

I mean can you edit your query and show a output table - Arion 2012-04-04 09:19
vehicleNo Modelyear (No column name) (No column name) (No column name) AAAD1234 2008 NULL 100% 40% VVV023333 2010 Warranty 100% 40% CRT456 2011 Warranty 100% 40% MTER6666 2012 Warranty 100% 40 - Gallop 2012-04-04 09:49
I edited your question with the output you are expecting - Arion 2012-04-04 10:31

1

what exactly are you trying to do with SSIS? Where are you moving data from and where are you inserting it to?

If you only need to run this query you don't need SSIS. You can do this logic in SQL. If you need to insert this into another table or database I would also do the calculation on SQL (like you just did) and use it as source to an OleDBSourtce component and then insert it into your destination.

I think you have to provide more information so we can help you

2012-04-04 10:39
by Diego
I want to check for the above mentioned conditions and then update the Transaction Type column in the Transaction table as Warranty if the vehicle was purchased before 2 years. the remaining vehicles that are > 2 years will be counted and out of it 60% will be updated as customer pay and remaining 40% as other - Gallop 2012-04-04 11:18
humm ok but I dont understand why you need SSIS. It seems that you can do everything using SQL queries. Whats exactly is the issue? Isnt your query working? If not can you post your table structure - Diego 2012-04-04 14:31
The business rules are part of SSIS package so thought, there might be a way to solve the problem using SSIS components.If it can be done only using a query kindly help with one...Thank - Gallop 2012-04-05 03:51
CREATE TABLE [dbo].VehicleModel ON [PRIMARY - Gallop 2012-04-05 04:20
Any updates???? - Gallop 2012-04-09 04:47
Ads