December 4, 2008 at 9:37 am
A user logs in to the site.
We get the username and from that get the userid.
A userid has one or many districts.
A district has one or many centers.
A center has one or many plans.
A plan has one or many details where each detail has a workback date.
The end result should show all the plans that's workback dates
is due that week.
Here is an example, a user ttest with userid 2 logs in, this userid 2 has 3 districts (2, 3, 4). Each of this district has more than 1 centers. Like district 2 has centers (1, 3, 5, 6, 33) and district 3 has (2, 4, 7, 9, 23, 32) whereas district 4 has (11, 15, 18, 21, 31, 41).
Now each of these centers has more than one plans. And each plans has more than one details.
I need to write a stored procedure where at the end returns only those plans where the workback date falls in what date range a user specifies. Any idea?
December 4, 2008 at 10:02 am
Please see the links in my signature to see how to post to get better answers. Some DDL for the tables would be helpful. I think you are basically looking for something like this:
Create Procedure dbo.proc_name
(
@username varchar(50),
@start_date datetime,
@end_date datetime
)
As
Select
desired columns
From
dbo.users U Join
dbo.districts D On
U.userid = D.userid Join
dbo.centers C On
D.district_id = C.district_id join
dbo.plans P On
C.center_id = P.center_id Join
dbo.details DTL On
P.plan_id = DTL.plan_id
Where
U.username = @username And
DTL.workback_date >= @start_date And
DTL.workback_date < @end_date
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 4, 2008 at 2:30 pm
Thans Jack. That helped me a lot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply