Stored Procedure not working Count/Stuff

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

Stored Procedure not working Count/Stuff

Author

Topic

Turnerc
Starting Member

1 Post

Posted - 2011-06-09 : 08:56:30

Hello,

I have created a stored procedure to count the number of unstaffed
beds at our facility. I am having problems with the following piece of code from the stored procedure not dispalying a zero value for a bed category that curently has no beds available. (placment 7 should
have a 0) instead I receive the following results:

0776,Hospital,201106090800,12,31,,,10,,,198,

Should be:
0776,Hospital,201106090800,12,31,,,10,0,198,

The program specs for the code are: When a hospital does not provide services for a specific bed category a comma holder value is expected. (which is working in the above result set). If hospital contains the bed category but no beds are avaialble at the time of the report the value needs to be 0. (this is the piece not working and I am unsure on how to fix the code below to get the desired results).

Code:
SELECT
'0776' + ',' +
'Hospital' + ',' +
CONVERT(varchar(4), datepart(yy, getdate()))+
right('0' + CONVERT(varchar(2), datepart(mm, getdate())),2)+
right('0' + CONVERT(varchar(2), datepart(dd, getdate())),2)+
right('0' + CONVERT(varchar(2), datepart(hour, getdate())),2)+
right('0' + CONVERT(varchar(2), datepart(minute, getdate())),2)+
stuff (( select ',' + CASE
WHEN placement IN (1, 2, 5, 7, 9) THEN CAST(TotalPlacement AS VARCHAR(9))
--WHEN placement IN (3, 4, 6, 8, 10) and TotalPlacement = 0 then ','
else ''
END
FROM (
SELECT placement, COUNT(*) AS TotalPlacement
FROM @CENSUS_WORK
group by placement
) X
for XML PATH('')
),1,0,'')

Any help would be much appreciated, maybe I need to go about this in
a different direction, I am currently holding the bed category utilizing a placment int column, however I need a "," varchar for bed categories we do not have at our site and all the values have to be displayed in a certain order.

Suggestions are welcomed.

thank you

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-06-09 : 14:25:36

could you include code to populate @census_work with enough sample data for us to observe the problem.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-06-09 : 14:35:49

this seems to work:


declare @placement table (placement int)
insert @placement
select 1 union all select 2 union all select 5 union all select 7 union all select 9

declare @census_work table (placement int)
insert @census_work
select 1 union all
select 1 union all
select 2 union all
select 2 union all
select 5 union all
select 7 union all
select 7 


SELECT
       '0776' + ',' + 'Hospital' + ',' + CONVERT(varchar(4), datepart(yy, getdate()))
       + right('0' + CONVERT(varchar(2), datepart(mm, getdate())),2) 
       + right('0' + CONVERT(varchar(2), datepart(dd, getdate())),2) 
       + right('0' + CONVERT(varchar(2), datepart(hour, getdate())),2)
       + right('0' + CONVERT(varchar(2), datepart(minute, getdate())),2)
       + stuff (
              ( select ',' 
                     + CASE
                     WHEN placement IN (1, 2, 5, 7, 9) THEN CAST(TotalPlacement AS VARCHAR(9))
                     else ''
                     END
              FROM (
                     SELECT p.placement
                            , COUNT(w.placement) AS TotalPlacement
                     FROM @placement p
                     left outer join @CENSUS_WORK w on w.placement = p.placement
                     group by p.placement
                     ) X
              for XML PATH(''))
              ,1,0,'')

EDIT:
output:
0776,Hospital,201106091435,2,2,1,2,0

The stuff function doesn't seem to be buying you anything

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources