Solved

SQL T-SQL Query

Posted on 2007-03-27
13
318 Views
Last Modified: 2013-11-30
SQL Query!

Help... So i created this query for this client


SELECT  TradePrice as Price, Quantity as Volume,  Contracttype as KIND, strike, expiringdate, comment, (substring (contract+convert(varchar,expiringdate),1,20)) as [FEEDCODE]    
FROM       TS_Positions
WHERE     (Contract LIKE 'LI%') OR
                      (Contract LIKE 'LK%') OR
                      (Contract LIKE 'LL%') OR
                      (Contract LIKE 'LM%')
ORDER BY Contract
__________________-

Data result was
Price              Volume        Kind               Strike             date              Comment       Life

0.0      1500      Call      97.25      20060106      NULL      LIF0620060106

2 questions--
1. How can I cut the result data for KIND just to show to C or P or F(basically the 1st letter not the whole word).
2. How can i change the result Decimal for Price column. It shows 0 but its really 15.625 or 16.25

Adding the query to above statement would be GREAT!!!!!! Thanks

0
Comment
Question by:jcollazo81
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18802934
SELECT  TradePrice as Price, Quantity as Volume,
                               LEFT(Contracttype,1) as KIND,
strike, expiringdate, comment, (substring (contract+convert(varchar,expiringdate),1,20)) as [FEEDCODE]    
FROM       TS_Positions
WHERE     (Contract LIKE 'LI%') OR
                      (Contract LIKE 'LK%') OR
                      (Contract LIKE 'LL%') OR
                      (Contract LIKE 'LM%')
ORDER BY Contract
0
 

Author Comment

by:jcollazo81
ID: 18802959
That worked perfect... and what about Decimal point for that query? Thank you again!!!!!
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18803011
I didn't understand that part clearly, if you are saying it is returning 0 , then you need to check the table, from the query side, there is nothing to do, as it is just a select statement

Now if you wanna round it to 2 decimals , use this

SELECT CAST(TradePrice as numeric(10,2) )  as Price
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18803066
note.
WHERE     (Contract LIKE 'LI%') OR
                      (Contract LIKE 'LK%') OR
                      (Contract LIKE 'LL%') OR
                      (Contract LIKE 'LM%')


can be written:
WHERE     (Contract LIKE 'L[IKLM]%' )
0
 

Author Comment

by:jcollazo81
ID: 18803362
Sorry if you actually open up the table thru EManager it shows TradePrice data for example:

15.625
48440
but my query shows as 0 for all of them.. Weird.
I right clicked on Design table and it shows as a FLOAT for Data type... Length 8. Any ideas?

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18803540
just run the script without the where part  :)
0
 

Author Comment

by:jcollazo81
ID: 18803662
I seee ahhhhhh. But there is no  way to add where and get the same data for the PRICE Column?

I see the data there without the Where in it...........
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18805834
>I right clicked on Design table and it shows as a FLOAT for Data type... Length 8. Any ideas?
for a PRICE, float is actually a bad choice. better use numeric / decimal. float / real are floating-point stored, and hence unprecise.

back to the problem: what version / edition are you using?
could it be that all the rows that you show with the where clause actually do have 0 in the price column?
0
 

Author Comment

by:jcollazo81
ID: 18807223
I opened up the table view EM and it shows data on there. When i don't include the where on the query it shows the correct data as well.
SELECT   case positiontype when '1' then 'buy' when '-1' then 'sell' else 'not either' end AS [B/S], comment, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
                               LEFT(Contracttype,1) as KIND,
strike, expiringdate, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE]    
FROM       TS_Positions

VS


SELECT   case positiontype when '1' then 'buy' when '-1' then 'sell' else 'not either' end AS [B/S], comment, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
                               LEFT(Contracttype,1) as KIND,
strike, expiringdate, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE]    
FROM       TS_Positions
WHERE     (Contract LIKE 'LI%') OR
                      (Contract LIKE 'LK%') OR
                      (Contract LIKE 'LL%') OR
                      (Contract LIKE 'LM%')
ORDER BY Contract
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18807256
can you please show (upload) the result of the following queries:

select top 10 * from ts_positions

select top 10 * from ts_positions
where (Contract LIKE 'L[IKLM]%')

select top 10 * from ts_positions
where (Contract LIKE 'L[IKLM]%')
order by Contract

0
 

Author Comment

by:jcollazo81
ID: 18807354
select top 10 * from ts_positions:
                                                                                                                                Price
600628      TYG06      Put      108.0      -1      567      15.625      

select top 10 * from ts_positions
where (Contract LIKE 'L[IKLM]%')
                                                                                                                                Price
1173259      LIM07      Call      96.125      -1      1000      0.01      


select top 10 * from ts_positions
where (Contract LIKE 'L[IKLM]%')
order by Contract
                                                                                                                                Price
1211617      LIF06      Call      97.25      -1      1500      0.0      NULL      
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18807838
which column is the price? I ask, because the number of columns seems to be different in the 2 outputs, ie the last output has 1 more column (at least what you posted...)

anyhow, this looks like all the rows with contracts like L... have prices that are empty/null ?
what data type is the field TradePrice in the table / view? (again, do NOT use float !!)

0
 

Author Comment

by:jcollazo81
ID: 18808012
well this is just a query i think the programmers created the FLoat on that column. Right clicked on the table and DEsign table..
                     Column Name  Data Type    Length       Allow Nulls
      TradePrice      float      8      1

we use Float for Strike and i am able to collect the corrcet data for that:
                     Column Name  Data Type    Length       Allow Nulls
      Strike      float      8      1

0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

728 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