SQL Server

SQL Server

COALESCE function instead of long CASE WHEN … ELSE (T-SQL)

0
9 months ago
by Solomon in

Instead of using long “SELECT … CASE WHEN … ELSE …” construction, you can use the COALESCE function when you need to find a value that is not NULL. Lets review the following T-SQL expression, in which we need to select an available “source”:

http://dotnettipoftheday.org/tips/sql-coalesce.aspx


SELECT TheSource =
   CASE
      WHEN localSource IS NOT NULL THEN localSource
      WHEN intranetSource IS NOT NULL THEN intranetSource
      WHEN internetSource IS NOT NULL THEN internetSource
      ELSE ''
   END
FROM ...

Now lets rewrite the code More >

SQL Server

SQL Server Date Formats

0
9 months ago
by Solomon in
Standard Date Formats Date Format Standard SQL Statement Sample Output Mon DD YYYY 1 HH:MIAM (or PM) Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1 MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98 MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998 YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01 YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01 DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72 DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972 DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS

More >

SQL Server

Comma Delimited Output – single select statement – SQL Server

0
1 year ago
by Solomon in

 

We can get a comma delimited output from a single select statement in SQL Server by using the following SQL Script.


select  stuff(( select  ',' + CountryRegionCode
                from    Person.CountryRegion
              for
                xml path('')
              ), 1, 1, '') as CodeList

 

SQL Server

Comma-Delimited Output (SQL Server)

1
2 years ago
by Solomon in

Using Query:


use Northwind
GO

declare @CustIDs varchar(8000)

select top 10
        @CustIDs = isnull(@CustIDs + ',', '') + CustomerID
from    Customers

select  @CustIDs

GO

Using Cursor:


declare cCustomerIDs cursor for

select [CustomerID] from [dbo].[Customers] order by [CustomerID]

declare @CustomerIDs varchar(8000)

declare @CustomerID varchar(10)

open cCustomerIDs

fetch next from cCustomerIDs into @CustomerID

while @@FETCH_STATUS = 0
    begin

        set @CustomerIDs = isnull(@CustomerIDs + ',', '') + @CustomerID

        fetch next from cCustomerIDs into @CustomerID

    end

close cCustomerIDs

deallocate cCustomerIDs

select  @CustomerIDs as CustomerIDs

GO
SQL Server

Find the balance amount (sum(credit) – sum(debit)) from the following table?

1
2 years ago
by Solomon in

Ans: find out the balance amount of account no: 1 


select  a.SumCr as Credit ,
        b.SumDr as Debit ,
        a.SumCr - b.SumDr as Balance
from    ( select    sum(A_Amount) as SumCr
          from      tblAccount
          where     A_ID = 1
                    and A_Type = 'Cr'
        ) a ,
        ( select    sum(A_Amount) as SumDr
          from      tblAccount
          where     A_ID = 1
                    and A_Type = 'Dr'
        ) b

Ans: find out the balance amount for all the account numbers in the More >

SQL Server

Find out the N’th maxium salary?

1
2 years ago
by Solomon in

Ans: find the 5th maxium salary: 

   1: SELECT TOP 1 * FROM tblEmployee WHERE E_Salary NOT IN 
   2:     (SELECT DISTINCT TOP 4 E_Salary FROM tblEmployee ORDER BY E_Salary DESC)
   3:     ORDER BY E_Salary DESC

Go to Top