WEB API

http://www.asp.net/web-api/overview/creating-web-apis/using-web-api-with-entity-framework/using-web-api-with-entity-framework,-part-1

insert multiple rows sql server 2008

For inserting multiple rows using one insert statement you can do as following example :

declare @dept table
 (
   DeptId int identity(1,1) not null,
   DeptName nvarchar(500)
 )

 insert into @dept values ('sales'),('marketting'),('hr'),('IT')

 select * from @dept 

SQL fiddle can be check here  

Get random rows from a table in mysql

if you want to get random rows from your table  so you can use rand() function of my sql in order by clause. 

so for using this you have to use this in order by clause. 

SELECT id,title,publishDate FROM mytable ORDER BY RAND()  LIMIT 5

but how its works ? how rand() function generates rows randomly . 

actually rand() function generates  a random floating-point value v in the range 0 <= v < 1.0. If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values

SHOW SERIAL NUMBER IN RDLC REPORT

may be you need to show serial number in your rdlc report then you can use following syntax for showing 1,2,3... continuous on in table field

=RowNumber(Nothing)

and if you want to re-generate serial number for each  group in rdlc then you can use following syntax

   =RowNumber("table1_Group1")

where table1_Group1 is group name . 

Thanks 

dynamic PIVOT where number of columns are dynamic in pivot columns

A PIVOT used to rotate the data from one column into multiple columns.

STATIC Pivot meaning you hard code the columns that you want to rotate

For  example :

CREATE TABLE temp
(
    period_id INTEGER ,
    lease_id  INTEGER  ,
    charge_id VARCHAR(20) ,
    charge_amount MONEY
)
INSERT INTO temp
        ( period_id ,
          lease_id ,
          charge_id ,
          charge_amount
        )
VALUES  ( 100 , -- period_id - integer
          2000 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          12345  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2000 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          678910  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2002 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          78950  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2002 , -- lease_id - integer
          '310' , -- charge_id - varchar(20)
          9002  -- charge_amount - money
        )

        Select period_id,lease_id,[300] as charge_300, [310] as Charge_310  FROM(Select period_id,lease_id,charge_id,charge_amount from temp )p     pivot(sum(charge_amount)FOR charge_id in ([300],[310])) as PVT

but in case of number of columns change dynamically then you need dynamic pivot . for this you need to create series of columns using dynamic sql . 

DECLARE @columns VARCHAR(8000)

SELECT 
@columns = 
COALESCE
(
 @columns + ',[' + charge_id + ']',
 '[' + charge_id + ']'
)
FROM 
#temp 
group by charge_id

// The above query tries to create distinct charge_id as the columns
// @columns= [300],[301]

DECLARE @query VARCHAR(8000)
SET @query = 'SELECT *
FROM
(
 SELECT 
 period_id,lease_id ,charge_id,charge_amount 
 FROM 
 #temp 
) P
PIVOT
(
 SUM(charge_amount) 
 FOR charge_id in (' + @columns + ')
) AS PVT'

 EXECUTE (@query)
 GO

Links for above sql fiddle 

function to use comma seprated values in to a table so that it can be use in IN statement in sql

function to use comma seprated values in to a table so that it can be use in IN statement in sql

ALTER FUNCTION [dbo].[fnNTextToIntTable] (@Data nvarchar(4000))
RETURNS
    @IntTable TABLE ([Value] NVARCHAR(500) NULL)
AS
BEGIN
    SET @Data=Replace(@Data,'$',',')
    DECLARE @Ptr int, @Length int, @v nchar, @vv nvarchar(10)
    SELECT @Length = (DATALENGTH(@Data) / 2) + 1, @Ptr = 1
    WHILE (@Ptr < @Length)
    BEGIN
        SET @v = SUBSTRING(@Data, @Ptr, 1)
        IF @v = ','
        BEGIN
            INSERT INTO @IntTable (Value) VALUES (CAST(RTRIM(LTRIM(@vv)) AS NVARCHAR(500)))
            SET @vv = NULL
        END
        ELSE
        BEGIN
            SET @vv = ISNULL(RTRIM(LTRIM(@vv)), '') + @v
        END
        SET @Ptr = @Ptr + 1
    END
    -- If the last number was not followed by a comma, add it to the result set
    IF @vv IS NOT NULL
        INSERT INTO @IntTable (Value) VALUES (CAST(RTRIM(LTRIM(@vv)) AS NVARCHAR(500)))
    RETURN
END

calculate age or get the duration between two dates in years, months and days

ALTER FUNCTION [dbo].[calc_age]
(
-- Add the parameters for the function here
@date smalldatetime
)
RETURNS nvarchar(4000)
AS
BEGIN
-- Declare the return variable here
DECLARE  @tmpdate datetime, @years int, @months int, @days int

SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
return  CAST(@years as nvarchar(50)) + ' Years '  + CAST(@months as nvarchar(50)) + ' Months ' + CAST(@days as nvarchar(50)) +' Days '
END