Need Assistance With Defining Column Value in a View

In view design I wish a row to calculate the UnitDiscountAmt by multiplying 
the UnitPrice * DiscountRate if DiscountRate >0, Else just show the 
UnitPrice. Now if I were doing this in Access I would IIf([DiscountRate]=0, 
[UnitPrice], [UnitPrice]*[DiscountRate]. How would I write this in the view?
0
Utf
3/25/2010 6:58:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
435 Views

Similar Articles

[PageSpeed] 15

CASE (which is more generic than IIF and also ANSI SQL). Something like:

SELECT
 col1
,CASE
   WHEN DiscountRate > 0 THEN UnitPRice*DiscountRate
   ELSE Unitprice
  AS colname
 END
,col3
FROM ...

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"meangene" <meangene@discussions.microsoft.com> wrote in message 
news:66249C6B-5B08-42CB-B851-DC6B269AD03C@microsoft.com...
> In view design I wish a row to calculate the UnitDiscountAmt by 
> multiplying
> the UnitPrice * DiscountRate if DiscountRate >0, Else just show the
> UnitPrice. Now if I were doing this in Access I would 
> IIf([DiscountRate]=0,
> [UnitPrice], [UnitPrice]*[DiscountRate]. How would I write this in the 
> view? 

0
Tibor
3/25/2010 7:12:26 PM
>> In view design I wish a row to calculate the UnitDiscountAmt by multiply=
ing the UnitPrice * DiscountRate if DiscountRate >0, Else just show the Uni=
tPrice. Now if I were doing this in Access I would IIf([DiscountRate]=3D0,[=
UnitPrice], [UnitPrice]*[DiscountRate]. How would I write this in the view?=
 <<

Have you considered making the default value of the discount rate 1.00
and always doing the multiplication? Or use  ((1.00 - discount_rate) *
unit_price) ?

0
CELKO
3/25/2010 7:37:34 PM
This did the trick - thanks!

"Tibor Karaszi" wrote:

> CASE (which is more generic than IIF and also ANSI SQL). Something like:
> 
> SELECT
>  col1
> ,CASE
>    WHEN DiscountRate > 0 THEN UnitPRice*DiscountRate
>    ELSE Unitprice
>   AS colname
>  END
> ,col3
> FROM ...
> 
> -- 
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
> 
> 
> 
> "meangene" <meangene@discussions.microsoft.com> wrote in message 
> news:66249C6B-5B08-42CB-B851-DC6B269AD03C@microsoft.com...
> > In view design I wish a row to calculate the UnitDiscountAmt by 
> > multiplying
> > the UnitPrice * DiscountRate if DiscountRate >0, Else just show the
> > UnitPrice. Now if I were doing this in Access I would 
> > IIf([DiscountRate]=0,
> > [UnitPrice], [UnitPrice]*[DiscountRate]. How would I write this in the 
> > view? 
> 
> .
> 
0
Utf
3/26/2010 2:41:01 PM
Reply:

Similar Artilces:

Macro or Formula needed to search data in cells
Hi I am looking for a way to create a formula/macro to do the following: My worksheet setup: A1:A30000 C1:C5000 01 02 03 04 05 06 07 08 09 10 01 04 05 06 08 09 12 22 23 27 02 03 04 05 06 07 08 09 10 11 01 03 05 06 07 08 09 14 22 32 03 04 05 06 07 08 09 10 11 12 04 05 06 07 08 09 10 11 12 13 etc Each cell contains a 10 number sequence. The range A1:A30000 is my randomly generated sequence. And C1:5000 is my database of archived sequences. If I wish to check if the combination in cell A1 is anywhere in the range C1:5000. I use the formula. =IF(COU...

Dynamic Bar of Pie Chart: Series1 Label & Value Issue
I have created a Bar of Pie chart and everything works great. I even have named ranges on my data so that the bar portion automatically updates when the data changes. However the Series1 Label on the pie itself does not change to reflect the new total so that it is the sum of all the values in the right column below. Does that make sense? I have one series and my data looks like this: Contracts 100 Name1 100 Name2 150 Name3 200 Name4 75 Name5 50 Name6 25 The pie portion has Contracts & Other as labels with the totals of...

View pointer in static function Help!!!
Help please , i need to implement a static function in CView derived class that must return to CDoc derived class the active view pointer...how can i do? i tried something similar CSdicap10View* CSdicap10View::GetView() { CSdicap10View* pView = GetActiveView(); return pView; } it return error GetActiveView undeclared identifier or also CSdicap10View* CSdicap10View::GetView() { return this; } but it return static member functions don't have this pointer and also CSdicap10View* CSdicap10View::GetView() { CFrameWnd * pFrame = (CFrameWnd *)(AfxGetApp()->m_pMa...

ability to view hotmail messages through outlook suddenly stopped
I have two email accounts, one that I access through my ISP and the other at Hotmail. Both email accounts have been set to open in Outlook (Outlook 2000 on Win XP OS). Until four days ago, I never had a problem viewing or responding to hotmail messages. Then suddenly, I could view the sender and subject of a message on the hotmail account viewed and formatted to work in Outlook, and I could delete an unread message, but I cannot open or view contents of any message received. Now I have to login to my hotmail account separately and view messages in a browser format, and it is driving me crazy....

negative values on axes
hi there; this is my first mail here. well my x axes start from -2 and my Y axes data start from -5, I change both to -5 (scale) they are shown (XY scatter dots) as cross, like + sign. what I need: how to change the graph punt the joint axes point from 0 to -5? so the chart shws as usual not like a cross. Best Darius Hi, Change the Values axis crosses at to -5 for both axis. This is an option on the Scale tab of the Format Axis dialog. Cheers Andy Darius wrote: > hi there; > this is my first mail here. well my x axes start from -2 and my Y axes data > start from -5, I chang...

Search for data in a column bring all related items in other colum
I have data in the excel sheet with .A column for OrderNo and B Column for items for that Order. A B ORD001 ITEM1 ITEM2 ITEM3 ORD002 ITEM4 ITEM1 ITEM5 There are more than thousand orders.I want search for an Order so that it brings all items with it. I cannot use filter in A column as it doesn't recognise blank cell.So Option to repeat order nos in A for every item involves a lot of data entry. Any Solution ? I'd fill those empty cells in column a with the previous value. And us...

Need Money & am confused...
Hello! This is a great site - I am rather confused, though. I have been using Money forever (I hardly use the download/internet features so this removal of Money isn't too catastrophic to me). Fast forward, I had to reinstall and I no longer have the documentation....so, I went online and downloaded the Plus TRIAL version. Well, it has expired - kept trying to 'purchase' but that is not an option, as we all know. So, I tried to download 2007 - but it then said that I cannot go backwards - so, here I am - my .pst file is now Microsoft Money Plus - I am not able ...

TabCtrl need to get rid of OK, ESC events
hi, i'm working on a CFormView subclass that contains a CTabCtrl subclass, which has two tabs. Each tab displays a CDialog subclass (tab1dialog, tab2dialog) that houses each tabs controls i learned all this from http://www.codersource.net/mfc_ctabctrl.html http://www.codersource.net/mfc_dialog_message.html now the PROBLEM is that I'm unable to get rid of the ENTER/ESC messages, which make my dialogs (for each tab) dissappear!!! arrghh.. i read online that overriding OnCancel() and OnOK() and PretranslateMessage() work, but in overriding those in the CDialog subclasses (tab1dialog,...

How to export values from XY (Scatter) graf?
Is it possible to export each x and y value from XY (Scatter) graph? ...

Running Office 2008 Setup Assistant Fails (?)
Installed Office 2008 successfully on my intel iMac. Did not remove Office 2004. When the Setup Assistant came up, I filled in all the boxes on both sides, pressed continue, and nothing happened. I repaired permissions and did it again with the same result. I rebooted. Same result. Anybody help??!!! On 2/22/08 8:05 AM, in article ee8e669.-1@webcrossing.caR9absDaxw, "ADDad@officeformac.com" <ADDad@officeformac.com> wrote: > Installed Office 2008 successfully on my intel iMac. Did not remove Office > 2004. When the Setup Assistant came up, I filled in all the boxes on bo...

If cell value is greater than another cell value, clear contents.
Okay, hopefully this is the last question today! I've found the below formula in one of the posts here, but my brain has completely failed on me and I'm not sure how to adapt it to my requirements. I have a value which is entered by the user after a prompt, that populates in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value is 30/11/09. I have another worksheet, 'Data', which has a column populated with dates (say column B). I'd like a macro that looks in column B on the data sheet, and clears out any dates which are gr...

Hide rows with value "Hours"
I would like to toggle rows to hide/unhide based on the value "Hours" in column B. I think filtering is an issue to some blank seperator rows and merged cell headings. There are no merged cells involving Column B. Thanks. Public Sub ProcessData() Const TEST_COLUMN As String = "B" '<=== change to suit Dim i As Long Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).row For i = 1 To iLastRow .Rows(i).Hidden = .Cells(i, TEST_COLUMN).Value = "Hours" Next i End With ...

How do you ensure that null-valued elements are not serialized?
I am using complex types in order to support serialization/deserialization of floating point numbers, since floating points can't be null. I've seen how to suppress attributes that are "not specified", such as having a float member called Value, and a bool member called ValueSpecified. This instructs the XML Serializer to omit that attribute altogether if it wasn't "Specified". But how can I tell it to omit the XML element altogether? Here's the problem: I deserialize an object that looks like this: <person> <name>Joe<...

how do I find an average number of specific words in a column
I am attempting to calculate a number of specific word occurrences. In example, I have a column with yes in certain cells, and no in the others. How can I display the total number of yes and no occurrences? I am fairly new to Excel and know very little about coding in it. Hi you can use the countif function to return the numbers of "yes" and "no" e.g. =COUNTIF(A1:A100,"yes") will count the number of "yes" answers in the range A1 to A100 likewise, =COUNTIF(A1:A100,"no") will count the number of "no" answers in the same range H...

View the Teams that the account is shared with
Hi All I urgently need to have a view of all the teams that a specific account is shared with, and possible the members of that team. Alternatively, I need a view of all the accounts that are shared with a specific team. I really can not believe that there is nowhere in the vanilla CRM that one can view the teams (and team members) that have access to an account. I will really appreciate any help or suggestions. Many Thanks Yolande du Preez MCP, MCSD, MCSE, MCDBA Yolande, When you are viewing an account record, you can go to Actions...Sharing to view all the Users and Teams that a...

public calendar- users can only view items they added????
hi lovely people, i'm trying to get my head round creating a public calendar, a year planner for the business with our small 1 server domain. we just got sbs 2003 after many years of a decrepid nt4 thing, its quite a culture shock!!! anyways here is my question... i've created a public folder calendar. i did this through outlook 2003 in my administrator account. i added numerous events to this public calendar fine and dandy from the same account. now, i can add to and view this public calendar quite happily. my problem is, i can add this new public calendar to any other user ac...

Capture initial value of text box
Not sure why I can't seem to get this to work, but here's what I'm trying to do: I have a text box, 'Actual_Due_Date', tied to a field in a table. In the same table is a text box named 'Org_Due_Date'. When the initial value is set in the 'Actual' field, I want to capture and store it permanently in the 'Org' field. I have two forms, a New Project form and an Edit Project form. My thought was to just setup a simple macro in the New Project form in the 'Actual' field that says After Update set the 'Org' field equal to the 'A...

How do I lock N left hand columns
How do I lock N left hand columns in excel so that when I want to see some right hand columns and slide the columns on the right to the left the locked columns do not move and remain visible? Don J ------------------------------------------------------------------------ With A,B,C visible select D1 and hit Window / Freeze panes. This will freeze A,B,C in view If you hit D2 and do it you will also lock the headers in view (ie Row 1) Whatever cell you select it will freeze to the left and above that cell. -- Regards Ken.........................

MS Query Column Headings and Calculated Fields
I'm using MS Query to pull data from an ODBC data source. Everything works fine and I can extract data just great. I'm wondering, however, calculating some fields with the query. I can't seem to add a column heading without enclosing the heading in quotes. That would normally be fine, but everytime I go to edit the query and extra set of quotes is added to the column heading. Is there any way to get around this? I also would like to know if there is a way to use calculated fields in other calculations. If so, is there any documentation out there on how to do this? Thanks, -...

Data was deleted and saved by mistake..need to go back
I need to go back to the data before it was saved, can anyone help Richard don't get your hopes up. Unless you have a copy or a backup ... it's gone. Regards Trevor "Richard" <anonymous@discussions.microsoft.com> wrote in message news:1084801c3bea6$222d5270$a601280a@phx.gbl... > I need to go back to the data before it was saved, can > anyone help ...

view by name
Hello, I have a customer that manages someone else's mailbox. We have it mapped in her outlook as another mailbox in her profile and she has full rights to it. She needed to find a particular email that was sent to someone so she went to the sent items and viewed by "To" and typed in the person's name. There should have been 2 messages that came up as there were 2 that were sent to that person that day but only one shows up. If I view by date, then the other one shows up. Now this happens occassionanly, not just this time. Most of the time if she view's by "to" ...

Need help
My company ask me to look for a freelance person who can translate CAD Files to VISIO file...please respond Go to controldrawings.com. I will try to assit you. "Jayne_from_Santa_Monica" wrote: > My company ask me to look for a freelance person who can translate CAD > Files to VISIO file...please respond > > ...

need to get form property
hi, this is probably a very easy solution but I can't figure it out. I have Form A (frm_ARCall), subForm B (sfrm_ARCallInv) and Form C. From Form C, I want to change the Record Source property of subform B. I have tried many things but they have all failed. The latest is: Forms![frm_ARCall]![sfrm_ARCallInv]![Form].[RecordSource] = "fqry_ttARCallInv" NOTE: This line of code is on Form C which is a subform on another form (Form D). Please help -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201002/1 t...

2 Y Axes: Lines and a Stacked Column
Hello, I'm using Excel 2003 SP3 and having trouble with the following... Sample data: X Axis Y Axis1 Y Axis1 Y Axis2 Y Axis2 Y Axis2 Date DataA DataB DataC DataD DataE 1/31/09 4.3 3.6 10% 40% 50% 2/28/09 2.9 1.9 30% 60% 10% 3/31/09 1.2 6.4 15% 10% 75% I need Y Axis1 to be two simple lines and Y Axis2 a stacked column that sums to 100%. Can't figure this out. Please advise... Thanks! Jeff First, clear the cell above the dates,...

Transfer data from Excel col. A to columns B-E in the same sheet
I have an Excel 2003 spreadsheet with only one column of player data: column A. The first three data items in column A are the same for every player: Name, Address and Phone. Every player also has at least one comment but could have any number of comments. Each player’s data is separated from the next by a blank cell in column A. Sometimes, a player’s last few comments are blank resulting in multiple blank cells in column A before the data for the next player starts. I need help writing an Excel 2003 VBA macro to: 1. Copy just the player’s name, but not the Name: label, to c...