Different Result Same Sql Queries
Sep 26, 2006
I wrote 2 sql queries in two different ways.
The only difference in both queries are One using '=' and other one using 'in'.
All the other data is same.In my sense '=' and 'in' same
Both queries should give same result.
But one query giving different result and other query giving different result.
Is this 2 queries different??
SELECT sum(SALES_ITEM.NET_PRICE)
FROM PRODUCT_GROUP_LINK,
PRODUCT,
SALES_ITEM
WHERE (PRODUCT_GROUP_LINK.PRODUCT_CODE=PRODUCT.PRODUCT_CODE)
AND (PRODUCT_GROUP_LINK.PRODUCT_CODE=SALES_ITEM.ORIG_PRODUCT)
AND PRODUCT.STYLE_CODE='MC001'
AND (SALES_ITEM.PRICE_LIST='1FAC'
OR SALES_ITEM.PRICE_LIST='1FAW' )
AND CLASS_CODE='TYPE'
group by STYLE_CODE
SELECT sum(sales_item.net_price)
from PRODUCT,
product_group_link,
SALES_ITEM
WHERE (PRODUCT_GROUP_LINK.PRODUCT_CODE=PRODUCT.PRODUCT_CODE)
AND (PRODUCT_GROUP_LINK.PRODUCT_CODE=SALES_ITEM.ORIG_PRODUCT)
AND PRODUCT.STYLE_CODE='MC001'
AND sales_item.PRICE_LIST IN ('1FAC','1FAW')
AND CLASS_CODE='TYPE'
group by style_code
Any idea?
Thanks
View 5 Replies
ADVERTISEMENT
Jun 18, 2008
I have 3 sql queries:ex:select * from table 1 where id = 2select * from table 1 where name = 'name'select * from table 1 where date = 'date' I want to combine these three queries into one stored procedure.I am not sure how to do this.i want to display some column data from these 3 queries on 3 table rows as:<td> colum1 </td><td> colum2 </td><td> colum3 </td>so my SP should return some datatable .any suggestiions
View 3 Replies
View Related
Apr 20, 2004
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ???
Please could someone out there offer some ideas ?
THANKS
JON
View 4 Replies
View Related
Apr 20, 2004
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ???
Please could someone out there offer some ideas ?
THANKS
JON
View 1 Replies
View Related
Apr 20, 2004
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 .... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ???
Please could someone out there offer some ideas ?
THANKS
JON
View 4 Replies
View Related
Apr 22, 2004
Hello guys,
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
Has anybody suffered this problem and what solution was adopted?
Thanks for any information you can provide.
View 5 Replies
View Related
May 4, 2015
Required Output
Country Category
Internet Sales Amount
Internet Order Count
[code]....
I need to perform a SQL kind of Cross join to get the Total Count as a column along side as there are .net code and json structures defined that expects output in a certain format.
View 2 Replies
View Related
Mar 5, 2012
Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.
I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.
I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common.
View 6 Replies
View Related
Jan 9, 2014
I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).
I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..
Here is query #1:
select investor, full_date, month_end_summary, category, loan_count
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) < 6
group by investor, full_date, month_end_summary, category, loan_count
order by investor, full_date
Here is query #2:
select investor, full_date, month_end_summary
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) =0
order by investor, full_date
Can they be combined into one query to produce one result table??
View 3 Replies
View Related
Mar 7, 2006
Hi AllStrange request I know, but could somebody give me pointers on how I can put3 queries into 1 'thing' and then get only the unique entries from this'thing'.To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queriesthemselves aren't that complex and all return the same 2 fieldsets of stockcode and stock desc. Because these separate queries might bring back thesame stock code/description I need to amalgamate the data and then queryagain to bring out only distinct stock values, eg:Query 1 brings back:stock code stock descIVP Invoice PaperSTP Statement PaperKGC Keyboard Coveretc... etc...Query 2 brings back:stock code stock descIVP Invoice PaperBOB Back PackKGC Keyboard Coveretc... etc...Query 3 brings back:stock code stock descKGC Keyboard Cover3.5"D 3.5" Disksetc... etc...I need to produce 1 resultset that shows:stock code stock descIVP Invoice PaperBOB Back Pack3.5"D 3.5" DisksKGC Keyboard CoverSTP Statement Paperetc... etc...(all unique entries)I'm currently just bringing back the 3 query results in Excel, but I'd liketo be able to do the above.In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?ThanksRobbie
View 1 Replies
View Related
Jul 20, 2005
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies
View Related
Dec 26, 2007
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
View 5 Replies
View Related
Dec 11, 2007
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
Feb 13, 2001
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
View 1 Replies
View Related
May 1, 2008
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
View 3 Replies
View Related
Aug 2, 2007
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
View 3 Replies
View Related
Apr 7, 2015
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday
select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],
[code]....
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
View 6 Replies
View Related
Mar 2, 2008
help strange result whan i do this
Code Snippet
SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit
FROM dbo.empList
i try to get next unit value to next month
why i get this -1
on date
01/01/2008
1
-1
unit_date unit new_unit
01/02/2008
2
1
01/02/2008
1
4
01/01/2008
1
-1
01/02/2008
1
4
21/01/2008
1
-1
21/01/2008
1
-1
01/02/2008
1
4
TNX
View 3 Replies
View Related
Jul 20, 2005
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies
View Related
Aug 9, 2006
Hi,
I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.
i want to write my own query without help of sqlDataSource control from start something like
sqldatasource con=new sqldatasource;
con.connection String=""
then what all things will come........ ???
and please give me some poitners to some articles which help one to do the requested.
Regards,
View 1 Replies
View Related
Dec 7, 2007
I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this. previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants.
View 4 Replies
View Related
Dec 18, 2007
how can i execute for example
2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product if i execute such one how can i get the 2 results in vb/c# ?
View 3 Replies
View Related
Jun 18, 2004
hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the code
View 3 Replies
View Related
Oct 21, 2004
Hi All,
Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.
All I have is a normal query (below). Can anyone help me please?
SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;
Regards,
Rich
View 2 Replies
View Related
Jan 23, 2006
Hi,I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.In Book table, BookID, BookTitle, CategoryIDIn Category table, CategoryID, CategoryNameIn Subject table, SubjectID, SubjectName, CategoryIDIn UserDownload table, UserID, BookIDIn User table, UserID, UserNameI used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.When user downloads book, I update UserDownload table.The result I want to get is, Top Ten Download Subject. How can I get? Please help me.
View 1 Replies
View Related
Jul 16, 2002
Could anyone help me?I need all the commands like select,from etc.Basically I need to learn writing queries etc.Please
View 1 Replies
View Related
Aug 3, 2000
I just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?
Thank you all very much in advance.
View 5 Replies
View Related
Feb 7, 2003
Hi!
Where to write MDX Queries and how to call the query in Analysis Services.
Please help me on this.
View 2 Replies
View Related
May 1, 2001
I'd like to know how (if it's possible) to write a TRIGGER in SQL that will, when a limit has been reached, remove an entry from a table...
Any ideas? Thanks.
View 1 Replies
View Related
Oct 12, 2007
Hi and thanks for reading.
I have a query that requires multiple joins and has several one-to-many relationships with other tables. The basic query I have is this:
Code:
SELECT TOP 50 eventID,eventTitle,eventAbstract,eventInsertDate,eventUpdateDate,eventAuthID_fk,
eo.eventOccurrenceID,eo.eoDescription,eo.eoApprovedDate,
l.LocationID,l.LocationShortName,c.categoryID,c.categoryName,a.ageID,a.ageDescription
FROM enm_eventOccurrence eo INNER JOIN enm_events e ON e.eventID=eo.eoEventID_fk
LEFT JOIN (enm_categories_occurrence co LEFT JOIN enm_categories c ON c.categoryID=co.co_categoryID_fk) ON eo.eventOccurrenceID=co.co_eventOccurrenceID_fk
LEFT JOIN (enm_location_occurrence lo LEFT JOIN location l ON lo.lo_locationID_fk=l.LocationID) ON lo.lo_eventOccurrenceID_fk=eo.eventOccurrenceID
LEFT JOIN (enm_ages_occurrence ao LEFT JOIN enm_ages a ON ao.ao_ageID_fk=a.ageID) ON ao.ao_eventOccurrenceID_fk=eo.eventOccurrenceID
This works, but it will pull out multiple rows for the same eventOccurrenceID (the PK for eventOccurrence) because of the several one-to-many relationships. For instance, there could be multiple categories, locations and ages for one event occurrence, so this will produce multiple rows.
But, since I need to get only a limited of rows, the TOP will fail since there are multiple rows for each, and there's no way to tell how many rows exactly will come out (so just multiplying the TOP value is out of the question).
Using DISTINCT or GROUP BY also seems to be out of the question, since I need to have the fields from the one-to-many tables in the SELECT list but cannot GROUP BY them.
My proposed solution is to run a query selecting only the eventOccurrenceID and using the WHERE information that the user is requesting, then running another query (with the full select list) with a "WHERE eventOccurrenceID IN (".
But, I hate using two queries and just wonder if there's a better way around this.
Appreciate the help, and thanks for reading.
-colin
View 1 Replies
View Related
Feb 15, 2007
I frequently need to run ad hoc queries against a database, capturing the output in a text file. In SQL Server 2000 I used Query Analyzer with no problems. In 2005 I'm trying to use the query function in Management Studio-but the output is (so far) unusable due to a Null character (hex 00) that follows every 'real' character. I assume this is due to Unicode but haven't found any way (yet) to tell it to just output in plain everyday text. (ASCII or ANSI-either will work.)
Is there some other tool I can use for SQL queries?
Thanks.
View 5 Replies
View Related
Jul 2, 2007
It is given the following schema of relations:
Donators(Id <<PK>>, Name, Surname, IdCity <<FK(City)>>, Group, Sex, Age);
Files(IdFile <<PK>>, Donator <<FK(Donators)>>, Date, Type);
City(IdCity <<PK>>, Name, State);
I need to find a query for each of the following:
1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.
My solution:
SELECT d.Name, d.Surname
FROM Donators d, Filles f
WHERE d.Id=d.IdFile AND f.type='plasma' AND d.sex='F' AND d.age<45
2. average age of the plasma donators
SELECT AVG(d.age)
FROM Donators d, Files f
WHERE f.type='plasma'
3. the states which have more donators
I have no idea how to do it
4. the id's of the plasma donators from Chicago
SELECT d.id
FROM Donators d, Files f, City c
WHERE d.id=f.idfile AND d.idcity=c.idcity AND f.type='plasma' AND c.name='Chicago'
5. the id's of the cities without plasma donators
SELECT c.idcity
FROM City c
WHERE NOT IN (SELECT c.idcity
FROM City c, Donators d, Files f
WHERE f.idfile=d.id AND f.type='plasma' AND d.idcity=c.idcity)
6. remove the donators older than 80, after having removed the corresponding files
No idea how to do it
In conclusion, could you tell me if my queries are correct and help me with the others?
Thanks
View 14 Replies
View Related
Jun 26, 2012
Table 'dbo.tblonenet_sdt_proj_hold' Schema as follows:
cust_id int
dateon DATETIME
dateoff DATETIME
There are two possibilites
1) Its no longer on hold and so a dateon and dateoff value will be in the table.
2) Its still on hold and so dateoff will be NULL.
I want to add the total time its been on hold between the two dates and there could be multiple entires for cust_id 802
SELECT Hon,Hoff FROM
(SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate()))
FROM dbo.tblonenet_sdt_proj_hold
WHERE cust_id='802' AND dateoff IS NULL) AS HON
[code]...
The resulting error in SQL Management Studio 2005 is 'No column was specified for column 1 of 'HON'.'
View 8 Replies
View Related