I have a table of sales data for example: SELECT ItemCode, ItemDesc, TotalYearlySales, ShareOfBusiness, ABCIndicator FROM Sales WHERE Yir = Year(getdate()) AND Manth = Month(getdate()) ORDER BY TotalYearlySales DESC The ShareOfBusiness is computed as the Item's (TotalYearlySales/SUM(TotalYearlySales))*100 The ABCIndicator is A for th...
I want to improve the performance of this update query because when it take lots of time to execute: Qry = "update tab1 set cDate=GetDate() where right('000000000000'+in_num,12)='" & InvNo.PadLeft(12, "0") & "' and (Total-Amount)<>Balance and cDate is null" ...
If I make a database backup job, where I set Expire day = 2. Run this backup job once every day. How can I delete backup sets that are more than 2 days old? I'm Using SQL 2005 Express, so everything runs as script. This is the script running: BACKUP DATABASE [DatabaseName] TO DISK = N'C:\Temp\DatabaseName.bak' WITH RETAINDAYS = 2, NOF...
I have scheduled a DTS to run from a scheduled job. The DTS has several steps in it. Now whenever the job is running and I take a look at the jobs section in Enterprise manager, then it always displays the following in the status: Executing Job Step 1'.... although its running all steps properly. How do I know at what step the DTS is run...
My classifieds website uses mainly PHP and MySql. On error, (for example if a variable isn't found), I would like to have an error-page to show, is this possible? And I mean for every error to point to the same error-page. I am thinking about htaccess, but maybe there are other ways also? Same with MySql, how is it done there? Thanks...
Whats wrong with this SQL? It should return results but returns nothing SELECT `pid` FROM `products` LEFT JOIN `prods_to_features` ON (`ptf_pid` = `pid`) WHERE ( `ptf_id` = '66' OR `ptf_id` = '67' ) AND (`ptf_id` = '76') Is it not possible to have the 2nd where clause for the table that has been used...
In TOAD (for SQL or Oracle), there is a simple AUTOFORMAT button that will nicely format your query - I couldn't find that option in SSMS 2005, but was advised by a co-worker that it was available in SSMS 2008. I am unable to locate the option there either. This is VERY helpful when pasting a query from another source. Thanks for any ...
Hi, I'm having a problem reading data from a text file into ms sql. I created a text file in my c:\ called data.txt, but for some reason ms sql server cannot find the file. I get the error "Cannot bulk load. The file "c:\data.txt" does not exist." Any ideas? The data file (yes I know the data looks crappy, but in the real world thats ho...
Hi! I'm working on a db but i'm kinda new to this so i've bumped into a problem today. I've got some tables: OFFICE, ROOM, EMPLOYEE and DOCUMENT. Document must specify the sender, which can be a single employee, an entire room or an entire office so it must have a reference to the primary keys of those tables. Should I do a "parallel" ta...
How do you perform a Select top N * from as400table type query against an as400/db2 database ...
I am making a web page which will allow users to input and view data for different dates, and to change the date, there are two buttons, one of which will display the previous day, and one which will show the next day. I know that I can do this by submitting forms and reloading the page every time they press one of these buttons, but I ...
i must get data from four tables. i wrote the script with four queries, but i use it in ajax, and i wan't to do it by one query. here is queries... $query1 = "SELECT `id`,`name_ar` FROM `tour_type` ORDER BY `order`"; $query2 = "SELECT `id`,`name_ar` FROM `hotel_type` ORDER BY `order`"; $query3 = "SELECT `id`,`name_ar` FROM `food_type` O...
We store a decimal(9,8) in our database. It can have any number of places after the decimal point (well, no more than 8). I am frustrated because I want to display it as human-readable text as part of a larger string created on the server. I want as many decimals to the right of the decimal point as are non-zero, for example: 0.05 0....
Just using SQL Server 2000 built in features ONLY, what is the best way to handle special characters. I am not sure if Regular Expression could be used by purely using built in features? I would like to search and replace the special characters in my queries. Thanks ...
I have a database with PermitHolders (PermitNum = PK) and DetailedFacilities of each Permit Holder. In the tblPermitDetails table there are 2 columns PermitNum (foreign Key) FacilityID (integer Foreign Key Lookup to Facility table). A permitee can have 1 - 29 items on their permit, e.i. Permit 50 can have a Boat Dock (FacID 4), a...
First of all, thank you guys. You always know how to direct me when I can't even find the words to explain what the heck I'm trying to do. The default values of the columns on a couple of my tables need to be equal the result of some complicated calculations on other columns in other tables. My first thought is to simply have the column...
Is it possible to get the information why/how given row returned by FTS query was matched (or which substring caused row to match)? For example, consider simpliest table with id and text columns, with FTS index on the later one. SELECT * FROM Example WHERE CONTAINS(text, 'FORMSOF(INFLECTIONAL, jump)'); This examplary query could retu...
This is part of my TV guide script: //Connect to the database mysql_connect("localhost","root","PASSWORD"); //Select DB mysql_select_db("mytvguide"); //Select only results for today and future $result = mysql_query("SELECT programme, channel, episode, airdate, expiration, setreminder FROM mediumonair where airdate...
I have a MVC web application with a table in the model that I would like to add to. I have the primary key set along with the other data fields, but every time I try to add to the table, I get the following error: "Cannot insert explicit value for identity column in table 'TABLE_NAME' when IDENTITY_INSERT is set to OFF." I'm not sure ...
help me please with a query. Assume that we have a table with columns: Transaction StartTime EndTime Now, I need a query with computed column of (value = EndTime-Startime). Actually I need to group Users(Transaction has a FK for Users) and sort them by average time spent for transaction. ...