cant figure out how to write query..

Hi,
I have 3 tables, a person table, a timeRecords table, and a
RegionPersonHistory table.
The timeRecords table holds how many days were worked for a particualr date,
and the RegionPersonHistory keeps track of the persons Region. People can be
allocated to work on different regions so they might be working in the US fo
r
3 days then the following 4 days are in Europe etc.
I am trying to figure out how to write a query that will calulate how many
days were worked in any one region of a any specific date period. The tricky
thing is that the RegionPersonHistory table only holds records for a person
if they change from their default region (their default region is held in th
e
Person table). It doesnt always hold records for the person.
Have a look at the sql below which sets up the tables and see if you
understand my problem.
Here is the sql for the tables and some sample data...
CREATE TABLE [SYSDBA].[RegionPersonHistory] (
[Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startdate] [datetime] NOT NULL ,
[enddate] [datetime] NOT NULL ,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
go
CREATE TABLE [SYSDBA].[TimeRecords] (
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startdate] [datetime] NOT NULL ,
[days] [integer] NOT NULL,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [SYSDBA].[Person] (
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
go
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-01',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-02',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-03',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-04',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-05',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-06',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-07',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-08',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-09',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-10',1
go
insert [SYSDBA].[Person]
select 'smithd','US'
go
insert [SYSDBA].[RegionPersonHistory]
select 'smithd','Europe','2006-01-04','2006-01-08'Can you give a sample result that you might need from the inputs?
"NH" wrote:

> Hi,
> I have 3 tables, a person table, a timeRecords table, and a
> RegionPersonHistory table.
> The timeRecords table holds how many days were worked for a particualr dat
e,
> and the RegionPersonHistory keeps track of the persons Region. People can
be
> allocated to work on different regions so they might be working in the US
for
> 3 days then the following 4 days are in Europe etc.
> I am trying to figure out how to write a query that will calulate how many
> days were worked in any one region of a any specific date period. The tric
ky
> thing is that the RegionPersonHistory table only holds records for a perso
n
> if they change from their default region (their default region is held in
the
> Person table). It doesnt always hold records for the person.
> Have a look at the sql below which sets up the tables and see if you
> understand my problem.
> Here is the sql for the tables and some sample data...
> CREATE TABLE [SYSDBA].[RegionPersonHistory] (
> [Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [startdate] [datetime] NOT NULL ,
> [enddate] [datetime] NOT NULL ,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [SYSDBA].[TimeRecords] (
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [startdate] [datetime] NOT NULL ,
> [days] [integer] NOT NULL,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [SYSDBA].[Person] (
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> go
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-01',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-02',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-03',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-04',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-05',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-06',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-07',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-08',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-09',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-10',1
> go
> insert [SYSDBA].[Person]
> select 'smithd','US'
> go
> insert [SYSDBA].[RegionPersonHistory]
> select 'smithd','Europe','2006-01-04','2006-01-08'|||Hello again Omnibuzz,
well, if I was to try to calculate the sum(days) that were worked in the US
region between 2006-01-01 and 2006-01-10 I would find that difficult because
theres a few days there that person 'smithd' logged while he was allocated t
o
the 'Europe' region. His default region is the US but the query needs to
check to see if any of the days logged between thoese dates were actually
part of a different region.
Those this make sense?
"Omnibuzz" wrote:
> Can you give a sample result that you might need from the inputs?
> "NH" wrote:
>|||Try this and let me know if this was what you wanted.
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-04'
select a.Persid,coalesce(b.region,c.region), count(a.days)
from Person c, TimeRecords a left outer join RegionPersonHistory b on
a.startdate between b.startdate and b.enddate
and a.persid = b.persid
where a.startdate between @.a and @.b
and a.persid = c.persid
group by a.persid,coalesce(b.region,c.region)|||Thats not quite right, that returns 8 days when it should be only 4.
Then also the query needs to take in a thrid paramter to filter for a
particualr region.
Maybe this is just a bit too messy...
"Omnibuzz" wrote:

> Try this and let me know if this was what you wanted.
>
> declare @.a datetime, @.b datetime
> set @.a = '2006-01-01'
> set @.b = '2006-01-04'
> select a.Persid,coalesce(b.region,c.region), count(a.days)
> from Person c, TimeRecords a left outer join RegionPersonHistory b on
> a.startdate between b.startdate and b.enddate
> and a.persid = b.persid
> where a.startdate between @.a and @.b
> and a.persid = c.persid
> group by a.persid,coalesce(b.region,c.region)|||It worked fine for the data you gave.
Can you give the data for which the error and tell me what are the filters
and what is the expected result
"NH" wrote:
> Thats not quite right, that returns 8 days when it should be only 4.
> Then also the query needs to take in a thrid paramter to filter for a
> particualr region.
> Maybe this is just a bit too messy...
> "Omnibuzz" wrote:
>|||sorry your query does return the same value I get.
I gave you a mistake in the source data, can you run this...
delete from RegionPersonHistory
insert [SYSDBA].[RegionPersonHistory]
select 'Europe','smithd','2006-01-04','2006-01-08'
I have this modified query now...
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-05'
select a.Persid, count(a.days)
from Person c, TimeRecords a
left join RegionPersonHistory b on (a.startdate between b.startdate and
b.enddate
and b.region='us')
where a.startdate between @.a and @.b
and a.persid = c.persid
and c.region='us'
group by a.persid
I am trying to only return days worked in the US... but cant figure it out..
.
"Omnibuzz" wrote:
> It worked fine for the data you gave.
> Can you give the data for which the error and tell me what are the filters
> and what is the expected result
> "NH" wrote:
>|||Okay, this works for me. You added the filter wrong.
I have changed my first query. And I saw the mitake in the insert and I
changed it :)
Check this and let me know if this works.
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-05'
select a.Persid,coalesce(b.region,c.region), count(a.days)
from Person c, TimeRecords a left outer join RegionPersonHistory b on
a.startdate between b.startdate and b.enddate
and a.persid = b.persid
where a.startdate between @.a and @.b
and a.persid = c.persid
and coalesce(b.region,c.region) = 'us'
group by a.persid,coalesce(b.region,c.region)|||Thanks Omnibuzz, it looks like this is working.
I appreciate your help once again.
NH
"Omnibuzz" wrote:

> Okay, this works for me. You added the filter wrong.
> I have changed my first query. And I saw the mitake in the insert and I
> changed it :)
> Check this and let me know if this works.
>
> declare @.a datetime, @.b datetime
> set @.a = '2006-01-01'
> set @.b = '2006-01-05'
> select a.Persid,coalesce(b.region,c.region), count(a.days)
> from Person c, TimeRecords a left outer join RegionPersonHistory b on
> a.startdate between b.startdate and b.enddate
> and a.persid = b.persid
> where a.startdate between @.a and @.b
> and a.persid = c.persid
> and coalesce(b.region,c.region) = 'us'
> group by a.persid,coalesce(b.region,c.region)
>