Another query question - 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

SQL Server Development (2000)

Another query question

Author

Topic

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-06 : 11:17:10

Playing around with these again. Doing this small sproc sort of experimenting. Like to know where I am missing something.
Create Procedure TimeTest @cname Varchar(50) As
Select CName
Case when GDate > Convert(Smalldatetime,'2003-04-30') then
'Firstvalue' as time,
Case when GDate > Convert(Smalldatetime, '2003-03-30') then
'Secondvalue' Else 'o' end as time,
SUM(UE) as earned
From Universal
Where CName = @cname
Group By
CName
I have just hardcoded in the dates. And the Firstvalue,Secondvalue just a test varchar. More interested in seeing it work.
Thank you.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-06 : 11:50:08

I think you've got some "malformed-ness" going on...

How about:


SELECT CName, xxx_time, sum(UE) As Sum_UE FROM (
	SELECT    CName
		, CASE WHEN GDate > CONVERT(Smalldatetime,'2003-04-30')  THEN 'Firstvalue' 
		       WHEN GDate > CONVERT(Smalldatetime, '2003-03-30') THEN 'Secondvalue' 
									 ELSE 'o' 
		  END as xxx_time
		, UE
	  FROM Universal
	 WHERE CName = @cname) AS XXX
GROUP BY CName, xxx_time

Brett

8-)

Edited by - x002548 on 05/06/2003 11:50:48

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-06 : 13:54:52

Brett-
I understand the "inner" select, but not totally the "outer". Sum_UE and XXX. Anyway, I did try to change the syntax of mine looking at your syntax. I get results but not correct.
Basically this is what I am trying to get to work. I am trying to do a little bit here and then hopefully add to it.
I am trying to just get the following. A user's total wins for yesterday and pass "yesterday" back to my vb.net page also. Same for Last 7 days and Last 30 days. In my code, I only select records for the last 30 days so the Case statement will always be one of the three.

Select cname,
Case when GDate = Convert(SmallDateTime,'2003-05-05') then
'Yesterday'
when GDate > Convert(SmallDateTime,'2003-04-28') then
'Last 7 days'
Else 'Last 30 days' End as time,
Sum(UE) as earned
From Universal
Where cname = @cname and GDate > Convert(SmallDateTime,'2003-04-04')
Group By
cname,GDate
Like I said, the results aren't what I expect. If you can tell me what the Sum_UE and XXX should be, I will give that a go.
I hard code the time values in. I will worry about getting that part right after I can get some sort of foundation laid here.
Thanks again.
John

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-06 : 14:32:48

Oh, I think I may be seeing the light. That code I have above generates a record for each record. Instead of the three that I want. As you can tell, I have trouble comprehending the SUM guy among other things. But I think I see a light here. When I was in the marines, they used to say there is no such thing as a stupid question. First time you ask one though, you get a mouthful of fist. I keep asking them though.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-06 : 15:09:24

John,

When build SQL DML statements (esp. when they get complex), start small and build up. You were able to describe it well in your last post...I need Yesterday, I need last week, I need last month.

Build those first, separatley, then bring them together, like:



CREATE TABLE #bk_universal (CName varchar(50), GDate datetime, UE money)
GO

INSERT INTO #bk_universal (CName, GDate, UE)
SELECT 'Brett','05/05/2003',1000.00 UNION ALL
SELECT 'Brett','05/05/2003',5000.00 UNION ALL
SELECT 'Brett','05/05/2003',6000.00 UNION ALL
SELECT 'Brett','05/01/2003',1000.00 UNION ALL
SELECT 'Brett','05/02/2003',1000.00 UNION ALL
SELECT 'Brett','05/03/2003',1000.00 UNION ALL
SELECT 'Brett','05/04/2003',1000.00 UNION ALL
SELECT 'Brett','04/30/2003',1000.00 UNION ALL
SELECT 'Brett','04/05/2003',1000.00 UNION ALL
SELECT 'Brett','04/06/2003',1000.00 UNION ALL
SELECT 'Brett','04/07/2003',1000.00 UNION ALL
SELECT 'Brett','04/08/2003',1000.00 UNION ALL
SELECT 'Brett','04/09/2003',1000.00 UNION ALL
SELECT 'Brett','04/10/2003',1000.00
GO

SELECT CName, 'Yesterday' as When_Occurred, Sum(UE) As Winnings
  FROM #bk_universal
 WHERE Convert(varchar(25),GDate,1) = Convert(varchar(25),DateAdd(d,-1,GetDate()),1)
GROUP BY CName
UNION ALL
SELECT CName, 'Last Week' as When_Occurred, Sum(UE) As Winnings
  FROM #bk_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)
GROUP BY CName
UNION ALL
SELECT CName, 'Last Month' as When_Occurred, Sum(UE) As Winnings
  FROM #bk_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)
GROUP BY CName
GO

DROP TABLE #bk_universal
Go

Brett

8-)

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-06 : 15:50:13

Thanks Brett. Will try digesting this. That is a lot of typing and thought. Many many thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-06 : 15:57:24

John,

You've been typing all this time?

The code I put out there is cut and pasteable.

Just copy in to a Quary Analyzer Window. It should build a table, insert test data, perform the select I think you want, and then clean up after itself by getting rid of the table.

Good Luck

Brett

8-)

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-06 : 16:28:30

LOL. No, I meant the code YOU typed in. You had to type it. Much appreciated. I cut and paste when I can. Thanks again Brett.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-06 : 16:33:25

Again, not to much typing..

quote:

When build SQL DML statements (esp. when they get complex), start small and build up.

The create table was typing, but the INSERT was just a series of cut and pastes, and for the SQL Statement I built the first one and copied and pasted it twice...

not too bad..

Brett

8-)

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-05-06 : 17:44:02

Beware when cutting and pasting from Web Sites....

I cut and pasted a code snippet from here into a trigger, then spent days puzzling over why it wouldn't work - no error messages or anything, a boolean comparison within the function just didn't work. None of my workmates could explain it despite lots of testing and head-scratching. After 3 days I finally fixed it by typing it in, instead of cutting and pasting. To the naked eye it was exactly the same as the query that didn't work. Since then I have cut and pasted things from the web first into a simple text editor and saved them and then cut and paste the result into QA.

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-06 : 23:17:22

Create Procedure TimeTest @cname VarChar(50) As
Select CName
'First' as time,
Sum(UE) as earned,
Sum(case when Result = 'win' then 1 else 0 end) as wins,
Sum(case when Result = 'loss' then 1 elso 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
CName = @cname and GTime > Convert(Smalldatetime, '2003-05-01')
Group By
CName
Union All
Select CName
'First' as time,
Sum(UE) as earned,
Sum(case when Result = 'win' then 1 else 0 end) as wins,
Sum(case when Result = 'loss' then 1 elso 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
CName = @cname and GTime > Convert(Smalldatetime, '2003-04-15')
Group By
CName

Now this thing works, thanks very much to Brett. But I have two problems. First, if count is 0, it looks like I am dividing by 0 and I know that does not compute. But that is my code and not what Brett gave me. Also, if there are no records available, I get nothing. I want to have zeros across the board in that case.
I tried this, which I thought takes care of both problems.
Case when count(*) = 0 then
Select CName,
'First' as time,
0 as earned,
0 as wins,
0 as losses,
'0.0%' as pct
else
Select CName
'First' as time,
Sum(UE) as earned,
Sum(case when Result = 'win' then 1 else 0 end) as wins,
Sum(case when Result = 'loss' then 1 elso 0 end) as losses,
Str(1.0 * Sum(case when Result = 'win' then 1 else 0 end)/count(*) * 100,5,1) + '%' as pct
end
From
Universal
etc.
I am wondering if I am on the right track for a change. Also, I am still hard coding the dates in until I get it working elsewise.
Thanks bigtime.

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-07 : 00:46:03

Also tried :
Select(Case when count(*) = 0 then
CName,
'First' as time,
0 as earned,
0 as wins,
0 as losses,
'0.0%' as pct
else
CName
'First' as time,
Sum(UE) as earned,
Sum(case when Result = 'win' then 1 else 0 end) as wins,
Sum(case when Result = 'loss' then 1 elso 0 end) as losses,
Str(1.0 * Sum(case when Result = 'win' then 1 else 0 end)/count(*) * 100,5,1) + '%' as pct
end)
from
Universal
etc.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-07 : 10:01:30

quote:

Beware when cutting and pasting from Web Sites....

I cut and pasted a code snippet from here into a trigger, then spent days puzzling over why it wouldn't work

Yeah, I would recommend testing things on a scratch pad BEFORE affecting any of your systems....I guess it could save days of debugging...

Brett

8-)

PS John I wouldn't use [time] as an alias as it's a reserved word to SQL Server...MOO

Edited by - x002548 on 05/07/2003 10:04:23

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-07 : 10:12:58

First you don't need the union

Select CName
'First' as time,
Sum(UE) as earned,
Sum(case when Result = 'win' then 1 else 0 end) as wins,
Sum(case when Result = 'loss' then 1 elso 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
CName = @cname and
(GTime > Convert(Smalldatetime, '2003-05-01') OR
(GTime > Convert(Smalldatetime, '2003-04-15')

To solve the divide by zero problem.
Check ahead before you run this query to see if there are records in the table otherwise don't run the query or run an alternate query.

Take a look at the T-SQL IF Statement and at the use of @@ROWCOUNT in the help (BOL)

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-07 : 11:06:24

Valter-
I posted on another thread because this thing is probably gotten to where anyone that was interested can't follow it anymore. And that is my fault. And you posted a reply almost simultaneously with my new one or I probably wouldn't have made the new post.
In your reply, which I certainly appreciate, I think I do need the union. The code following works just fine. I used Brett's example of with the union and all is well. Except for the instances when there are no records. I want to have zeros displayed to my datagrid, not a blank grid.
<code>
Create Procedure TimeTest @cname VarChar(50) As
Select CName,
'First' as time,
Sum(UE) as earned,
Sum(case when Result = 'win' then 1 else 0 end) as wins,
Sum(case when Result = 'loss' then 1 elso 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
CName = @cname and GTime > Convert(Smalldatetime, '2003-05-01')
Group By
CName
Union All
Select CName
'Second' as time,
Sum(UE) as earned,
Sum(case when Result = 'win' then 1 else 0 end) as wins,
Sum(case when Result = 'loss' then 1 elso 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
CName = @cname and GTime > Convert(Smalldatetime, '2003-04-15')
Group By
CName
</code>
Trying to follow along on the way you very outstanding people have been showing/teaching me, I took the above code and tried adding another Case statement to get around the 0 record thing.
<code>
Create Procedure TimeTest @cname VarChar(50) As
Select Case( where count(*) = 0 then
CName,
0 as earned,
0 as wins,
0 as losses,
'0.0%' as pct
else
CName,
'First' as time,
Sum(UE) as earned,
Sum(case when Result = 'win' then 1 else 0 end) as wins,
Sum(case when Result = 'loss' then 1 elso 0 end) as losses,
Str(1.0 * Sum(case when Result = 'win' then 1 else 0 end)/count(*) * 100,5,1) + '%' as pct
end)
from
Universal
Where
CName = @cname and GTime > Convert(Smalldatetime, '2003-05-01')
Group By
CName
Union All
Select CName
'Second' as time,
Sum(UE) as earned,
Sum(case when Result = 'win' then 1 else 0 end) as wins,
Sum(case when Result = 'loss' then 1 elso 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
CName = @cname and GTime > Convert(Smalldatetime, '2003-04-15')
Group By
CName
</code>
I did make a mistake of not showing a 'Second' on a prior thread. And I sincerely apologize. For that or any other inconvenience /p.i.t.a.
I am learning somewhat. I value all the help I get here. I have the utmost respect for the knowledge/skills of those of you at this site.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-07 : 22:29:46

You still don't need the union.
You'd need a union if you were selecting from a different set of tables for example.

IF EXISTS(SELECT TOP 1 * FROM Universal)
SELECT
CName,
CASE
WHEN GTime > Convert(Smalldatetime, '2003-05-01') THEN 'First'
WHEN GTime > Convert(Smalldatetime, '2003-04-15') THEN 'Second'
END As time,
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
CName = @cname and
(GTime > Convert(Smalldatetime, '2003-05-01') OR
GTime > Convert(Smalldatetime, '2003-04-15'))
ELSE
SELECT
'Don't know what you want here' As CName,
'None' as time,
0 as earned,
0 as wins,
0 as losses,
'0.0%' as pct

Edited by - ValterBorges on 05/07/2003 22:44:19

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-08 : 10:15:45

I am very close with this. But close only counts in horseshoes and hand grenades.
First of all, I will clarify. I am going through a results table. I am computing a person's scoring for three time ranges. Yesterday, Last 7 days, and Last 30 days. My only problem now is this. If the person had no selections for yesterday, then my grid will not show the desired 0's for earned,wins,losses and pct. It skips that record.
I used Valter's exists, and the thing still refuses to give me those darn 0's. One thing that I have learned from all of this, but really don't understand, is the Count function. Seems to me this thing might be easy if it returned 0 when there were no records satisfying the query.

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
========
Now I have only tried the If exists in the part of the code dealing with 'Yesterday'. I assume that if I ever get it working for that much of the program, I can add If exists to the part dealing with 'Last 7 Days' and 'Last 30 Days'.
I did use much of Brett's code in this. I changed time to timeframe, as per his advice on reserved words.

I cannot understand how it omits the 'Yesterday' record(s). With that exists statement from Valter.

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%

Hope that helps. Mucho thanko.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-08 : 21:16:09

Don't need the from in the else statement but you do need to decide what to return for CName. You see there are no records so you need to create a record not select from a table.

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' as CName,
'Yesterday' as timeframe,
0 as earned,
0 as wins,
0 as losses,
'0.0%' as pct

After reading more close I think you really want this.

Create Procedure MyProc @cname varchar(50) as

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
UNION ALL
Select 'CName' as CName,
'Yesterday' as timeframe,
0 as earned,
0 as wins,
0 as losses,
'0.0%' as pct

You want the yesterday record with 0's regardless of what's in the Universal table. Am I correct?

Ok let's get something straight.

0 Records UNION ALL 1 record = 1 record
0 Records UNION 1 record = 1 record
X Records UNION ALL 1 record = X+1 records
X Records UNION 1 Record = X+1 records the single record does not exist in the first set otherwise X records.

Example1:

SELECT 1,2,3
UNION ALL
SELECT 1,2,3

Result:

1,2,3
1,2,3

Example2:

SELECT 1,2,3
UNION
SELECT 1,2,3

Result:

1,2,3

Example3:
SELECT A.a,A.b,A.c
FROM
(
SELECT 1 As a,2 As b ,3 As c
UNION ALL
SELECT 1 As a,2 As b,3 As c
) A
GROUP BY A.a, A.b, A.c

Result:
1,2,3

Example4:

Assume TableY has 2 records
Col1,Col2,Col3
1,2,3
4,5,6

SELECT Col1,Col2,Col3
FROM TableY
UNION ALL
Select 1,2,3

RESULT:

1,2,3
4,5,6
1,2,3

Example 5:
SELECT Col1,Col2,Col3
FROM TableY
UNION
Select 1,2,3

Result:
1,2,3
4,5,6

Example 6:
Suppose TableY has no records

SELECT col1,col2,col3
FROM TableY
UNION ALL
SELECT 1,2,3

Result:
1,2,3

Example7:
Suppose TableY has no records

SELECT col1, col2,col3
FROM TableY
UNION
SELECT 1,2,3

Results:
1,2,3

Let's now look at a different method of generating records the left join.

Suppose TableA has the following

Col1,Col2,Col3
1,A,B
2,C,D

Suppose TableB has the following

Col1
1
2
3

You can do the following

SELECT A.Col1,
CASE WHEN Col2 IS NULL THEN 0 ELSE Col2 END As Col2,
CASE WHEN Col3 IS NULL THEN 0 ELSE Col3 END As Col3
From TableB B Left Join TableA A On B.Col1 = A.Col1

Result:

1,A,B
2,C,D
3,0,0

Edited by - ValterBorges on 05/08/2003 21:19:01

Edited by - ValterBorges on 05/08/2003 21:45:56

John T.
Posting Yak Master

112 Posts

Posted - 2003-05-09 : 15:23:05

Valter-
I had given up on displaying this. It works just as well with blanks. But I would still like to make it work just for the knowledge part of it.
It looks to me like your union will/could give me two records. If I am reading it correctly. I do have a degree in math from many moons ago(actually, decades) and never used it. I do remember this Cartesian product stuff somewhat. You took the time to answer, I will take the time to read and digest it. Thank you.
Back to the zero thing. If a guy did not make any selections the prior day, I want my grid to display the following :
Yesterday 0.0 0 0 0.0%
otherwise it will be something like :
Yesterday 3.0 1 0 100.0%
I think your union would give me both if there is at least one record. Hell I could be wrong. Like I said. It works just fine and I want to really cause no more frustration.
I am not a professional techie like most of you guys are. Or even been at it very long. Just started looking at this stuff in January when I saw a site that I wanted to do. If I would have known it would have been this big of a project, I may have said phooey to it. But I have come pretty far. Using only the web matrix tool and asp.net with not having a clue what a database was(lol- I still wouldn't know one if it hit me in the face). But when I do finish this, and I will unless I die first, I would like to continue doing this sort of thing. A mind is a terrible thing to waste even if it ain't the most nimble one out there.
Thanks old buddy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources