complicated problem![:(]
-
hi!
im having a rather complicated query to be written
There are 2 tables members and franchisee in two different databases which are in 2 different locations.It is not possible to copy the tables to a common database.
I have the following problems
1)How to do a join on the 2 tables?
2)i need to write a query which accomplishes the following
List maximum 75 memebrs from the members table where the paidFlag in memebrs table is 'Paid' and franchisee.city=members.city
and month=previous month
This query will be run every month to show
paid memebrs of previous months,in each city
Also,
If more than 75 members are present then just show 75 in this month
and show the remaining records clubbed together with the paid members of NEXT month.
My problem is that if in a month the number of paid members is more than 75, how to keep track of the members which have not been shown in past month and show them in next months query??
Im a newbie and this is a rather complicated problem for me!
Thanks in advance!
-
To query across more than one database the best way is to add a linked server. Takes 10-15 mins the first time. Try BOL and see if you can get it working - to test just do a simple select on the remote table.
As for the puzzle, can you post DDL describing the tables involved? Makes it easier for us to find a solution.
Andy
-
quote:
My problem is that if in a month the number of paid members is more than 75, how to keep track of the members which have not been shown in past month and show them in next months query??
First what will be your deciding factor on who gets shown (TOP 75 ordered by date or name or city)? Then you could do two subqueries, 1st for KeyFeild Not In previous months top 75 and 2nd for KeyField In previous months Paid. Then you end up with knowing who was not in the Top 75 but was paid. For a better understanding please post the DDL of your tables and I can help with the writting of a query. Otherwise if you want more info on what I talking about I will be glad to give an example to help. But either way try what I said first to get a feel for what it does (all the more enjoyable to do that way IMHO).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
hi i figured out a solution and its working but there is one problem still
wat i did is i copied few records from members table into tempmembers and put tempmemebrs in the same location as franmaster.im able to get the record and im keeping track of remaining recs also using a query and then saving the recordset to a file.its all working fine with my test data.
but now my PM says that its not possible to copy members and franmaster in a same location.im not sure abt using a linked server also.is there any other option u all can suggest??Thanks so much!!
here is the DDL of the tables
tempMembers
-----------
MemberId char(60) not null
LoginName char(16) not null
PassWd char(16) not null
address1 char(40)
City char(40)
paidFlag char(1)
dddate datetime 'thjis is the date on which the demand draft was paid
franMaster
----------
franID bigint IDENTITY(10000001,1) primary key
MemberId char(16) NOT NULL
LoginName char(16)
PassWd(char(16)
Rate int
City char(40)
Firstname char(40)
Lastname char(40)
birthdate datetime
address
city
here are the procedures i wrote
1) this is to get ALL records meeting the specified condtions
CREATE PROCEDURE getITMates @cityParam char(50)
as
select m.firstname,m.lastname,m.dddate,m.phoneno,m.address1,m.emailid from
tempmembers m ,franMaster f where paidflag='1' and dddate is not null
and f.city=@cityParam and f.city=m.city and f.memberid=m.memberid
and Datepart(mm,dddate)=Month(dateadd(mm,-1,getdate()))
ORDER BY m.FIRSTNAME, m.LastName
2)this to get the remaining records
CREATE PROCEDURE getRemaining @cityParam char(50)
AS
select m.firstname,m.lastname,m.dddate,m.phoneno,m.address1,m.emailid from
tempmembers m ,franMaster f where paidflag='1' and dddate is not null
and f.city=@cityParam and f.city=m.city and f.memberid=m.memberid
and Datepart(mm,dddate)=Month(dateadd(mm,-1,getdate()))
and f.memberid not in
(select top 75 m.memberid from tempmembers m ,franMaster f where paidflag='1' and dddate is not null
and f.city=@cityParam and f.city=m.city and f.memberid=m.memberid and Datepart(mm,dddate)=Month(dateadd(mm,-1,getdate())) ORDER BY m.firstname,m.lastname)
i run the first procedure in my ASP page
,count number of total recs and show only 75. i then chk for remaining recs using procedure getReMaining.i save the recordset to a file.
now pls tell me how do i modify these if tempmemmbers is in a different location altogether(not jus a diff database).Also if any improvements can be done for the above.
Thanks!
-
A linked server is your only real option if you cannot copy data. This merely allows one server to query the other server.
If you do not want a linked server, you could always use DTS to copy over the records if you need to.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply