complex Query

Structure of DB
Fields
--
FieldID
Description
Details
--
DetailsID
FieldID
RangeDayID
RangeHourMonID
RangeHourTueID
RangeHourWedID
RangeHourThuID
RangeHourFriID
RangeHourSatID
RangeHourSunID
RangeDays
--
RangeDayID
>From (DateTime)
To (DateTime)
Open (Bool)
RangeHours
--
RangeHourID
>From (DateTime)
To (DateTime)
Open (Bool)
Reservations
--
ReservationID
UserID
DetailID
From
To
Users
--
UserID
Name
Example of RangeDays
From To Open
01/06/06 31/06/06 Yes
28/06/06 28/06/06 No
Example of RangeHours
From To Open
09:00 18:00 Yes
12:00 13:00 No
how should be the query for this result:
Mon-26 Tue-27 Wed-28 ...
08.00 Close Free Close
08.30 Close Free Close
09.00 Libero Luca Close
09.30 Marco Luca Close
10.00 Marco Free Close
...
Thanks a lot.Something to use to make test.
Thanks
use tempdb
set xact_abort on
begin transaction
create table Fields(FieldID int identity,Description varchar(20))
create table Details(DetailsID int identity,FieldID int,DayRange
int,HourLunRange int,HourMarRange int,HourMerRange int,HourGioRange
int,HourVenRange int,HourSabRange int,HourDomRange int)
create table RangeDays(DayID int identity,DayRange int,Da DateTime,A
DateTime,IsOpen bit)
create table RangeHours(HourID int identity,HourRange int,Da DateTime,A
DateTime,IsOpen bit)
create table Resevations(ResevationID int identity,UserID int,DetailsID
int,Da datetime,A datetime)
create table Users(UserID int identity,Nome varchar(20))
ALTER TABLE [dbo].[Fields] ADD
CONSTRAINT [PK_Fields] PRIMARY KEY CLUSTERED
(
[FieldID]
) ON [PRIMARY]
ALTER TABLE [dbo].[RangeDays] ADD
CONSTRAINT [PK_RangeDays] PRIMARY KEY CLUSTERED
(
[DayID]
) ON [PRIMARY]
ALTER TABLE [dbo].[RangeHours] ADD
CONSTRAINT [PK_RangeHours] PRIMARY KEY CLUSTERED
(
[HourID]
) ON [PRIMARY]
ALTER TABLE [dbo].[Users] ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
ALTER TABLE [dbo].[Details] ADD
CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED
(
[DetailsID]
) ON [PRIMARY]
ALTER TABLE [dbo].[Resevations] ADD
CONSTRAINT [PK_Resevations] PRIMARY KEY CLUSTERED
(
[ResevationID]
) ON [PRIMARY]
ALTER TABLE [dbo].[Details] ADD
CONSTRAINT [FK_Details_Fields] FOREIGN KEY
(
[FieldID]
) REFERENCES [dbo].[Fields] (
[FieldID]
)
ALTER TABLE [dbo].[Resevations] ADD
CONSTRAINT [FK_Resevations_Details] FOREIGN KEY
(
[DetailsID]
) REFERENCES [dbo].[Details] (
[DetailsID]
),
CONSTRAINT [FK_Resevations_Users] FOREIGN KEY
(
[UserID]
) REFERENCES [dbo].[Users] (
[UserID]
)
Insert into Users values('Marco')
Insert into Users values('Luca')
insert into Fields values('Soccer')
insert into RangeHours values(1,'09:00','18:00',1)
insert into RangeHours values(1,'12:00','13:00',0)
insert into RangeDays values(1,'20060601', '20060630', 1)
insert into RangeDays values(1,'20060611', '20060611', 0)
insert into Details values(1, 1, 1, 1, 1, 1, 1, 1, 1)
insert into Resevations values( 1, 1, '20060626 09:00' ,'20060626
10:00')
insert into Resevations values( 2, 1, '20060627 10:00' ,'20060627
12:00')
commit transaction|||Marco
First of all thanks fro posting DDL , it is realy helpful , however , can
you explain about your final result ?
It is not clear what's "Free","Close" and why do the users appear there?
Thanks
"Marco Montagnani" <marco.montagnani@.gmail.com> wrote in message
news:1151492024.943216.278430@.i40g2000cwc.googlegroups.com...
> Something to use to make test.
> Thanks
> use tempdb
> set xact_abort on
> begin transaction
> create table Fields(FieldID int identity,Description varchar(20))
> create table Details(DetailsID int identity,FieldID int,DayRange
> int,HourLunRange int,HourMarRange int,HourMerRange int,HourGioRange
> int,HourVenRange int,HourSabRange int,HourDomRange int)
> create table RangeDays(DayID int identity,DayRange int,Da DateTime,A
> DateTime,IsOpen bit)
> create table RangeHours(HourID int identity,HourRange int,Da DateTime,A
> DateTime,IsOpen bit)
> create table Resevations(ResevationID int identity,UserID int,DetailsID
> int,Da datetime,A datetime)
> create table Users(UserID int identity,Nome varchar(20))
> ALTER TABLE [dbo].[Fields] ADD
> CONSTRAINT [PK_Fields] PRIMARY KEY CLUSTERED
> (
> [FieldID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[RangeDays] ADD
> CONSTRAINT [PK_RangeDays] PRIMARY KEY CLUSTERED
> (
> [DayID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[RangeHours] ADD
> CONSTRAINT [PK_RangeHours] PRIMARY KEY CLUSTERED
> (
> [HourID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Users] ADD
> CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
> (
> [UserID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Details] ADD
> CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED
> (
> [DetailsID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Resevations] ADD
> CONSTRAINT [PK_Resevations] PRIMARY KEY CLUSTERED
> (
> [ResevationID]
> ) ON [PRIMARY]
> ALTER TABLE [dbo].[Details] ADD
> CONSTRAINT [FK_Details_Fields] FOREIGN KEY
> (
> [FieldID]
> ) REFERENCES [dbo].[Fields] (
> [FieldID]
> )
> ALTER TABLE [dbo].[Resevations] ADD
> CONSTRAINT [FK_Resevations_Details] FOREIGN KEY
> (
> [DetailsID]
> ) REFERENCES [dbo].[Details] (
> [DetailsID]
> ),
> CONSTRAINT [FK_Resevations_Users] FOREIGN KEY
> (
> [UserID]
> ) REFERENCES [dbo].[Users] (
> [UserID]
> )
>
> Insert into Users values('Marco')
> Insert into Users values('Luca')
> insert into Fields values('Soccer')
> insert into RangeHours values(1,'09:00','18:00',1)
> insert into RangeHours values(1,'12:00','13:00',0)
> insert into RangeDays values(1,'20060601', '20060630', 1)
> insert into RangeDays values(1,'20060611', '20060611', 0)
> insert into Details values(1, 1, 1, 1, 1, 1, 1, 1, 1)
> insert into Resevations values( 1, 1, '20060626 09:00' ,'20060626
> 10:00')
> insert into Resevations values( 2, 1, '20060627 10:00' ,'20060627
> 12:00')
> commit transaction
>|||Uri Dimant wrote:
> Marco
> First of all thanks fro posting DDL , it is realy helpful , however , can
> you explain about your final result ?
The result may be a wly report of availability of fields

> It is not clear what's "Free","Close" and why do the users appear there?
Free means that there is'nt a resevation to this fiels at that time.
Close means that the field is close (not reservable) at that time.|||Marco
create trigger tr_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0 return
insert MyAuditTable
select i.ID, d.MyColumn, i.MyColumn from inserted i join deleted d on
d.ID = o.Id
"Marco Montagnani" <marco.montagnani@.gmail.com> wrote in message
news:1151495588.559261.303250@.75g2000cwc.googlegroups.com...
> Uri Dimant wrote:
> The result may be a wly report of availability of fields
>
> Free means that there is'nt a resevation to this fiels at that time.
> Close means that the field is close (not reservable) at that time.
>|||Marco,sorry
Mon-26 Tue-27 Wed-28 ...
>08.00 Close Free Close
>08.30 Close Free Close
>09.00 Libero Luca Close
>09.30 Marco Luca Close
>10.00 Marco Free Close
Do you mean that your report will start on 8AM and till what?

> The result may be a wly report of availability of fields
Yep, I got it , but can you explain a little bit your business logic , what
are you trying to achive?
"Marco Montagnani" <marco.montagnani@.gmail.com> wrote in message
news:1151495588.559261.303250@.75g2000cwc.googlegroups.com...
> Uri Dimant wrote:
> The result may be a wly report of availability of fields
>
> Free means that there is'nt a resevation to this fiels at that time.
> Close means that the field is close (not reservable) at that time.
>|||> Yep, I got it , but can you explain a little bit your business logic , what
> are you trying to achive?
Sorry My english explanation.
I'am trying to archive a structure of fields reservations.
The result of the query may be a wly report of these reservations.