March 21, 2003 at 11:39 am
Ok, i'm not sure about the best way to explain this but here goes. I have a report. Currently it shows:
Repname|weekend|data1|data2|data3|data4
I am showing this data for several weeks so I have a bit or repetition
Ex:
Joe Blow|1/10/2003|data1|data2|data3|
Joe Blow|1/17/2003|data1|data2|data3|
Joe Blow|2/07/2003|data1|data2|data3|
while this isn't exactly the way I want this print I can deal with it. However because i'm pulling this data from two tables I have a problem. Currently i'm joing two tables, Reps and RepAppts. I need to show ALL of the reps for a certain Mgr and all of the appts for that rep. My problem is every rep doesn't have an appt in RepAppts and if they do they many only have appts for 2 of the weekends in the query.
My question: How do I show ALL of the reps and ALL of the weekends.
Ex:
Joe Blow|1/10/2003|data1|data2|data3|
Joe Blow|1/17/2003|data1|data2|data3|
Joe Blow|2/07/2003|data1|data2|data3|
Bob Blow|1/10/2003|null|null|null|
Bob Blow|1/17/2003|data1|data2|data3|
Bob Blow|2/07/2003|null|null|null|
so far I have this:
SELECT (dbo.Reps.[Last] + ', ' + dbo.Reps.[First]) as repname, dbo.RepWeeklyApp.Made1st, dbo.RepWeeklyApp.Kept1st, dbo.RepWeeklyApp.PGs, dbo.RepWeeklyApp.MS,
dbo.RepWeeklyApp.Made2nd, dbo.RepWeeklyApp.Kept2nd, dbo.RepWeeklyApp.Closed2nd, dbo.RepWeeklyApp.LastEditDate,
dbo.RepWeeklyApp.RegManager, @weekend as 'weekend'
FROM dbo.Reps LEFT OUTER JOIN
dbo.RepWeeklyApp ON dbo.Reps.ID = dbo.RepWeeklyApp.RepID
where reps.regmanager=@mgr and (@weekend = weekend or weekend is null) and dateterminated is null order by last </code>
this gives me exactly what I need however instead of doing this for one weekend I need to be able to put in a date range
March 24, 2003 at 8:00 am
This was removed by the editor as SPAM
March 24, 2003 at 9:19 am
I wrote this prior to reading you entire post. I'm sure my table structure does not match yours. But possible some pivot table query like this might work for you. If not please disregard this post that does not really match your situation.
create table Reps (id int, name char(20))
create table RepAppts(id int, apptdate datetime, appt char(10))
insert into reps values (1,'Joe Blow')
insert into reps values (2,'Bob Blow')
insert into reps values (3,'Steve Blow')
insert into RepAppts values(1,'1/10/2003','data1')
insert into RepAppts values(1,'1/10/2003','data2')
insert into RepAppts values(1,'1/10/2003','data3')
insert into RepAppts values(1,'1/17/2003','data1')
insert into RepAppts values(1,'1/17/2003','data2')
insert into RepAppts values(1,'1/17/2003','data3')
insert into RepAppts values(1,'2/07/2003','data1')
insert into RepAppts values(1,'2/07/2003','data2')
insert into RepAppts values(1,'2/07/2003','data3')
insert into RepAppts values(3,'1/10/2003','data1')
insert into RepAppts values(3,'1/17/2003','data1')
insert into RepAppts values(3,'1/17/2003','data2')
-- declare variables
declare @p char(1000)
declare @i datetime
declare @cnta int
declare @cntr int
declare @m int
declare @wd datetime
declare @id int
declare @name char(20)
set @p = ''
select @cnta=count(distinct apptdate) from RepAppts
select @cntr=count(distinct id) from Reps
-- set @m to the first id number
set @m = 1
set @i = 1
select top 1 @wd = apptdate from (select distinct(apptdate) from RepAppts) a
select top 1 @id = id from Reps
while @i < = @cntr
begin
-- Process until no more items
while @m <= @cnta
begin
-- string together all items with a comma between
select @p = rtrim(@p) + '|'+ appt
from RepAppts a
where apptdate = @wd and id = @id
select @name = name from Reps where id =@id
-- print detail row
print @name + '|' + convert(char(10),@wd,101) + '|' + rtrim(substring(@p,2,len(@p)))
-- increment id number
set @p = ''
select top 1 @wd = apptdate from (select distinct(apptdate) from RepAppts) a where apptdate > @wd
end
set @i = @i+1
set @m = 1
select top 1 @wd = apptdate from (select distinct(apptdate) from RepAppts) a
select top 1 @id = id from Reps where id > @id
end
drop table Reps, RepAppts
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply