Code Snippets

public static DateTime GetDate(int month, int year, DayOfWeek day, int dayNumber)

{
// validation
if (dayNumber < 1
dayNumber > 5) return DateTime.Now;
if (month < 1
month > 12) return DateTime.Now;
if (year < 1900) return DateTime.Now;

// set up our root variables
int daysInMonth = DaysInMonth(month, year);
int earliestDay = (dayNumber * 7) - 6;
DateTime earliestDate = new DateTime(year, month, earliestDay);
// if the earliest date matches the day we are looking for, it is the correct date
if ((DayOfWeek)earliestDate.DayOfWeek == day)
return earliestDate;
else
{
// calculate the new day
int newDay = earliestDate.Day;
if ((int)earliestDate.DayOfWeek > (int)day)
{
newDay += 7 - ((int)earliestDate.DayOfWeek) + 1;
}
else
{
newDay += ((int)day - (int)earliestDate.DayOfWeek);
}
if (newDay > daysInMonth)
{
// new day is more than the days in the month, so we need to go backwards
// start the day at the last day of the month
return GetDate(month, year, day, 4);
}
return new DateTime(year, month, newDay);
}
}


PIVOT TABLES -- SQL SERVER 2005

CREATE TABLE [dbo].[Quotas](
[Week] [smallint] NOT NULL,
[Days] [varchar](3) NOT NULL,
[Quota] [smallint] NOT NULL,
CONSTRAINT [PK_Quotas] PRIMARY KEY CLUSTERED
(
[Week] ASC,
[Days] ASC
)
Inserts
insert into Quotas (Week, Days, Quota) values (1, '1', 8)
insert into Quotas (Week, Days, Quota) values (1, '2', 7)
insert into Quotas (Week, Days, Quota) values (1, '3', 6)
insert into Quotas (Week, Days, Quota) values (2, '1', 5)
insert into Quotas (Week, Days, Quota) values (2, '2', 4)
insert into Quotas (Week, Days, Quota) values (2, '3', 3)
insert into Quotas (Week, Days, Quota) values (3, '3.5', 2)

SQL

select *
from
(select Week, days, quota from Quotas) q
PIVOT
(
sum(quota)
FOR days IN([1],[2],[3],[3.5])
) as pvt

It requires a function in the first argument of the pivot keyword, so as long as the week/day combination are unique, your "sum" will always only be on one row, so it won't matter. Maybe you could try an isnull(quota,0) and see if that works.