In a single table I have 2 columns. Date | Number2006/09/01 - 2352006/09/03 - 2452009/08/01 - 230 I want to write a query that will return the AVG number between two dates. I am assuming this will require a JOIN but I'm having problems implementing my solution. I think about it, it's probably not a join but a sub query...I was trying the following: SELECT Date, AVG(Number) as AVERAGE_NUMBER FROM test.Table WHERE ( Date>='09/01/2006' AND DATE<='09/04/2006' ) But I keep getting date is invalid in the select list because it is not contained in either an aggregate funtion or a group by clause. Thanks in advance for your help.
Hi,We are in the process of buying a new server to run mssql. Howeverbefore this as a tempory fix to using a msaccess backend i believethrough odbc i need to address the following issue:SELECT ai.entry_date as CallTime,ai.agent_login as AgentsLogin,ai.campaign as MarketingCampaign,ai.agent_input2 as ProductsSold,ai.first_name as Cust_FirstName,ai.last_name as Cust_LastName,ai.agent_input1 as Cust_PersonalNumber,ai.street_address as Cust_AddressStreet,ai.city as Cust_AddressCity,ai.state as Cust_AddressState,ai.zip as Cust_AddressZIP,rec.file_name as AgreementRecordingFileFROM agent_input ai, leads l, recordings recWHERE ai.whole_phone_number = l.whole_phone_number ANDl.call_status = 1110 ANDrec.whole_phone_number = l.whole_phone_number ANDrec.last_name = l.last_name ANDrec.agent = ai.agent_login ANDrec.campaign = l.campaign ANDlast_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#ORDER BY ai.agent_login, ai.entry_dateI want to make the recordings entry optional so the same results comeout whether it matches a recording or not. If it does i want it topopulate the AgreementRecordingFile column above, if not just put a ''as you would with '' as AgreementRecordFile.Does anyone know how you can do this, in a access based database systemusing SQL through i believe ODBC?ThanksDavid
declare @AccountID numeric(3,0) set @AccountID = 101 select * from AccountTest WHERE AccountID IN (CASE WHEN @AccountID = 101 THEN (100,101) ELSE @AccountID END)
I m getting error like
Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ','.
While running query below on SQL Server 2005 (Build 3790: Service Pack 2):
SELECT DISTINCT pkg.PrimaryBarcode
FROM dbo.Package AS pkg (NOLOCK)
JOIN dbo.PackageCycle AS pc (NOLOCK)
ON pkg.PackageKey = pc.PackageKey
WHERE (pkg.BillCycleDateKey >= 20061201) OR
(pkg.BillStatusKey = 1)
I received a partial result set followed by
An error occurred while executing batch. Error message is: Couldn't replace text
I suspect this is a memory issue, but cannot find any reference to this particular msg on the Microsoft forums or the other 3rd party forums. PrimaryBarcode is a varchar(50)
I am not sure where to go from here. I would appreciate any ideas. Thanks in advance.
Trying to run a query to get total number of direct mail campaigns by company. Would like the totals to be (1) total number of direct mail campaigns (there are 2 types) (2) total number of direct mail campaigns type direct mail (3) total number of direct mail campaigns type publication
Below is the query I put together with INNER JOIN:
SELECT Agency.PrimaryAgencyName, COUNT(Distinct Campaigns.DirectMailID) AS TotalCampaigns, COUNT(Distinct DirectMail.Type) AS TotalDirectMail
FROM Agency INNER JOIN (Clients INNER JOIN (Campaigns INNER JOIN DirectMail ON DirectMail.DirectMailID = Campaigns.DirectMailID) ON Clients.ClientID = Campaigns.CampaignID) ON Agency.AgencyID = Clients.AgencyID
GROUP BY Agency.PrimaryAgencyName, DirectMail.Month, DirectMail.Type ORDER BY Agency.PrimaryAgencyName
When I run the query, I recent the below error:
Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value '1683 1-15017' to data type int.
The value mentioned in the above error message is the ClientID.
Below are the tables for reference (bold for entity name and underline for PK) and datatypes
I have been working on this for a little bit and have gotten to this point. Below is the query in blue, the error in red. Now, from what I gather the error is telling me I can't insert a duplicate product code into tblProduct. Isn't that what the left join is exactly not doing, as in, the left join is inserting all records from Complete_records into tblProduct where the code is null(does not exist) in tblProduct? Or, is this an issue where the identity number, productID in tblProduct, isn't starting at the next number in turn?
Thanks.
Set Identity_Insert tblProduct on DECLARE @MaxId int
SELECT @MaxID=MAX(productID) FROM tblProduct
SELECT IDENTITY(int,1,1) AS ID,Complete_products.APNum, Complete_products.Title, Complete_products.CategoryID, Complete_products.Mountable, Complete_products.price, Complete_products.Height, Complete_products.Width, Complete_products.IRank, Complete_products.frameable, Complete_products.Typ INTO #Temp FROM Complete_products LEFT OUTER JOIN tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode where tblProduct_1.productCode IS NULL
INSERT INTO tblProduct (productID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType) SELECT @MaxID + ID, APNum, Title, CategoryID, Mountable, price, Height, Width, IRank, frameable, Typ FROM #Temp
(236752 row(s) affected) Msg 2601, Level 14, State 1, Line 13 Cannot insert duplicate key row in object 'dbo.tblProduct' with unique index 'IX_tblProductt_productCode'. The statement has been terminated.
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
I don't know if it's Friday or what, but I can't for the life of me come up with an easy way to do this:
I have 3 tables I want to join:
Sale Table: Sale_No Cus_No Sale_Qty 1 Joe01 250
Order Table: Ord_No Sale_No Order_Qty ShipToCode 1 1 20 DestA 2 1 20 DestA 3 1 20 DestA 4 1 20 DestB 5 1 20 DestB
ShipTo Table:
Cus_No ShipToCode ShipToName Joe01 DestA Philadelphia Joe01 DestB Chicago Bob01 DestA Boston
A sale for say 100 tons would have 5 orders (each for 20 tons) associated with it by Sale_No. Each of those orders can go to a different ShipTo destination. Since only the ShipTo Code is stored in the Orders table, I need to get the ShipToName. However, As demonstrated in the example table above, the key in the ShipTo table is both Cus_No AND ShipToCode.
I want a list of Sales and Orders, which is an inner join on Sale_No, piece of cake. However, I then need to use the ShipTo table to go from the ShipToCode to the ShipToName. Unfortunately, Cus_No is not in the Orders table, it is back in the Sales table (proper normalization is a pain sometimes).
What I came up with is this, but is this correct?:
FROM Sales INNER JOIN Orders ON Sales.sale_no = Orders.sale_no INNER JOIN ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND Sales.cus_no = ShipTo.cus_no
Which is of messages in a mailbox system, the MessageNum is my primary key, MailboxNum indicates which mailbox it is for and State indicates whether it is 'New' (value = 1) or 'Saved' (value = 0).
What I want to do is write a query to obtain a list of mailboxes, along with how many New and how many Saved messages they have, producing a result table like this:
My problem is I cannot seem to get my SQL right, so far I've got:
PHP Code:
SELECT NewQuery.MailboxNum, NewQuery.NewCount, SavedQuery.SavedCount FROM (SELECT MailboxNum, COUNT(1) AS NewCount FROM VMailMessages WHERE (State = '1') GROUP BY MailboxNum) NewQuery FULL OUTER JOIN (SELECT MailboxNum, COUNT(1) AS SavedCount FROM VMailMessages WHERE (State = '0') GROUP BY MailboxNum) SavedQuery ON NewQuery.MailboxNum = SavedQuery.MailboxNum
WHich works only if the mailbox has New messages as well as Saved messages. For mailboxes with only Saved messages, the count appears but, the MailboxNum is NULL. The opposite occurs if I change my SELECT clause to ask for SavedQuery.MailboxNum, but I really want both.
I feel like this is an easy question, but I can't describe it well enough to find the answer I need by searching. Anyway, in my spare time (I'm definitely not a SQL Query pro) I'm putting together a small app for our local Little League to help with scheduling.
I have 2 tables I need to join:
T1 = Schedule has the following fields: ID hTeamID (ID of Home team) vTeamID (ID of Vistor team) Time Date
T2 = Teams ID Name Other info...
I want to be able to do a SELECT statement on the schedule table and JOIN the team names for both home and visitor. I've tried a bunch of different ways but keep getting errors. I can think of 100's of reasons to join the same table more than once, but I still don't know how to and can't find the answer online.
I have listed two tables table 1 has some data. I have to update table 2 [reports] column from table 1 [reports] using self join..I should get as table 2 after updating
I've got a simple ( I think) question on views. I've got a view that has a table join in it. With this view, we want to be able to perform updates, inserts, and deletes. At this time we can do the updates and inserts, but not deletes. I've checked the permissions and the users have SELECT, INSERT, UPDATE, and DELETE. Am I missing something or are deletes just not possible in a view with a join?
CREATE VIEW update_bd_view AS select D.BD_ID, D.BD_DESC, T.BT_TYPE_TID, T.BT_TYPE_FID, T.BT_JOB_FID FROM BILLING_DESC D JOIN BILLING_TIME T ON D.BD_ID=T.BT_ID GO
Please see the attached word file. It has screen shots of the query I am running. My first query shows the result where i have M.ReservationID = MA.MeetingID and it counts NoofRSVP (# of times the query runs). I have to modify first query in such a way that it returns records from eCDReservations table even if there is no matching MeetingID in MeetingAttendees table (means Null, see the result of 2nd query in attached file). So in my result for that case NoofRSVP column should show either Null or 0.
Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...
This simple query is designed to show all members with the same first name as other members. The result I got shows duplicates of existing members an inconsistent number of times even though I specified not to show duplicates with WHERE m1.MemberID <> m2.MemberID
2 Scharenguivil Rodney 1958-06-24 00:00:00.000 2 Scharenguivil Rodney 1958-06-24 00:00:00.000 2 Scharenguivil Rodney 1958-06-24 00:00:00.000 5 O'Grady Patrick 1975-09-23 00:00:00.000 7 Greenfield Lynne 1955-07-26 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000
Any help in explaining where I have gone wrong here would be greatly appreciated.
select pname, cname from parent table a, child_table b where a.pid = b.pid
Except! Instead of getting the results in the form of:
Ben ben_Child1 Ben ben_Child2 Ben ben_Child3 ...
I would like them in
Ben ben_Child1 ben_Child2
Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table. The makeid and orderdate belong to the OrderDetails table. The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.
dear friends, i started using Asp.net for developing webparts, web parts automatically connect to a database to be created and saved in the database. i had already Sql Server 2005 Express Edition installed so my webpart page ran and automatically created the database "AspNetDb" in the "App_Data" folder. but when i uninstalled Sql Server 2005 Express Edition and then installed the Sql Server 2005 Enterprise Edition, it gave me the following Error 26 as below :
--------------------------------------------------------- Beginning of Error Message -------------------------------------------------- An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error: The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist. If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database. Sql Server Express must be installed on the machine. The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts. --------------------------------------------------------- End of Error Message -------------------------------------------------- then when i checked the Machine.config file, i found this.... <connectionStrings> <add name="LocalSqlServer" connectionString="data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" /> </connectionStrings> then i thought it's becoz, of the datasource thing that the Machine.Config file supports by default the default free edition of Sql Server 2005 which is the Sql Server 2005 Express Edition. but friend in my company we use Sql Server 2005 Enterprise Edition, so can anybody pls guide me and get me out of this soup on helping me to use Sql Server 2005 Enterprise Edition by modifying either 1) Machine.Config File and/or 2) Web.Config file and/or 3) any other way, but please help i want to do this, please...... regards and thanks Gurjit Singh
I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected. I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>" SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work. I get a "server tag is not well formed" error.
I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas
Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far: Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris
hi,i have three fields in the database tablep1 p2 rank10% 20% 3 21% 40% 2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh
Hi All Can anyone tell me what this simple SQL query would be: Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"
I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere. That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help. Here's what's going on: I have a "Documents" table. Let's call it DI have a "Benefits" table. Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships. Let's call that table BD. When I create a document it creates a row in D. Let's call that D1, D2...DN. When I create a benefit it creates a row in B. Let's call that B1, B2...BN When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table. Now here's what my challenge is. Suppose I have these entries in my tables: D: D1, D2, D3B: B1, B2, B3 Table BD:B1-D1B2-D1B2-D2 When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1. In this case, I want to show the user D2 and D3 but not D1 since that one's already added. How do I write a SQL query to do this? Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3) SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL) Any thoughts?
I want to write a sql query for an asp page which will display only unique rows from the specified column along with the number of count for each unique row.
Example:
Table that I want to query
Last Name || First Name Gates || Bill Boyce || Mike Gates || Bill Gates || Phil
Results I want:
Last Name || First Name || Count Gates || Bill || 2 Boyce || Mike || 1 Gates || Phil || 1
