Very slow query

[Guest]

I have a query that's running exceptionally slow. I've never seen a query
bahave this way but when the table view comes up, that data slowly fils in
each of the fields and if I scroll to the right, it slowly fills in again
cell-by-cell. The only thing that's onusual about this query is that is ises
a couple of custom functions that I downloaded from various places. Could
this be the issue? If so, what can I do about it?

I've posted the SQL below

SELECT dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon,
First(dbo_DailyHistRange.dtBegDate) AS FirstOfdtBegDate,
Last(dbo_DailyHistRange.dtEndDate) AS LastOfdtEndDate,
Sum(dbo_DailyHist.cSales) AS SumOfcSales, ([SumOfcSales]/[Interval
workday])*[IntervalBusMonth] AS NSales, ([SumOfcNetCollect]/[Interval
workday])*[IntervalBusMonth] AS NCollections, Sum(dbo_DailyHist.cCredits) AS
SumOfcCredits, Sum(dbo_DailyHist.cPlusAdj) AS SumOfcPlusAdj,
Sum(dbo_DailyHist.cNegAdj) AS SumOfcNegAdj, Sum(dbo_DailyHist.cNetCollect) AS
SumOfcNetCollect, Sum(dbo_DailyHist.cDiscounts) AS SumOfcDiscounts,
WorkingDays2([FirstOfdtBegDate],[LastOfdtEndDate]) AS [Interval workday],
FirstWorkDayOfMonth(dbo_DailyHist!dtSysMon) AS Expr1,
LastWorkDayOfMonth(dbo_DailyHist!dtSysMon) AS Expr2,
WorkingDays2([expr1],[expr2]) AS IntervalBusMonth
FROM dbo_DailyHistRange INNER JOIN dbo_DailyHist ON
(dbo_DailyHistRange.sAssignNum = dbo_DailyHist.sAssignNum) AND
(dbo_DailyHistRange.sRandNum = dbo_DailyHist.sRandNum) AND
(dbo_DailyHistRange.sTime = dbo_DailyHist.sTime) AND
(dbo_DailyHistRange.dtSysMon = dbo_DailyHist.dtSysMon) AND
(dbo_DailyHistRange.dtProc = dbo_DailyHist.dtProc) AND
(dbo_DailyHistRange.sLoanNum = dbo_DailyHist.sLoanNum) AND
(dbo_DailyHistRange.sCoNum = dbo_DailyHist.sCoNum) AND
(dbo_DailyHistRange.sCliNum = dbo_DailyHist.sCliNum)
WHERE (((dbo_DailyHistRange.sAssignNum) Like "*BB*"))
GROUP BY dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon,
([SumOfcSales]/[Interval workday])*[IntervalBusMonth]
HAVING (((First(dbo_DailyHistRange.dtBegDate)) Is Not Null) AND
((Last(dbo_DailyHistRange.dtEndDate)) Is Not Null))
ORDER BY dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon;

 

[Avlan]

I'm not an expert on this but you might try some simple
toruble-shooting: Is the CPU or disk or smt. using 100% capacity?

I can also imagine that it depends on how big your actual database is.
If the database is 5 G of data, I can imagine it takes some time to
perform a complicated query like yours. Test it on a 10 MB database and
see if performance is better.

Test it on a (much) faster computer, see if that makes a difference.

Remove parts of the query one by one and run it, does it suddenly
perform much better when a certain part is removed?

etc.

 

[Guest]

Thanks for the reply,

It's not a problem with the hardware or the size of the database. The query
itself is really that complicated either albeit, the naming conventions make
it look nasty.

 

[Avlan]

ok then I don't know :-D

 

[RuralGuy]

Functions in Query's slow them down, somethimes considerably. Could you
just bring down the raw data and perform the function on a form or report?

I have a query that's running exceptionally slow. I've never seen a
query bahave this way but when the table view comes up, that data slowly
fils in each of the fields and if I scroll to the right, it slowly fills
in again cell-by-cell. The only thing that's onusual about this query
is that is ises a couple of custom functions that I downloaded from
various places. Could this be the issue? If so, what can I do about
it?

I've posted the SQL below

SELECT dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon,
First(dbo_DailyHistRange.dtBegDate) AS FirstOfdtBegDate,
Last(dbo_DailyHistRange.dtEndDate) AS LastOfdtEndDate,
Sum(dbo_DailyHist.cSales) AS SumOfcSales, ([SumOfcSales]/[Interval
workday])*[IntervalBusMonth] AS NSales, ([SumOfcNetCollect]/[Interval
workday])*[IntervalBusMonth] AS NCollections,
Sum(dbo_DailyHist.cCredits) AS SumOfcCredits,
Sum(dbo_DailyHist.cPlusAdj) AS SumOfcPlusAdj, Sum(dbo_DailyHist.cNegAdj)
AS SumOfcNegAdj, Sum(dbo_DailyHist.cNetCollect) AS SumOfcNetCollect,
Sum(dbo_DailyHist.cDiscounts) AS SumOfcDiscounts,
WorkingDays2([FirstOfdtBegDate],[LastOfdtEndDate]) AS [Interval
workday], FirstWorkDayOfMonth(dbo_DailyHist!dtSysMon) AS Expr1,
LastWorkDayOfMonth(dbo_DailyHist!dtSysMon) AS Expr2,
WorkingDays2([expr1],[expr2]) AS IntervalBusMonth
FROM dbo_DailyHistRange INNER JOIN dbo_DailyHist ON
(dbo_DailyHistRange.sAssignNum = dbo_DailyHist.sAssignNum) AND
(dbo_DailyHistRange.sRandNum = dbo_DailyHist.sRandNum) AND
(dbo_DailyHistRange.sTime = dbo_DailyHist.sTime) AND
(dbo_DailyHistRange.dtSysMon = dbo_DailyHist.dtSysMon) AND
(dbo_DailyHistRange.dtProc = dbo_DailyHist.dtProc) AND
(dbo_DailyHistRange.sLoanNum = dbo_DailyHist.sLoanNum) AND
(dbo_DailyHistRange.sCoNum = dbo_DailyHist.sCoNum) AND
(dbo_DailyHistRange.sCliNum = dbo_DailyHist.sCliNum)
WHERE (((dbo_DailyHistRange.sAssignNum) Like "*BB*"))
GROUP BY dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon,
([SumOfcSales]/[Interval workday])*[IntervalBusMonth]
HAVING (((First(dbo_DailyHistRange.dtBegDate)) Is Not Null) AND
((Last(dbo_DailyHistRange.dtEndDate)) Is Not Null))
ORDER BY dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon;

hth

 

[Dirk Goldgar]

I have a query that's running exceptionally slow. I've never seen a
query bahave this way but when the table view comes up, that data
slowly fils in each of the fields and if I scroll to the right, it
slowly fills in again cell-by-cell. The only thing that's onusual
about this query is that is ises a couple of custom functions that I
downloaded from various places. Could this be the issue? If so,
what can I do about it?

I've posted the SQL below

SELECT dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon,
First(dbo_DailyHistRange.dtBegDate) AS FirstOfdtBegDate,
Last(dbo_DailyHistRange.dtEndDate) AS LastOfdtEndDate,
Sum(dbo_DailyHist.cSales) AS SumOfcSales, ([SumOfcSales]/[Interval
workday])*[IntervalBusMonth] AS NSales, ([SumOfcNetCollect]/[Interval
workday])*[IntervalBusMonth] AS NCollections,
Sum(dbo_DailyHist.cCredits) AS SumOfcCredits,
Sum(dbo_DailyHist.cPlusAdj) AS SumOfcPlusAdj,
Sum(dbo_DailyHist.cNegAdj) AS SumOfcNegAdj,
Sum(dbo_DailyHist.cNetCollect) AS SumOfcNetCollect,
Sum(dbo_DailyHist.cDiscounts) AS SumOfcDiscounts,
WorkingDays2([FirstOfdtBegDate],[LastOfdtEndDate]) AS [Interval
workday], FirstWorkDayOfMonth(dbo_DailyHist!dtSysMon) AS Expr1,
LastWorkDayOfMonth(dbo_DailyHist!dtSysMon) AS Expr2,
WorkingDays2([expr1],[expr2]) AS IntervalBusMonth
FROM dbo_DailyHistRange INNER JOIN dbo_DailyHist ON
(dbo_DailyHistRange.sAssignNum = dbo_DailyHist.sAssignNum) AND
(dbo_DailyHistRange.sRandNum = dbo_DailyHist.sRandNum) AND
(dbo_DailyHistRange.sTime = dbo_DailyHist.sTime) AND
(dbo_DailyHistRange.dtSysMon = dbo_DailyHist.dtSysMon) AND
(dbo_DailyHistRange.dtProc = dbo_DailyHist.dtProc) AND
(dbo_DailyHistRange.sLoanNum = dbo_DailyHist.sLoanNum) AND
(dbo_DailyHistRange.sCoNum = dbo_DailyHist.sCoNum) AND
(dbo_DailyHistRange.sCliNum = dbo_DailyHist.sCliNum)
WHERE (((dbo_DailyHistRange.sAssignNum) Like "*BB*"))
GROUP BY dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon,
([SumOfcSales]/[Interval workday])*[IntervalBusMonth]
HAVING (((First(dbo_DailyHistRange.dtBegDate)) Is Not Null) AND
((Last(dbo_DailyHistRange.dtEndDate)) Is Not Null))
ORDER BY dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon;

It looks to me like your use of custom functions is going to require
that every record in the table be brought across the network, or at
least all those that match the WHERE clause. Since it look like you're
using a SQL Server back-end, you might try recreating those functions as
UDFs in the back-end database (assuming you're using SQL 2000), then
convert the whole query to T-SQL and execute it as a pass-through query.

 

[Guest]

Hey Dirk,

Thanks for the response. I sincerly wish I had the first clue on how to
take the steps you just mentioned. I really don't know the first thing about
SQL and my expieriements with Pass-Through queries have been unsuccesful.

Thanks anyway though.

I have a query that's running exceptionally slow. I've never seen a
query bahave this way but when the table view comes up, that data
slowly fils in each of the fields and if I scroll to the right, it
slowly fills in again cell-by-cell. The only thing that's onusual
about this query is that is ises a couple of custom functions that I
downloaded from various places. Could this be the issue? If so,
what can I do about it?

I've posted the SQL below

SELECT dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon,
First(dbo_DailyHistRange.dtBegDate) AS FirstOfdtBegDate,
Last(dbo_DailyHistRange.dtEndDate) AS LastOfdtEndDate,
Sum(dbo_DailyHist.cSales) AS SumOfcSales, ([SumOfcSales]/[Interval
workday])*[IntervalBusMonth] AS NSales, ([SumOfcNetCollect]/[Interval
workday])*[IntervalBusMonth] AS NCollections,
Sum(dbo_DailyHist.cCredits) AS SumOfcCredits,
Sum(dbo_DailyHist.cPlusAdj) AS SumOfcPlusAdj,
Sum(dbo_DailyHist.cNegAdj) AS SumOfcNegAdj,
Sum(dbo_DailyHist.cNetCollect) AS SumOfcNetCollect,
Sum(dbo_DailyHist.cDiscounts) AS SumOfcDiscounts,
WorkingDays2([FirstOfdtBegDate],[LastOfdtEndDate]) AS [Interval
workday], FirstWorkDayOfMonth(dbo_DailyHist!dtSysMon) AS Expr1,
LastWorkDayOfMonth(dbo_DailyHist!dtSysMon) AS Expr2,
WorkingDays2([expr1],[expr2]) AS IntervalBusMonth
FROM dbo_DailyHistRange INNER JOIN dbo_DailyHist ON
(dbo_DailyHistRange.sAssignNum = dbo_DailyHist.sAssignNum) AND
(dbo_DailyHistRange.sRandNum = dbo_DailyHist.sRandNum) AND
(dbo_DailyHistRange.sTime = dbo_DailyHist.sTime) AND
(dbo_DailyHistRange.dtSysMon = dbo_DailyHist.dtSysMon) AND
(dbo_DailyHistRange.dtProc = dbo_DailyHist.dtProc) AND
(dbo_DailyHistRange.sLoanNum = dbo_DailyHist.sLoanNum) AND
(dbo_DailyHistRange.sCoNum = dbo_DailyHist.sCoNum) AND
(dbo_DailyHistRange.sCliNum = dbo_DailyHist.sCliNum)
WHERE (((dbo_DailyHistRange.sAssignNum) Like "*BB*"))
GROUP BY dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon,
([SumOfcSales]/[Interval workday])*[IntervalBusMonth]
HAVING (((First(dbo_DailyHistRange.dtBegDate)) Is Not Null) AND
((Last(dbo_DailyHistRange.dtEndDate)) Is Not Null))
ORDER BY dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon;

It looks to me like your use of custom functions is going to require
that every record in the table be brought across the network, or at
least all those that match the WHERE clause. Since it look like you're
using a SQL Server back-end, you might try recreating those functions as
UDFs in the back-end database (assuming you're using SQL 2000), then
convert the whole query to T-SQL and execute it as a pass-through query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hey Dirk,

Thanks for the response. I sincerly wish I had the first clue on how
to take the steps you just mentioned. I really don't know the first
thing about SQL and my expieriements with Pass-Through queries have
been unsuccesful.

I don't know if it will help you or not, but I've included below a SQL
script that creates user-defined functions (for SQL Server 2000 or
later) that should emulate the WorkingDays2 function. The function that
your pass-through query would call is fncCountWorkdays. This script
could be run in the SQL Query Analyzer, logged into the back-end
database, to create the functions. Note that some lines in the script
will have been wrapped by the newsreader; this may or may not create
any errors in the functions.

As a pass-through query, you'd have to change your query's SQL
significantly to make it conform to T-SQL syntax. There is no First()
or Last() aggregate function in T-SQL, so those would have to be
replaced with, maybe, Min() and Max(). And the wild-card characters are
different; you'd need to change

Like "*BB*"

to

Like '%BB%'

I don't have replacements handy for FirstWorkDayOfMonth() and
LastWorkDayOfMonth(), but I expect they'd be fairly easy to implement
using the functions that are defined in the script below, or you could
maybe redefine the expression where you use those functions just to get
fncCountWorkdays(<first day of the month>, <last day of the month>).

I do realize this may not be much help to you if you don't know much
about SQL Server and T-SQL, but maybe there's a DBA who can help you
out. Failing that, your best bet is probably to do as RuralGuy has
suggested and write a simple query that pulls only the records you need,
then base another query on that to do whatever further manipulations may
be necessary on those records alone.

Here's the script:

------------- start of SQL script ----------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO


/******
Object: User Defined Function dbo.fncCountHolidays
Script Date: 12/8/2004 4:06:47 PM
******/
CREATE FUNCTION dbo.fncCountHolidays(
@FromDate datetime,
@ToDate datetime)
RETURNS int
/**
Count the holidays between the two dates passed as @FromDate and
@ToDate. Any holidays known to the system are specified in a table
named "tblHolidays" with key "HolidayDate". This table may not be
present at all, indicating by its absence that the system is not taking
holidays into account.
**/
AS
BEGIN

DECLARE @CNT int

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME = 'tblHolidays')
SELECT @CNT = Count(*) FROM tblHolidays WHERE HolidayDate BETWEEN
@FromDate AND @ToDate
ELSE
SET @CNT = 0

RETURN @CNT

END



GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO


/******
Object: User Defined Function dbo.fncCountWorkdays
Script Date: 12/8/2004 4:06:47 PM
******/
CREATE FUNCTION dbo.fncCountWorkdays(
@FromDate datetime,
@ToDate datetime)
RETURNS int
/**
Count the business days (not counting weekends/holidays) in
a given date range.

Adapted from VB code published in
"Visual Basic Language Developer's Handbook"
by Ken Getz and Mike Gilbert
Copyright 2000; Sybex, Inc. All rights reserved.

Requires:
dbo.fncSkipHolidays
dbo.fncCountHolidays
dbo.IsWeekend

In:
@FromDate:
Date specifying the start of the range
@ToDate:
Date specifying the end of the range
(dates will be swapped if out of order)
Out:
Return Value:
Number of working days (not counting weekends
and, optionally, holidays) in the specified range.
**/
AS
BEGIN

DECLARE @Days int
DECLARE @WorkFrom datetime
DECLARE @WorkTo datetime

-- Copy the parameter dates to work variables, swapping them
-- if necessary.
IF @ToDate < @FromDate
BEGIN
SET @WorkFrom = @ToDate
SET @WorkTo = @FromDate
END
ELSE
BEGIN
SET @WorkFrom = @FromDate
SET @WorkTo = @ToDate
END

-- Adjust the start and end dates to the nearest weekdays
-- enclosed in the period.
SET @WorkFrom = dbo.fncSkipHolidays(@WorkFrom, 1)
SET @WorkTo = dbo.fncSkipHolidays(@WorkTo, -1)

-- If there are no weekdays in the period, return zero.
IF @WorkFrom > @WorkTo
SET @Days = 0
ELSE
BEGIN
-- start with the total days between the two dates.
SET @Days = DATEDIFF(d, @WorkFrom, @WorkTo) + 1

-- subtract the weekend days between the two dates.
SET @Days = @Days - (DATEDIFF(ww, @WorkFrom, @WorkTo) * 2)

-- also subtract any holidays between the dates.
SET @Days = @Days - dbo.fncCountHolidays(@WorkFrom, @WorkTo)
END

RETURN @Days

END


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/******
Object: User Defined Function dbo.fncIsWeekend
Script Date: 12/8/2004 4:06:48 PM
******/
CREATE FUNCTION dbo.fncIsWeekend(@InputDate datetime)
RETURNS smallint
/**
Return 1 if @InputDate is a weekend day, 0 if not.
This function must be modified if the local weekend
days aren't the ones that are hard-coded here.
**/
AS
BEGIN
RETURN CASE DATEPART(dw, @InputDate)
WHEN 1 THEN 1
WHEN 7 THEN 1
ELSE 0
END
END




GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/******
Object: User Defined Function dbo.fncSkipHolidays
Script Date: 12/8/2004 4:06:48 PM
******/
CREATE FUNCTION dbo.fncSkipHolidays(
@FromDate datetime,
@Increment As smallint)
RETURNS datetime
/**
Skip weekend days, and holidays if a table named "tblHolidays" exists
to define them. Return @FromDate + as many days as it takes to get to
a day that's not a holiday or weekend.

Based on an algorithm published in
"Visual Basic Language Developer's Handbook"
by Ken Getz and Mike Gilbert
**/
AS
BEGIN

DECLARE @WorkDate datetime
DECLARE @Holiday datetime

SET @WorkDate = @FromDate

/**
Move forward or backward from @FromDate, depending on
the value of @Increment, to the first Monday/last Friday
(if the date passed was a weekend day). Then skip holidays,
if any are defined. Repeat this entire process until you
get to a weekday. Unless the holiday table contains a row
for every day in the year (!), this should finally converge
on a weekday.
**/

WHILE 1=1 /* "do forever" */
BEGIN
WHILE dbo.fncIsWeekend(@WorkDate) = 1 SET @WorkDate = @WorkDate
+ @Increment

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME = 'tblHolidays')
BEGIN
SET @Holiday = (SELECT HolidayDate FROM tblHolidays WHERE
HolidayDate = @WorkDate)
IF @Holiday IS NOT NULL SET @WorkDate = @WorkDate + @Increment
END

IF dbo.fncIsWeekend(@WorkDate) = 0 BREAK
END

RETURN @WorkDate

END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------- end of SQL script ----------------

 

[Guest]

a couple of custom functions that I downloaded from various places. Could

Yes, this can make your query slow, as can GROUP BY,

"WorkingDays2([FirstOfdtBegDate],[LastOfdtEndDate]) "

will be called EVERY TIME "Interval workday" OR ANY
DERIVED VALUE is used in a field. So if [Interval Workday]
is slow, you can make the query faster by returning a saved
value for repeated calls. I've modified the Arvin Meyer
WorkingDays2 function to make it faster for situations like this
(see below).

Also, that "Having" clause is going to be slow. Can you
transform it into a "Where" clause? If the first BeginDate
is Null, doesn't that mean that all of the BeginDates will be
null?

I would recomend splitting the query into two, with some
of the grouping done before the functions are applied, so
that the having clause could be tested first and so that there
would be less sorting and grouping, EXCEPT THAT
FIRST and LAST are NOT RELIABLE in nested queries.
The sort order gets mixed up, and you get the wrong answer.
If at all possible, you should use MIN and MAX rather than
FIRST and LAST.

(david)


---------------------------
Public Function WorkingDays2(StartDate As Date, _
EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
' Note that this function has been modified to account for holidays. It
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

'----NEW CODE --- (david)
static savWorkingDays2
static savFirstDate
static savLastDate

if (StartDate = savFirstDate) _
and (EndDate = savLastDate) then
WorkingDays2 = savWorkingDays2
exit function
end if
'----MORE NEW CODE LATER --

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount
'---NEW CODE -- (david)
savWorkingDays2 = intCount
'---END NEW CODE ---

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function


---------------------------

I have a query that's running exceptionally slow. I've never seen a query
bahave this way but when the table view comes up, that data slowly fils in
each of the fields and if I scroll to the right, it slowly fills in again
cell-by-cell. The only thing that's onusual about this query is that is ises
a couple of custom functions that I downloaded from various places. Could
this be the issue? If so, what can I do about it?

I've posted the SQL below

SELECT dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon,
First(dbo_DailyHistRange.dtBegDate) AS FirstOfdtBegDate,
Last(dbo_DailyHistRange.dtEndDate) AS LastOfdtEndDate,
Sum(dbo_DailyHist.cSales) AS SumOfcSales, ([SumOfcSales]/[Interval
workday])*[IntervalBusMonth] AS NSales, ([SumOfcNetCollect]/[Interval
workday])*[IntervalBusMonth] AS NCollections, Sum(dbo_DailyHist.cCredits) AS
SumOfcCredits, Sum(dbo_DailyHist.cPlusAdj) AS SumOfcPlusAdj,
Sum(dbo_DailyHist.cNegAdj) AS SumOfcNegAdj, Sum(dbo_DailyHist.cNetCollect) AS
SumOfcNetCollect, Sum(dbo_DailyHist.cDiscounts) AS SumOfcDiscounts,
WorkingDays2([FirstOfdtBegDate],[LastOfdtEndDate]) AS [Interval workday],
FirstWorkDayOfMonth(dbo_DailyHist!dtSysMon) AS Expr1,
LastWorkDayOfMonth(dbo_DailyHist!dtSysMon) AS Expr2,
WorkingDays2([expr1],[expr2]) AS IntervalBusMonth
FROM dbo_DailyHistRange INNER JOIN dbo_DailyHist ON
(dbo_DailyHistRange.sAssignNum = dbo_DailyHist.sAssignNum) AND
(dbo_DailyHistRange.sRandNum = dbo_DailyHist.sRandNum) AND
(dbo_DailyHistRange.sTime = dbo_DailyHist.sTime) AND
(dbo_DailyHistRange.dtSysMon = dbo_DailyHist.dtSysMon) AND
(dbo_DailyHistRange.dtProc = dbo_DailyHist.dtProc) AND
(dbo_DailyHistRange.sLoanNum = dbo_DailyHist.sLoanNum) AND
(dbo_DailyHistRange.sCoNum = dbo_DailyHist.sCoNum) AND
(dbo_DailyHistRange.sCliNum = dbo_DailyHist.sCliNum)
WHERE (((dbo_DailyHistRange.sAssignNum) Like "*BB*"))
GROUP BY dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon,
([SumOfcSales]/[Interval workday])*[IntervalBusMonth]
HAVING (((First(dbo_DailyHistRange.dtBegDate)) Is Not Null) AND
((Last(dbo_DailyHistRange.dtEndDate)) Is Not Null))
ORDER BY dbo_DailyHist.sCliNum, dbo_DailyHist.dtSysMon;