Age Calculation

Hi,
In most of the project we need to calculate the age. Consider below TSQL to calculate age, it should be used where age needs to be displayed.

SELECT CAST((DATEDIFF(dd, <date_of_birth>, GETDATE())/365.25) AS INT)

Calendar In Sql Server Reporting Services.

In SSRS there is no any option by which we can create the calender in the reports but this thing can be done using SQL-Server here I am giving a example code of creating a calender for a month

For this we have to write the some sql queries in SSRS Data Source

The query is as follows :

--First day of current month

DECLARE @StartDate datetime = '01/01/2011'

DECLARE @EndDate datetime= '01/31/2011'

SET @StartDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@StartDate),0))

DECLARE @CurrentMonth VARCHAR(50)

SET @CurrentMonth = CAST(DATENAME(MONTH, @StartDate) AS VARCHAR)+' '+ CAST(DATENAME(YEAR, @StartDate) AS VARCHAR)

--First day to display on calendar

SET @StartDate = DATEADD(DAY,-DATEPART(WEEKDAY,@StartDate)+1,@StartDate)

--Last day of month

SET @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0))

--Last day to display on calendar

SET @EndDate = DATEADD(DAY,6-DATEPART(WEEKDAY,@EndDate),@EndDate)

WITH

Dates([DATE]) AS

(

--Select First day in range

SELECT

CONVERT(DATETIME,@StartDate) AS [DATE]

UNION ALL

--Add a record for every day in the range

SELECT

DATEADD(DAY, 1, [DATE])

FROM

Dates

WHERE

DATE < CONVERT(DATETIME,@EndDate)

), Events AS

(

--Create some events to place on the calendar

SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, -1,GETDATE()),101) + '/30/2009 02:00:00 PM', Note = 'Event 1'

UNION

SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/23/2009 12:00:00 PM', Note = 'Event 2'

UNION

SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2009 02:00:00 PM', Note = 'Event 3'

UNION

SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/28/2009 06:30:00 PM', Note = 'Event 4'

UNION

SELECT EventDate = CONVERT(VARCHAR(2),GETDATE(),101) + '/30/2009 07:00:00 PM', Note = 'Event 5'

UNION

SELECT EventDate = CONVERT(VARCHAR(2),DATEADD(MONTH, 1,GETDATE()),101) + '/01/2009 01:30:00 PM', Note = 'Event 6'

)

SELECT

-- Number the records based on the date, if multiple records have

-- the same date then they will be numbered the same. Used in

-- calculation to determine row record is to display on.

[ORDER] = DENSE_RANK() OVER (ORDER BY d.[Date]),

-- date used in all caluclation for date

d.[Date],

--generates matrix columns

[WeekDay] = DATEPART(WEEKDAY, d.[Date]),

--used to display day of month on calendar

[DAY] = DATEPART(DAY,d.[Date]),

--used in some calculations for display

[MONTH] = DATEPART(MONTH,d.[Date])

FROM

Dates d

LEFT JOIN

Events e

ON

CAST(CONVERT(VARCHAR(10),e.EventDate,101) AS DATETIME) = d.[Date]

--Set the maximum times the Dates cte can recurse

OPTION (MAXRECURSION 100)

After writing this query we have to follow these steps :

1. Right Click on the report page and select matrix table.
2. Right Click on the row group property of this table and give group on value as “=Ceiling(Fields!RowOrder.Value / 7)”
3. Give the width for first column as “0.0inch” and set the visible property of that column as false.
4. For second column right click on Column group property and givr group on value as = “=Fields!WeekDay.Value”
5. Now For the First row in text box write the following code
   =IIF(Fields!WeekDay.Value = 1,"Sunday",
   IIF(Fields!WeekDay.Value = 2,"Monday",
   IIF(Fields!WeekDay.Value = 3,"Tuesday",
   IIF(Fields!WeekDay.Value = 4,"Wednesday",
   IIF(Fields!WeekDay.Value = 5,"Thrusday",
   IIF(Fields!WeekDay.Value = 6,"Friday","Saturday"))))))
6. In second row write the following Code :
   =Fields!Day.Value
   
   
   Now Run the report.

Split the comma separated value

Some times we have some values with the delimiter and we need to split that value with the that delimiter and need that value in the column with the help of below code we can achieve this :



CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(MAX) -- List of delimited items
  , @sDelimiter VARCHAR(50) = ',' -- delimiter that separates items
) RETURNS @List TABLE (ID INT,item VARCHAR(8000))


BEGIN
DECLARE @sItem VARCHAR(MAX);
DECLARE @ID INT;
SET @ID = 0;


WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SET @ID = @ID + 1;
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @ID, @sItem
 END


IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @ID+1, @sInputList -- Put the last item in
RETURN
END


we can use this function like this :
select item from fnSplit('1,2,3,4,5,6',',');
and result is :
1
2
3
4
5
6

Comma separated Value In SQL-Server

Some times we required a column values in a row as comma separated for example
Column1
---------
---------
A1
A2
A3
A4
A5
---------
is like : A1, A2, A3, A4, A5
I n sql we can get this type of result like this :



DECLARE @A VARCHAR(MAX)
SET @A = 
(
SELECT 
LTRIM(RTRIM(t1.column1))+ ', '   
FROM 
Temp1 t1 WITH(NOLOCK) 
INNER JOIN 
Temp2 t2
ON 
t1.column1 = t2.column2
FOR XML PATH('')     
)


SELECT @A

JQuery Date Function

Hi

Date validation is very much required function in web application hare is the function which checks the date validity that it is in "MM/dd/yyyy" format or not

 function checkdate(input) {

            var validformat = /^\d{2}\/\d{2}\/\d{4}$/ //Basic check for format validity

            var returnval = false

            if (input != null && input != "") {

                if (!validformat.test(input))

                    returnval = false;

                else { //Detailed check for valid date ranges

                    var monthfield = input.split("/")[0]

                    var dayfield = input.split("/")[1]

                    var yearfield = input.split("/")[2]

                    var dayobj = new Date(yearfield, monthfield - 1, dayfield)

                    if ((dayobj.getMonth() + 1 != monthfield) || (dayobj.getDate() != dayfield) || (dayobj.getFullYear() != yearfield)) {

                        returnval = false;

                    }

                    else

                        returnval = true;

                }

            }

            else {

                returnval = true;

            }

            return returnval

        }

Case Statement in INNER JOIN

We some times we need the conditional joins in the select statement for example if condition 1 matches then we want some different column joins and when condition 2 then we want some different join

in this case we can use CASE statement in our sql query.

For Example :

select
T1.Column1

,T1.Column2
from
Table1 T1
inner join

ON

Table2 T2
case when <CONDITION> then T1.<Column1> else T1.<Column2> End

= T2.<SomeColumn>

Similarly, we can give case statement in both side of the condition

For Example :

select
T1.Column1

,T1.Column2
from
Table1 T1
inner join

ON

Table2 T2
case when <CONDITION> then T1.<Column1> else T1.<Column2> End

= case when <CONDITION> then T2.<SomeColumn1> else T2.<SomeColumn2> End 

SSRS Column header is not repeating???

SSRS Column header is not repeating this is a very common error in Sql Server Reporting Services.

Till now I am facing this issue in three conditions

1. When I am not setting the It is a Tablix Member property.
2. When I am using grouping in the reports.
3. When I am using Subreports in the Reports.

Here is the solution of all three problems.

1. Solution of first problem

For this follow these steps:

• In the grouping pane, make sure to turn on advanced mode (click on the small black down arrow on the far right of the grouping pane
  
• Select the corresponding (Static) item in the row group hierarchy
  
• In the properties grid, set RepeatOnNewPage to true

2. Solution of the second problem

Whenever we are using grouping in the reports in that case some times we are deleting the corresponding column. Then in the tablix structure in a report definition that do not have a corresponding visual representation on the design surface. When you delete the column that was automatically added when you defined a row group, you are deleting the part of the tablix definition in the report that controls the rows that are associated with the row group, including t he row that contains the column headings.

So For this problem did not delete that column just give the width 0.0 inch to that column and set visibility property of that column.

3. Solution of the third problem

When we are using sub reports in our Reporting services that time in some cases column header is not repeating for this problem do one simple thing just delete the Table header and place that header in reports header with the proper alignment with reports. 

Unique constraint to allow multiple NULL's in Sql server

Unique constraint

Creating Unique constraint as NONCLUSTERED INDEX will allow the values are entered in a column of a table to be unique. Therefore only one NULL value can be allowed to be entered in a column.

However, you often get requirements when a column having unique constraint should allow the multiple NULLs ( not the values).

For example email address column in the login users table of public websites where email address should be unique and should all multiple NULLs to be inserted for non internet users.

Changing Unique constraint to allows multiple NULLs in Sql server:

In SQL2008, you can define a unique filtered index based on a predicate that excludes NULLs.

Syntax:

CREATE UNIQUE NONCLUSTERED INDEX idx_column_notnull ON table_name(column_name) WHERE column_name IS NOT NULL;

Here,

idx_column_notnull is the constraint name.

table_name is the table name.

column_name is the column name.

WHERE clause is to define the predicate to exclude what?