?
Solved

I want to create a SQL Select query with calculated fields to show a Trend column

Posted on 2014-08-22
2
Medium Priority
?
862 Views
Last Modified: 2014-08-24
Hi Experts,

I am just creating a SQL query using sql server 2012. I want to convert my data for a SSRS report. my sql is not great hence why I am asking the question. I believe and easy answer?  I am creating a report and rather than do the calculated field in the ssrs report (which I believe would cause more overhead/performance decrease) I want to create a trend column in the SQL select query.

I will need to cast first. The values are stored as varchar. They will only be whole numbers however. but maybe Nulll.

I want to compare two columns. e.g. Table1.ColumnA and table2.ColumnB.

The output I want is weather it is positive, negative, no change or no previous value.

or if easier. a whole number which shows either positive/negative/zero or Null.

Many thanks,
0
Comment
Question by:Papa1NZ
2 Comments
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 2000 total points
ID: 40278414
Hi,

You can do like this

SELECT 
 [b]CASE 
 WHEN CONVERT(INT,B.FIELD)-CONVERT(INT,A.Col1) > 0 THEN 1
 WHEN CONVERT(INT,B.FIELD)-CONVERT(INT,A.Col1) =  0 THEN 0
 WHEN CONVERT(INT,B.FIELD)-CONVERT(INT,A.Col1) < 0 THEN -1
  ELSE NULL END[/b]
 
  FROM table1 A INNER JOIN table2 B ON ---- 

Open in new window

0
 

Author Closing Comment

by:Papa1NZ
ID: 40281289
Works great thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

850 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