Stored Procedure Question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure Question

Author

Topic

malikh
Starting Member

7 Posts

Posted - 2002-07-24 : 17:15:48

I asked this question through the "ask SQL Team" link. I need a quick answer , therefore, I apologize for the dual posting.

I need to create a report that will return a count of all applications submitted on a particular day. Then at the bottom of the report I need to count all the applications by status.

Example:

Date Number of Applications
7/18/02 4
7/20/02 7
7/30/02 1

Total Applications: 12
Processed: 4
Denied: 1
Approved: 7

I am trying to write the top part of the report. How do I get one row of date and the count to print and then move to the next date in the table?

Thanks

Here is what I have so far...

DECLARE submitdt_cursor CURSOR FOR

SELECT submitteddt FROM loanapp

--WHERE submitteddt >= '07/18/02'
ORDER BY submitteddt

declare @bydatecount int
set @bydatecount = 0

OPEN submitdt_cursor

-- Perform the first fetch.
FETCH NEXT FROM submitdt_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN
set @bydatecount=@bydatecount + 1;

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM submitdt_cursor

END

print 'Date Submitted'+ ' ' + 'Count'
print 'Count= '+ rtrim(convert(varchar(5),@bydatecount))

CLOSE submitdt_cursor

DEALLOCATE submitdt_cursor

GO

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-24 : 17:39:08

No No No !

select convert(char(10),submitteddt,1),count(*) as 'Applications'
from loanapp
group by convert(char(10),submitteddt,1)
order by convert(char(10),submitteddt,1)

HTH
Jasper Smith

malikh
Starting Member

7 Posts

Posted - 2002-07-24 : 18:07:54

quote:

No No No !

select convert(char(10),submitteddt,1),count(*) as 'Applications'
from loanapp
group by convert(char(10),submitteddt,1)
order by convert(char(10),submitteddt,1)

HTH
Jasper Smith

I replaced my SELECT with the above one. Here is how it stands now..

DECLARE submitdt_cursor CURSOR FOR

select convert(char(10),submitteddt,1)as 'Date Submitted' ,count(*) as 'Received'
from loanapp
group by convert(char(10),submitteddt,1)
order by convert(char(10),submitteddt,1)

declare @bydatecount int
set @bydatecount = 0

OPEN submitdt_cursor ...........

Here is the result set I get. How can I not get the "rows affected"?
Date Submitted Received
-------------- -----------
07/18/02 1

(1 row(s) affected)

Date Submitted Received
-------------- -----------
07/19/02 1

(1 row(s) affected)

Date Submitted Received
-------------- -----------
07/22/02 3

(1 row(s) affected)

Date Submitted Received
-------------- -----------
07/30/02 1

(1 row(s) affected)

Date Submitted Received
-------------- -----------

(0 row(s) affected)

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-24 : 18:44:39

quote:

No No No !

By this I was referring to the CURSOR - there is no need for it

just do this

SET NOCOUNT ON

select convert(char(10),submitteddt,1)as 'Date Submitted' ,count(*) as 'Received'
from loanapp
group by convert(char(10),submitteddt,1)
order by convert(char(10),submitteddt,1)

That's it - nothing else

HTH
Jasper Smith

malikh
Starting Member

7 Posts

Posted - 2002-07-24 : 18:49:16

Thank you, Thank you, Thank you! You guys are great. Love this site.

malikh
Starting Member

7 Posts

Posted - 2002-07-24 : 18:52:07

One more quick question..what if I just want to find all applications in a date range? Would this SELECT you gave me become an embedded SELECT?

For example, if I want to find all applications sent between '06/01/02' and '08/01/02'.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-24 : 19:23:24

I'd throw in a having statement there...

select convert(char(10),submitteddt,1)as 'Date Submitted' ,count(*) as 'Received'
from loanapp
group by convert(char(10),submitteddt,1)
having submitteddt between '06/01/02' and '08/01/02'
order by convert(char(10),submitteddt,1)

-----------------------
Take my advice, I dare ya

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-25 : 04:51:58

Whilst the optimizer will deal with this by putting it in as a where clause and thus evaluate before the group by I'd still explicitly use a WHERE clause rather than a HAVING clause. Its a good idea to filter as many rows as possible before the grouping takes place. i.e.

select convert(char(10),submitteddt,1)as 'Date Submitted' ,count(*) as 'Received'
from loanapp
where submitteddt between '06/01/02' and '08/01/02'
group by convert(char(10),submitteddt,1)
order by convert(char(10),submitteddt,1)

FROM BOL:

quote:

Understanding the correct sequence in which the WHERE, GROUP BY, and HAVING clauses are applied helps in coding efficient queries:

The WHERE clause is used to filter the rows that result from the operations specified in the FROM clause.

The GROUP BY clause is used to group the output of the WHERE clause.

The HAVING clause is used to filter rows from the grouped result.
For any search conditions that could be applied either before or after the grouping operation, it is more efficient to specify them in the WHERE clause. This reduces the number of rows that have to be grouped. The only search conditions that should be specified in the HAVING clause are those search conditions that must be applied after the grouping operation has been performed.

The Microsoft® SQL Server™ 2000 query optimizer can deal with most of these conditions. If the query optimizer determines that a HAVING search condition can be applied before the grouping operation, it will do so. The query optimizer might not be able to recognize all of the HAVING search conditions that can be applied before the grouping operation. It is recommended that you place all such search conditions in the WHERE clause instead of the HAVING clause.

HTH
Jasper Smith

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-07-25 : 11:46:52

SIMCLMAO

Voted best SQL forum nickname...."Tutorial-D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources