June 14, 2007 at 11:44 pm
Hi,
I have the following tables :
Town
towncode
townname
Area
areano
areaname
towncode
Locality
locno(pk)
areano(pk)
RequestType
reqid
reqdtls
Eg:
1 - Addition
2 - Removal
3 - Relocate
WebSummit
SummitId
RequestorName
DateOfRequest
reqid
Areano
LocNo
A Town has areas, Areas have locality so...
I want to find out the Town/Area/Locality wise Addition/Removal/Relocation request that have come
during the last 1 month.
The query I have written so far is as follows :
SELECT WebRequest.SummitId, RequestType.reqdtls, Area.areaname, Locality.locno, Town.townname
FROM RequestType INNER JOIN
WebRequest ON RequestType.reqid = WebRequest.reqid INNER JOIN
Area ON WebRequest.areano = Area.areano INNER JOIN
TownList ON Area.towncode = TownList.towncode INNER JOIN
Locality ON Area.areano = Locality.areano
However the results are with a lot of duplicates, repeated for each locality.
Kindly suggest me the right query.
Here's some sample data
Town
TownCode TownName
1 Conteck
Area
AreaNo AreaName TownCode
1 Area1 1
2 Area2 1
3 Area 3 1
4 Area4 1
5 Area5 1
6 Area6 1
7 Area7 1
Locality
LocNo AreaNo
1 1
2 1
3 1
1 2
2 2
1 3
2 3
3 3
4 3
1 4
2 4
1 5
2 5
3 5
Even though locality 1 may be in area 1 or area 3 as shown in the data above, the uniqueness is achieved using both the locno and areano together.
RequestType
reqid reqdtls
1 Addition
2 Removal
3 Relocate
WebSummit
SummitId RequestorName DateOfRequest reqid
AreaNo LocNo
1 John 12/6/2007 1 1 1
2 Jack 13/6/2007 1 1 1
3 Bill 12/6/2007 2 2 1
4 Ben 12/6/2007 2 2 2
5 Dale 14/6/2007 2 3 2
6 Evjen 15/6/2007 3 1 3
7 Fuller 16/6/2007 1 4 1
8 Jimmy 16/6/2007 3 4 2
9 Kart 16/6/2007 1 5 2
10 Fuller 16/6/2007 1 5 3
Thanks,
Vids
June 15, 2007 at 9:50 am
Your query doesn't match (table name wise) with the tables you posted. Assuming that "WebRequest" = "WebSummit" and "TownList" = "Town" I think the following will work:
SELECT distinct month(dateofrequest) [month], town.townname, area.areaname, locality.locno, RequestType.reqdtls
FROM RequestType INNER JOIN
Websummit ON RequestType.reqid = Websummit.reqid INNER JOIN
Area ON Websummit.areano = Area.areano INNER JOIN
Town ON Area.towncode = Town.towncode INNER JOIN
Locality ON Area.areano = Locality.areano
where dateadd(dd,-30,getdate()) < dateofrequest
NOTE: The date range for "last month" is a quick approximation there are forumulas for getting exactly 1 month a go or just from 1st of month.
James.
P.S. Here is your test data back in a usable format: (So you can see the output the same as I'm seeing it if you need to ask for a change).
if object_id('town') is not null drop table town ;
if object_id('area') is not null drop table area ;
if object_id('locality') is not null drop table locality ;
if object_id('requesttype') is not null drop table requesttype ;
if object_id('webrequest') is not null drop table websummit ;
create table town (towncode int, townname varchar(40)) ;
create table area (areano int,areaname varchar(40),towncode varchar(10)) ;
create table locality (locno int, areano int) ;
create table requesttype (reqid int,reqdtls varchar(10)) ;
create table websummit(summitid int, requestorname varchar(20),dateofrequest datetime, reqid int, areano int, locno int) ;
insert into town values (1,'contec')
insert into area select 1,'area1',1 union all
select 1, 'Area1', 1 union all
select 2, 'Area2', 1 union all
select 3, 'Area3', 1 union all
select 4, 'Area4', 1 union all
select 5, 'Area5', 1 union all
select 6, 'Area6', 1 union all
select 7, 'Area7', 1
insert into locality
select 1, 1 union all
select 2, 1 union all
select 3, 1 union all
select 1, 2 union all
select 2, 2 union all
select 1, 3 union all
select 2, 3 union all
select 3, 3 union all
select 4, 3 union all
select 1, 4 union all
select 2, 4 union all
select 1, 5 union all
select 2, 5 union all
select 3, 5
insert into requesttype
select 1, 'Addition' union all
select 2, 'Removal' union all
select 3, 'Relocate'
insert into websummit
select 1,'John','6/12/2007',1,1,1 union all
select 2,'Jack','6/13/2007',1,1,1 union all
select 3,'Bill','6/12/2007',2,2,1 union all
select 4,'Ben','6/12/2007',2,2,2 union all
select 5,'Dale','6/14/2007',2,3,2 union all
select 6,'Evjen','6/15/2007',3,1,3 union all
select 7,'Fuller','6/16/2007',1,4,1 union all
select 8,'Jimmy','6/16/2007',3,4,2 union all
select 9,'Kart','6/16/2007',1,5,2 union all
select 10,'Fuller','6/16/2007',1,5,3
go
SELECT distinct month(dateofrequest) [month], town.townname, area.areaname, locality.locno, RequestType.reqdtls
FROM RequestType INNER JOIN
Websummit ON RequestType.reqid = Websummit.reqid INNER JOIN
Area ON Websummit.areano = Area.areano INNER JOIN
Town ON Area.towncode = Town.towncode INNER JOIN
Locality ON Area.areano = Locality.areano
where dateadd(dd,-30,getdate()) < dateofrequest
--James
June 15, 2007 at 12:27 pm
Thanks JLK.
That was very helpful. A friend of mine badly needed this solution.
I will try out the solution.
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply