top with group by...

[gtjr921]

I am trying to do a group by that will give me the top 5 sales items for each sales man on each date.

I know if i do something like

select date,salesman,salesitems,Numberofitemssold
group by date,salesman,salesitem,Numberofitemssold

it will give me all of the items sold by everysales men
I just want the top 5 items sold for each salesman per day.


I thought something like

select top 5 (Numberofitemssold),date,salesman,salesitems,
group by date,salesman,salesitem,Numberofitemssold

would give me tht info, but it only give me the top 5 for all users rather then for each user.

Last but not leat it there anyway to display this info on one row?
Something like

date    salesman salesitems            # Of itemssold
11/1/2006 Joe   Watch,ring,bracelet    20,15,8

 

[DotNetGnat]

try this:

Select A.salesman, A.salesitem, A.date,
A.numberofitemssold, A.[date] from mytable A
WHERE A.[date] IN (Select Top 5 B.[date]
from mytable B WHERE A.salesman = B.salesman
Order by B.[date] desc)

-DNG

 

[gtjr921]

I get incorrect syntax for Level 15, State 1, Line 6
Incorrect syntax near 'A'.
I copied your query exactly. I tried to figure out what was missing to no avail

 

[JD1866DSC]

Don't forget the WITH TIES code just in case you have more than 5 salesitems that have the same sales quantity
e.g
Sales Item Quantity
hat 6
gloves 6
scarf 5
Coat 4
Trousers 3
Shoes 3

Would you want to return all the salesitems as Trousers and Shoes have the same amount?

 

[gtjr921]

thanks for the tip about ties, but they just want the top 5 it doesn't matter to them if there are ties.
Any idea about my syntax error above?
Thanks

 

[JD1866DSC]

No, I just looked at it and it seems fine although A.date is referenced twice. I did the code below to check, I copied it straight into query analyzer and it returned data fine.

CREATE TABLE #MyTable(
SALESMAN VARCHAR(50)
,SALESITEM varchar(50)
,[Date] smalldatetime
,numberofitemssold int
)

insert into #mytable values('Fred','Scarf','1 Jan 05',9)

Select A.salesman, A.salesitem, A.date,
A.numberofitemssold, A.[date] from #mytable A
WHERE A.[date] IN (Select Top 5 B.[date]
from #mytable B WHERE A.salesman = B.salesman
Order by B.[date] desc)

 

[gtjr921]

Ok i got it working, except i must need to do something similar to with ties, with the opposite effect. If there is a tie say joe sells 1 scarfs and 1 watches on the same day then just give me one or the other not both. I guess there is not a without ties...

 

[JD1866DSC]

The code above is without WITH TIES so it should return 1 or the other not both. To include ties you would SELECT WITH TIES col1, col2, col3 etc. ok However it does seem that DotNetGnat's code doesn't fit your exact requirements.

I would have a column that marks each sale and reset the value on change of salesman

CREATE TABLE #MyTable(
IDENT INT
,SALESMAN VARCHAR(50)
,SALESITEM varchar(50)
,[Date] smalldatetime
,numberofitemssold int
)

insert into #mytable values(1,'Tom','Scarf','1 Jan 05',8)
insert into #mytable values(2,'Tom','Scarf','1 Jan 05',8)
insert into #mytable values(3,'Tom','Scarf','1 Jan 05',8)
insert into #mytable values(4,'Tom','Scarf','1 Jan 05',8)
insert into #mytable values(5,'Tom','Scarf','1 Jan 05',8)
insert into #mytable values(6,'Tom','Scarf','1 Jan 05',7)
insert into #mytable values(1,'John','Scarf','1 Jan 05',8)
insert into #mytable values(2,'John','Scarf','1 Jan 05',8)
insert into #mytable values(3,'John','Scarf','1 Jan 05',8)
insert into #mytable values(4,'John','Scarf','1 Jan 05',8)
insert into #mytable values(5,'John','Scarf','1 Jan 05',3)
insert into #mytable values(6,'John','Scarf','1 Jan 05',7)
insert into #mytable values(7,'John','Scarf','2 Jan 05',35)

Select A.salesman, A.salesitem, A.date,
A.numberofitemssold, A.[date],ident from #mytable A
where ident in (Select Top 5 IDENT
from #mytable B WHERE A.date = B.date
Order by B.[date] desc)

I understand that this might not be feasible, Just a suggestion let me know.

 

[gtjr921]

No it is returning more thank the top 5 if there are ties.
I am using sql 2005

for instance for one
person i get

salesmen item #sold
joe Scarf 20
joe watch 15
joe coat 15
joe shirt 7
joe pants 4
joe shoes 1

I also have a several(3 or 4) more complex query that relates to this in a way that i need to nest together i am having difficulty with,
if i send that or post it here can you help me out?
I am not very familer with nested queries etc.
Let me know thanks.

 

[JD1866DSC]

ok so you can't add an integer column that resets I'll have another think, post the query and I'll be happy to look at it.

 

[gtjr921]

Thanks here goes.. I am using Sql 2005
I have a DB Table that stores telephone call information for VOIP phones
I need to get
the total long distance calls per date by caller (which is the targetnum field LEN(Targetnum) >= 11 ),
total long distance calls per month by caller,
total long distance calls per day by caller,
average call length per day by caller (talkduration),
average call length year to date by caller,
number of local calls for a particular date by caller (LEN(Targetnum) >7 and LEN(Targetnum) <11)
number of local calls by year by caller (LEN(Targetnum) >7 and LEN(Targetnum) <11)
and the top 5 area codes(substring(A.Targetnum,2,3)) called by each caller for the year, the month, and the particular day
I have 4 queries that give me this data seperatly, I would like, if possible to combine them so i get get all the results as once
and then put the results in a report.
I get the bus date and year day counts so i get calculate averages in my report.
the queries will have a parameter (i know how to do that) that will represent a parameter for the date,month,year. so when i have the report someone will
click on a dropdown box with a list of dates and the date selected will send the complete date calls by day query,the datepart (month) to the calls by month query
and the datepart(year) to the calls by year query)
Here are my queries

--Calls by year
SELECT 
datepart(Month,GtCalcDate) As [Month],
datepart(Year,GtCalcDate) As [Year],
CallerName,
Sum (TalkDuration) /60 As TalkTimeTot,
  SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) >= 11  THEN 1 ELSE 0 END) AS [TotLDCallsPerMonth],
(select SUM(CASE WHEN datename(Weekday, q.GtCalcDate) = 'Saturday' or datename(Weekday, q.GtCalcDate) = 'Sunday'  THEN 0 ELSE 1 END) 
from (select distinct GtCalcDate from cdrmain )q)AS yeardaycount,
       A.DayCount As BusDayCount

FROM   CDRMAIN
       Inner Join (
         select  Month(GtCalcDate) As GTMonth,
                 Year(GtCalcDate) As GTYear,
                  SUM(CASE WHEN datename(Weekday, q.GtCalcDate) In ('Saturday', 'Sunday')  
                         THEN 0
                         ELSE 1
                         END) As DayCount
         from   (select distinct GtCalcDate from cdrmain) q
         Group By Month(GtCalcDate), Year(GtCalcDate)
         ) As A
         On Month(GtCalcDate) = A.GTMonth
         And Year(GtCalcDate) = A.GTYear

Where Len (TargetNum) >= 11  and  datepart(year,GtCalcDate)='2006'
GROUP BY datepart(Month,GtCalcDate),
datepart(Year,GtCalcDate), CallerName,a.daycount
HAVING  (CallerName = 'Caller1')Or
 (CallerName = 'Caller2')Or
 (CallerName = 'Caller3')Or
 (CallerName = 'Caller4')
order by
datepart(Month,GtCalcDate)desc,
datepart(Year,GtCalcDate)desc,callername asc

--calls by month

SELECT 
datepart(Month,GtCalcDate) As [Month],
datepart(Year,GtCalcDate) As [Year],
CallerName,
Sum (TalkDuration) /60 As TalkTimeTot,
 --sum (TalkDuration) /60 /@BusDay As AvgCallLengthDay,
  SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) >= 11  THEN 1 ELSE 0 END) AS [TotLDCallsPerMonth],
(select SUM(CASE WHEN datename(Weekday, q.GtCalcDate) = 'Saturday' or datename(Weekday, q.GtCalcDate) = 'Sunday'  THEN 0 ELSE 1 END) 
from (select distinct GtCalcDate from cdrmain )q)AS yeardaycount,
       A.DayCount As BusDayCount

FROM   CDRMAIN
       Inner Join (
         select  Month(GtCalcDate) As GTMonth,
                 Year(GtCalcDate) As GTYear,
                  SUM(CASE WHEN datename(Weekday, q.GtCalcDate) In ('Saturday', 'Sunday')  
                         THEN 0
                         ELSE 1
                         END) As DayCount
         from   (select distinct GtCalcDate from cdrmain) q
         Group By Month(GtCalcDate), Year(GtCalcDate)
         ) As A
         On Month(GtCalcDate) = A.GTMonth
         And Year(GtCalcDate) = A.GTYear

Where Len (TargetNum) >= 11  and  datepart(Month,GtCalcDate)='10'
GROUP BY datepart(Month,GtCalcDate),
datepart(Year,GtCalcDate), CallerName,a.daycount
HAVING  (CallerName = 'Caller1')Or
 (CallerName = 'Caller2')Or
 (CallerName = 'Caller3')Or
 (CallerName = 'Caller4')


order by
datepart(Month,GtCalcDate)desc,
datepart(Year,GtCalcDate)desc,callername asc

--calls by Day

SELECT 
gtcalcdate,
CallerName,
Sum (TalkDuration) /60 As TalkTimeTot,
  SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) >= 11  THEN 1 ELSE 0 END) AS [TotLDCallsDay]
from cdrmain
Where Len (TargetNum) >= 11  and  gtcalcdate = '2006-11-06 00:00:00.000'

GROUP BY CallerName,gtcalcdate
HAVING  (CallerName = 'Caller1')Or
 (CallerName = 'Caller2')Or
 (CallerName = 'Caller3')Or
 (CallerName = 'Caller4')

--Get top 5 area Codes per day I will need area codes by month and year i figured i didn't need to post the where clauses for that.

Select A.callername, count (substring(A.Targetnum,2,3)) As Acodecount,(substring(A.Targetnum,2,3)) as ACode, A.gtcalcdate
 from Cdrmain A Where
(LEN(TargetNum) >= 11) and (substring(A.Targetnum,2,3)) not like '800%'  And A.[gtcalcdate] 
IN (Select Top 5  B.[gtcalcdate]
from Cdrmain B WHERE A.GtcalcDate=B.Gtcalcdate and A.CallerName = B.CallerName
Order by B.[gtcalcdate] desc)
group by gtcalcdate,callername,(substring(A.Targetnum,2,3)) 
  HAVING  (CallerName = 'Caller1')Or
 (CallerName = 'Caller2')Or
 (CallerName = 'Caller3')Or
 (CallerName = 'Caller4')
order by gtcalcdate,CallerName,AcodeCount desc

here is the table

CREATE TABLE [dbo].[CDRMAIN](
	[Version] [int] NOT NULL,
	[NodeID] [int] NOT NULL,
	[StartTime] [int] NOT NULL,
	[EndTime] [int] NOT NULL,
	[GMTOffset] [int] NOT NULL,
	[LocalDay] [int] NOT NULL,
	[DayOfWeek] [int] NOT NULL,
	[SessionID] [int] NOT NULL,
	[SequenceID] [int] NOT NULL,
	[TrunkCall] [int] NULL,
	[Direction] [int] NULL,
	[OriginalPriority] [int] NULL,
	[StartPriority] [int] NULL,
	[EndPriority] [int] NULL,
	[CallerType] [int] NULL,
	[CallerPad] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CallerNum] [varchar](41) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CallerName] [varchar](65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CallerTenant] [varchar](33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CallerProject] [int] NULL,
	[TargetType] [int] NULL,
	[TargetPad] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TargetNum] [varchar](41) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TargetName] [varchar](65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TargetTenant] [varchar](33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TargetProject] [int] NULL,
	[TargetWGNum] [int] NULL,
	[WGSessionID] [int] NULL,
	[OutGoingWG] [int] NULL,
	[ConfSessionID] [int] NULL,
	[DNIS] [varchar](33) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[AccountCode] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[AADuration] [int] NULL,
	[RingDuration] [int] NULL,
	[QueueDuration] [int] NULL,
	[VMDuration] [int] NULL,
	[VMRecDuration] [int] NULL,
	[HoldDuration] [int] NULL,
	[TalkDuration] [int] NULL,
	[RecordDuration] [int] NULL,
	[MMCallType] [int] NULL,
	[PriorityQueueDuration] [int] NULL,
	[AnswerWithinSLT] [int] NULL,
	[ExitState] [int] NULL,
	[AbnTargetType] [int] NULL,
	[AdvQAppType] [int] NULL,
	[IVRExitPoint] [varchar](63) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[IVRData] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[UserData] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FormData] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[URLData] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[GtCalcDate]  AS (dateadd(month,([localday]%(10000))/(100)-(1),dateadd(day,[localday]%(100)-(1),dateadd(year,[localday]/(10000)-(1900),(0))))),
	[starttimecalc]  AS (dateadd(second,[starttime],'1 January 1970')),
	[endtimecalc]  AS (dateadd(second,[endtime],'1 January 1970'))
) ON [PRIMARY]

 

[gtjr921]

oops calls per day query should actually be

[SUM(CASE WHEN Direction = '2' and   (LEN(Targetnum) >7 and LEN(Targetnum) <11)   THEN 1 ELSE 0 END) AS [TotLDCallsDay]
from cdrmain
Where Len  (LEN(Targetnum) >7 and LEN(Targetnum)  <11) 
 and  gtcalcdate = '2006-11-06 00:00:00.000'

 

[JD1866DSC]

ok before I get carried away looking into the nity grity so to speak, when you say all results at once, do you mean you want to join the queries together like tables? or do you want to combine the result sets?

example to join the reults togther use the queries in derived tables like below

SELECT
A.col1
,B.col4
FROM
(
SELECT
1 AS ID
,col1
,col2
FROM
"Big Complex query"
)A
JOIN
(
SELECT
1 AS ID
,col3
,col4
FROM
"Big Complex query"
)B ON A.ID = B.ID

or to combine the results, columns must be the same type

SELECT
1 AS ID
,col1
,col2
FROM
"Big Complex query"

UNION ALL

SELECT
1 AS ID
,col3
,col4
FROM
"Big Complex query"

 

[gtjr921]

i guess i want to combine the result sets so
it ends up something like


callername daily calls monthly calls yearly calls .........
Dave 50 500 4000 .........

My ultimate goal here is to get this data into one sql report
without having to use subreports if that is at all possible.

Thanks

 

[gtjr921]

i think i want to join the results..

 

[JD1866DSC]

ok to join the results, you need to derive each query like in the first example literally put the query in brackets and use it as if the results set from the query was a table. I don't want to patronise you but do you understand the concept of Joining tables together ie JOIN, LEFT JOIN etc. if so this is exactly what you need to do using a column with matching values in each query like I've used ID in my example. If you don't fully understand then come back to me

 

[gtjr921]

Yes i know about joining tables let me see what i can figure out thanks for the help< I am sure i'll have more questions

 

[gtjr921]

I got it to work when I joined my first 2 queries but I tried to join a 3rd query and i get incorrect syntax near on on line 171 which is my on for Daily

SELECT
Yearly.CallerName as CallerNameID,
 Monthly.LocalCallCount,
Yearly.TotLDCallsPerYear,
monthly.year,
monthly.month,
Daily.TotLDDailyCalls
--,Day1.
FROM
(   --Monthly Calls
SELECT
     
datepart(Month,GtCalcDate) As [Month],
datepart(Year,GtCalcDate) As [Year],
CallerName,
Sum (TalkDuration) /60 As TalkTimeTot,
 --sum (TalkDuration) /60 /@BusDay As AvgCallLengthDay,

 SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) = 7  THEN 1 ELSE 0 END) AS [LocalCallCount],
  SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) >= 11  THEN 1 ELSE 0 END) AS [TotLDCallsPerMonth],
--SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) >= 11  THEN 1 ELSE 0 END) /21.5 AS [AvgLDCallsPerDay],Avg (TalkDuration)/60 As AvgTalk,
--SUM(CASE WHEN TargetNum = '411' THEN 1 ELSE 0 END) AS [411Ccount],
--SUM(CASE WHEN datename(weekday(GtCalcDate in 'Saturday','Sunday' THEN 0 ELSE 1 END)) AS busdaycount,
(select SUM(CASE WHEN datename(Weekday, q.GtCalcDate) = 'Saturday' or datename(Weekday, q.GtCalcDate) = 'Sunday'  THEN 0 ELSE 1 END) 
from (select distinct GtCalcDate from cdrmain )q)AS yeardaycount,
       A.DayCount As BusDayCount

FROM   CDRMAIN
       Inner Join (
         select  Month(GtCalcDate) As GTMonth,
                 Year(GtCalcDate) As GTYear,
                  SUM(CASE WHEN datename(Weekday, q.GtCalcDate) In ('Saturday', 'Sunday')  
                         THEN 0
                         ELSE 1
                         END) As DayCount
         from   (select distinct GtCalcDate from cdrmain) q
         Group By Month(GtCalcDate), Year(GtCalcDate)
         ) As A
         On Month(GtCalcDate) = A.GTMonth
         And Year(GtCalcDate) = A.GTYear
Where Len (TargetNum) >= 10 or TargetNum ='411' or  Len (TargetNum) >= 7
GROUP BY datepart(Month,GtCalcDate),
datepart(Year,GtCalcDate), CallerName,a.daycount
HAVING  (CallerName = 'Beth Scharstein')Or
 (CallerName = 'Betsy Enderle')Or
 (CallerName = 'Christian Heilman')Or
 (CallerName = 'Christine Fuka')Or
 (CallerName = 'Donna Crenshaw')Or
 (CallerName = 'Erin Love')Or
 (CallerName = 'Jackie Malblanc')Or
 (CallerName = 'Jeanne Peddicord')Or
 (CallerName = 'Jeff Dorrance')Or
 (CallerName = 'Jessica Vail')Or
 (CallerName = 'Jill Garand')Or
 (CallerName = 'Kelly Hardin')Or
 (CallerName = 'Laura Moore')Or
 (CallerName = 'Laura Wosiak')Or
 (CallerName = 'Lynn Romer')Or
 (CallerName = 'Malblanc Martin')Or
 (CallerName = 'Monica Leighty')Or
 (CallerName = 'Regina Carraher')Or
 (CallerName = 'Robin Welling')Or
 (CallerName = 'Rochelle Myers')Or
 (CallerName = 'Ryan Tibbs')Or
 (CallerName = 'Sonya Hughes')Or
 (CallerName = 'James Tansor')Or
 (CallerName = 'Renee Lowe')

--order by
--datepart(Month,GtCalcDate)desc,
--datepart(Year,GtCalcDate)desc,callername asc

)Monthly
JOIN
(   --Yearly Calls
SELECT 
datepart(Month,GtCalcDate) As [Month],
datepart(Year,GtCalcDate) As [Year],
CallerName,
Sum (TalkDuration) /60 As TalkTimeTot,
 --sum (TalkDuration) /60 /@BusDay As AvgCallLengthDay,
  SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) >= 11  THEN 1 ELSE 0 END) AS [TotLDCallsPerYear],
(select SUM(CASE WHEN datename(Weekday, q.GtCalcDate) = 'Saturday' or datename(Weekday, q.GtCalcDate) = 'Sunday'  THEN 0 ELSE 1 END) 
from (select distinct GtCalcDate from cdrmain )q)AS yeardaycount,
       A.DayCount As BusDayCount

FROM   CDRMAIN
       Inner Join (
         select  Month(GtCalcDate) As GTMonth,
                 Year(GtCalcDate) As GTYear,
                  SUM(CASE WHEN datename(Weekday, q.GtCalcDate) In ('Saturday', 'Sunday')  
                         THEN 0
                         ELSE 1
                         END) As DayCount
         from   (select distinct GtCalcDate from cdrmain) q
         Group By Month(GtCalcDate), Year(GtCalcDate)
         ) As A
         On Month(GtCalcDate) = A.GTMonth
         And Year(GtCalcDate) = A.GTYear

Where Len (TargetNum) >= 11  and  datepart(year,GtCalcDate)='2006'
GROUP BY datepart(Month,GtCalcDate),
datepart(Year,GtCalcDate), CallerName,a.daycount
HAVING  (CallerName = 'Beth Scharstein')Or
 (CallerName = 'Betsy Enderle')Or
 (CallerName = 'Christian Heilman')Or
 (CallerName = 'Christine Fuka')Or
 (CallerName = 'Donna Crenshaw')Or
 (CallerName = 'Erin Love')Or
 (CallerName = 'Jackie Malblanc')Or
 (CallerName = 'Jeanne Peddicord')Or
 (CallerName = 'Jeff Dorrance')Or
 (CallerName = 'Jessica Vail')Or
 (CallerName = 'Jill Garand')Or
 (CallerName = 'Kelly Hardin')Or
 (CallerName = 'Laura Moore')Or
 (CallerName = 'Laura Wosiak')Or
 (CallerName = 'Lynn Romer')Or
 (CallerName = 'Malblanc Martin')Or
 (CallerName = 'Monica Leighty')Or
 (CallerName = 'Regina Carraher')Or
 (CallerName = 'Robin Welling')Or
 (CallerName = 'Rochelle Myers')Or
 (CallerName = 'Ryan Tibbs')Or
 (CallerName = 'Sonya Hughes')Or
 (CallerName = 'James Tansor')Or
 (CallerName = 'Renee Lowe')
--order by
--datepart(Month,GtCalcDate)desc,
--datepart(Year,GtCalcDate)desc,callername asc

)Yearly on Yearly.CallerName = Monthly.CallerName
Join
(SELECT 
datepart(Month,GtCalcDate) As [Month],
datepart(Year,GtCalcDate) As [Year],
CallerName,
Sum (TalkDuration) /60 As TalkTimeTot,
 --sum (TalkDuration) /60 /@BusDay As AvgCallLengthDay,
  SUM(CASE WHEN Direction = '2' and  LEN(Targetnum) >= 11  THEN 1 ELSE 0 END) AS [TotLDDailyCalls],
(select SUM(CASE WHEN datename(Weekday, q.GtCalcDate) = 'Saturday' or datename(Weekday, q.GtCalcDate) = 'Sunday'  THEN 0 ELSE 1 END) 


Where Len (TargetNum) >= 11  and  GtCalcDate like '2006-11-06%' 
GROUP BY GtCalcDate, CallerName,talkduration
HAVING  (CallerName = 'Beth Scharstein')Or
 (CallerName = 'Betsy Enderle')Or
 (CallerName = 'Christian Heilman')Or
 (CallerName = 'Christine Fuka')Or
 (CallerName = 'Donna Crenshaw')Or
 (CallerName = 'Erin Love')Or
 (CallerName = 'Jackie Malblanc')Or
 (CallerName = 'Jeanne Peddicord')Or
 (CallerName = 'Jeff Dorrance')Or
 (CallerName = 'Jessica Vail')Or
 (CallerName = 'Jill Garand')Or
 (CallerName = 'Kelly Hardin')Or
 (CallerName = 'Laura Moore')Or
 (CallerName = 'Laura Wosiak')Or
 (CallerName = 'Lynn Romer')Or
 (CallerName = 'Malblanc Martin')Or
 (CallerName = 'Monica Leighty')Or
 (CallerName = 'Regina Carraher')Or
 (CallerName = 'Robin Welling')Or
 (CallerName = 'Rochelle Myers')Or
 (CallerName = 'Ryan Tibbs')Or
 (CallerName = 'Sonya Hughes')Or
 (CallerName = 'James Tansor')Or
 (CallerName = 'Renee Lowe')
[b]
)Daily ON Yearly.CallerName=Daily.CallerName[/b]

Group By yearly.callername,monthly.localcallcount,Yearly.TotLDCallsPerYear,monthly.year,daily.TotLDDailyCalls
monthly.month

 

[JD1866DSC]

Your missing a closing bracket of your sub select

(select SUM(CASE WHEN datename(Weekday, q.GtCalcDate) = 'Saturday' or datename(Weekday, q.GtCalcDate) = 'Sunday' THEN 0 ELSE 1 END))

Instead of

(select SUM(CASE WHEN datename(Weekday, q.GtCalcDate) = 'Saturday' or datename(Weekday, q.GtCalcDate) = 'Sunday' THEN 0 ELSE 1 END)