?
Solved

modify existing procedure

Posted on 2005-04-21
6
Medium Priority
?
216 Views
Last Modified: 2010-05-18
i'm doing this to print zeros instead of nothing when the counts = 0.  so, if there's no buys and no sells, i get this:

B  0      0      
S  0      0      

The problem is, if there are no buys but some sells, or vice verse, it doesn't return the zeros for the buys/sells .  

How do i modify this:
B  2      500      
to this:
B  2      500      
S  0           0

IF EXISTS (SELECT 'X' FROM table
WHERE userid=@userid
AND timeofexecution >= @starttime)
select BuySell, count(*) AS '#',CONVERT(char(16),SUM(LastFillQuantity)) AS 'SUM'
FROM table
WHERE userid=@userid
AND timeofexecution >= @starttime
group by buysell
ELSE
SELECT 'B' AS BuySell, 0 as '#' , '0' as 'SUM'
UNION ALL
SELECT 'S' AS BuySell, 0 AS '#', '0' as 'SUM'
0
Comment
Question by:RLLewis
  • 4
  • 2
6 Comments
 
LVL 10

Expert Comment

by:PSSUser
ID: 13836238
A quick solution would be to create a "lookup" table for the BuySell column:
Table BuySell_Lookup:
Code          Description
B               Buy
S               Sell

Then use:
Select L.Code, IsNull(Count(T.*),0) as '#', IsNull(CONVERT(char(16),SUM(T.LastFillQuantity))) As 'SUM'
From BuySell_Lookup L Left Join Table T
     On L.Code=T.BuySell
WHERE T.UserId=@UserId
AND TimeOfExecution >= @StartTime
Group By L.Code

There is more than likely a more elegant way of doing this, without the lookup, but this is will work.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13836317
Line 12: Incorrect syntax near '*'.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13836489
fixed syntax error, but it doesn't return any records at all.  i've validated there are ample records for the userid's i've tested, so i should be getting something.

Select l.code AS BuySell, ISNULL(Count(*),0) as '#', IsNull(CONVERT(char(20),SUM(T.LastFillQuantity)),0) As 'SUM'
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 1

Author Comment

by:RLLewis
ID: 13839872
i've got to up the points on this.  small as it may be, it's very important.  right now, if there are no buys or sells, i get this:

B   0    0
S  0    0

but, if there is one but not the other, i only get this:

B  52    52555

If there is one but not the other, I need to have zeros printed for the one w/out counts.  Like this:

B  52     5255  
S  0     0  

This is my current statement:

IF EXISTS (SELECT 'X' FROM table
WHERE userid=@userid
AND timeofexecution >= @starttime)
select BuySell, count(*) AS '#',CONVERT(char(16),SUM(LastFillQuantity)) AS 'SUM'
FROM table
WHERE userid=@userid
AND timeofexecution >= @starttime
group by buysell
ELSE
SELECT 'B' AS BuySell, 0 as '#' , '0' as 'SUM'
UNION ALL
SELECT 'S' AS BuySell, 0 AS '#', '0' as 'SUM'
0
 
LVL 10

Accepted Solution

by:
PSSUser earned 2000 total points
ID: 13840981
Oops, sorry. Try this:
Select L.Code, IsNull(Count(T.BuySell),0) as '#', IsNull(CONVERT(char(16),SUM(T.LastFillQuantity)),0) As 'SUM'
From BuySell_Lookup L Left Join Table1 T
     On L.Code=T.BuySell
     AND T.UserId=1
     AND TimeOfExecution >= '01/01/2005'
Group By L.Code

The clause should be in the "on" condition, not the "where", and using T.* is from a different database language I have been using.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13842175
perfect.  thank you very much pss.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question