April 9, 2009 at 10:28 am
Hello All,
I have created a store procedure code and it displayed the outlook below:
SSN: 111223333 Cleareddate: 1/5/2009 Type: DE DDS: BO TotalCleared: 2
SSN: 222114444 ClearedDate: 1/7/2009 Type: SM DDS: BO TotalCleared: 2
Store procedure Code: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
declare @cnt int
select @cnt=count(*) from #temp
update #temp set TotalCleared = @cnt
select * from #temp
drop table #temp
END
I wish to display the outlook like below:
SSN:111223333 Cleareddate:1/5/2009 Type:DE DDS: BO TotalCleared: 1
SSN: 222114444 ClearedDate: 1/7/2009 Type: SM DDS: BO TotalCleared: 1
I wish to display the total case in the column "TOTALCLEARED" for each case is 1, and then sum all cases at the bottom of the column TOTALCLEARED.
Thank you in advances
April 9, 2009 at 10:42 am
You could try simply dropping the TotalCleared from #temp. It is not needed. The update you are doing to that column will always contain just the rowcount. I assume you want the total cleared as the last row? Two way to accomplish this.
Select SocialSecurityNumber, ClearedDate, [Type], DDS, count(*)
from #temp
group by SocialSecurityNumber, ClearedDate, [Type], DDS
If you always want the detail row to list 1 just tweak to something like.
select SocialSecurityNumber, ClearedDate, [Type], DDS, 1
from #temp
union
select null, null, null, null, count(*) from #temp
Hope that helps.
Sean
josephptran2002 (4/9/2009)
...create table #temp
(
SocialSecurityNumber varchar(9),
ClearedDate datetime,
[Type] varchar(5),
DDS varchar(50),
TotalCleared int
)
update #temp set TotalCleared = @cnt
select * from #temp
drop table #temp
END
[/code]
I wish to display the outlook like below:
SSN:111223333 Cleareddate:1/5/2009 Type:DE DDS: BO TotalCleared: 1
SSN: 222114444 ClearedDate: 1/7/2009 Type: SM DDS: BO TotalCleared: 1
I wish to display the total case in the column "TOTALCLEARED" for each case is 1, and then sum all cases at the bottom of the column TOTALCLEARED.
Thank you in advances
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2009 at 11:23 am
hello Slange,
I take your suggestion and added the codes in my codes. But It gave me an error : Invalid column name 'Type'
Do you know why?
Thanks
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
Group By SocialSecurityNumber, DEClearedDate, [Type], DDS, Count(*)
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
Group By SocialSecurityNumber, SomaticMCClearedDate, [Type], DDS, Count(*)
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
Group By SocialSecurityNumber, PsycMCClearedDate,[Type], DDS, Count(*)
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
Group By SocialSecurityNumber, DEClearedDate, [Type], DDS, Count(*)
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
Group By SocialSecurityNumber, SomaticMCClearedDate, [Type], DDS, Count(*)
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
Group By SocialSecurityNumber, PsycMCClearedDate, [Type], DDS, Count(*)
Order BY ISNULL( PsycMCClearedDate, '31-Dec-2090')
end
declare @cnt int
select @cnt=count(*) from #temp
update #temp set TotalCleared = @cnt
select * from #temp
drop table #temp
END
April 9, 2009 at 11:28 am
Can you post the new code? I tested the code i put it up and it works fine. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2009 at 11:35 am
Hello Slange,
Here is the new code:
It gives me an error: "Invalid column name 'ClearedDate'"
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, count(*)
from ROCAPData where
DEClearedDate between @start and @End
and DESecondClearedDate is NULL
and DEThirdClearedDate is NULL
and DEFourthClearedDate is NULL
Group by SocialSecurityNumber, ClearedDate, [Type], DDS
Order BY ISNULL( DEClearedDate, '31-Dec-2090')
--Somatic
insert into #temp
select SocialSecurityNumber, SomaticMCClearedDate as ClearedDate, 'SM' as [Type], DDS, Count(*)
from ROCAPData where
SomaticMCClearedDate between @start and @End
and SomaticMCSecondClearedDate is NULL
and SomaticMCThirdClearedDate is NULL
and SomaticMCFourthClearedDate is NULL
Group by SocialSecurityNumber, ClearedDate, [Type], DDS
Order BY ISNULL( SomaticMCClearedDate, '31-Dec-2090')
--Psyc
insert into #temp
select SocialSecurityNumber, PsycMCClearedDate as ClearedDate, 'PM' as [Type], DDS, Count(*)
from ROCAPData where
PsycMCClearedDate between @Start and @End
and PsycMCSecondClearedDate is NULL
and PsycMCThirdClearedDate is NULL
and PsycMCFourthClearedDate is NULL
Group by SocialSecurityNumber, ClearedDate, [Type], DDS
Order BY ISNULL( PsycMCClearedDate, '31-Dec-2090')
end
else
begin
--DE
insert into #temp
select SocialSecurityNumber, DEClearedDate as ClearedDate, 'DE' as [Type], DDS, count(*)
from ROCAPData where
DEClearedDate between @Start and @End
and DESecondClearedDate is NULL
and DEThirdClearedDate is NULL
and DEFourthClearedDate is NULL
and DDS = @office
Group by SocialSecurityNumber, ClearedDate, [Type], DDS
Order BY ISNULL( DEClearedDate, '31-Dec-2090')
--Somatic
insert into #temp
select SocialSecurityNumber, SomaticMCClearedDate as ClearedDate, 'SM' as [Type], DDS, count(*)
from ROCAPData where
SomaticMCClearedDate between @Start and @End
and SomaticMCSecondClearedDate is NULL
and SomaticMCThirdClearedDate is NULL
and SomaticMCFourthClearedDate is NULL
and DDS = @office
Group by SocialSecurityNumber, ClearedDate, [Type], DDS
Order BY ISNULL( SomaticMCClearedDate, '31-Dec-2090')
--Psyc
insert into #temp
select SocialSecurityNumber, PsycMCClearedDate as ClearedDate, 'PM' as [Type], DDS, Count(*)
from ROCAPData where
PsycMCClearedDate between @Start and @End
and PsycMCSecondClearedDate is NULL
and PsycMCThirdClearedDate is NULL
and PsycMCFourthClearedDate is NULL
and DDS = @office
Group by SocialSecurityNumber, ClearedDate, [Type], DDS
Order BY ISNULL( PsycMCClearedDate, '31-Dec-2090')
end
declare @cnt int
select @cnt=count(*) from #temp
update #temp set TotalCleared = @cnt
select * from #temp
drop table #temp
END
April 9, 2009 at 11:40 am
Oh I see what you did. You should put all the inserts back to the original. The only change should be at the end.
josephptran2002 (4/9/2009)
Hello Slange,declare @cnt int
select @cnt=count(*) from #temp
update #temp set TotalCleared = @cnt
select * from #temp
drop table #temp
END[/code]
Drop this whole section use the select i posted. That should get you what you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2009 at 11:48 am
Hello Slange,
I am confused, would you please tell me what insert I should put back? and What changes should I change at the end?
Thank you
April 9, 2009 at 11:54 am
Start with your original code.
Remove the TotalCleared column from your temp table.
instead of :
declare @cnt int
select @cnt=count(*) from #temp
update #temp set TotalCleared = @cnt
select * from #temp
put in one of the two new select statements.
then continue to drop table #temp
Sean
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2009 at 1:40 pm
Hello Slange,
I ran the codes that you suggested me to change it, and it works. But there is a small problem that I want to ask you, and that question is:
the outlook of the codes that I have changed like below
SocialSecuriyNumber, ClearedDate, [Type], DDS, Column
How can I name the column name "Column"? Because this is Total column of each row, I wish to name it as "TOTALCLEARED" instead of Column.
Thank you
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(*)
from #temp
Group by SocialSecurityNumber, ClearedDate, [Type], DDS
Order BY ISNULL( ClearedDate, '31-Dec-2090')
END
April 9, 2009 at 1:42 pm
Hello Slange,
Forget about the question that I just ask you, I solved it.
Thanks
Joseph
April 9, 2009 at 1:42 pm
Aggregate columns get no column name unless you alias it. Do something like this:
Select SocialSecurityNumber, ClearedDate, [Type], DDS, Count(*) as TotalCleared
HTH
Sean
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2009 at 8:41 am
Hello Slange,
I have a question which relates to this question, if I want to have sum at the bottom of the column TotalCleared. Do you know how?
Thanks
Joe
April 10, 2009 at 9:23 am
Hi
Append the below query:
union all
select null, null, null, null, count(*) from #temp
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply