T-SQL get neares date czyli znajdź najbliższą datę

SELECT a.ID , x.* FROM dbo.table_a a CROSS APPLY (SELECT TOP 1 b.* FROM dbo.table_b WHERE b.ID=a.ID AND date_from_b <= a.date_from_a ORDER BY b.date_from_b desc) x

SSIS Script Component Transformation: Split record into multiple records (ver. dynamic columns)

'... Output has to be created manually (there's no solution for that) and column mappings Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Reflection Imports Microsoft.SqlServer.Dts.Pipeline _ _ Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim column As IDTSInputColumn100 Dim rowType As Type = Row.GetType() Dim columnValue As PropertyInfo Dim rok As Integer Dim colName As String Dim reg As String Dim arr() As String Dim farr As New DataTable() Dim ds As New DataSet() Dim i As Integer = 0 Dim j As Decimal = 0 '= Nothing Dim id As New DataColumn("qtr", GetType(Integer)) ds.Tables.Add(farr) farr.Columns.Add(id) farr.Rows.Add(1) farr.Rows.Add(2) farr.Rows.Add(3) farr.Rows.Add(4) farr.PrimaryKey = New DataColumn() {id} 'If Not farr.Columns.Contains(column.Name) Then ' farr.Columns.Add(colName) ' MessageBox.Show("Dodana kolumna:" + colName) 'End If For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection() colName = column.Name.ToString() columnValue = rowType.GetProperty(column.Name) If colName = "rok" Then rok = columnValue.GetValue(Row, Nothing) ElseIf colName = "regon" Then reg = columnValue.GetValue(Row, Nothing) Else i = 0 Dim dt As DataTable = ds.Tables.Add(colName) Dim id1 As New DataColumn("qtr", GetType(Integer)) dt.Columns.Add(id1) 'dt.PrimaryKey = New DataColumn() {id1} Dim var As New DataColumn(colName, GetType(String)) dt.Columns.Add(var) arr = columnValue.GetValue(Row, Nothing).ToString.Split(";") For Each s In arr i = i + 1 'Try ' j = Convert.ToDecimal(s) 'Catch 'ex As Exception ' j = 0 'End Try 'With farr.Columns(colName) dt.Rows.Add(i, s) 'End With Next ReDim arr(0) farr.Columns.Add(colName, GetType(String)) 'MessageBox.Show("nazwa kol " + colName) 'Dim rel As DataRelation = New DataRelation(colName, farr.Columns("qtr"), dt.Columns("qtr")) 'ds.Relations.Add(rel) For Each chi As DataRow In dt.Rows For Each par As DataRow In farr.Rows If par("qtr") = chi("qtr") Then 'MessageBox.Show(colName + " col0: " + dt.Columns(0).ColumnName + " q: " + chi(0).ToString() + " val: " + chi(1).ToString()) par.Item(colName) = chi(colName) 'farr.Rows.Add(pr) 'MessageBox.Show(colName) End If Next Next dt.Clear() End If Next 'For Each dtt As DataTable In ds.Tables ' farr.Merge(dtt) 'Next For Each r As DataRow In farr.Rows 'MessageBox.Show("Next column") Output0Buffer.AddRow() Output0Buffer.regon = reg Output0Buffer.rok = rok Output0Buffer.nnk = r.Item("nnk") Output0Buffer.nno = r.Item("nno") Output0Buffer.nnp = r.Item("nnp") Output0Buffer.nnw = r.Item("nnw") Output0Buffer.nbk = r.Item("nbk") Output0Buffer.nbo = r.Item("nbo") Output0Buffer.nbp = r.Item("nbp") Output0Buffer.nbw = r.Item("nbw") Output0Buffer.nnp = r.Item("nnp") Output0Buffer.nnw = r.Item("nnw") Output0Buffer.pnk = r.Item("pnk") Output0Buffer.pno = r.Item("pno") Output0Buffer.pnp = r.Item("pnp") Output0Buffer.pnw = r.Item("pnw") Output0Buffer.zw1 = r.Item("zw1") Output0Buffer.zw2 = r.Item("zw2") Output0Buffer.zw3 = r.Item("zw3") Output0Buffer.zw4 = r.Item("zw4") Output0Buffer.zw5 = r.Item("zw5") Output0Buffer.zp1 = r.Item("zp1") Output0Buffer.zp2 = r.Item("zp2") Output0Buffer.zp3 = r.Item("zp3") Output0Buffer.zp4 = r.Item("zp4") Output0Buffer.zp5 = r.Item("zp5") Output0Buffer.wzbk = r.Item("wzbk") Output0Buffer.wzbr = r.Item("wzbr") Output0Buffer.wzbz = r.Item("wzbz") Output0Buffer.wznk = r.Item("wznk") Output0Buffer.wznr = r.Item("wznr") Output0Buffer.wznz = r.Item("wznz") Output0Buffer.qtr = DateAdd("d", -1, DateSerial(rok, r.Item("qtr") * 3 + 1, 1)) Next farr.Clear() End Sub End Class

SSIS Script Component Transformation: Split record into multiple records (ver. one common column)

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Reflection Imports Microsoft.SqlServer.Dts.Pipeline _ _ Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim column As IDTSInputColumn100 Dim rowType As Type = Row.GetType() Dim columnValue As PropertyInfo Dim rok As Integer Dim colName As String Dim regon As String Dim arr() As String Dim farr As New DataTable() 'Dim ds As New DataSet() Dim i As Integer = 0 Dim j As Decimal = Nothing farr.Columns.Add("cName", GetType(String)) farr.Columns.Add("cValue", GetType(String)) farr.Columns.Add("qtr", GetType(Integer)) For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection i = 0 columnValue = rowType.GetProperty(column.Name) If column.Name.ToString() = "rok" Then rok = columnValue.GetValue(Row, Nothing) ElseIf column.Name.ToString() = "regon" Then regon = columnValue.GetValue(Row, Nothing) Else colName = column.Name 'ds.Tables.Add(colName) ' If Not farr.Columns.Contains(column.Name) Then ' farr.Columns.Add(colName) 'End If arr = columnValue.GetValue(Row, Nothing).ToString.Split(";") For Each s In arr i = i + 1 farr.Rows.Add(colName, s, i) Next ReDim arr(0) i = 0 End If Next For Each r As DataRow In farr.Rows Output0Buffer.AddRow() Output0Buffer.regon = regon Output0Buffer.rok = rok Output0Buffer.colName = r.Item("cName") Try j = Convert.ToDecimal(r.Item("cValue")) If j = 0 Then j = Nothing End If Catch ex As Exception j = Nothing End Try Output0Buffer.colValue = j 'r.Item("cValue") Output0Buffer.qtr = DateAdd("d", -1, DateSerial(rok, r.Item("qtr") * 3 + 1, 1)) Next farr.Clear() End Sub End Class