April 10, 2009 at 8:36 am
Hello All,
I have created this store procedure, and it gives me what I wanted. But there is one thing that I don't know, is anyone in here know how to get SUM at the bottom of one column in the output.
Below is my store procedure:
ALTER PROCEDURE [dbo].[WklyCasesClearedWithNoReturns]
-- Add the parameters for the stored procedure here
@Start Datetime,
@End Datetime,
@office varchar(5)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--select * from ROCAP
create table #temp
(
SocialSecurityNumber varchar(9),
ClearedDate datetime,
[Type] varchar(5),
DDS varchar(50),
TotalCleared int
)
if @office = 'ALL'
begin
--DE
insert into #temp
select SocialSecurityNumber, DEClearedDate as ClearedDate, 'DE' as [Type], DDS, NULL
from ROCAPData where
DEClearedDate between @start and @End
and DESecondClearedDate is NULL
and DEThirdClearedDate is NULL
and DEFourthClearedDate is NULL
Order BY ISNULL( DEClearedDate, '31-Dec-2090')
--Somatic
insert into #temp
select SocialSecurityNumber, SomaticMCClearedDate as ClearedDate, 'SM' as [Type], DDS, NULL
from ROCAPData where
SomaticMCClearedDate between @start and @End
and SomaticMCSecondClearedDate is NULL
and SomaticMCThirdClearedDate is NULL
and SomaticMCFourthClearedDate is NULL
Order BY ISNULL( SomaticMCClearedDate, '31-Dec-2090')
--Psyc
insert into #temp
select SocialSecurityNumber, PsycMCClearedDate as ClearedDate, 'PM' as [Type], DDS, NULL
from ROCAPData where
PsycMCClearedDate between @Start and @End
and PsycMCSecondClearedDate is NULL
and PsycMCThirdClearedDate is NULL
and PsycMCFourthClearedDate is NULL
Order BY ISNULL( PsycMCClearedDate, '31-Dec-2090')
end
else
begin
--DE
insert into #temp
select SocialSecurityNumber, DEClearedDate as ClearedDate, 'DE' as [Type], DDS, NULL
from ROCAPData where
DEClearedDate between @Start and @End
and DESecondClearedDate is NULL
and DEThirdClearedDate is NULL
and DEFourthClearedDate is NULL
and DDS = @office
Order BY ISNULL( DEClearedDate, '31-Dec-2090')
--Somatic
insert into #temp
select SocialSecurityNumber, SomaticMCClearedDate as ClearedDate, 'SM' as [Type], DDS, NULL
from ROCAPData where
SomaticMCClearedDate between @Start and @End
and SomaticMCSecondClearedDate is NULL
and SomaticMCThirdClearedDate is NULL
and SomaticMCFourthClearedDate is NULL
and DDS = @office
Order BY ISNULL( SomaticMCClearedDate, '31-Dec-2090')
--Psyc
insert into #temp
select SocialSecurityNumber, PsycMCClearedDate as ClearedDate, 'PM' as [Type], DDS, NULL
from ROCAPData where
PsycMCClearedDate between @Start and @End
and PsycMCSecondClearedDate is NULL
and PsycMCThirdClearedDate is NULL
and PsycMCFourthClearedDate is NULL
and DDS = @office
Order BY ISNULL( PsycMCClearedDate, '31-Dec-2090')
end
Select SocialSecurityNumber, ClearedDate, [Type], DDS, Count(*) As TotalCleared
from #temp
Group by SocialSecurityNumber, ClearedDate, [Type], DDS
Order BY ISNULL( ClearedDate, '31-Dec-2090')
END
Below is the output of store procedure:
SocialsecurityNumber ClearedDate Type DDS TotalCleared
111223333 1/1/2009 DE BO 1
123445153 1/3/2009 SM CT 1
--------------------------------------------------
2--> I wish to have sum at totalcleared
Thank you in advance
April 10, 2009 at 8:44 am
Lookup the ROLLUP keyword in Books Online. (you'll probably find examples under the section "GROUP BY (Transact-SQL)")
One other point, looking at your procedure. There's no point in an ORDER BY in an INSERT INTO statement. It won't affect anything.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2009 at 9:11 am
Select SocialSecurityNumber, ClearedDate, [Type], DDS, Count(*) As TotalCleared
from #temp
Group by SocialSecurityNumber, ClearedDate, [Type], DDS
Order BY ISNULL( ClearedDate, '31-Dec-2090')
You can have one Union All with the above query to count teh "TotalCleared".
Is this in any report? or any Front-End application.
April 10, 2009 at 9:19 am
Vijaya Kadiyala (4/10/2009)
Is this in any report? or any Front-End application.
Good point. This is definitely something that's best done in the front end/report rather than in the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2009 at 9:28 am
Normally all this kind of Summing along with the details comes from Reports or Front-End application. If this is the case then it is best to do at those layers instead of reading the same table again.
April 10, 2009 at 9:28 am
It looks like there is another thread on the same question:
http://www.sqlservercentral.com/Forums/Topic694194-338-1.aspx
April 10, 2009 at 10:14 am
Hello There,
This is in a report.
I wish to have a sum at the bottom of the column TotalCleared
Thanks
April 10, 2009 at 10:28 am
Reporting services? Crystal Reports? Some other reporting tool?
Most reporting tools have the ability to produce totals within the report, based on the data coming back from the DB. It's generally better to do this kind of totalling there, not in the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2009 at 10:44 am
Hello,
I think I solved my problem, I use reporting service.
But there is a small thing, I just want to ask you opionion and that is:
When I changed my store procedure code, it displays the results that I wish to have. But sum of the Totalcleared appears on Top (not bottom as I wish it to be). Do you know why? For example,
SocialSecurityNumber ClearedDate Type DDS TotalCleared
2
111223333 1/1/2009 DE BO 1
222334589 1/5/2009 SM CT 1
the number 2 should be at the bottom of TotalCleared (not on top).
ALTER PROCEDURE [dbo].[WklyCasesClearedWithNoReturns]
-- Add the parameters for the stored procedure here
@Start Datetime,
@End Datetime,
@office varchar(5)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--select * from ROCAP
create table #temp
(
SocialSecurityNumber varchar(9),
ClearedDate datetime,
[Type] varchar(5),
DDS varchar(50),
TotalCleared int
)
if @office = 'ALL'
begin
--DE
insert into #temp
select SocialSecurityNumber, DEClearedDate as ClearedDate, 'DE' as [Type], DDS, NULL
from ROCAPData where
DEClearedDate between @start and @End
and DESecondClearedDate is NULL
and DEThirdClearedDate is NULL
and DEFourthClearedDate is NULL
Order BY ISNULL( DEClearedDate, '31-Dec-2090')
--Somatic
insert into #temp
select SocialSecurityNumber, SomaticMCClearedDate as ClearedDate, 'SM' as [Type], DDS, NULL
from ROCAPData where
SomaticMCClearedDate between @start and @End
and SomaticMCSecondClearedDate is NULL
and SomaticMCThirdClearedDate is NULL
and SomaticMCFourthClearedDate is NULL
Order BY ISNULL( SomaticMCClearedDate, '31-Dec-2090')
--Psyc
insert into #temp
select SocialSecurityNumber, PsycMCClearedDate as ClearedDate, 'PM' as [Type], DDS, NULL
from ROCAPData where
PsycMCClearedDate between @Start and @End
and PsycMCSecondClearedDate is NULL
and PsycMCThirdClearedDate is NULL
and PsycMCFourthClearedDate is NULL
Order BY ISNULL( PsycMCClearedDate, '31-Dec-2090')
end
else
begin
--DE
insert into #temp
select SocialSecurityNumber, DEClearedDate as ClearedDate, 'DE' as [Type], DDS, NULL
from ROCAPData where
DEClearedDate between @Start and @End
and DESecondClearedDate is NULL
and DEThirdClearedDate is NULL
and DEFourthClearedDate is NULL
and DDS = @office
Order BY ISNULL( DEClearedDate, '31-Dec-2090')
--Somatic
insert into #temp
select SocialSecurityNumber, SomaticMCClearedDate as ClearedDate, 'SM' as [Type], DDS, NULL
from ROCAPData where
SomaticMCClearedDate between @Start and @End
and SomaticMCSecondClearedDate is NULL
and SomaticMCThirdClearedDate is NULL
and SomaticMCFourthClearedDate is NULL
and DDS = @office
Order BY ISNULL( SomaticMCClearedDate, '31-Dec-2090')
--Psyc
insert into #temp
select SocialSecurityNumber, PsycMCClearedDate as ClearedDate, 'PM' as [Type], DDS, NULL
from ROCAPData where
PsycMCClearedDate between @Start and @End
and PsycMCSecondClearedDate is NULL
and PsycMCThirdClearedDate is NULL
and PsycMCFourthClearedDate is NULL
and DDS = @office
Order BY ISNULL( PsycMCClearedDate, '31-Dec-2090')
end
Select SocialSecurityNumber, ClearedDate, [Type], DDS, Count(*) As TotalCleared
from #temp
Group By SocialSecurityNumber, ClearedDate, [Type], DDS
Union
Select Null, Null, Null, Null, count(*)as TotalCleared
from #temp
END
Thanks
April 10, 2009 at 11:56 am
josephptran2002 (4/10/2009)
I think I solved my problem, I use reporting service.
In that case I would strongly advise you to do the totalling in the report. Reporting services has very good support for groups and totals per group. Totalling should be done there, not in the database.
Re your ordering question. Since you didn't specify an ORDER BY in that last select, any order of rows is as valid as any other order. If you want a specific order, use ORDER BY.
But again, I will stress that the best place to do totalling for a report is on the report, not in the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2009 at 12:07 pm
If I use "Order By ISNULL(ClearedDate, '31-Dec-2090')" and place it like below:
Select SocialSecurityNumber, ClearedDate, [Type], DDS, Count(*) As TotalCleared
from #temp
Group By SocialSecurityNumber, ClearedDate, [Type], DDS
Union
Select Null, Null, Null, Null, count(*)as TotalCleared
from #temp
Order By ISNULL(ClearedDate, '31-Dec-2090')
END
then it gives a syntax error:
"ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator."
But if I place it like below, then it gives different syntax error:
"Incorrect syntax near the keyword Union"
Select SocialSecurityNumber, ClearedDate, [Type], DDS, Count(*) As TotalCleared
from #temp
Group By SocialSecurityNumber, ClearedDate, [Type], DDS
Order By ISNULL(ClearedDate, '31-Dec-2090')
Union
Select Null, Null, Null, Null, count(*)as TotalCleared
from #temp
END
Thanks
April 10, 2009 at 12:33 pm
That's not what you want to order the final result set by. You want to order it by something that makes the total come after the detail lines.
Think about it. How can you make that total come after the detail lines while still keeping the detail lines in the order that you want? Hint: you can have a derived column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2009 at 12:40 pm
I have to agree with Gail on this one - this should be done in the report, and could be done easily at that.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply