sql

what does 'legend' mean in sql?

im doing some practice sql problems one of the questions is: Prepare a shipping statement for customer 'C001' it should show the date, the legend 'Delivery' and the total value of the products shipped on each day. 'delivery' is not a table nor is it a column in any table. what does legend mean?? ...

how do i get the sum of the 4th column

this statement will generate a 4 column table: SELECT shipped.badguy AS badguy, shipped.sdate AS LineDate, 'Delivery' AS Legend, -price*quantity AS amount FROM product JOIN shipped ON (product.id = shipped.product) UNION SELECT receipt.badguy, receipt.rdate,notes, amount FROM receipt how do i get the total sum of the 4...

sql: self-joins explained

i dont understand the need for self-joins. can someone please explain them to me? a simple example would be very helpful ...

What are your best practices for ensuring the correctness of the reports from SQL?

Part of my work involves creating reports and data from SQL Server to be used as information for decision. The majority of the data is aggregated, like inventory, sales and costs totals from departments, and other dimensions. When I am creating the reports, and more specifically, I am developing the SELECTs to extract the aggregated dat...

Early (or re-ordered) re-use of derived columns in a query - is this valid ANSI SQL?

Is this valid ANSI SQL?: SELECT 1 AS X ,2 * X AS Y ,3 * Y AS Z Because Teradata (12) can do this, as well as this (yes, crazy isn't it): SELECT 3 * Y AS Z ,2 * X AS Y ,1 AS X But SQL Server 2005 requires something like this: SELECT X ,Y ,3 * Y AS Z FROM ( SELECT X ...

MySQL: Limit output according to associated ID

So here's my situation. I have a books table and authors table. An author can have many books... In my authors page view, the user (logged in) can click an author in a tabled row and be directed to a page displaying the author's books (collected like this URI format: viewauthorbooks.php?author_id=23), very straight forward... However, i...

SQL Function that calculates Shift from StartTime and EndTime

Hello Folks I have been trying to get a function going that calculates what Shift the Employees worked from their StartTime and EndTime. Here is the code i have so far, there seems to be calculating the shift wrong. Shift 1 from 08:00:00 - 16:30:00 Shift 2 from 16:00:00 - 00:30:00 Shift 3 from 00:00:00 - 08:30:00 Also the shift with ...

Calendar Table - Week number of month

I have a calendar table with data from year 2000 to 2012 (2012 wasn't intentional!). I just realize that I don't have the week number of month (e.g In January 1,2,3,4 February 1,2,3,4) How do I go about calculating the week numbers in a month to fill this table? Here is the table schema CREATE TABLE [TCalendar] ( [TimeKey] [int] N...

How do I get 5 records before AND after a record with a specific ID?

I have a table named scores with the columns id and score. I want to access a specific record by its id as well as the 5 records before and after it. Is there a way in SQL to say "grab the score with the id of n and x items before and after it?" ...

Help with SQL Join on two tables

I have two tables, one is a table of forum threads. It has a last post date column. Another table has PostID, UserId, and DateViewed. I want to join these tables so I can compare DateViewed and LastPostDate for the current user. However, if they have never viewed the thread, there will not be a row in the 2nd table. This seems easy bu...

I've got to update a column in one SQL table with a counter stored in another table, and update that table too

I'm using SQL server 2005 (for testing) & 2007 (for production). I have to add a unique record ID to all the records in my table, in an existing column, using a "last record ID" column from another table. So, I'm going to do some sort of UPDATE of my table, but I have to get the "last record ID" from the other table, increment it, upd...

How can I use two or more COUNT()s in one SELECT statament?

i develop this code: SELECT COUNT(NewEmployee.EmployeeID), NewEmployee.EmployeeId,EmployeeName FROM NewEmployee INNER JOIN NewTimeAttendance ON NewEmployee.EmployeeID = NewTimeAttendance.EmployeeID and NewTimeAttendance.TotalTime is null and (NewTimeAttendance.note = '' or NewTimeAttendance.note is null) and (month = 1 or ...

Running a Query in SQL Plus

Hi, I have to run a query in the loop in SQLPLUS. and the count of loop is coming from some other SQL query. So i have to declared a variable which will take the value of count. Now I want to use this variable in my query. How would i be able to do the same . Please suggest me Thanks in advance ...

jdbc query - date ranges as parameters

Hi all, I'd like to write a single JDBC statement that can handle the equivalent of any number of NOT BETWEEN date1 AND date2 where clauses. By single query, i mean that the same SQL string will be used to create the JDBC statements and then have different parameters supplied. This is so that the underlying frameworks can efficiently...

issue with selecting 1 or 0 in mysql db

I am having trouble with the following SQL statement. I have had this issue before but I can't remember how I fixed the problem. I am guessing the issue with this is that MySQL sees 0 as null? Note I didn't show the first part of the Select statement as it's irrelevant. My SQL works. It's showing rows with toffline that are = to 1 as wel...

Can anyone explain me about this SQL Query

Select Null as Empty from (select * from TblMetaData) ...

How to get some randomized concats based on 2 columns from 1 table?

Hey folks, i have a large user Database (13k+), and for some reason i need to create random names. The users table has "first_name" and "last_name". Now i want to have 10 concats of full_name and last_name of two completely random rows. Is that even possible with SQL? My other idea was just to create a full_names and last_names table …...

What is Ad Hoc Query?

Recently, I'm reading a book about SQL. In that book, I can't understand this term - Ad Hoc Query. I want to know what is exactly means "Ad Hoc Query" Help me, please. Thanks in advance. ...

SSIS oledb destination property OpenRowSet

What is significance of OpenRowSet property of oledb destination control in SSIS? My SQL admin has declared a new policy in which he says "Applications which use OpenRowset queries will not function." so I'm concerned about this whether my packages will work or not. Please advice. ...

How to store data from SQL query

Hi, I want to store the data retrieved from a SQL query running in a loop to a file in SQL PLUS. I have used spool for this but didn't get the solution. Please suggest me the solution. Thanks ...