Total Replacing
Jul 20, 2005
Hi,
Is it possible to do from one script? We have a set of user's tables like
"tbl%". We can get this list very easy using this script:
SELECT name FROM sysobjects WHERE xtype = 'U' AND name LIKE 'tbl%' ORDER BY
name;
We need to change some column names if these names are in a special list
that we have. What can we do? Use FOR EACH ROW? Or what?
So, I need to get a column list for each table and check if every column
name is equal to one of the names from the list and then if YES replace it
by something or add some symbol to this name. Terrible or possible?
Regards,
Dmitri
View 4 Replies
ADVERTISEMENT
Jun 9, 2004
Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.
TIA
View 5 Replies
View Related
Feb 26, 2014
Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....
My code is as follows;
Insert Into [dbo].[CombinedTripTotalsDaily]
(
Year,
Month,
Week,
DayNo,
Day,
Trip_Date,
[Code] .....
View 3 Replies
View Related
Jul 20, 2005
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
View 4 Replies
View Related
Oct 26, 2015
For some reason my Add Total is grey out, when i tried to add grand total using some expression.
I have two row & two column groups?
Is there any alternative or how can i enable add total? using expression..as you can see in my Attached Image
I'm using iff condition in my expression..Â
View 15 Replies
View Related
Jun 28, 2015
I have a table that writes daily sales each night but it adds the day's sales to the cumulative total for the month. I need to pull the difference of todays cumulative total less yesterdays. So when my total for today is 30,000 and yesterday's is 28,800, my sales for today would be 1,200. I want to write this to a new field but I just can't seen to get the net sales for the day. Here is some sample data. For daily sales for 6-24 I want to see 2,000, for 6-25 3,000, 6-26 3,500, and 6-27 3,500. I'm thinking a case when but can't seem to get it right.
CREATE TABLE sales
(date_created date,
sales decimal (19,2))
INSERT INTO sales (date_created, sales)
VALUES ('6-23-15', '20000.00'),
('6-24-15', '22000.00'),
('6-25-15', '25000.00'),
('6-26-15', '28500.00'),
('6-27-15', '32000.00')
View 9 Replies
View Related
Jul 26, 2013
My table contains 1000 records,
I need to know the total record count with the below paging query
SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
How to get?
View 2 Replies
View Related
Apr 28, 2006
Hi, can anyone help?
I have created a Report using Visual studio-the report displays a subreport within it.
On the Subjective Report I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value), and I
have named this text box €™SubRepM1€™
The name of the subreport is €˜subreport1'.
On my Main Report, again I have 12 values for each month of the year.
For the first month the value is =sum(Fields! Month_1.Value)*-1, and I
have named this text box 'MainRepM1'
The name of the main report is 'GMSHA Budget Adjustment Differentials'
The report displays both of the subreport and main report values
but I now need to total these values together for each month in order to
produce a grand total.
I have tried using the following to add the totals for Month 1 together,
=subreport1.Report.SubRepM1 + MainRepM1
but this does not work and I get the following error message €˜The value expression for the text box 'textbox18'contains an error [BC30451] Name subreport1 is not declared'.
I feel that it should be a simple matter of adding the two sets of values together but I€™m having major problems trying to get these totals to work.
Can anyone help, thanks
View 7 Replies
View Related
Jul 20, 2005
Anyone has a "one sql statement" to get the total spaceused and totalspace allocated of an instance ? ie same as sum of relevance fieldsfrom sp_spaceused for each database in an instance, that works accrossversion of mssql from 6 onward.ThanksKD
View 1 Replies
View Related
Aug 10, 2006
Hi all,
I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .
I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.
I kindly request for help.
Thanx in advance.
Ronnie
View 4 Replies
View Related
Feb 29, 2008
Hi,
I am trying to create a report on some data. I have about 8 tables and 30+ queries attached to those 15 reports. In one of those reports I want to get the percentage based on the data in the tables and queries. Say I have the minimum hours for an employee as 176 hours and the employee works for 227 hours in a month. I want to see the result in percentage.
My report looks something like this :
ID Name Oct Nov Dec Jan Feb March Total
001 alex 87.6% 104.1% 65.1% 50.2% 85.6%
002 Linda 87.4% 109.1% 68.1% 35.2% 90.8%
003 Jon 87.6% 104.1%
004 alex 87.6% 104.1% 65.1% 50.2% 85.6%
005 Linda 87.4% 109.1% 68.1% 35.2% 90.8%
For the 002 ID, though he has worked for Nov and Dec the total % is blank.
The formula that I used for all of these entries is :
=IIf(IsError([total]),"",[total])
and for the month it is : =IIf(IsError([Oct]),"",[oct]), nov and so on.
It works fine for all, but where ever there is blank in one field it doesn;t calculates for the others too..
Please help, how can I get the total for all.
Thanks,
Farn
View 1 Replies
View Related
Aug 28, 2000
Hi, I'm trying to conver an Access database to SQL 7, but I can't find a easy way to replicate the aggregate function FIRST in SQL, can anyone give me some advise on how to "fake" the FIRST agg function in SQL7? Thanks
View 1 Replies
View Related
Aug 13, 2004
I am incorporating a perl script loading data into my SQL Server. If I receive a message with a single backslash I know to replace it with a double backslash \. But what if it is a " double quote what do I need to do to get it to appear as is?
Thanks
View 2 Replies
View Related
Nov 20, 2013
I want to replace the value in a column with the content from listbox.
I have wrote the code like,
Dim i as Integer
dim sql as string
For i = 0 to Customerslist1.ListCount-1
sql = "UPDATE master (gstl) VALUES(" +chrb(34)+Customerslist1.Cell(i,8)+chrb(34)+")"
app.Gudangstock.SQLExecute(sql)
app.Gudangstock.commit
next
msgbox"insert ok"
UpdateCustomerList1
but no error found and the sql command is not executing.
View 5 Replies
View Related
Aug 31, 2005
Nevil Mascarenhas writes "Hi SQL team,
I am just a beginer in SQL
Here is sample output of a SQL query
Alarm No Site Name Startdate Starttime
7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD
5453 QWE 22-08-05 01:00
In this above example, I would like to replace blank fields in Startdate and Starttime coloumn with XXX
Output required is
Alarm No Site Name Startdate Starttime
7767 ABC 20-08-05 00:00
7765 XYZ 20-08-05 00:00
7762 ASD XXX XXX
5453 QWE 22-08-05 01:00
Can you please guide me."
View 1 Replies
View Related
Nov 18, 2005
Hi i have 2 cols
col1 col2
A1 21
A2 22
A3 21
A4 23
I want to create a report such that i should see all values of col1 but in col2 i want to show only value of 21 and the remaining should be zero.
So basically all the other values except 21 should be zero in the report but should not be updated in the database.
So the report should look like
col1 col2
A1 21
A2 0
A3 21
A4 0
can you please tell me how to do that
Thanks
vic
Vicky
View 10 Replies
View Related
Jul 30, 2007
Hi
I have a table with column type as ntext. I need to modify the column value. I wanted to replace a given characterstring with another one in this column. Any assistance on this is highly appreciated.
Thanks!
Santhosh
View 1 Replies
View Related
Jan 15, 2008
Hi all,
We are trying to migrate a SQL Server 2005 database/schema onto SQL CE. The original SQL Server 2005 schema uses views. And VIEWs are not supported on SQL CE. What can we use as an alternative?
Here are the option I can think of:
Use Tables instead of views : We are currently using this approach. But the disadvantage is that data remains on the DB. We need to clean and reuse the table everytimeCan we use a DataSet or DataTable instead? Any sample code available?
Can we replace them by some in-memory data structure?
View 3 Replies
View Related
Nov 24, 2006
Hi all
I need to remove all the ' in a table and i am having some problems with the script, any help would be great
Thanks
Richard
View 1 Replies
View Related
Jan 21, 2008
Hi,
I would like to replace all instances of a code that starts with 'GEM' with the word 'Perfect'', and all instances of the code that starts with 'GOLD' replaced with 'Imperfect'.
For example, if I have a table named STONES, and a field within the table STONES.Code contained codes such as 'GEMPART4000', 'GEMPART5000', 'GOLDSIDE2300' and 'GOLDSIDE3000', I want to return the first two codes as the words 'Perfect' for each and the last two codes with the word 'Imperfect' for each.
Similarly, how would it be written so I could select a middle part of the code to use as the trigger to replace the code with something different, e.g. if I wanted to use the 'PART' from the first two codes?
I have discovered that the REPLACE and CASE functions don't work with % (so I am looking for any code that starts with what I stipulate but can end with anything (or using it on either side of the middle part of the code)).
Any help would really be appreciated please.
View 5 Replies
View Related
Dec 3, 2007
Hi Guys,
I am faced with a problem that is giving me headaches. I have t1, t2 and t3. I t1 I have students that have a reference to t2 which are the schools they belong to. The problem arises when I see that there is redundancy in t2. There more records for the same schools. This was posible in giving the same school (postcode and name make it the same school) a different ID. In t3 I removed these redundant schools by using fuzzy grouping.
My problem is I want to ensure that the students are put using the schools from t3 instead of t2. So what I need to do is is to replace the redundant id from t2 with the correct id from t3.
Please help. Thans in advance
View 3 Replies
View Related
Jul 20, 2007
I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other.
My tables are like this:
ITEMSitemID,itemName,itemDescriptionetc...
ORDERITEMSOrderLineID,OrderID,itemID
ORDERSOrderID,OrderCompleted
Currently my query looks something like this:
Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1))
So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table. As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table. Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join?
Thanks for reading!
Ryan
View 7 Replies
View Related
Aug 2, 2007
Hi, below is the query that i use.update user set user_description=' "+ userDesc +" ' where user_id = 1;userDesc is the value taken from a textbox and is supposed to be used with the update query.userDesc = "kad'nsad'kasnd'nak";The quotes in userDesc is affecting the update. Is there anyway to replace quotes with ' using stringbuilder?
View 1 Replies
View Related
Apr 1, 2004
Hi, I have the following query.
sql = "select firstname AS Expr1, lastname AS Expr2, status AS Expr3 from person order by lastname"
Status is either 0,1,2 or 3
How can I use the query to create "a temp Alias" for the query, so that there is a "temp Alias" AS Expr4, AS Expr5, AS Expr6 and AS Expr7.
So if status = 0 then Expr4 = "true" else false
So if status = 1 then Expr5 = "true" else false
So if status = 2 then Expr6 = "true" else false
So if status = 3 then Expr7 = "true" else false
So when the reader reads Expr4 its either true or false.
Is this possible i a query?
View 1 Replies
View Related
Dec 24, 2005
In an *existing* database, how would you remove the City column in the
Employees table, and put in the CityID key column from the Cities table
?
View 2 Replies
View Related
Jan 10, 2002
Hi,
how can I remove newline characters and tabs from a char field in sql.
We are directing the results from a query to an excel and having the newlines in one of the columns is messing up the whole output.
thanks
Zoey
View 1 Replies
View Related
Mar 1, 2001
I am thrawn at the deep end
I have been given the task of moving certain tables (that have PK, FK Constraints) from an existing Database (A) to form another Database (B) but making sure all the references to other tables in the original DB (A) are not affected. I was talled that triggers can solve this but I am not sure where to start. The Question is what I am looking for to replace, and how ??
Please help !!!!!!
Saad
View 3 Replies
View Related
Sep 4, 2005
Need to update data from one table with data from another table.
I'm very, very new to MS SQL Server
I have the following two tables.
First table
tblEmp
Field - empno
Second table
tbltemp
Field - oldempno
Field - newempno
tblemp.empno = tbltemp.oldemno
I need to replace the data in tblEmp.empno with tbltemp.newempno where tblemp.empno = tbltemp.oldempo.
Thank you.
View 1 Replies
View Related
Jun 26, 2001
Hi, and tanks for taking time to read this question. I am still a novice at SQL (Server 2000)programming.
Is there a way to replace the contents of a column of a table derivied from SELECT and WHERE statements?
For example,
SELECT * FROM table1
WHERE column1 = 'text'
and replace 'text' with say, 'newtext'
Any help would be appreciated!
Regards,
Chandran
View 5 Replies
View Related
Dec 1, 2004
I need to change the first digit (0) in a number, like 05678494 and replace it with 0046 so it would look like 00465678494 when it is done.
I have tried to use the REPLACE function without any success.
Can anyone help me?
View 4 Replies
View Related
Sep 18, 2007
I have a instance with many databases in it.
due to company/product name change,
I want to search for a string "xyz" in
database name,
table name,
column name,
stored procedure name
content of all stored procedures
and replace all of them with "abc" without affecting the databases an application.
Can u please help me with step by step guidance?
muralidaran r
View 14 Replies
View Related
Dec 2, 2013
I have a SQL table with 5000 rows in it. Some of the other SQL columns have different values but if I wanted to swap say 500 of the rows would it be something like
UPDATE Coupins
SET Name='test1,test2,test3'
WHERE Name='test4,test5,test6';
I need basically to swap the Name value of these without affecting any other values.
View 4 Replies
View Related
Feb 11, 2014
I've got a sproc that we are migrating from SS2K that uses the reserved keyword 'Pivot'. Best way to replace it. I think it is renameable until the select from TempDB (which is mysterious as I can't see a table in tempDB by that name!!) as below...
CREATE PROCEDURE [dbo].[sp_CreateXtab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100),
@SQLOutput varchar(8000) OUTPUT
[code]....
View 3 Replies
View Related
