Deaf to Win

Better be deaf than normal, so every time you want to climb the highest mountain, you will not hear someone says "The mountain is too high!!! No one can climb that mountain!!!"

God's work

All your fear and negative assumption/thinking will only slow the God's work in your life

Different Responses

Try to responses problem/situation with different responses besides the general responses that people usually do. It will make your life more rich.

Point of View

If you face a problem, try to see it from a lot of different point of view, so you can see it clearly.

Dynamic Column Query Using Pivot

SET @strSQL =
'select tax_type "Tax Type", tax_service_Description "Tax Service Description",' + @cols + '
FROM (
select
ttSSP0.tax_type,
tmtarif.TAX_SERVICE_DESCRIPTION,
ttSSP0.company_code,
ttSSP0.tax_period_year,
case ttSSP0.tax_period_month
when ''01'' then ''January''
when ''02'' then ''February''
when ''03'' then ''March''
when ''04'' then ''April''
when ''05'' then ''May''
when ''06'' then ''June''
when ''07'' then ''July''
when ''08'' then ''August''
when ''09'' then ''September''
when ''10'' then ''October''
when ''11'' then ''November''
when ''12'' then ''December'' end tax_period_month,
sum(ttSSP0.PAY_AMOUNT) PAY_AMOUNT
from
ttSSP0 left join tmtarif
on ttSSP0.tax_type = tmtarif.tax_type and
ttSSP0.TAX_MAP_CODE = tmTarif.TAX_MAP_CODE
WHERE
ttSSP0.COMPANY_CODE = ' + CAST(@Company_Code as varchar) + '
AND ttSSP0.tax_period_year = ''' + @Tax_Period_Year + '''
AND ttSSP0.tax_period_month between ' + @Tax_Period_monthFrom + ' and ' + @Tax_Period_monthTo + '
group by
ttSSP0.tax_type,
tmtarif.TAX_SERVICE_DESCRIPTION,
ttSSP0.company_code,
ttSSP0.tax_period_year,
ttSSP0.tax_period_month
) p PIVOT ( SUM(PAY_AMOUNT)
FOR [tax_period_month]
IN (' + @colspvt + ')
) AS pvt'

EXEC SP_EXECUTESQL @strSQL

The @cols variable here must be set with value [January],[February]...etc. So the pivot query will change the original query where month name in a row to a column and it will sum the field PAY_AMOUNT for each month.
Examples :
The Original Query Result:
MonthName   Payamount
=====================
January   100
February   200
March   300
January   150

Pivot Query Result :
January   February   March
==========================
250   200   300

Import Excel File to Dataset (ASP.NET)

Dim filename as string
filename = Mid(uplFile.PostedFile.FileName, InStrRev(uplFile.PostedFile.FileName, "\") + 1, Len(uplFile.PostedFile.FileName) - InStrRev(uplFile.PostedFile.FileName, "\"))

'uplFile is ASP.NET Upload Component uplFile.PostedFile.SaveAs(System.AppDomain.CurrentDomain.BaseDirectory & filename)


Dim dbconn As New OleDb.OleDbConnection
Dim strconn As String
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.AppDomain.CurrentDomain.BaseDirectory & filename & ";Extended Properties=Excel 8.0"
dbconn.ConnectionString = strconn
dbconn.Open()


Dim excelcmd As New OleDb.OleDbCommand
excelcmd.Connection = dbconn
excelcmd.CommandText = "select * from [Sheet1$]"


Dim excelda As New OleDb.OleDbDataAdapter(excelcmd)
Dim excelDs As New DataSet
excelda.Fill(excelDs)