Sunday, November 27, 2005

www.omsme.com

and finally my company has a web site

Thursday, November 17, 2005

My Engagement

yep, today will be my enageement day.

Friday, November 11, 2005

Note To Self

I guess today is going to be a remarkable day in my life

Saturday, October 01, 2005

My Birth Day

Just 23 years old.

Monday, June 13, 2005

Thursday, May 05, 2005

SQL Server 2005 TSQL Enhancements: PIVOT and UNPIVOT Operators

We all know the old complex SELECT...CASE way of creating cross tab reports
in SQL 2005 the new PIVOT operator provides a more readable and easy way to create cross tab reports
the general syntax :

SELECT * FROM <table_source>
PIVOT (
aggregate_function ( value_column )
FOR pivot_column IN ( <column_list>)
) table_alias

for example we have a database with these two tables

Emps(EmpID,Name,ManagerID,DeptID)
Depts(DeptID,DeptName)

and we want to create a report that shows for each department the number of employees in each level
so the report should look like this




DeptNameLevel1Level2Level3
Development Department134
Sales Department110


first of all we need a query that returns the level of the emplyee we will use a Common Table Expression for this

with Emps_CTE (EmpID,Name,DeptID,Level)
as
(
select EmpID,Name,Emps.DeptID,0 as Level FROM Emps WHERE ManagerID is null
Union All
Select Emps.EmpID,Emps.Name,Emps.DeptID,Level + 1 FROM Emps Join Emps_CTE ON
Emps_CTE.EmpID=Emps.ManagerID
)

Now we have a table that contains the EmpID,Name,DeptID,Level this table will be our <table_source>
and we want to show the number of employees so our aggregate_function will be COUNT and our value_column will be EmpID,
the pivot_column is the column from which unique values will be turned into columns
in our case we want the values of the Level column (0,1,2,..) to appear
as columns so our pivot_column is the Level column

the <column_list> lists the values in the pivot_column that will become the column names of the output table,
the strange thing is that you can't populate the from a query i mean you can't for example say FOR pivot_column IN ( select * FROM other_table)

any way the final query will be

with Emps_CTE (EmpID,Name,DeptID,Level)
as
(
select EmpID,Name,Emps.DeptID,0 as Level FROM Emps WHERE ManagerID is null
Union All
Select Emps.EmpID,Emps.Name,Emps.DeptID,Level + 1 FROM Emps Join Emps_CTE ON
Emps_CTE.EmpID=Emps.ManagerID
)
select DeptName,[0] as FirstLevel,[1] as SecondLevel,[2] as ThirdLevel FROM
(select Emps_CTE.Level,Emps_CTE.DeptID,EmpID,DeptName from Emps_CTE JOIN Depts on Emps_CTE.DeptID=Depts.DeptID) p
Pivot
(Count(EmpID) For Level in([0],[1],[2])) as pvt

Saturday, April 30, 2005

SQL Server 2005 TSQL Enhancements: New APPLY Operator

Suppose you have a query that returns a data set and you want for each row returned you want to invoke a function or query that returns a table (table-valued function or query ), the new APPLY operator allowa you to do this

for example if you have a table Employees(EmpID,Name,ManagerID)
and you have the following function that takes an Employee id as a parameter and returns a table of employees under this employee and their level in the hierarchy


CREATE FUNCTION [dbo].[GetEmps](@EmpID [int])
RETURNS @RES TABLE (
Empid INT ,
EmpName nvarchar(50),
Level INT
)

AS
begin

with Emps_CTE (EmpID,Name,Level)
as
(
select EmpID,Name,0 as Level FROM Employees WHERE ManagerID is null and EmpID=@EmpID
Union All
Select Employees.EmpID,Employees.Name,Level + 1 FROM Employees Join Emps_CTE ON
Emps_CTE.EmpID=Employees.ManagerID

)

INSERT INTO @RES
select * FROM Emps_CTE
return



The following query will return for each employee the employees working under him and their level

SELECT Employees.EmpID,Employees.Name ,FollowsEmps.Empid as FollowEmpID,
FollowsEmps.EmpName as FollowEmpName,FollowsEmps.Level as Level

FROM Employees
cross APPLY dbo.GetEmps(Employees.EmpID) as FollowsEmps
end

Wednesday, April 27, 2005

SQL Server 2005 TSQL Enhancements: Common Table Expressions

A CTE is composed of an expression name, optional column list,
and a defining query.
Once defined, the CTE may be referenced in the executing SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement
for example:

use Northwind;
with Customers_CTE
as
(
select ContactName,CompanyName from Customers
)
select * from Customers_CTE


the beautiful thing about CTEs is that you can use them to create recursive queries
say you have the following table Employees(EmpID,Name,ManagerID) and you want to select
each employee and his level in the employees hierarchy (with 0 as the top level) this can
be accomplished with the following CTE

With Emps_CTE (EmpID,Name,Level)
As
(
SELECT EmpID,Name, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL

Union All

Select Employees.EmpID,Employees.Name,Emps_CTE.Level+1 as Level
FROM Emps_CTE join Employees
on Emps_CTE.EmpID=Employees.ManagerID
)
Select * FROM Emps_CTE


Note that the defining query must contain at least two SELECT statements
combined by a UNION ALL operator

Thursday, April 07, 2005

Channel 9's First Birthday

Channel 9 is celebrating its first birthday

Monday, April 04, 2005

Programmers


"If builders built buildings the way that programmers wrote
programs, the first woodpecker that came along would destroy civilization"
- Weinberg’s Second Law
Dijkstra, A discipline of programming, 1976

Tuesday, February 08, 2005

Scrolling DataGrid (.Net CF)

this is what i found on the net on how to scroll a data grid
//Get a reference to the scroll bars in the data grid
VScrollBar vsb = (VScrollBar)grd.GetType().GetField("m_sbVert", BindingFlags.NonPublic|BindingFlags.GetField|BindingFlags.Instance).GetValue(grd);

HScrollBar hsb = (HScrollBar)grd.GetType().GetField("m_sbHorz", BindingFlags.NonPublic|BindingFlags.GetField|BindingFlags.Instance).GetValue(grd);


hsb.Value=hsb.Maximum ;
vsb.Value=vsb.Maximum ;

if anyone knows how to do the same for a ListView please let me know