Dates Help Needed

Hey Gurus
Can you give me a clue to how to do the produce the following Output from
below table.
insert into Q2 (Emp_name,Category,StartDate,EndDate)
Select 'John', 'A10', '19961001','20000807'
Union
Select'John', 'G20', '20000803','20000815'
Union
Select 'John', 'A20', '20000807','20000822'
Union
Select'John', 'G30', '20000817','20000825'
Union
Select'John', 'A30', '20000822','99991231'
I want the result to Look like
Emp_Name Category1 Category2 StartDate EndDAte
John A10 20000801 20000803
John A10 G20 20000803 20000807
John A20 G20 20000807 20000815
John A20 20000815 20000817
John A20 G30 20000817 20000822
John A30 G30 20000822 20000825
John A30 20000825 20000831Jason
What is the purpose? Can you explain why would you want this ouptut? Based
on what?
"Jason" <bornscorpio30@.yahoo.com> wrote in message
news:eV341XZSGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Hey Gurus
> Can you give me a clue to how to do the produce the following Output from
> below table.
> insert into Q2 (Emp_name,Category,StartDate,EndDate)
> Select 'John', 'A10', '19961001','20000807'
> Union
> Select'John', 'G20', '20000803','20000815'
> Union
> Select 'John', 'A20', '20000807','20000822'
> Union
> Select'John', 'G30', '20000817','20000825'
> Union
> Select'John', 'A30', '20000822','99991231'
> I want the result to Look like
> Emp_Name Category1 Category2 StartDate EndDAte
> John A10 20000801 20000803
> John A10 G20 20000803 20000807
> John A20 G20 20000807 20000815
> John A20 20000815 20000817
> John A20 G30 20000817 20000822
> John A30 G30 20000822 20000825
> John A30 20000825 20000831
>|||Cause that is the report that i need to produce.
for employees, showing what catefory they fall under dusing different time
span
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%234L3keZSGHA.5728@.tk2msftngp13.phx.gbl...
> Jason
> What is the purpose? Can you explain why would you want this ouptut?
> Based on what?
>
> "Jason" <bornscorpio30@.yahoo.com> wrote in message
> news:eV341XZSGHA.3192@.TK2MSFTNGP09.phx.gbl...
>|||I think Uri is suggesting you give both more explanation and 'details' of
your output.Around here the more info you give the better off you are.
Guys like Uri are smart and skilled but the less you make him guess details
the more apt you are for him to figure out a solution.
"Jason" <bornscorpio30@.yahoo.com> wrote in message
news:%23od2tOeSGHA.4168@.tk2msftngp13.phx.gbl...
> Cause that is the report that i need to produce.
> for employees, showing what catefory they fall under dusing different time
> span
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%234L3keZSGHA.5728@.tk2msftngp13.phx.gbl...
>|||Please post DDL and better specs. I am assuming that on any given
date, you have 1 or 2 (vague, unnamed) categories and that you know
what a Calendar table is.
CREATE TABLE Foobar
(emp_name CHAR(10) NOT NULL,
foo_cat CHAR(3) NOT NULL
CHECK (SUBSTRING (foo_cat,1,1) IN ('A', 'G')),
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK(start_date < end_date),
PRIMARY KEY (emp_name, start_date));
Pick a date range (@.my_start_date, @.my_end_date) and use this query to
get the status on every date in that range.
SELECT F1.emp_name,
MIN(F.foo_cat) AS cat_1,
MAX(F.foo_cat) AS cat_2,
C.cal_date
FROM Calendar AS C
LEFT OUTER JOIN
Foobar AS F,
ON C.cal_date BETWEEN F.start_date AND F.end_date
WHERE C,cal_date BETWEEN @.my_start_date AND @.my_end_date;
If you really need to see this in ranges instead of day by day, we can
do that but it is messy and slow.