Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sharon_Jean
Starting Member
4 Posts |
 Posted - 2009-01-06 : 10:51:45
|
| I am a newbie to SQL and have successfully created a view that will be exported to Excel. So far I have all the correct data but would like to change some data prior to export if possible. Here is the scenario: all the sales data is in one field - I want to convert the returns based on document type to a negative prior to the export - I am not sure how to approach this.Thanks,Sharon |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-06 : 10:54:13
|
| show some sample data and explain how you want output from them. |
 |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-06 : 10:56:46
|
You can use DTS,bcp to export. You can do that from a view or simply specify a query to export it from.Your query should be something like,select case when documenttype..(your condition) then sales*-1.00 else sales end from yourtable |
|
|
|
Sharon_Jean
Starting Member
4 Posts |
Posted - 2009-01-06 : 12:14:19
|
| Here is a sample: the last 2 columns Calculated Qty and Calculated Extended price are not in my tables but I would like those columns calculated prior to exporting to Excel.Calculated Quantity = IF(SOPTYPE=3,QTYTOINV*-1,QUANTITY)Calculated Extended Price = IF(SOPTYPE=3,EXTENDED PRICE*-1,EXTENDED PRICE)How do you add calculated fields in a SQL View? CUSTNMBR CUSTNAME ITEMNMBR ITEMDESC DOCDATE QTYTOINV XTNDPRCE SOPTYPE QUANTITY SLPRSNID Calculated Qty Calculated Extended PriceABC1234 Customer 1 Item #1 Item Desc #1 1/4/2000 5 310.00 3 5 Salesperson 1 (5.00) (310.00)ABC1235 Customer 2 Item #2 Item Desc #2 6/12/2002 25 250.00 3 25 Salesperson 2 (25.00) (250.00)ABC1236 Customer 3 Item #3 Item Desc #3 2/2/2007 20 235.60 3 20 Salesperson 3 (20.00) (235.60)ABC1237 Customer 4 Item #4 Item Desc #4 2/2/2007 80 2,008.00 3 80 Salesperson 4 (80.00) (2,008.00)ABC1238 Customer 5 Item #5 Item Desc #5 2/2/2007 0 1,429.80 4 5 Salesperson 5 5.00 1,429.80 |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-06 : 12:20:49
|
| [code]Create view myview asselectCUSTNMBR, CUSTNAME ,ITEMNMBR ,ITEMDESC ,DOCDATE ,QTYTOINV ,XTNDPRCE ,SOPTYPE ,QUANTITY ,SLPRSNID ,[Calculated Qty]=case when SOPTYPE=3 then QTYTOINV*-1.0 else QUANTITY end ,[Calculated Extended Price]=case when SOPTYPE=3 then [EXTENDED PRICE]*-1.0 else [EXTENDED PRICE] endfromtablename[/code] |
|
|
|
|
|
|
|
|
SQL View