Writing conditions in Conditional Split Transformation Editor

the logic of why you want to use conditional transformation is not clear to me yet.
if you want to put payment1 into one table , and payment2 into another table. so add a multi cast transformation after your data source, this will make double the result, then put payment1 from first result to first table. and payment2 from second result to table2.

Am I understood your problem correctly?
let me know if you mean anything else.

Ok maybe Conditional transformation is not what I'm looking for but maybe you could give me some advice what would be the best approach to solve my problem.

In the table I'm working with there are some rows that need to be split to 2 rows if certain condition are met.
There are many columns in the table but all of them will be transfered to the new table/tables. Two of the columns include dates, the column [gsdgsvfr] is the date from and the column [gsdgsvti] is the date to. The third column, [gsfjodag] is a calculation of how many payment days are in the range from [gsdgsvfr]:[gsdgsvti].

I will only take the ranges where the 20th of the month is included in the range, as the payment period 1 is 1st-19th and payment period 2 is the =>20th of the month.

F.exs. if the range is [2010-17-01]:[2010-22-01] then I want to split the transaction to the destination table so there will be two rows. The first transaction will have the value 2 in the payment table and the second transaction will have the value 3 in the payment table, and sum of those two values will be equal to the value in [gsfjodag].

What I had in mind was to make some transformation to two temp tables, one with the data from 1st to the 19th and another with the data from 20th to end of month. I then wanted to merge those two temp tables to one destination table and link the new payment fields to the [gsfjodag] field so that I now have a new set of rows, where [gsfjodag] has the value 2 for row one and  the  [gsfjodag] has the value 3 for the second row.

I hope you understand my problem, if some things are still unclear then please let me know.

I think it would be more understandable if you bring sample row of data from payments table.
I got something in your explanation , but sample data rows will be helpful.

Here are some sample data
SampleData-EE.rpt

As I see in your sample data gsfjodag should be amount of days between two date columns. but it is not!
do you calculate only working days? or logic is something else?

gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2001.09.17     2001.09.24           5      
2001.10.08     2001.10.22          10      
2001.11.19     2001.11.30          9      
2001.12.17     2001.12.31          10      

It is only working days, weekends are excluded. But that is another problem which I'm trying to solve. The reason I didn't mention it in my first post was that I wanted to start by getting the transformation to work, without considering the weekends, but the problems with the weekends are that the gsfjodag is getting the amount of working days manually from the client input. To split it right, then I would need to know if there is a weekend on the period and exlude it in the calculations.

Example:
The range [2001.09.17] to  [2001.09.24] should have 1 day in period one and 4 days in period two as the 17.09.2001 is friday and the 19th is the last day of the period one. Is there any way in SSIS to do that?

This is maybe another problem and not sure if this belongs to the SSIS, but should give overview of the whole problem.

so,
do you mean result table should be like this: ????

gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2001.09.17     2001.09.19           1      
2001.09.20     2001.09.24           4      
....
....


if yes, you can use UNION ALL in your query, like this:

SELECT SUM(19-DATEPART("dd",gsdgsvfr)) as paymentFROM [Bal].[dbo].[payments]Group by [gsnumrec]UNION ALLSELECT SUM(gsfjodag- (19-DATEPART("dd",gsdgsvfr))) as  paymentFROM [Bal].[dbo].[payments]Group by [gsnumrec]

now just fill destination table with this result.

you can also have two different data sources , one for each of above queries , and then use a UNION transformation between them . and fill push result of union transformation to destination table.

let me know if something is missing.

I have also a way to fetch working days,
this query return working days between gsdgsvfr and 19th of month as Payment1,
and days between 19th of month and gsdgsvti as Payment2   :


SELECT[gsnumrec],sum(   (DATEDIFF(dd, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') )  -(DATEDIFF(wk, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') * 2)  -(CASE WHEN DATENAME(dw, gsdgsvfr) = 'Sunday' THEN 1 ELSE 0 END)  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Saturday' THEN 1 ELSE 0 END)) as Payment1,sum(   (DATEDIFF(dd, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) )  -(DATEDIFF(wk, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) * 2)  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Sunday' THEN 1 ELSE 0 END)  -(CASE WHEN DATENAME(dw, gsdgsvti) = 'Saturday' THEN 1 ELSE 0 END)) as Payment2

from payments
Group by [gsnumrec]

so, final query will be this: ( UNION ALL added)

SELECT
[gsnumrec],
sum(   (DATEDIFF(dd, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') )
  -(DATEDIFF(wk, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') * 2)
  -(CASE WHEN DATENAME(dw, gsdgsvfr) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Saturday' THEN 1 ELSE 0 END)
) as Payment

from payments
Group by [gsnumrec]

UNION ALL

SELECT
[gsnumrec],
sum(   (DATEDIFF(dd, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) )
  -(DATEDIFF(wk, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) * 2)
  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, gsdgsvti) = 'Saturday' THEN 1 ELSE 0 END)
) as Payment2


from payments
Group by [gsnumrec]

Select allOpen in new window

the minus values replaced by 0 in this query: ( so this can be final version)

SELECT
[gsnumrec],
case 
when sum(DATEDIFF(dd, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18'))<0 then 0
else
sum(   (DATEDIFF(dd, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') )
  -(DATEDIFF(wk, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') * 2)
  -(CASE WHEN DATENAME(dw, gsdgsvfr) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Saturday' THEN 1 ELSE 0 END)
) 
end
as Payment

from payments
Group by [gsnumrec]

UNION ALL

SELECT
[gsnumrec],
case 
when sum(   (DATEDIFF(dd, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) ))<0 then 0
else
sum(   (DATEDIFF(dd, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) )
  -(DATEDIFF(wk, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) * 2)
  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, gsdgsvti) = 'Saturday' THEN 1 ELSE 0 END)
) 
end
as Payment


from payments
Group by [gsnumrec]

Select allOpen in new window

Thanks alot for this. However I'm not getting this totally correct. I wrote earlier that:

gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2001.09.17     2001.09.19           1      
2001.09.20     2001.09.24           4      

but that is not right, I was looking at 2010.09.17 instead of 2001.09.17 and that day is a monday. So this should actually be:

gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2001.09.17     2001.09.19           2    
2001.09.20     2001.09.24           3

total of gsfjodag=5
The first date should also not count in gsfjodag, so the payment 1 period is 2001.09.18 & 2001.08.19.
sorry for that.

We could also look at some newer period, like

gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2009.05.15     2009.05.21            4    

but should be:
gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2009.05.15     2009.05.19            2   (15.05 is excluded as it's the first day, and 16.05 & 17.05 also as it's weekend)
2009.05.20     2009.05.21            2  

Could this affect your query so it's not returning the right results?
 

This solves  my problem, thank you very much for this, your help has been priceless :)