Need help writing a stored procedure

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

Need help writing a stored procedure

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-01 : 10:55:46

I'm setting up a webpage with the last Friday of the month appearing in a dropdown box. A user can select 1, 2, 5 etc... months they want to see. So say I have a user who wants to see 6/26/2009 and 10/30/2009 info how would a stored procedure to see that since I have dates as the header column?


Reg1    Sort     Region    6/26/2009     7/31/2009       8/28/2009      9/25/2009      10/30/2009
PHI	1	PHI	  13.0	          11.5	       10.5	    11.1	               9.5	
A01 	x	01 	  14.9	          11.5	       14.5	    16.6	             12.3	
A02 	x	02 	  24.6	          19.8	  14.1               16.4	             16.6	
A03 	x	03 	    6.7	           5.6	        7.0	      3.8	               5.4	
A04 	x	04 	    7.4	           7.7	        6.1	      5.4	               6.9	
A05 	x	05 	    5.4	           9.2	        6.5                  6.1	               3.2	
A06 	x	06 	  10.3	         11.6	      12.5	     12.6	               6.9

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-01 : 11:29:04

Okay I got this to work but how do I add the word distinct in there to just get the distinct weekdat of '06/26/09'

SELECT sort, area, reg, CASE
WHEN SUM(totovr)= 0 THEN 0
WHEN SUM(totpnd) = 0 THEN 0
ELSE
(convert(decimal(6,1),(((((sum(totovr))) * 1.00) / (sum(totpnd))) * 100)))
END AS '06/26/09', convert(char, weekdat,1) as weekdat
FROM DiaryPct
where (sort = '1') or (sort = 'x') and weekdat='06/26/2009'
Group by sort, doc, dist, area, reg, weekdat

Also how do I set the sum to come up as which week the person selected right now I placed it in there.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-01 : 11:30:32

Sorry...how do I add the name of the sum column as a variable (from what the user selected) instead of my just hardcoding it in there. I hope that makes sense.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-12-01 : 12:04:39

Okay I switched things up and can use a Pivot table but need to add a where clause to it. Does anyone know about Pivot tables?

The error message I'm getting is from the last line in front of the Exec SQL stating...

Incorrect syntax near ' (where weekdat=06/26/2009);'


DECLARE @listCol VARCHAR(2000)

SELECT  @listCol = STUFF(( SELECT DISTINCT

            '],[' + convert(varchar(10),(Weekdat),1) --converting the weekdat

                        FROM    DiaryPct

		--where (dateadd(ww, -14, getdate()) < weekdat) --getting 13 weeks of data out

                        ORDER BY '],[' + convert(varchar(10),(weekdat),1)

                        FOR XML PATH('')

                                    ), 1, 2, '') + ']';
DECLARE @sql NVARCHAR(2000); 
SET @sql = N' 
WITH Totals AS ( 
SELECT sort, doc, dist, area, reg, CASE 
  WHEN SUM(totovr)= 0 THEN 0 
  WHEN SUM(totpnd) = 0 THEN 0 
  ELSE 
(convert(decimal(6,1),(((((sum(totovr))) * 1.00)  / (sum(totpnd))) * 100))) 
  END AS total, weekdat 
FROM DiaryPct
GROUP BY sort,doc, dist, area, reg, weekdat) 
SELECT sort,reg, ' + @listCol + 
N' ,doc, dist,area ' +
--N' Into DiariesPivot ' +
N'FROM 
   (SELECT sort,doc, dist, area, reg,total, weekdat 
    FROM Totals) as O ' + 
         N' PIVOT ' + 
N'(SUM(total) FOR weekdat IN (' + @listCol + N')) AS P'; 
N' (where weekdat=06/26/2009);'

EXEC(@sql);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources