RS - Using IIF Statement

IIF statement is pretty much easy if it is a straight forward single condition. If it is nested IIF statement (multiple conditions), it might some time takes a little time to understand and use it.
Ex: If there are 4 conditions to check then use only 3 IIF conditions.
     This is an example of cell Background Color Expression in SSRS.

= IIF(Fields!Data1.Value=”Lite Turquoise”,”#CCFFFF”, (IIF(Fields!Data1.Value="Ivory","#FFFFCC",   (IIF(Fields!Data1.Value=”Plum”, “#993366”, “#9999FF”)))))

Hope this article helps...
Thank you.

RS - Color Pallette codes

This pallette, shows the color names and they significant HTML color code/ BGColor.

Note: This above images taken from http://www.mvps.org/dmcritchie/excel/colors.htm

Hope this article help you to use colors/color codes.
Thank you. :)

TSQL - BETWEEN - DateTime

Convert the Date in to integer:

SELECT GETDATE()                                             ---- 2010-11-29 10:12:13.880

SELECT    (DATEPART(YY,GETDATE())*10000)  + 
               (DATEPART(M,GETDATE())*100)      +
               (DATEPART(DD,GETDATE()))                              ---- 20101129 

TSQL - BETWEEN - DateTime

I was using BETWEEN statement with the column that has data type of DATETIME. When debugging or validating data, i found out that the count is mismatch due to the DATETIME.
Ex:

Create table #BetweenDateTime (RowNumber INT Identity(1,1), DateTimeTest DateTime)

Insert into #BetweenDateTime values ( '2010-07-21 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-21 00:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-20 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-23 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-25 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-26 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-23 16:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-28 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-29 17:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-29 15:11:50.760')
Insert into #BetweenDateTime values ( '2010-07-30 10:11:50.760')
From the above table:

Select * From #BetweenDateTime Where DateTimeTest Between '2010-07-21' And '2010-07-29'

It retrieve the following data:

RowNumber   DateTimeTest
1                     2010-07-21 17:11:50.760
2                     2010-07-21 00:11:50.760
4                     2010-07-23 17:11:50.760
5                     2010-07-25 17:11:50.760
6                     2010-07-26 17:11:50.760
7                     2010-07-23 16:11:50.760
8                     2010-07-28 17:11:50.760
the above retrieved data was invalid.
Solution:

Select * From #BetweenDateTime

Where Dateadd(Day, Datediff(Day, 0, DateTimeTest), 0) Between '2010-07-21' And '2010-07-29'

This returns the following data:
RowNumber  DateTimeTest
1                  2010-07-21 17:11:50.760
2                  2010-07-21 00:11:50.760
4                  2010-07-23 17:11:50.760
5                  2010-07-25 17:11:50.760
6                  2010-07-26 17:11:50.760
7                  2010-07-23 16:11:50.760
8                  2010-07-28 17:11:50.760
9                  2010-07-29 17:11:50.760
10                2010-07-29 15:11:50.760
i.e.  DATEADD(Day, DATEDIFF(Day, 0, DateTimeTest), 0) condition to round the DATETIME value to DATE.

try this for rounding values:
Select GetDate()
Select Dateadd(D, DateDiff(D, 0, GetDate()), 0)
Select Dateadd (M, DateDiff (M, 0, GetDate()), 0)
Select Dateadd (YYYY, DateDiff (YYYY, 0, GetDate()), 0)
Select Dateadd (Hour, DateDiff (HOUR, 0, GetDate()), 0)
I hope this article helps you!!!..
Thank you. :)

My First Post.....

Hi everyone!!!... This is my first post. I thought many times to create a blog to share my experiences. May be i was busy or lazy, but i couldn't start until today. Hopefully with full energy i started my blog BiSpecialist. I will update my blog with new item with simple to complex problems with solutions.