May 8, 2003 at 9:34 am
TimeFrame Earned Wins Losses Pct.
Yesterday 0.0 0 0 0.0%
Last 7 Days 2.2 2 0 100.0%
Last 30 Days 4.0 6 4 60.0%
If the above is what it should be, I get the following :
TimeFrame Earned Wins Losses Pct.
Last 7 Days 2.2 2 0 100.0%
Last 30 Days 4.0 6 4 60.0%
==============
Create Procedure MyProc @cname varchar(50) as
If exists(Select * from Universal where convert(varchar(25),GDate,1) = convert(varchar(25),DateAdd(d,-1,GetDate()),1) and CName = @cname)
Select CName,
'Yesterday' as timeframe,
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 pct
From Universal where convert(varchar(25),GDate,1) = convert(varchar(25),DateAdd(d,-1,GetDate()),1) and CName = @cname
Group By
CName
Else
Select CName,
'Yesterday' as timeframe,
0 as earned,
0 as wins,
0 as losses,
'0.0%' as pct
From Universal where convert(varchar(25),GDate,1) = convert(varchar(25),DateAdd(d,-1,GetDate()),1) and CName = @cname Group By
CName
Union All
If exists(Select * from Universal where convert(varchar(25),GDate,1) between convert(varchar(25),DateAdd(d,-7,GetDate()),1)and convert(varchar(25),DateAdd(d,-1,GetDate()),1) and CName = @cname)
Select CName,
'Last 7 Days' as timeframe,
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 pct
From Universal where convert(varchar(25),GDate,1) between convert(varchar(25),DateAdd(d,-7,GetDate()),1) and convert(varchar(25),DateAdd(d,-1,GetDate()),1) and CName = @cname
Group By
CName
Else
Select CName,
'Last 7 Days' as timeframe,
0 as earned,
0 as wins,
0 as losses,
'0.0%' as pct
From Universal where convert(varchar(25),GDate,1) between convert(varchar(25),DateAdd(d,-7,GetDate()),1) and convert(varchar(25),DateAdd(d,-1,GetDate()),1) and CName = @cname
Group By
CName
If exists(Select * from Universal where convert(varchar(25),GDate,1) between convert(varchar(25),DateAdd(d,-30,GetDate()),1)and convert(varchar(25),DateAdd(d,-1,GetDate()),1) and CName = @cname)
Select CName,
'Last 30 Days' as timeframe,
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 pct
From Universal where convert(varchar(25),GDate,1) between convert(varchar(25),DateAdd(d,-30,GetDate()),1)and convert(varchar(25),DateAdd(d,-1,GetDate()),1) and CName = @cname
Group By
CName
Else
Select CName,
'Last 30 Days' as timeframe,
0 as earned,
0 as wins,
0 as losses,
'0.0%' as pct
From Universal where convert(varchar(25),GDate,1) between convert(varchar(25),DateAdd(d,-30,GetDate()),1)and convert(varchar(25),DateAdd(d,-1,GetDate()),1) and CName = @cname
Group By
CName
================
If I can get the program to recognize that when there are no records for any of the timeframes, then I want 0's displayed.
Heck of a first post. Thanks.
May 8, 2003 at 10:12 am
John - I must be reading this wrong. Please help me understand.
It appears that the structure is
if exists ( select * from <table> where <timeframe condition )
SELECT Data in timeframe
ELSE
SELECT zeroes from data in timeframe
Since you have already established the fact that there are no entries in the timeframe, would it be possible to say..
Else
Select CName,
'Last 7 Days' as timeframe,
0 as earned,
0 as wins,
0 as losses,
'0.0%' as pct
and just leave it at that? What am I missing?
Guarddata-
May 8, 2003 at 10:37 am
Guarddata-
Tried that but because of the unions, I can't get away with it.
I can let this thing go as it is. The program will work either way. But it's a learning thing here. Want to understand why I can't get it as I would like.
Thanks.
May 8, 2003 at 12:47 pm
How about this...
Cut out the IF EXISTS STATEMENTS
SELECT 'Yesterday'....
WHERE <condition>
UNION ALL
SELECT 'Yesterday', 0, 0, 0
WHERE NOT EXISTS ( Yesterday condition )
UNION ALL
SELECT 'Last Week' ....
WHERE condition
UNION ALL
SELECT 'Last Week', 0, 0,
WHERE NOT EXISTS ( Last Week Condition )
etc.
Guarddata-
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply