Project WowNow: January 2009

Wednesday 28 January 2009

Position a DIV Horizontally and Vertically Using CSS.

Currently missing from CSS is the ability to position an element so that it is centred on the page, both horizontally and verticalally. But fear not, this effect is very simple to achieve.

This has been tested in IE7, Firefox3 and Safari3 and all work fine. (It may work in other browsers I just haven't tested it)

The idea is quite straight forward:

We build a standard horizontal layout, using text-align:center (for IE) and a wrapper div with margin:0 auto.

Then we add a div above the wrapper with height:50% and an top negative offset position of half the height of the wrapper div.

The wrapper will then be centred horizontally and vertically.

<html>
<head>
<style>

html, body {
margin: 0;
padding: 0;
text-align: center;
}

#layoutspacer {
position: relative;
margin-top: -100px; /* Half height of layoutwrapper */
height: 50%;
width: 100%;
}

#layoutwrapper {
background: yellow;
position: relative;
margin: 0 auto;
height: 200px; /* Adjust as appropriate */
width: 200px; /* Adjust as appropriate */
overflow: hidden;
}

</style>
</head>
<body>

<div id="layoutspacer"></div>
<div id="layoutwrapper">
Enter content here
</div>

</body>
</html>

...Centred...

Update: This solution only works in quirks mode, for a better solution see here

Tuesday 20 January 2009

T-SQL Function to Calculate Working Days Between Given Dates

This T-SQL function for SqlServer 2008 calculates the number of working days between two given dates.

IF OBJECT_ID('dbo.fnGetWorkingDays') IS NOT NULL
DROP FUNCTION dbo.fnGetWorkingDays;
GO

CREATE FUNCTION dbo.fnGetWorkingDays
( @pStartDate DATE,
@pEndDate DATE,
@pWorkingDays VARCHAR(7) = 'YYYYYNN' )
RETURNS INT
AS
BEGIN

DECLARE
@cMonday INT = DATEPART(dw,CONVERT(DATE,'29 OCT 2001',103)),
@vCheckDate DATE = @pStartDate,
@vDay INT,
@vDuration INT,
@vNoOfWeeks INT

IF @pStartDate IS NOT NULL AND @pEndDate IS NOT NULL
BEGIN

SET @vDuration = 0

WHILE @vCheckDate <= @pEndDate
BEGIN
SET @vDay = CAST(REPLACE((DATEPART(dw,@vCheckDate)+8-@cMonday)%7,'0','7') AS INT);
IF DATEDIFF(day,@vCheckDate,@pEndDate) < 7 OR @vDay != 1
BEGIN
IF SUBSTRING(@pWorkingDays,@vDay,1) = 'Y'
SET @vDuration = @vDuration + 1;
SET @vCheckDate = DATEADD(day,1,@vCheckDate);
END
ELSE
BEGIN
SET @vNoOfWeeks = FLOOR(DATEDIFF(day,@vCheckDate,@pEndDate)/7);
SET @vCheckDate = DATEADD(day,@vNoOfWeeks*7,@vCheckDate);
SET @vDuration = @vDuration+(@vNoOfWeeks*LEN(REPLACE(@pWorkingDays,'N','')));
END
END

END;

RETURN(@vDuration);

END
GO

To query the function for working days between 10th October 2009 and 11th November 2009

SELECT dbo.fnGetWorkingDays('10/10/2009','11/11/2009',DEFAULT) 'Working Days'
=
Working Days
23

This figure is for the default working days, Monday to Friday. This can be changed by providing a pWorkingDays parameter. The parameter contains seven characters of either Y or N. The first character is for Monday, second for Tuesday and so on. A Y represents it is a working day, an N represents it is a non-working day.

For those lucky enough not to work on a Wednesday, the same query would look like.

SELECT dbo.fnGetWorkingDays('10/10/2009','11/11/2009','YYNYYNN')
=
Working Days
18

Done

Thursday 15 January 2009

T-SQL Alternative to Oracle's REPLACE Stored Procedure Option

When working with Oracle (11g) stored procedures it's easy to add the "OR REPLACE" option so you can keep making modifications and re-creating the code.

However no such direct alternative exists in SqlServer T-SQL (2008). So when running scripts you need to add a check before creating your code to drop it if it exists.

So, a (very basic) Oracle PLSQL stored procedure, using the OR REPLACE option, may look like

CREATE OR REPLACE PROCEDURE spTest AS
BEGIN
NULL;
END;
/

the T-SQL script alternative needs to look like

IF OBJECT_ID('dbo.spTest') IS NOT NULL
DROP PROCEDURE dbo.spTest;
GO

CREATE PROCEDURE dbo.spTest AS
BEGIN
RETURN;
END
GO

Replaced.

Wednesday 7 January 2009

Display Child Rows as a Column With T-SQL (SQLServer)

It is often required to display data as a single column, especially data from child tables. This example looks at displaying a comma seperated list of data from a child table as a column in each row.

Senerio: Two tables, dept and emp, which stores departments and employees respectively. We want to display a comma seperated list of employee names as a column on the department record.

CREATE TABLE dept
( deptNo INT,
deptName VARCHAR(30) );

CREATE TABLE emp
( empNo INT,
deptNo INT,
empName VARCHAR(30) );

INSERT INTO dept VALUES (1,'Accounts');
INSERT INTO dept VALUES (2,'Sales');
INSERT INTO dept VALUES (3,'Operations');

INSERT INTO emp VALUES (1,1,'Frank');
INSERT INTO emp VALUES (2,1,'Jane');
INSERT INTO emp VALUES (3,2,'Joe');
INSERT INTO emp VALUES (4,2,'Harry');
INSERT INTO emp VALUES (5,2,'Sarah');
INSERT INTO emp VALUES (6,3,'Nate');
INSERT INTO emp VALUES (7,3,'Buddy');

To add the emp data as a column we include a select statement as a column. To make the select statement return one row, rather than each record as a new row, we use the "FOR XML PATH" clause.

SELECT d.deptNo,
d.deptName,
( SELECT (CASE WHEN ROW_NUMBER()OVER(ORDER BY e.empName)=1 THEN '' ELSE ',' END)+e.empName
FROM emp e
WHERE e.deptNo = d.deptNo
ORDER BY e.empName
FOR XML PATH('') ) employees
FROM dept d;

deptNo deptName employees
------ -------- ---------
1 Accounts Frank,Jane
2 Sales Harry,Joe,Sarah
3 Operations Buddy,Nate

The CASE clause checks the ROW_NUMBER value, to avoid a leading, (or trailing) comma.

Done

See Also: Oracle SQL equivilent, to display row data as a column