April 15, 2009 at 9:04 am
Hello ALL,
The codes that I wrote and it did not display the output as I wish it to be. But it's still not quite as I wish ( I don't know you know how to change it or not), I need it to bring out the outlook like below:
SocialSecurityNumber ClearedDate Type DDS TotalCleared
111223333 1/1/2009 SM BO 1
222351489 1/5/2009 DE CT 1
524478915 1/7/2009 PM ME 1
3 --> 3 is sum of all cases cleared in the column field name TotalCleared.
I wish to display sum at bottom of the column of "TotalCleared" such as 3 is sum of 1 + 1 +1. But somehow the result of the codes that you wrote and I changed a little bit at the end (bold words), did not display the result at the bottom but it displays on top as below.
SocialSecurityNumber ClearedDate Type DDS TotalCleared
3
111223333 1/1/2009 SM BO 1
222351489 1/5/2009 DE CT 1
524478915 1/7/2009 PM ME 1
DO YOU KNOW HOW TO CHANGE THE CODE, SO IT CAN DISPLAY AT THE BOTTOM?
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
THERE WERE SEVERAL ATTEMPTS OF WHAT I TRIED TO DISPLAY SUM AT BOTTOM BUT HAVE NOT HAD ANY SUCCESSES.
Select SocialSecurityNumber, ClearedDate, [Type], DDS, Count(*) As TotalCleared
from #temp
Group By SocialSecurityNumber, ClearedDate, [Type], DDS WITH ROLLUP
Union
Select NULL, NULL, NULL, NULL, count(*)as TotalCleared
from #temp
END
OR
Select SocialSecurityNumber, ClearedDate, [Type], DDS, Count(*) As TotalCleared
from #temp
Group By SocialSecurityNumber, ClearedDate, [Type], DDS, TOTALCLEARED WITH ROLLUP
Union
Select NULL, NULL, NULL, NULL, count(*)as TotalCleared
from #temp
END
Thanks
April 15, 2009 at 9:16 am
When I use your code in my test db I can produce the sum row, so I guess your problem is only to display it at the bottom instead of the top of the selection.
If that is so, just add another (synthetic) column that creates an order in which the sum is last.
For your ordering seems to be date, I'd suggest to use a varchar field sortOrder with the formula ' '+convert(varchar, ClearedDate, 126) for each row and 'z' for the sum row.
Prepending the blank char makes all values less than 'z', so your sum row will appear at the end, if you use order by sortOrder asc to sort the output.
WM_HOPETHATHELPS
Guenter
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply