Recently, I was writing a report that selected some data out of a normalized SQL Server database. For one of the queries, I needed a “flattened” form of the rows in one of the tables. In essence, what I needed to do was turn several rows of data into a single column.

More...

DECLARE @SUN INT, @MON INT, @TUE INT, @WED INT, @THU INT, @FRI INT, @SAT INT
SELECT @SUN = 1,
@MON = 2,
@TUE = 4,
@WED = 8,
@THU = 16,
@FRI = 32,
@SAT = 64

'@DayMask may be stored in the database somewhere

DECLARE @DayMask INT
SELECT @DayMask = @SUN @MON @TUE @FRI
Print 'Day Mask: ' + CAST(@DayMask AS VARCHAR(10))

DECLARE @IsSUN BIT, @IsMON BIT, @IsTUE BIT, @IsWED BIT, @IsTHU BIT, @IsFRI BIT, @IsSAT BIT
SELECT @IsSUN = @DayMask & @SUN,
@IsMON = @DayMask & @MON,
@IsTUE = @DayMask & @TUE,
@IsWED = @DayMask & @WED,
@IsTHU = @DayMask & @THU,
@IsFRI = @DayMask & @FRI,
@IsSAT = @DayMask & @SAT

SELECT @IsSUN AS Sunday,
@IsMON AS Monday,
@IsTUE AS Tuesday,
@IsWED AS Wednesday,
@IsTHU AS Thursday,
@IsFRI AS Friday,
@IsSAT AS Saturday

If you are looking for the Monday that starts a work week and/or the Friday that ends the work week, this is how you do it in SQL:

DECLARE @Today AS DATETIME
SET @Today = GETDATE()

SELECT DATEADD(wk, DATEDIFF(wk, 0, @Today), 0) AS Monday, DATEADD(wk, DATEDIFF(wk, 4, @Today), 4) AS Friday

This Microsoft SQL script figures out what the last day of the month is (28, 30, or 31) so that you don't have to recite the "30 days has September, April, May, and November..." poem or do the "knuckle trick."

DECLARE @Date DATETIME
SET @Date = '1/1/2006'

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS LastDayOfMonth

There are several parameters that can be passed in to the SRS Report viewer through query string parameters. Some of them are listed below:

Change the way it is rendered:
rs:Command=Render& (4 types of re:Commands)

Pass in parameter values:
PARAMNAME1:isnull=true&PARAMNAME2=PARAMVALUE2&

Control the type of output:
rs:Format=PDF

Control what toolbar(s) show in the viewer:
rc:Toolbar=False
rc:Parameters=False