Using Formula For Colum Name!!
Oct 2, 2005
i wonder what is the best approach to use !!!i have creditLimit column in Customer Table the default value will be 500 and this limit to allow users to send sms from my website ..... every month they will be allowed to send 500 sms referring to the credit limit column.... now !!if the user sent today some sms and after few days sent another 20 and after one week he sent 150 SMS as a total so that means he has only 350 SMS as credit to use this month!!!so what is the best approach to implement this solution ? shall i have another field in the table called 'CreditUsed' and that will be updated each time the user will send SMS and this value will be compared with the credit limit ORi use the formula for the column to calculate the credit left and do the maths !!!what is the best approach as you think ???thnaks for reading this question !!
View 7 Replies
ADVERTISEMENT
Sep 1, 2015
In SQL reporting, How do I add the formula in the Formula bar?All the data is coming from a sproc.
View 3 Replies
View Related
Dec 9, 2005
Hi
I wonder if its possible to return a value at same row from another colum if col1 contains NULL (or other criteria)
ex:
the output is:
col1,col2,col3
text1,text10,text100
text2,text20,text200
text3,NULL,text300
Can i take a value from col3 and return it to col2 in the output if a criteria is meet (like NULL)
the DDL is nvarchar254 at thoose colums.
thx in advance //Mr
View 3 Replies
View Related
Jan 21, 2008
i am running this script
SELECT dbo.SHOVEL_A.HOLEID, dbo.Shovel_Hole.HOLEID AS shovel_id, dbo.SHOVEL_A.PROJECTCODE, dbo.Shovel_Hole.Shotname,
dbo.Shovel_Hole.B_FEET, dbo.Shovel_Hole.S_FEET, SQRT(square(dbo.SHOVEL_A.EAST - dbo.Shovel_Hole.EAST)
+ square(dbo.SHOVEL_A.NORTH - dbo.Shovel_Hole.NORTH)) AS distance_total
FROM dbo.SHOVEL_A FULL OUTER JOIN
dbo.Shovel_Hole ON dbo.SHOVEL_A.POLYGONO = dbo.Shovel_Hole.Shotname AND distance_total <= dbo.Shovel_Hole.B_FEET
GROUP BY dbo.SHOVEL_A.HOLEID, dbo.Shovel_Hole.HOLEID, dbo.Shovel_Hole.Shotname, dbo.Shovel_Hole.B_FEET, dbo.Shovel_Hole.S_FEET,
dbo.SHOVEL_A.PROJECTCODE, SQRT(square(dbo.SHOVEL_A.EAST - dbo.Shovel_Hole.EAST)
+ square(dbo.SHOVEL_A.NORTH - dbo.Shovel_Hole.NORTH))
HAVING (dbo.SHOVEL_A.PROJECTCODE = 'PRODL') AND (dbo.Shovel_Hole.Shotname = '20040524029')
but i have a result with the message that my colum name distance_total is wrong, so how can i fix that?
cheers
edwin
View 2 Replies
View Related
Aug 10, 2007
We are thinking of using general purpose column names in our application schema. We want to give the option to the end user to customize the filed names to fit their business . We want to build the functionality on the generic names so that it will work for multiple customes.
Example: We may want to have 10 Strings, 10 numbers and 5 booleans pre defined and reports running off of the table. The customer can name first number as pressure, second one for length and map their data to the table. Other customer can use the first number for temperature and the second one for width.
Is there a way to do it in SQL server w/o having a lookup table for column name aliasing?
Thanks
View 1 Replies
View Related
Jan 21, 2008
i am running this script , but i have this result even though my script is ok, so i do not what is the problem.
SELECT dbo.SHOVEL_A.HOLEID, dbo.Shovel_Hole.HOLEID AS shovel_id, dbo.SHOVEL_A.PROJECTCODE, dbo.Shovel_Hole.Shotname,
dbo.Shovel_Hole.B_FEET, dbo.Shovel_Hole.S_FEET, SQRT(square(dbo.SHOVEL_A.EAST - dbo.Shovel_Hole.EAST)
+ square(dbo.SHOVEL_A.NORTH - dbo.Shovel_Hole.NORTH)) AS distance_total
FROM dbo.SHOVEL_A FULL OUTER JOIN
dbo.Shovel_Hole ON dbo.SHOVEL_A.POLYGONO = dbo.Shovel_Hole.Shotname AND distance_total <= dbo.Shovel_Hole.B_FEET
GROUP BY dbo.SHOVEL_A.HOLEID, dbo.Shovel_Hole.HOLEID, dbo.Shovel_Hole.Shotname, dbo.Shovel_Hole.B_FEET, dbo.Shovel_Hole.S_FEET,
dbo.SHOVEL_A.PROJECTCODE, SQRT(square(dbo.SHOVEL_A.EAST - dbo.Shovel_Hole.EAST)
+ square(dbo.SHOVEL_A.NORTH - dbo.Shovel_Hole.NORTH))
HAVING (dbo.SHOVEL_A.PROJECTCODE = 'PRODL') AND (dbo.Shovel_Hole.Shotname = '20040524029')
View 1 Replies
View Related
May 20, 2008
Hi All,
I have a column called TIER in my database which is a long string. There are piece of these strings that are separated by spaces like this:
'A3A00 A2B00 B1A00 C2C06 C3A06 C5A00 D2C00 G6B00 M2B00 M3B00 P7A00 T2A00 G4C00 G3C00 T5A06'
How do I select the last 2 characters of the piece of string when the first 2 chacters are M2?
I have the following:
This is retruning me all the rows that have 'M2'.
declare @code varchar (2)set @code = 'M2'select tier from companywhere tier like '%'+@code+'___ %'
Any help is appreciated.
Thank you in advance.
View 3 Replies
View Related
Feb 14, 2001
HI,
how can i drop the identity on a column from the table.
I have no access to the sql enterprise manager, i have to do it thru query analyser only.
regards,
varadish
View 3 Replies
View Related
Jun 7, 2001
Hi All,
Just want to know if a column with IDENTITY can be altered. If yes, how?
I have a table with colum defined like this:
CREATE TABLE [dbo].[Test] (
[TestID] [int] IDENTITY (1, 1) NOT NULL ,
.......
Through ALTER TABLE script (I don't want to do it through Enterprise Manager) I want it to be like this:
CREATE TABLE [dbo].[Test] (
[TestID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
.......
I want the constrain "NOT FOR REPLICATION" done through ALTER TABLE script. Is that possible?
Please let me know with the syntax.
Thanks
Sri
View 1 Replies
View Related
Feb 16, 2007
hi guys,
i have a problem, i have a query which does a search based on a parameter, thing is i want to order them according to how relevant the results are such as 5 for beingan exact match, 0 for being no match. using a series of liek statements.
so that the most relevnat results are displayed at the top.
how do i achieve this - im a bit confused about this.
appreciate any and all help guys
thanks
andy
View 7 Replies
View Related
Aug 28, 2007
Hi All,
I'm having this many rows(around 25 columns am only showing some 3),
but in the output i want only 2 rows, how can i do that.
16_00SB3215-02
16_0100301A39A041
16_02Successful
16_03Successful
16_04Successful
16_0500301A39A042
18_00Pass
18_0200301A39A041
18_0300904BC1A1D6
18_325.18
18_335.19
18_345.24
18_365.40
18_375.39
18_385.32
18_40-49
18_41-56
18_42-53
18_44-62
18_45-63
18_46-60
26_00SB3216-02
26_0100301A39A041
26_02Successful
26_03Successful
26_04Successful
26_0500301A39A042
28_00Pass
28_0200301A39A041
28_0300904BC1A1D6
28_325.68
28_336.19
28_349.24
28_365.40
28_375.89
28_385.32
28_40-49
28_41-56
28_42-53
28_44-82
28_45-63
28_46-69
Output like this
----------------
16_00SB3215-02 6_0100301A39A0416_02Successful6_03Successful6_04Successful6_0500301A39A0428_00Pass8_0200301A39A0418_0300904BC1A1D68_325.18 8_335.19 8_345.24 ....................
like this
please help me, how can i do this
with Regards
Amjath
View 18 Replies
View Related
Apr 8, 2008
I have about 5 statements like the update below, depending on the PID different columns will be update "C2005, G2005,E2005...."
I would like to use 1 update statement in stead of 5 to update all columns below are 2 original update statements and my attempt at when then update. Note a different column is updated depending on the PID.
If when then isnt possible, any other suggestions are welcomed. Thanks
UPDATE #Sec
SET C2005 = Pos.USD / 1000
FROM #Sec INNER JOIN
Pos ON #Sec.ID = Pos.ID
WHERE (Pos.PID = 'B')
UPDATE #Sec
SET G2005 = Pos.USD / 1000
FROM #Sec INNER JOIN
Pos ON #Sec.ID = Pos.ID
WHERE (Pos.PID = 'G')
UPDATE #Sec
WHEN (Pos.PID = 'C') THEN SET C2005 = Pos.USD / 1000 end
WHEN (Pos.PID = 'G') THEN SET G2005 = Pos.USD / 1000 end
WHEN (Pos.PID = 'E') THEN SET E2005 = Pos.USD / 1000 end
FROM #Sec INNER JOIN
Pos ON #Sec.ID = Pos.ID
View 3 Replies
View Related
Aug 29, 2006
Hello,
I have a table in witch i would like to replace all the data form one columm following a criteria.
The criteria is:
Where 1010222222 will be 0020222222
So i want to make a script that enables me to substitute all the data in that columm that begin by "101" for "002"
Any help?
Thanks
View 6 Replies
View Related
Feb 6, 2008
Im Working with stored procedure. How can i compare Columns with specific values. I want to get the greater values of those column and inserted it to other columns. i want something like these
CASE
WHEN a> b,c,d THEN a
WHEN b> a,c,d THEN b
WHEN c> a,b,d THEN c
WHEN d> a,d,c THEN d
is there any ways to implement this? i got an error..
thanks please help..
View 7 Replies
View Related
May 20, 2006
Hi all, I am trying to sum a column into different variables depending on another column. Let me explain my self better with an example
DECLARE @Initial decimal(18,2), @incomings decimal(18,2), @outgoings decimal(18,2)
SELECT
@initial = CASE WHEN type = 1 THEN SUM(amount) END,
@incomings = CASE WHEN type = 2 THEN SUM(amount) END,
@outgoings = CASE WHEN type = 3 THEN SUM(amount) END,
FROM Transactions
WHERE date = '05/14/2006' AND STATION = 'apuyinc'
GROUP BY type, amount
What I am trying to do is to sum all of the incomings transactions into @incomings, all of the outgoing transactions into @outgoings and the initial transaction into @initial where
The incoming transactions is type 2,
outgoing transactions is type 3
Thanks for the help
@puy
View 5 Replies
View Related
Aug 2, 2001
In SQL7, if you add a column to a table, is it necessary to recompile all
stored procs that reference that table?
Under 6.5, this caused problems at our site unless the Procs were 'refreshed'.
Is this only necessary where select * is used?
Thanks
View 1 Replies
View Related
Jul 7, 2004
I have a large text colum I am trying to insert into a DB
This colum is about 800 chars longs
I have set the colum type in the table to text
I have set the table option for text in row to on
I have set the table option for text in row to 1000
But it is still chopping the text at the 256 char mark on insert.
Anyone have any ideas ?? This is SQL 2000.
Chris
View 4 Replies
View Related
Jul 20, 2005
Hi ,I have this querypaprojnumber is varcharpatx500 is textpalineitemseq is intselect Paprojnumber,Patx500,max(palineitemseq) from pa02101,pa01601wherepa02101.pabillnoteidx=pa01601.pabillnoteidx group bypaprojnumber,patx500it throws this errorServer: Msg 306, Level 16, State 2, Line 1The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.Thanks a lot for your help.AJ
View 2 Replies
View Related
Aug 16, 2007
THe unique Id column is set to identity seed. Yet when transfering 1500 records it fails to stuff them all becuase it says there is a NULL in unique id column.
"Cannot insert the value NULL into column UID column does not allow"
Yet in the transformation screen I am setting the UID column to <ignore> meaning I do not have that value in my source and I am wanting the destination table to do it's thing and incrememnt by 1 the identity seed.
Is this common?
THe source and destination tables are in the same databes.
View 4 Replies
View Related
Apr 13, 2007
Hi all,We're selecting data from our database, FirstName, LastName, MobileNumber etc.We're using the detaials view function to return it in a table upon selection. However all of the variables are returned as they are in the database, ie: without spaces. We tried putting in spaces by selecting "AS what ever", but MSSQL does not seem to like spaces.Any ideas?Thanks
View 2 Replies
View Related
Jul 30, 2006
Urgent help needed!I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I havethe following problem:If the join on two tables results on duplicate colum names (whichappear in both tables) I could reference them by using:RECORDSET("TABLENAME.COLUMNAME")However with SQLServer if I try this kind of reference I get an errormessage.How can between two colums with the same name from two differen tables?Thanks in advance!Adam
View 5 Replies
View Related
Feb 7, 2007
I have a complex view in my sql 2005 database.
The view returns a column that could be null (as the result of a left outer join).
The coulmn that is returned is an integer.
Everything works fine if I run the view from SQL 2005 Management Studio.
My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable.
Has anybody seen this behaviour before?
Any help appreciated.
Regards,
Paul.
View 2 Replies
View Related
Dec 14, 2007
I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?
View 1 Replies
View Related
Jan 18, 2008
Having a hard time writing a formula in RS...
Trying to say if the funding date is equal to today or before the beginning of the month then sum the loan amount.
Looking for something like this SUM(IF(Funding Date,>=Date(),Loan Amount))
Know it not right but someone please help!
View 1 Replies
View Related
Jan 26, 2008
I am create a database and want to store some value automatically in some field i.e say i have 3 column 1) salary(int),2)tax(int) and 3rd field total salary(float) . i want to automatically fill total salary field as Column1-Column2 while i daont have any Idea How to do that?
please help me?
any idea or tutorial..example anything...
View 3 Replies
View Related
Jul 20, 2005
Hello!I have an MS SQL-server with an database, that runs replication. In thisdatabase there is an table with an columni want to extend; varchar(50)->varchar(60).But I get this error (using design window of Enterprise Manager): Cannotdrop the table 'MytableName' because it is being used for replication.Thanks for helpBjoern
View 1 Replies
View Related
Nov 17, 2007
hi,my users can make posts in my web application, i mean they fill a form and the information they filled will be saved in sql server 2000 and can be shown in web application,now i want to give each post an Id and save it in the database, how can i do that? does sql server have the abilities or i should do sth in my c# application
thanx
View 1 Replies
View Related
Mar 30, 2006
hi there.
I have asked this question before in a different section of the forum without much reply, and its probably because i was asking the wrong people.
I need to calculate a qouta for an election.
SqlCommand SqlCmd1 = new SqlCommand("SELECT count(vote)FROM PRTest", SqlCon1);
int quota = (count(Vote) + 1) / ((11) + 1);
My problem is this: how to i get the count(vote) value from the Sql Statement to the formula.
View 2 Replies
View Related
May 10, 2006
This does nothing:([rush24] + [rush6] + [addLocationsTotal] + 50)
This gives me my total correctly:([rush24] + [rush6] + 50)
All column are numeric(9) except the "total" column which is numeric(13).
Why is this happening?
View 1 Replies
View Related
Jun 13, 2002
I want to use the proprity Formula related to a column propreties :
Exempel : table XFRS i want to pupulate the field XF2 (int 4) using the value of another Field XF1 (int 4) Which is an identity field.
But When i try to insert into the table XFRS then i had an error
"Insert faild because the following set options have incorrect settings"
'ARITHABORT'"
Anyone to help me ? Thinks
View 1 Replies
View Related
Nov 3, 2004
Can anyone tell me how to find out if a column is a 'formula' or computed column - and what the formula is?
sp_columns @table_name = 'table' gives me most of the stuff I want but it doesn't show what the formula is for any formula columns...
Cheers in advance,
View 1 Replies
View Related
Apr 10, 2008
I have a quick question. created a report with this formula below. It works when I put a date range from 1-1-2007 to 2-1-2007, but when I put in a date range of 03-01-2008 through 03-31-2008. i get an error message of arithmetic overflow error converting numeric to data type numberic.
So instead of haveing the formula below be (4,2) I put it as (5,2) and now it work. Why is that?
CAST(clm_sppo / clm_tchg * 100 AS decimal(4, 2)) AS PercentSavings
View 3 Replies
View Related
May 26, 2008
in front end application,iam using pivot table,there is no option to create the growth rate calcuation/formula in FE.
my table data consists like below:
country_name Revenue PERIOD_TYPE_OUT Amount_out
UK Solutions01 Apr 2007 To 31 Mar 200856.83000000
UK Solutions01 Apr 2006 To 31 Mar 2007116.07000000
while iam using the cross tab in front end application data view is coming as
country_name
01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008
solution solution
uk 116.07 56.83
Actual o/p should be:
01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 Growth
solution solution
uk 116.07 56.83 -0.51
can anybody tell how to calcualate growth in stored procedure level formala for calculating growth rate
is :
(56.83-116.07)/116.07
([current year]-[previous year])/[previous year]
View 2 Replies
View Related
