ReUsing Calculated Columns
Feb 20, 2008
Okay, so yes, I am new to SQL server...
I have this SP below, and I am trying to reuse the value returned by the Dateofplanningdate column so that I don't have to enter the code for each additional column I create. I have tried temp tables and derived tables with no luck.
REATE Proc CreateMasterSchedule
as
Select
dbo.[MOP_Planning Overview].warehouse,
dbo.[MOP_Planning Overview].[Item Number],
dbo.[MOP_Planning Overview].[Planning Date],
CAST (Convert (char(10),[Planning Date], 110)as DateTime)as DateofPlanningDate,
(case when dbo.[MOP_Planning Overview].[Order Category]='101' AND CAST (Convert (char(10),[Planning Date], 110)as DateTime)
<= (CAST (Convert (char(10),(dateadd(day, 8 - DATEPART(dw, dateadd(d,@@DATEFIRST-8,getdate())) ,getdate())-7),110) as DateTime)-1)then dbo.[MOP_Planning Overview].[Transaction Quantity - Basic U/M] else 0 end)as PriorInProc,
If I try to use DateofPlanningDate in the above case statement, I get the invalid column name error.
Basically, I just need a way to reuse the value returned by this column.
Can anyone help?
View 6 Replies
ADVERTISEMENT
Apr 20, 2014
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
View 0 Replies
View Related
Aug 12, 2014
MS SQL 2008 R2
I have the following effectively random numbers in a table:
n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i
I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.
if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1
So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.
declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2
from
allNumbers
View 5 Replies
View Related
Mar 12, 2014
How can I sum the calculated columns.
select [Funding] [Fundings], [Original] [Originals],[Variance] = [Previous_Year]-[Current_Year],[SumValue] = [CurrentYear]/4, [ActualValue] = [Variance] * 0.75,[FinanceYear], [New Value] = [Previous_Year]+[Current_Year] from Finance
View 1 Replies
View Related
Oct 3, 2006
hi
What are calculated colunms?
View 5 Replies
View Related
Dec 13, 2002
I'm trying to create calculated columns in a SELECT query (inside a sp) that are based on other calculated columns in the same SELECT list. But since I'm not allowed to refer to each calculated column by name, I'm forced to repeat long calculations every time I build one calculation based on another.
I'd like to do something on the order of:
SELECT Price*Quantity AS SalePrice, SalePrice*1.08 AS SalesPriceWithTax
FROM Orders
Access used to let me do that, but not SQL. Any suggestions? Can I use variables declared and assigned before I start my SELECT, and then put those variables in my SELECT list? Anyone have a useful shortcut?
View 6 Replies
View Related
Apr 8, 2008
I have an OrderDetails table, ShipHeader table and a ShipDetails table in my database. I am trying to add a couple of columns to my OrderDetails table. I need two additional columns in my OrderDetails that are calculated. One should be called "Shipped" and give me the sum of all the ship Qty records for theat line item. The other should be called "Returned" and do the same for the return records. I was able to create this in a View without a problem. However, I need a method of creating this table that will still allow me to edit the information in the other columns in the OrderDetail table while viewing the shipped and returned information for each line item. With a View, I cannot edit any of the information while viewing it. Should I use a function? A stored procedure? If so, how do I code this? The tables are listed below.
CREATE TABLE OrderHeader
(
OrderID int identity primary key,
Status int,
StartDate datetime,
EndDate datetime
)--Use Type 1 = "Quote" Type 2 = "Cancelled" Type 3 = "Confirmed", Type 4 = "Shipped", Type 5 = "Part Shipped", Type 6 = "Part Returned", Type 7 = "Returned, Type 8 = "Mixed"
CREATE TABLE OrderDetail
(
OrderDetailID int identity primary key,
OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID),
ItemName varchar(30),
Qty int,
SiteID int,
)
CREATE TABLE ShipHeader
(
ShippingID int identity primary key,
Type int,
SiteID int,
ActionDate datetime
)--Use Type 1 = "Ship" Type 2 = "Return" Type 3 = "Lost"
CREATE TABLE ShipDetail
(
ShipDetailID int identity primary key,
ShippingID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID),
OrderDetailID int,
Qty int
)
View 3 Replies
View Related
Jan 31, 2008
Hi,
Im trying to create a calulated value in my matrix table
I have the following matrix
ADSL CABLE BROADBAND %of adsl %ofcable %ofbroadband
AWAITING 5 9 11 0.2 0.36 etc
IN PROGRESS 67 10 5 0.8 0.1 etc
CHURNED 8 1 15 0.3 etc etc
I would like to create columns called
% of ADSL
% of cable
% of broadband
which is the count for that product divided by the total off adsl+cable+broadband for that particular status
I have the following problems
1. How do I add columns to a matrix which would allow these calculated columns to display?
thanks
View 3 Replies
View Related
Feb 7, 2007
Q: How do I use Calculated Columns from a Data Source View in an OLEDB Data Source Adapter.
I took the following steps:
- Created new SSIS project
- Added a Data Source connecting to a SQLServer2005 DB (MyDataSource)
- Added a Data Source View based on MyDataSource (MyDSV)
- Created a Calcualted field to Table Object MyTable (MyCalcField)
- Added a Connection Manager based on MyDSV
- Added Data Flow to Project
- Added OLEDB Source Adapter to Data Flow
- Attempting to Access Calculated Field MyCalcField to be used in Data Flow.
ISSUE: I can't seem to find a way to get the Calculated field to pass through. It's as though this metadata is not available to the Flow.
Anyone have any ideas?
Thanks - MikeyNero
View 6 Replies
View Related
Jun 21, 2015
I need to sort my tablix report where I have several calculated columns like:
=ReportItems!Textbox47.value+ReportItems!Textbox48.value..
Now I would like to sort these by using the Interactive sort functions - but I have seen elsewhere that this is not possible..(I'm also getting an error when trying..)Is there not a way that I can bypass this (using Code function or similar) ? The datasource for the data is a OLAP cube
View 3 Replies
View Related
Nov 12, 2003
Hi all,
I am accessing one database a bunch of different times all throughout my code...in various functions and different web pages. Is there a a way to create an sqlconnection that I can access all the time, instead of constanting hardcoding which database to go to? I've tried putting the info in another file and just including it where I want the database to open, but I can't use <!-- #INCLUDE --> inside of the server scripts.
Can anyone help
View 1 Replies
View Related
Jan 2, 2006
Every time my asp.net app needs to open a connection, it tries to establish a new connection with the mssql server. I´ve already set the max pool size property in the connection string. After that, my app raises an "time out"error saying it couldn´t obtain a connection from the pool. The problem is that I have a lot of iddle connections. With the Enterprise Manager I can see the status of the connections. They´re all the same "awaiting command". How can I reuse this connections? I know that the connection string must be the same for all connections and it is. I´ve set it in the web.config file. If I remove the max pool size property from the connection string I get a lot, I mean A LOT of connections with the sql server. Any ideas?
View 1 Replies
View Related
Apr 19, 2008
Hi ,
I want to open and close sql connection only once and want to use in every function without open or close this connection in class file in asp.net 2003 .
how can it possible .
View 1 Replies
View Related
Oct 23, 2006
I was looking at a means of reusing dialogs.
The attempt I tried was looking up an existing dialog in the conversation_endpoints.
However on doing a scale test I would that the non blocking I was hoping wasn't happening. Even through I was giving each spid a new dialog by using a conversation_group_id related to the spid. I found that the following SQL was blocked by a transaction that contains a begin dialog. This suggests the locking on conversation_endpoints is too excessive.
select top 1 conversation_handle
from sys.conversation_endpoints ce
join sys.services s on s.service_id = ce.service_id
join sys.service_contracts c on c.service_contract_id = ce.service_contract_id
where s.name = 'jobStats'
and ce.far_service = 'jobStats'
and (ce.far_broker_instance = @targetBroker OR @targetBroker = 'CURRENT DATABASE')
and ce.state IN ('SO','CO')
and ce.is_initiator = 1
and (ce.conversation_group_id = @conversation_group_id )--or @conversation_group_id is null)
and c.name = @contractName
View 2 Replies
View Related
Apr 30, 2008
In the Package configurations wizard, I am trying to edit an existing configuration using the edit button. In the Configuration Filter, I get the list of several filters (the filters which were used for other packages). Whe I try to reuse an used filter, it is forcing me to set a new value and when I go back to SQL Server tables , I see the old value has got erased.
Can I not use an existing filter?. Do I need to use new filters for every new package?.
Thanks.
View 1 Replies
View Related
Mar 23, 2004
Hi,
I'm trying to test some queries in SQL analyser without reusing the query plan (already cached). I know that there is a way to avoid that but I don't remember right now. Another option would be to restart MS SQL service but I don't want to do that.
Any thoughts...?
Thanks,
S.
View 7 Replies
View Related
Aug 30, 2007
Hi
I have a replicated table that has a trigger attached to the it. The trigger fires off a service broker message for inserts. Originally for every insert, I would begin a conversation, send, and end the conversation when target send an end conversation. Since replication process is only using a single spid, I would like to reuse 1 conversation. the following is what I have for the send procedure in the initiator. I check the conversation_endpoints for any open conversation, if it's null, I start a new conversation and send else just send with the existing conversation. Is there anything wrong with this code? What could cause the conversation on the initiator to be null if I never end the conversation on the initiator side? thanks
DECLARE @dialog_handle uniqueidentifier
select @dialog_handle = conversation_handle from sys.conversation_endpoints where state = 'CO'
IF @dialog_handle is NULL
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [initiator]
TO SERVICE 'target'
ON CONTRACT [portcontract];
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE [Port] (@msg)
View 1 Replies
View Related
May 10, 2006
Is it possible to reuse a Lookup component which is configured with Full chaching?
My requirement is as follows....
A input file have 2 columns called CurrentLocation and PreviousLocation. In the dataflow, values of these two columns needs to be replaced with values from a look up table called "Location".
In my package i have added two LookUp components which replaces values of CurrentLocation and PreviousLocation with the values available in the table "Location". Is there any way to reuse the cache of first lookup component for second column also?
View 9 Replies
View Related
Oct 22, 2007
Hi,I'm constructing a query that will performs a lot o datetimecalculumns to generate columns.All that operations are dependent of a base calculum that is performedon the query and its result is stored in a columna returned.I wanna find a way of reusing this generated column, to avoidreprocessing that calculumn to perform the other operations, causethat query will be used in a critical application, and all saving isfew.Thanks a lot.
View 2 Replies
View Related
Aug 5, 2007
Hi! I'm wondering why is my sys.conversation_endpoints table inserting a new row for each message i send even when i reuse conversations?
when i send the first message i get the first row in the sys.conversation_endpoints with a uniqueidentifier for the conversation_handle. this uniqueidentifier is then saved in the table which i query the next time i send a message to reuse the dialog conversation.
But even though it looks like the uniqueidentifier is reused i still get a new row for every message i send with a different conversation_handle?
this happens in both target and initator db.
I've tried to understand this by i don't.
Also for the moment i don't end conversations. But as i understand it this shouldn't matter.
Also the message successfully arives to the target and sys.transmission_queue is empty in both databases.
Neither queues have any error messages in them.
Thanx
View 1 Replies
View Related
Feb 1, 2007
I currently have multiple (parent and child) packages using the same config file. The config file has entries for connections to a number of systems. All of them are not used from the child packages. Hence, my child package throws an error when it tries to configure using the same config file because it can't find the extra connections in my connection collection.
Does anyone have any ideas on the best way to go about resolving this? Is multiple config files (one for each connection) the only way?
Sachin
View 4 Replies
View Related
Mar 3, 2004
Hello,
I have a real heartache with runtime parameter interogation on my DB.
Sure I get the latest and greatest and sure I don't have to type in all those lovely parameter types..but...the hit I take on performance for making no less then 3 DB hits for each SqlAdapter is unreasonable!
So ...I like the idea of maybe calling it once for all my stored procs on application startup...and then maybe saving this in CacheObject.
My problem is that I can't see where you can even serialize a SqlParametersCollection or even for that matter assign it to a Command object. Can you cache a command object ?
LOL
I think I may just have to write some generic routine for creating and populating my command objects based on a key (type) and then use that to fetch my command.Update,
command.Insert and command.
I would like to use the new AsynchBlock to do the fetching of the stored proc parameters and then just pull them from the Cache object....put a file watch so that if the DB's change my params it re-pulls them again.
*nice*.....
Then I get the best of both worlds...caching...and no parameter writing...
Eric
View 4 Replies
View Related
Oct 26, 2005
I have 5 packages in a solution.
View 19 Replies
View Related
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
Oct 10, 2007
I know you can't aggregate a calculated value and I'm having a problem finding a solution
I have 3 groups: Course(1), Term(2), Category(3). Course = a particular class (i.e) Algebra; Term = 1 iof 4 terms in our school year; Category = for a class, one may have multiple categories like, participation, homework, quizzes, test, etc.
A class can be weighted for a Category like homework and will count 20% of the grade, tests 40% of the grade and so on. For each Category, I calculate the weighted total using the following:
=iif(Fields!Weight.Value<>0,sum(Fields!Earned.Value)/sum(Fields!Poss.Value)*Fields!Weight.Value*.01,0)
My delema is to sum up all those calculated values in the Term footer.
Thanks
View 4 Replies
View Related
May 14, 2008
HiI am using Management Studio with SQL Server 2005 Express. I am trying to use the Calculated Column Specification by entering a formula.Every attempt results in the same error 'Error Validating the formula'Lets say I have 3 columns a,b, and cI wish to put a formula into c so that it becomes a/bCan anyone either help me with the syntax or point me a resource. I have googled without success. There seems to little or nothing out there on this topic.Thanks,Bill
View 5 Replies
View Related
Jan 19, 2005
Hello,
I have following problem:
A measure in a cube need to be divided by another measure as follows:
MEASURE1MEASURE2
Measure 1 (SUM of Money spent by each person)
Measure 2 (Amount of Money available for each country per person).
Example:
USA: 155
Germany:134
France:143)
Measure1 is a SUM and works fine, but Measure2 should only be a distinct value for each country. So if person comes from Germany, then the SUM of Spent Money should be divided by 134.
Any idea how this can be done.
View 1 Replies
View Related
May 9, 2005
This is correct
store.currentmember.properties("Store_Manager")
I want to obtain information of the column Store_Manager, the table Store, this is dimension, but BUT IT SHOW TO ME A MESSAGE OF ERROR: #ERR
Do you can help me?
View 1 Replies
View Related
Jul 25, 2005
I am a newbie to SQL Server using SQL Server 2000.
I am trying to use a calculated field in a table, what I want is for the result to be shown as an integer (int)?
However the fields that I base the calculation on are of the type (money), and the calculted field although giving me the correct result makes the field of type (money).
The fields that I am basing the calculation on are:-
ColumnName Type Size Allow Nulls
PurchasePrice money 8 0
LoanSize money 8 0
this is the code for the calculated field
([loansize] / [PurchasePrice] * 100)
and this is the description of the calculated field that is forced in the designer
ColumnName Type Size Allow Nulls
LTV money 8 1
and the designer does not allow me to change the type.
I would be grateful of any pointers
Regards
Tony
View 1 Replies
View Related
Jun 21, 2006
Hi...
I'm trying to make a calculated member but I want it at the last level only, with the others I want that shows the sum of the previus :confused: .... Somebody can help me????
View 1 Replies
View Related
Nov 8, 2014
How is the below formula written in a calculated field in SQL?
If TransactionType = 3 then
10% * ValueTransaction else
1.5% * ValueTransaction
end if
View 1 Replies
View Related
Apr 11, 2006
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI
===================================================
1000|liability | -10,000.00 | 08 | 2005
===================================================
1001| asset | 20,000.00 | 08 | 2005
===================================================
1000|liability | -9,000.00 | 09 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Description Amount
asset 20,000.00
liability (10,000.00)
===========
Net Asset 10,000.00
===========
The above report would list 2 columns as Description & Amount, next it would sort the Description
column by GL_ID, next by Year 2005 & lastly by Period 08, with a net figure of asset minus liability.
Guys, hope someone out there can help me with the sql command for the above report?
View 3 Replies
View Related
Jan 1, 2008
I have a location table with columns for [state tax rate], [local tax rate] and a bit column for each to indicate if it is active. From this I need to calculate a total tax percentage; i.e.(state tax rate * bit) +(local tax rate * bit). If I want to stay at the database with this I can identify three alternatives (I'm sure there are more):
1. Calculated column
2. Table Variable (or Temp. Table)
3. View
I'm interested in feedback as to which of these methods is more appropriate or if there is a better way I haven't identified.
Thanks
View 3 Replies
View Related
