looking to refine a sproc - 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

SQL Server 2000 Forums

Transact-SQL (2000)

looking to refine a sproc

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-01-06 : 11:38:07

Not sure how to word the subject ...

I have a sproc as seen below -


CREATE PROCEDURE myProc

@PageID int

AS

SELECT left(ZoneNo,5) + '0' as ZoneNo, area as [Area],

	max(case when month(proddate) = 1 then 1 else 0 end) as [Jan],
	max(case when month(proddate) = 2 then 1 else 0 end) as [Feb],
	max(case when month(proddate) = 3 then 1 else 0 end) as [Mar],
	max(case when month(proddate) = 4 then 1 else 0 end) as [Apr],
	max(case when month(proddate) = 5 then 1 else 0 end) as [May],
	max(case when month(proddate) = 6 then 1 else 0 end) as [Jun],
	max(case when month(proddate) = 7 then 1 else 0 end) as [Jul],
	max(case when month(proddate) = 8 then 1 else 0 end) as [Aug],
	max(case when month(proddate) = 9 then 1 else 0 end) as [Sep],
	max(case when month(proddate) = 10 then 1 else 0 end) as [Oct],
	max(case when month(proddate) = 11 then 1 else 0 end) as [Nov],
	max(case when month(proddate) = 12 then 1 else 0 end) as [Dec]

FROM tblcontractdetail
WHERE (franchiseid = 'mdiccl') and productid = @PageID and ProdYear = 2010
	group by (left(ZoneNo,5) + '0'), area
	order by (left(ZoneNo,5) + '0') desc
GO

But what I want to now do is have the case information be based off the @PageID I pass in. So instead of returning no results if say @PageID = 174. I rather it return all my zones, areas where WHERE (franchiseid = 'mdiccl') and ProdYear = 2010 with 0's across in my case statement b/c the @PageID returns no results. And obviously if there is data matching the @PageID i pass in the 1's and 0's would fill in appropriately.

Let me know if I am not explaining this well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 11:42:39

seems like this is what you're asking for


CREATE PROCEDURE myProc

@PageID int

AS

SELECT left(ZoneNo,5) + '0' as ZoneNo, area as [Area],

	max(case when month(proddate) = 1 and productid = @PageID then 1 else 0 end) as [Jan],
	max(case when month(proddate) = 2 and productid = @PageID then 1 else 0 end) as [Feb],
	max(case when month(proddate) = 3 and productid = @PageID then 1 else 0 end) as [Mar],
	max(case when month(proddate) = 4 and productid = @PageID then 1 else 0 end) as [Apr],
	max(case when month(proddate) = 5 and productid = @PageID then 1 else 0 end) as [May],
	max(case when month(proddate) = 6 and productid = @PageID then 1 else 0 end) as [Jun],
	max(case when month(proddate) = 7 and productid = @PageID then 1 else 0 end) as [Jul],
	max(case when month(proddate) = 8 and productid = @PageID then 1 else 0 end) as [Aug],
	max(case when month(proddate) = 9 and productid = @PageID then 1 else 0 end) as [Sep],
	max(case when month(proddate) = 10 and productid = @PageID then 1 else 0 end) as [Oct],
	max(case when month(proddate) = 11 and productid = @PageID then 1 else 0 end) as [Nov],
	max(case when month(proddate) = 12 and productid = @PageID then 1 else 0 end) as [Dec]

FROM tblcontractdetail
WHERE (franchiseid = 'mdiccl')  and ProdYear = 2010
	group by (left(ZoneNo,5) + '0'), area
	order by (left(ZoneNo,5) + '0') desc
GO

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-01-06 : 11:49:29

AHh yes. That was easy.

I was looking at this thing with tunnel vision.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 12:03:24

yeah it really was

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources