Switin Kotian

Have you were felt need for passing outer query field value to inner or Sub Query as a where clause or join and have sub query field in outer Select. With conventional SQL it’s not possible. This can be done using SQL SERVER 2005 and 2008 new feature called as Outer Apply. It works like left outer join with outer Query field in sub Query. Sample: Note: In this sample I am trying to get pervious total invoice value for that customer as on bill date Selec t Srno,Amount ,DocDT, PreAmount From tranSales sl Outer Apply ( Select SUM(Amount) PreAmount From tranSales PrSl where COID = 1 and PrSl.DocDt sl.DocDt and PrSl.CustomerID = sl.CustomerID Group by CustomerID ) PreSl where COID = 1 and sl.CustomerID = 1 Order by SRNO I am sure this will help lot of peoples. Switin Kotian

Temp table is valid for a session (Connection). Defination : Create table #temp (Name as Varchar(100)) Insert into #temp (Name) Value ('Switin'); It can be used in Into Statement Select * Into #Temp From SomeTable Go 1.If you try to create one more Temp Table with Same Name with same Connection Exceptation will be raised 2.Best Part you can create Primary Key, Indexes, Constraints. As we do for normal tables 3.As soon as connection is dead the Temp Table is Dropped automatically by SQL SERVER . Cool Part Temp Variable are Tables but for the Scope. They are Scope driven Defination : Declare @temp table(Name Varchar(100)) Insert into @temp (Name) Value ('Switin'); Go Declare @temp table(Name Varchar(100)) Insert into @temp (Name) Value ('Switin'); Above mentioned syntax will not generate any error as after first creation of table scope of variable end when Go is Called. Only Weak point in Table Variable is SELECT INTO statment cannot be used Mostly used in SP, Tr...

Hi, To All Database Connection string is one thing i find diffcult to remember for different datasource. I found one web site which provides ready to use any datasource Connection strings. http://www.connectionstrings.com/ I thought this may help you to. Switin Kotian

Hi, To All Reader. This blog is specially for developer who are comming from VB 6.0 IDE To .net IDE. In VB 6.0 there is a option Goto Last Position. Same is missing in .net IDE. As i known how important that feature is . I recently found the shortcut to key to it. So i thought i would like to share with you people. ShortCut Key :- CTRL And - (Minus Key) . Switin Kotian

Public Sub MailData(ByVal Path As String, ByVal strEmailID As String, _ ByVal strEmailCC As String, ByVal strEmailBCC As String, Optional ByVal Subject As String = "", _ Optional ByVal Message As String = "", _ Optional ByVal KillPath As Boolean = True) Dim msg As New MailMessage Dim arrPath() As String arrPath = Path.Split(";") msg.From = New MailAddress( xyz@abc.com ) msg.To.Add("abc@123.co.in") msg.CC.Add("switinkotian@rediffmail.com") msg. Bcc.Add("switinkotian@gmail.com") msg.Subject = "Attached is a Mulitple PDF file" msg.Body = "Developed by - Switin Kotian" 'Dim attachment As Attachment = New Attachment(Path) ' inCase of Multiple Single Attachment Dim attachment As Attachment ...

Running Totals in SQL Select Base.CustomerID ,Base.DocDt , TotalforCust , SUM(TTotal) RunningTotal from ( Select CustomerID , DocDt ,SUM(Amount) TotalforCust from Transales GROUP By CustomerID , DocDt )Base Inner join ( Select CustomerID , DocDt ,SUM(Amount) TTotal from Transales GROUP By CustomerID , DocDt ) RnT on Base.CustomerID = RnT.CustomerID and RnT.DocDt GROUP BY Base.CustomerID ,Base.DocDt , TotalforCust

Hi, Recently i got my windows upgraded to Windows 7. After installation i found one of the important tool was missing from Windows Taskbar. Quick Launch Bar. Did try some of the common R&D on windows property but failed to get the bar.Searched it on the web and found the solution.So it would like to share the same with you people. To add Quick Lanch Bar. 1).Right Click on Task Bar -> ToolBar -> New Toolbar -> OPen Dialog bar pops Up -> Goto C:\Users\ \AppData\Roaming\Microsoft\Internet Explorer. your are done with Quick Launch Bar. Regards Switin Kotian