Solved

SQL T-SQL Query

Posted on 2007-03-27
13
315 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

739 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