Selecting Multiple Rows as a Single Column in SQL

by jason29. March 2010 19:14

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...

Tags: ,

Development | SQL

SQL Bitwise Mask Example

by jason6. September 2007 11:53
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

Tags: , ,

Development | SQL

SQL Script to Find Work Week Start and End

by jason13. July 2006 00:23

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

Tags: , , ,

SQL

SQL Script to Find Last Day of the Month

by jason10. July 2006 23:42

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

Tags: , ,

SQL

Microsoft SQL Reporting Services URL Query Parameters and Commands

by jason9. July 2006 22:02

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

 

Tags: , ,

SQL | SSRS

About

Jason Williams is a .NET developer in Lincoln, Nebraska.

The name "Centrolutions" came out of a long search for a domain name. The goal was to create a name that conveyed an ideology of writing software centered (Centr--) on a solution (--olutions) for a particular problem. In other words, it was the only name in a long list that wasn't already registered on the internet.

If you're looking for the products I have for sale, you should go here.