simpe stored procedure - 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

General SQL Server Forums

New to SQL Server Programming

simpe stored procedure

Author

Topic

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-03-11 : 07:35:28

Hi,
i need to create a Stored Procedure to display data as shown bellow


----------------------------------------------
SubmitDate	Aster	DBS	Dogan
3-Dec-08	34	2	124
5-Dec-08	16	2	120
8-Dec-08	18	9	106
10-Dec-08	14	9	103
11-Dec-08	14	9	97
17-Dec-08	19	9	117
----------------------------------------------

from the bellow database table
i need to pass MORE THAN 1 paremeter(submitdate ), then the result must be grouped on that parameter(submitdate)


----------------------------
shortid	Product	Submitdate
----------------------------
1	Aster	1/2/2009
2	Aster	1/2/2009
3	Aster	1/2/2008
4	Aster	1/2/2008
5	DBS	1/2/2008
6	DBS	1/2/2008
7	DBS	1/2/2007
8	Dogan	1/2/2007
9	Dogan	1/2/2007
10	Dogan	1/2/2007
----------------------------

i tried something, but i could able to get only 1 row by passing 1 parameter


ALTER PROC [dbo].[CQ_Progress]
(
	@submitdate datetime
)
as
select @submitdate as 'Submit Date',  
sum(case  when product like '%aster%' then 1 else 0 end) as [Aster],
sum(case when product  like '%DBS%' then 1 else 0 end) as [DBS],
sum(case when product like '%Dogan%'then 1 else 0 end) as [Dogan]
from defectall where submitdate< @submitdate

Please modify it so that i can pass multiple parameter.
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 07:40:21

[code]SELECT SubmitDate,
SUM(CASE WHEN Product = 'Aster' THEN 1 ELSE 0 END) AS Aster,
SUM(CASE WHEN Product = 'DBS' THEN 1 ELSE 0 END) AS DBS,
SUM(CASE WHEN Product = 'Dogan' THEN 1 ELSE 0 END) AS Dogan
FROM Table1
WHERE SubmitDate >= @Param1
AND SubmitDate < DATEADD(DAY, 1, @Param2)
GROUP BY SubmitDate
ORDER BY SubmitDate[/code]

E 12°55'05.63"
N 56°04'39.26"

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-03-11 : 07:48:05

Thanks,

but i need 1 row of result for each parameter passed.
for eg:
if i pass 2 parameter '2-2-9' and '1-1-8'
then i should get


----------------------------------------------
SubmitDate	Aster	DBS	Dogan
2-2-9	        34	2	124
1-1-8     	16	2	120

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 07:59:33

[code]SELECT SubmitDate,
SUM(CASE WHEN Product = 'Aster' THEN 1 ELSE 0 END) AS Aster,
SUM(CASE WHEN Product = 'DBS' THEN 1 ELSE 0 END) AS DBS,
SUM(CASE WHEN Product = 'Dogan' THEN 1 ELSE 0 END) AS Dogan
FROM Table1
WHERE SubmitDate IN (@Param1, @Param2)
GROUP BY SubmitDate
ORDER BY SubmitDate[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 13:27:00

quote:
Originally posted by krishna_yess

Thanks,

but i need 1 row of result for each parameter passed.
for eg:
if i pass 2 parameter '2-2-9' and '1-1-8'
then i should get

----------------------------------------------
SubmitDate Aster DBS Dogan
2-2-9 34 2 124
1-1-8 16 2 120

beter to use single parameter and pass comma seperated date values. then you can use


ALTER PROC [dbo].[CQ_Progress]
(
	@submitdate varchar(8000)
)
as
select @submitdate as 'Submit Date',  
sum(case  when product like '%aster%' then 1 else 0 end) as [Aster],
sum(case when product  like '%DBS%' then 1 else 0 end) as [DBS],
sum(case when product like '%Dogan%'then 1 else 0 end) as [Dogan]
from defectall where ','+ @submitdate+','  LIKE '%,'+ convert(varchar(11),submitdate,121) +',%'
GROUP BY submitdate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources