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 !!
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?
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?
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.
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
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.
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
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..
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
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
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
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.
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
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
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.
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?
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...
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
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
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.
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?
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'"
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
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]
