Sum function Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum function Problem

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2005-12-19 : 20:17:00

I have qurey in SQL server that I am running in Ms Access, which looks like this :
PARAMETERS [Forms]![frmReports]![txtFromDate] DateTime, [Forms]![frmReports]![txtToDate] DateTime;
SELECT tblRequestData.QueueID, tblQueues.QueueName, tblRequestData.StatusID, Count(tblRequestData.RequestID) AS CountOfRequestID, Sum(IIf(tblRequestData.[plannedcompletion]<Date(),1,0)) AS NumLate
FROM tblRequestData INNER JOIN tblQueues ON tblRequestData.QueueID = tblQueues.QueueID
WHERE (((tblRequestData.BeenDispatched)=True) AND ((tblRequestData.DateReceived)>=[Forms]![frmReports]![txtFromDate] And (tblRequestData.DateReceived)<=[Forms]![frmReports]![txtToDate]))
GROUP BY tblRequestData.QueueID, tblQueues.QueueName, tblRequestData.StatusID
ORDER BY tblQueues.QueueName;
Natually, I replace the from date with say 01/10/2001 and the To date with 12/30/2005, and I get back ( in Access) about 283 rows.

However, when I set this table up in SQL Server, and then tryed to do the same thing through a stored proceedure, I get most of the same results, but the NumLate collumn is not being sumed. This is my stored procedure:
CREATE PROCEDURE qryStatisticsNumLate
@FromDate AS smalldatetime,
@DateTo AS smalldatetime
AS
SELECT Distinct tblRequestData.QueueID, tblQueues.QueueName, tblRequestData.StatusID, Count(tblRequestData.RequestID) AS CountOfRequestID, Sum(CASE
When [tblRequestData].[plannedcompletion]<GetDate()
Then 1
Else 0
End ) AS NumLate
FROM tblRequestData INNER JOIN tblQueues ON tblRequestData.QueueID = tblQueues.QueueID
WHERE tblRequestData.BeenDispatched= 1 AND tblRequestData.DateReceived >= @FromDate And tblRequestData.DateReceived <= @DateTo
GROUP BY tblRequestData.QueueID, tblQueues.QueueName, tblRequestData.StatusID,[tblRequestData].[plannedcompletion]
ORDER BY tblQueues.QueueName;
GO

and this is how I am entering the data in query analyzer:

DECLARE @RC int
DECLARE @FromDate varchar(20)
DECLARE @DateTo varchar(20)
-- Set parameter values
EXEC @RC = [xdb12q_syst].[RTSuser].[qryStatisticsNumLate] @FromDate = '01/10/2001', @DateTo = '12/30/2005'

can someone tell me what I am doing worng?
Thank you

ITM

Arijit
Starting Member

25 Posts

Posted - 2005-12-20 : 07:44:20

Hi,
Can you please show me the recordset with sample data..
I need to build the dummy table..I think its a known probs..
Just explain it with more details..
Reg
Arijit

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2005-12-20 : 09:40:14

I am not sure how I would show you field and data here for the two tables used. but if you can tell be how I would be happy to show you.
thanks

ITM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources