Saturday, October 20, 2012

How to convert c# array into list type of object

hotel[] hotels= new [] { 10, 20, 10, 34, 113 };


 public hotel[] availableHotels ;

availableHotels=

print month name

simple DATENAME function can be used to print month name 

 select DATENAME(month,getdate())


most simple example to understand common table expression in sql

Just declaration of a temporary table with single column 
declare @temp1 table
 (
   id int
 )

insert statement to insert one row in temporary table @temp1

 insert into @temp1 select 1
 common table expression example to recursively increase the column Id. i think this is the most simple example of looping or recursion to print 1 to 10 with out printing each statement individually .... :)








Here is code of common table expression in sql  
;with cte As
   (
     select id from @temp1
     union all
     select id+1 from cte where id<10 br="br">   )
   select * from cte
sql fiddle for this post can be find here sqlfiddle link 

Thursday, October 11, 2012

Select all dates between first day of month and current date without loop


DECLARE @startDate DATETIME=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR) -- mm/dd/yyyy
DECLARE @endDate DATETIME= GETDATE() -- mm/dd/yyyy
;WITH Calender AS (
    SELECT @startDate AS CalanderDate
    UNION ALL
    SELECT CalanderDate + 1 FROM Calender
    WHERE CalanderDate + 1 <= @endDate)
SELECT [Date] = CONVERT(VARCHAR(10),CalanderDate,25) FROM CalenderOPTION (MAXRECURSION 0)
DECLARE @startDate DATE=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR)  -- mm/dd/yyyy
DECLARE @endDate DATE=GETDATE() -- mm/dd/yyyy
SELECT [Date] = DATEADD(Day,Number,@startDate) FROM  master..spt_values WHERE Type='P'
AND DATEADD(day,Number,@startDate) <= @endDate
declare @temp table (ddate datetime);
insert @tempselect DATEDIFF(d,0,GetDate()-Number)
from master..spt_valueswhere type='p' and number < DatePart(d,Getdate())
order by 1;

Thursday, October 4, 2012

simple example of pivot in sql

declare @temp table (


id int identity(1,1),

deptname nvarchar(500),

type nvarchar(1),

value int

)



insert into @temp

select 'payroll','a',20

union all

select 'payroll','b',20

union all

select 'payroll','b',10

union all

select 'sales','a',20

union all

select 'sales','b',20

union all

select 'sales','b',10







select * from @temp



select deptname,a,b,c from

(select SUM(value) as value,deptname,type from @temp group by type,deptname) p

pivot (SUM(value) FOR type IN (a,b,c)) AS pvt