Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
I have to perform a weighted search. I have 2 criteria and each will be weighted on a 100 sum(e.g 25/75, 50/50). I am just wondering if there is an easy way to encompass a weighted value on SELECTS. Sorry if this is a dumb question. Thanks,Kyle
Hi there... I've got an interesting one, that I can't seem to get my head around. Maybe some legend out there might be able to give me a hand...
I'm looking for a way to produce a weighted set of random numbers. I'm doing some work for a client at the moment, and they want to issue 3 random "reward cards" to their members at certain times. These are a bit like discount vouchers etc. The problem is some cards have need to have a higher frequency than the others. I guess a similar problem to baseball cards, you buy a pack of cards, you get mostly common cards, but every now and then, you get a rare card.
Here is the table setup: CREATE TABLE [dbo].[Cards]( [CardID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Cards_CardID] DEFAULT (newid()), [CardName] [nvarchar](50) NOT NULL, [InsertRatio] [float] NULL, CONSTRAINT [PK_Cards] PRIMARY KEY CLUSTERED ( [CardID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 1', NULL) /* Null implies the card is a common card */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 2', NULL) /* Null implies the card is a common card */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 3', NULL) /* Null implies the card is a common card */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 4', NULL) /* Null implies the card is a common card */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 5', NULL) /* Null implies the card is a common card */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 6', NULL) /* Null implies the card is a common card */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 7', NULL) /* Null implies the card is a common card */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 8', NULL) /* Null implies the card is a common card */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 9', NULL) /* Null implies the card is a common card */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 1', 0.02) /* 1:50 ratio */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 2', 0.02) /* 1:50 ratio */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 3', 0.02) /* 1:50 ratio */ INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Very Rare 1', 0.005) /* 1:200 ratio */
So what I need to do, is have a Stored Proc that I can execute and it returns back 3 random rows. Now in that single run, a card can't be duplicated.
Notice the Insert Ratio column? This has the ratio of the probability, eg a 1:50 insert ratio is equal to 0.02. For the common cards, a NULL value indicates it is a common.
Eventually, this table would have about 1000 rows in it, and about 200 of those would have various ratios (eg 1:50, 1:200, 1:1000, 1:8000 etc)
The select command below will output one patient’s information in 1 row:
Patient id Last name First name Address 1 OP Coverage Plan 1 OP Policy # 1 OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1, max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1, max(case when rn = 1 then src.policy_id end) as OP_Policy1,
SQL Server 2005: Anyone know how best to rewrite this SQL string to perform a weighted search on my table? here's my code: SQL="SELECT RecipeName FROM recipeList WHERE FREETEXT(*,'ISABOUT " & ing01 & " WEIGHT (.1) or " & ing02 & " WEIGHT (.2) or " & ing03 & " WEIGHT (.3) or " & ing04 & " WEIGHT (.4) or " & ing05 & " WEIGHT (.5)')" the idea is to get a list of recipes most pertinant to the ingredients entered. table 'recipeList' contains the ingredients in several columns. so column 2 might say '5oz of flour', column 2 - '2oz butter' etc.
at the moment it returns the correct recipes but not in the right order. If I use 'CONTAINS' it returns nothing. hope that's not too vague!
Hi,Does anyone know of a script that will give "weighted job duration"?I want to use it, to identify which jobs are hogging the CPU. That isfor a given server, list the sql agent jobs ordered by:(avg job duration in minutes) times (avg num of times job runs in agiven day).
I have a question about writing a prediction query against a clustering model that has the same column added more than once.
Per Jamie, I can accomplish some crude weighting by adding a column to my model multiple times. See this post for an explnation... Now that I have that worked out, I was wondering how my DM query would look? If I have Input_A1, Input_A2 , & Input_A3 all being source from the same column in my structure do I have to reference all three when writing my prediction query?
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
I have a table in `PowerPivot` which contains the logged data of a traffic control camera mounted on a road. This table is filled the velocity and the number of vehicles that pass this camera during a specific time(e.g. 14:10 - 15:25). Now I want to know that how can I get the average velocity of cars for an specific hour and list them in a separate table with 24 rows(hour 0 - 23) where the second column of each row is the weighted average velocity of that hour?
In a separate `PowerPivot` table I have 24 rows and 2 columns(column1 is for hours and column 2 for weighted averages) and when I enter my formula, the whole rows get updated with the same number. My formula is:
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
What is the difference from performance point of view, when you select from 3 different tables to show fieldnames across the 3 tables based on one common key, OR using Join between the 3 tables.
What I want to do is SUM(Cost) for the Transportation and the Optional Charges table. If I do a normal INNER JOIN, with the SUM for Cost on the table Transportation and Optional Charges, it will SUM each twice. For example if Transportation has three rows with a cost of 10, 20, 30, it will SUM a total of 120 instead of 60.
SELECT QuoteID, SUM(COST) AS TransportationTotalCost FROM Quote.Transportation GROUP BY QuoteID ) t on Quote.QuoteID = t.QuoteID WHERE Quote.QuoteID = 135
Which gives me the total for the Transportation Table, but how do I go about adding in the Optional Charges Table?
My SQL is a little rusty, and I need a little help for a client that I'm helping.
I'm simply trying to do create the following output:
HEADINGS: Sale Order | Customer Number | Customer Name | # of Pallets | (etc) DATA: 456188 | 12355890 | Acme Customer | 4 | other stuff I have figured out that is irrelevant
This data comes from 3 tables: 1) SOE_HEADER SALE_ORDER_NO | CUSTOMER_NO | (etc) 456188 | 12355890
I have 2 queries that independently pull the right data. 1 query joins the customer info to pull in the customer name, and the 2nd query calculates the # of pallets per sales order.
BUT I CAN'T GET THEM TO WORK WHEN I DO A JOIN!!! Can someone please help me? Here are the queries as I currently have them. This returns zero rows, but when I run my queries independently, the both return multiple rows with the correct data. PLEASE HELP ME!
SELECT SOE.INSIDE_ROUTE, SOE.SALESMAN_NO, SOE.SALE_ORDER_NO, CUST.CUST_NAME, SOE.CUSTOMER_NO, (SOE.SALES_AMT/100) AS SALES, (SOE.COST_GOODS_SOLD/100) AS COGS, (SOE.SALES_AMT/100) - (SOE.COST_GOODS_SOLD/100) AS MARGIN, COUNT(PALLET.SALE_ORDER_NO) AS NumOccurrences FROM SOE_HEADER SOE, CUST_NAME CUST, PALLET_SALES_ORD PALLET WHERE SOE.SALE_ORDER_NO = PALLET.SALE_ORDER_NO AND SOE.CUSTOMER_NO = CUST.CUSTOMER_NO GROUP BY PALLET_SALES_ORD.SALE_ORDER_NO HAVING (COUNT(PALLET_SALES_ORD.SALE_ORDER_NO) > 1 )
THANKS. This will be a big help for the work I need to get done tomorrow, and I can't take it anymore. I've tweaked this all weekend, to no avail.
I'd appreciate any help with the following problem.
I'm trying to update a table using an inner self join.
I've a list of historical records with start dates, and I need to add end dates to the records, using the start date of the next record.
The table I'm using looks like this CREATE TABLE [dbo].[IbesEstimateHist]( [IbesEstimateHistId] [int] IDENTITY(1,1) NOT NULL, [IbesEstimateId] [int] NULL, [EstimateDate] [datetime] NULL, [EstimateEndDate] [datetime] NULL CONSTRAINT [DF_IbesEstimateHist_EstimateEndDate] DEFAULT ('9999-12-31 00:00.000'), [Value] [decimal](13, 4) NULL, CONSTRAINT [PK_IbesEstimateHist] PRIMARY KEY CLUSTERED ( [IbesEstimateHistId] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
and here's some example data
insert into IbesEstimateHist ([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value]) values(1,'2006-01-01','9999-12-31',100) insert into IbesEstimateHist ([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value] ) values (1,'2006-02-01','9999-12-31',100) insert into IbesEstimateHist ([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value]) values (1,'2006-03-01','9999-12-31',100)
These are three historical records for the same estimate, I want to set the end dates of the earlier records to the start date of the next record that was recieved.
This is the SQL that I've tried using but I can't seem to get it right
select esth1.IbesEstimateId, esth1.EstimateEndDate,min(next.estimatedate) from IbesEstimateHist esth1 inner join ( select esth2.EstimateDate as estimatedate, esth2.IbesEstimateId from IbesEstimateHist esth2 ) as next on esth1.IbesEstimateId = next.IbesEstimateId and esth1.EstimateDate < next.estimatedate group by esth1.IbesEstimateId, esth1.EstimateEndDate
Here the value for appname should be created by appname+'V'+latest version of that appname(eg. Application name- Test Ltest version- 11 thus appname becomes 'TestV11')
Could anybody help me to solve this problem.. Thanx in advance..
My database with 300.000 records , uses 350 MB RAM when I send this query. is there any way I can avoid this?
SELECT * FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS,(_NAME,ESTATEOTHERPROPERTIES),'FORSALE') as KEY_TBL ON KEY_TBL.[KEY]= ADTBL._ID Where _DELETIONSTATUS=0
I need to Update a table with information from another table. Below is my psuedo code - need help with the syntax needed for Sql2000 server. JOIN tblStateLoc ON tblCompanies.LocationID = tblStateLoc.LocationIDUPDATE tblCompaniesSET tblCompanies.StoreType = tblStateLoc.StoreTypeWHERE tblCompanies.LocationID = tblStateLoc.LocationID
My environment:XP Home, VWD, SQLEXPRESS.A purely local setting, no network, no remote servers. I try to do a JOIN query between tables in the membership ASPNETDB.mdf and one table in a self created 3L_Daten.mdf. After dragging the tables into the Query Design window and connecting them VWD creates this query (here I added the control declaration): <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAspNetDB %>" SelectCommand="SELECT aspnet_Users.UserName, aspnet_Membership.Password, aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Roles.RoleName, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Age, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Sex, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Area FROM [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten INNER JOIN aspnet_Users ON [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.User_ID = aspnet_Users.UserId LEFT OUTER JOIN aspnet_Roles INNER JOIN aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId LEFT OUTER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId"></asp:SqlDataSource> THIS WORKS, BUT: As you can see the database 3L_Daten.mdf is inserted with its full path, which is not feasible for deployment reasons. My question: How can I address both databases purely by their database names ? Both have been created within VWD and lie under App_Data. (I tried almost everything, I practiced with the SQL Server 2005 Management Studio Express Edition, I tried linked servers, all without success). Thank you for your consideration.
Here is the working query, shortened for the example:
SELECT a.SalesMan,a.CustomerName,b.Entry_Comments,b.Entry _Date FROM MyMaster a LEFT OUTER JOIN MyDetail b ON a.id = b.id WHERE blah ORDER BY blah
This works fine and I get all my detail reocrds for each master. Now I need to be able to select only a single most recent b.Entry_Date. How can I do this, Ive played with MAX but cannot get the sytax correct?
In my new job I have to administer an existing SQL-database with approx. 50 tables. In this database are no joins :confused: defined between the tables. We use a Visual Basic 6 application to create a GUI and within this VB6 app. there are several SELECT statements to retrieve the required data. In these SELECT statements are all the INNER and OUTER JOINS between the tables defined. My question: is this a correct way to work with or is it better to create all the JOINs between the tables on the database itself? Or should I create different views and define the JOINs in there? My main concern is the speed to retrieve data and second the required time to administer this database.
Hi all. I'm selecting all customers and trying to count alll the orders where at least one item has the itemstatus of "SHIPPED" on their order. Each customer will have only one order. I'm trying to see if I can do this in one query. Is it possible?? Is it something like below?
SELECT customers.id,COUNT( orders.id) AS 'total', from customers LEFT JOIN orders ON customers.id=orders.id AND orders.itemstatus="SHIPPED"
how can i join these 2 queries to produce 1 result
Query 1: select R.Name, T.Branchid, t.TradingDate,t. TransactionDate, convert(varchar,T.Tillid)+'-'+convert(varchar,t.Saleid) as DocketNumber, t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount as CommissionAmt from TransactionHeader T join SalesRep R on R.SalesRepid = T.SalesRepid where T.SalesRepid is not null
Query 2 : select C.TradingName,C.AccountNo From Sale S Join ClMast c on C.Clientid = s.CustomerAccountID
The result should be R.Name,T.Branchid, t.TradingDate,t. TransactionDate,DocketNumber,t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount, TradingName,Accountno..Field Saleid is present in Transactionheader Table and Sale table
select distinct duo.messageid_ from [detected unique opens] duo
left outer join (select MailingID, count(*) as cnt from lyrCompletedRecips where mailingid = duo.messageid_ and FinalAttempt is not null AND FinalAttempt >= '1945-09-10 00:00:00' group by MailingID) ad on ad.mailingid = duo.messageid_
i m getting error like:
The column prefix 'duo' does not match with a table name or alias name used in the query.
The query:SELECT BTbl.PKey, BTbl.ResultFROM BTbl INNER JOINATbl ON BTbl.PKey = ATbl.PKeyWHERE (ATbl.Status = 'DROPPED') AND (BTbl.Result <> 'RESOLVED')Returns no rows.If I do:SELECT BTbl.PKey, BTbl.ResultFROM BTbl INNER JOINATbl ON BTbl.PKey = ATbl.PKeyWHERE (ATbl.Status = 'DROPPED')Returns:PKeyResult125127RESOLVEDI want the first query to return the row with PKey: 125 because it'sresult field does not equal 'RESOLVED'Any ideas what I'm doing wrong?My tables:CREATE TABLE [dbo].[ATbl] ([PKey] [int] NOT NULL ,[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[BTbl] ([PKey] [int] NOT NULL ,[Result] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO
Hi everyoneHave a problem I would areally appreciate help with.I have 3 tables in a standard format for a Bookshop, egProductsCategoriesCategories_Productsthe latter allowing me to have products in multiple categories.Everthing works well except for one annoying little thing.When an individual product (which is in more than one topcategory) is addedto the Shopping Cart it displays twice, because in my select statement Ihave the Category listed. I realise I could remove the TopCategory from thestatement and that makes my DISTINCT work as I wanted, but Id prefer to havethe TopCategory as it saves me later having to another SQL query (Im alreadydoing one to allow me not to list category in the Statement .... but If Ican overcome this one ... then I can remove this as well).Here is my table structure (the necessary bits)productsidProduct int....categoriesidcategory intidParentCategory inttopcategory int...categories_productsidCatProd intidProduct intidCategoryWhen I run a query such asSELECT DISTINCT a.idProduct, a.description,a.descriptionLong,a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,c.topcategoryFROM products a, categories_products b, categories cWHERE active = -1 AND homePage = -1AND a.idProduct = b.idProductAND c.idcategory=b.idcategoryAND prodType = 1 ORDER BY a.idProduct DESCThis will return all products as expected, as well as any products which arein more than one TopCategory.Any ideas how to overcome this would be greatly appreciated.CheersCraig
I'm having trouble finding the correct way to proceed. My object is to have a selection set of the most recent log entry for each user. I want to display this info in an ASP.Net grid. I have a user table related to a datetime stamped log table. I have the stored proceedure that finds the latest log for a specific user, but I'm having a problem constructing a statement that will produce records for all users.
This is (basically) what I use to get a single User's data.
Code Block SELECT First, Last FROM UserDetails INNER JOIN UserStatusLog ON UserDetails.UserID = UserStatusLog.UserID WHERE DateTimeStamp IN (SELECT max(DateTimeStamp) FROM serStatusLog WHERE UserID = @UserID)
I'm using SQL Server 2005 Express and somewhat of a newbie to SQL.
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.