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.
| Author |
Topic |
|
John T.
Posting Yak Master
112 Posts |
 Posted - 2004-01-02 : 09:24:44
|
| [code]CREATE PROCEDURE Last30Sel ASSELECT Member, Convert(varchar(25),SUM (UE)) as earned, SUM (case when Result = 'win' then 1 ELSE 0 end) as wins, SUM (case when Result = 'loss' then 1 ELSE 0 end) as losses, Str(1.0 * SUM (case when result = 'win' then 1 ELSE 0 end) / count(*) * 100,5,1) + '%' as pctFROM UniversalWhere convert(varchar(25),GameDate,1) between convert(varchar(25), DateAdd(d,-30,GetDate()),1) and convert(varchar(25), DateAdd(d,-1,GetDate()),1) Group ByMemberOrder BySum(UE) DESCGO[/code]The above code worked just fine for 6 months. Because of the new year, I am getting no results at all for the past 30 days. Any help appreciated.Thanks and Happy New Year.John |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-01-02 : 10:55:06
|
| Go back to first principles....remove the 'group by', etc...and 1st prove you have data to sum.Also without DDL, sample data etc....we're guessing as to your problem...bad data or a bad WHERE statement...I suspect the latter.You need to specify in english what the WHERE condition is trying to achieve for you...ie data from the past 30 days, or data from the past month....ALSO....the method of comparison (ie converting date values to string...may be the flaw)...dates can work fine with the BETWEEN operator...without requiring to be 1st converted to strings....One approach would be to include the fields used in the WHERE clause in your SELECT statement...until you figure out the proper construct(s) you need from the values displayed. |
 |
|
|
|
|
|
|
|
Results for specified dates procedure problem