I'm trying to select the TOP 20 Products per Catagory out of my Star Topology Database
if someone would PLEASE be able to help me out.
Here is my Query: -
SELECT Distinct (SELECT TOP 20 DP.[Description]), DP.Mims_Sub_Cat, SUM(FD.Cost) AS 'Cost'
FROM DIM_Product DP, FACT_Dispensary FD, DIM_Time DT, DIM_Client DC
WHERE DP.Product_KEY = FD.Product_Key
AND FD.Time_KEY = DT.Time_KEY
AND FD.Client_Key = DC.Client_KEY
AND DT.[Year] = 2007
AND DT.[Month] IN (2)
AND Client_name LIKE '%Medicare%'
AND DP.Manufacture_Name LIKE '%Cipla%'
--AND DP.[Description] IN (SELECT TOP 20 [Description])
AND DP.Mims_Sub_Cat IN (SELECT Mims_Sub_Cat)
GROUP BY DP.Mims_Sub_Cat, DP.[Description], FD.Cost
ORDER by DP.Mims_Sub_Cat, SUM(FD.Cost) DESC
My other problem is that it keeps on selecting the same products although i have a distinct in my query
The query will select a product with the amount and then select the same product again with a different amount.
selecting number of products that comes under a parent categorysqlserver2005CREATE TABLE [ProductCategoryAssociation]([CategoryID] [bigint] , --this is primary key of table Category[ProductID] [bigint] ,--this is primary key of table product) the above table binds a product to its category CEATE TABLE [Category]([CategoryID] [bigint] IDENTITY(1,1) -- this is primary key of table category[Name] [nvarchar](255)[ParentCategory] [bigint] NULL,) I have a Category"A" as a supper parentcategoryI have 1product in Category"A"Category"A" have 2 child category A11,A12. A11,A12 both have 2 products in the category (totally 4)Now I can get the count of all product belong Category"A" (ie is 5= 1+2*2) fivelet 1 be the id of category "A" Now the following querry give me the expected resultselect count(ProductId) from ProductCategoryAssociation where categoryid=1 or categoryid in (select categoryid from Category where Parentcategory=1) BUT if i have child for A11 named A111 and if there is ONE product belong to A111 this querry wont take that in to cosiderationso the following querry will do itselect count(ProductId) from ProductCategoryAssociation where categoryid=1 or categoryid in (select categoryid from Category where Parentcategory=1 or Parentcategory in (select categoryid from Category where Parentcategory=1))ie i nested one more selectbuthis has limitationsSo kindly give me a general solution to check up to the las level of category
Hi every body! I created a report project by SQL Server Business Intelligence Development.I use toolbox chart to display report.But I want to edit format of catagory fields when it appear.That mean I have a report have two catagory(category1 and category2),one is parent of another.I want to edit style of catagory( such as catagory1 will have lines to separate with another category1......) Please help me! Thank you very much.
I've got a big problem that I'm trying to figure out: I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like: [AddressID] [int] [LocationID] [int]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table. So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
hello, i have a Products table, and i want to make an image data type to one of his rows (Picture1 for example), i want to know, what is best, to store the picture in the database or store only the direction to the picture? if i store only the direction, i should take it from some output parameters of the Upload function of ASP and the add it to the Database? can i add it to a special folder for example MySiteUserName + file name? and another thing: let's say i have Promotion - tinyint, to store the promotion value of this product..If i show products using DataList, i could order my products first after Promotion and then after date added? could i use a special CssClass(font weight or other background) for the products witch has the Promotion more than 10 for example? how can i know the exact date time (yy/mm/dd/hh/mm) ? - it is taked from the Server date? thank you
For the last 2 years we have been using ArcServe for Windows NT (version 6.0 and 6.5) with the Backup Agent for SQL Server. We are now re-evaluating our backup software. Our primary issues with ArcServe are that it has been unstable, unreliable and it does not support the full SQL Server recovery functionality. ArcServe does not include the ability to do point-in-time database recovery. Nor does it support single table restores (when a full database backup was performed). These last 2 items are critical and necessary features at my installation.
What products do people recommend for performs Windows NT files and MS-SQL Server backups?
Say if I want to get the top 3 products that fall under category "J2" and have them ordered by ranking then I would use the
below query
ELECT DISTINCT TOP 3 mid, pid, pname, prank, mname FROM Products, Manufacturers, Categories WHERE Products.mid = Manufacturers.mid AND (cat1=2 OR cat2=2 OR cat3=2) ORDER BY ranking
Now what if I need the top 3 ranked products that fall under category "J2" and have them ordered by ranking selecting only
one product from each manufacturer. So in order words I want the top ranked product from each manufacturer.
What would that query be?
I am having a really tough time and have already spent quite sometime on it unsuccessfully. I would appreciate any help.
I want to have visitors to my website get price calculations from my database based on input variables which the visitor enters. I think I need the combination of Access, ASP.NET, and Sql Sever. Is this correct?
I am trying to write a bit of code that I can pass a brand name to. If the brand name exists I want to return the brandid to the calling middle tier. If the brand id does not exist I want to insert and then return the new brand id. The code below works unless the brand does not exist. Then it inserts, and I get an application exception. Next time I run the code it continues on until the next time it has to do an insert. So the inserts are working, but getting the value back is resulting in an application excetio.
Middle Tier Function ( private static int GetBrandForProduct(clsProduct o) { int brandid = -1; // If the brand name comes in blank use the first word of the overstock product o.BrandName = o.BrandName.Trim(); // if we do not have a brand for this product if (o.BrandName.Length == 0) return -1; Database db = CommonManager.GetDatabase(); ; try { // Get the brand id for this brand name // If it does not exist we will add it and STILL return a brand id object obj = db.ExecuteScalar("BrandIDGetOrInsert", o.BrandName); string catid = obj.ToString(); *** FAILING LINE *** return Convert.ToInt32(obj.ToString()); } catch (Exception ex) { throw ex; return -1; } return brandid; } Stored Procedure: ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- ALTER PROCEDURE [dbo].[BrandIDGetOrInsert] -- Add the parameters for the stored procedure here @brandnameparm varchar(50) AS BEGIN -- SET NOCOUNT ON SELECT brandid from brands where Lower(brandname) = Lower(@brandnameparm) -- If we found a record, exit
if @@rowcount > 0 return -- We did not find a record, so add a new one.
begin
insert into brands (Brandname) values(@brandnameparm)
end SELECT brandid from brands where Lower(brandname) = Lower(@brandnameparm) END
I have a shopping cart table and two products tables (legacy reasons...).
I need to create a relationship between the ShoppingCart table and the two products tables such that no products are entered in the Cart which don't exist in either product table 1 or product table 2.
How can I store more than one category in a products table? For exampe: I have a dvd website where the admin can add new dvd's. On the website all the categories are listed with a checkbox. If the dvd is a action comedy the admin have to check these two checkboxes. But how do I store that in the sql database an retrieve it? Thanks David
[crossposted]Hi, I wonder if anyone might lend me a brain.I have a stock database to build that covers over 1000 products, whichmight be said to exist in around 50 product families.Obviously, just to be awkward all the types of stock will havedifferent attributes. So one product might be a tube withinside/outside diameter and length and another a T shaped cable joint.All I can come up with is a separate table for each stock type familyand store the table name and product code in the main stock table, so:Tables:ProdAProdBProdCStockStock attributes:ProdIdProdTableAmountDateetc..ProdA attribute:ProdIdAttributeXAttributeYAttributeZetc..Then use code to parse the table and product ID to select the correctquery to get the product details. BUT This seems awefuly inelegant andpotentially wrong so I'm loathe to continue down this route.Can anyone tell me the "right" way to do this, I feel sure it must bea classic db design exercise, but unfortunatly one they didn't teachus at University -- or maybe I was asleep...Thanks!
I got a stored proc which returns the result dataset.
My requirement is to sort the final output depending on a parameter value (which is dependent on 2 different amount column from the result dataset) , display only top 10 products and equate the % mix for those 10 products only. Basically the % mix column should sum up to 100% for those top 10 products.
The query I have written below works fine. However, I now want to uncomment the WHERE clause below to find entries where the PatientAge does not fall between the PAGBeginningAge and PAGEndingAge. However, when I uncomment the WHERE clause line I receive the following error message:
i have two table one of them is products and it has id,code,name columns and the other one is option with id,proid,option,optiondes column. and there is a relationship with id from products to proid in option table.
i want to make query that results is compare two or more products with the same option column.
Hello, I'm wondering what would be the best approach to designing a database that will have different products one of them being T-Shirts of different sizes... for example 1 t-shirt design might only have 2 available sizes while another may have 4. I'm kinda stumped on how to approach this cuz there is multiple products like CD's, DVD's, Magazines etc which is pretty straight forward, but the T-shirts have this "variable" to it. What i'm really wondering is should i have 1 main "Products" Table or should i have a separate table for the t-shirts? Should there be a column for each available size? Currently my database has a "products table" that has foreign keys to "Product Type", "Artists", "Genre" The database is basically for a record company If anyone has designed a database similar to this i'd love any insight or even possibly to see a database diagram Thanks
select product.type, pc.price as pcprice, laptop.price lapprice, pc.model as pcmod, laptop.model as lapmod from product join pc on product.model=pc.model join laptop on laptop.model=product.model where maker = 'B'
the syntex runs but its not displaying any results + I know that I have some extra columns there but its for some thing else I was trying
We have a 3 month old quad processor/dual core server running SQLServer 2005 and already it is getting close to hitting the CPU wall.An 8 way CPU box is prohibitively expensive and out of the question. Iam looking desperately for a way to TRULY scale out SQL server...in thesame way that IIS can be scaled out via App Center.The "in the box" solution for SQL Server 2005 scaling out is the DMV.Unfortunately this solution makes the system less available rather thanmore (one server outage takes them all out for that table) and requiresserious rearchitecting of the software to use. Contrast this to IISand AppCenter where each added server makes the system more available,and requires no rearchitecting to work.Before someone says "what you want can't be done in adatabase"...Oracle has an application server middleware product thatlets you do both of the above. Just plug a new server with Oracle onit, and you've doubled your capacity. But SQL Server 2005 doesn't yethave a similar capability.So I read with great interest the following article that talks aboutwhy this is the case with SQL Server. There are two issues that makeit very difficult to do:http://www.sql-server-performance.c...ability_availab...You can create a crude pool using replication, but the performancetimes look horrendous.However, the article also talks about the latest developments in thisfield...specifically MIDDLEWARE that can create a scale out solutionthat is more available and that requires simply adding new servers toscale up.I found two companies which seem to offer this new capability:http://www.metaverse.cc/newsevents.asp?cid=17999andhttp://www.pcticorp.com/product.aspxBoth companies appear to have patents or a patent pending on theprocess. I tried to contact metaverse but got no reply, despite theirrecent press release. I just emailed Pcticorp today to see if I couldlearn more about their product.My question for this group is:Does anyone have experience with either of the two products (or anyothers that provide this capability)?Many thanks in advance for your help.Ian Ippolitohttp://www.rentacoder.com
I would like to get some advice on Visual Studio and Business Intelligence products. I currently work with SQL Server 2005 and I created an export package which I turned into an SSIS package. I would like to be able to change the SSIS package when I need to and I read that I needed Business Intelligence to modify the package. I installed the Business Intelligence software through our SQL Server install disks and still didn't see the Business Intelligence app in my program files. We have another server running SQL 2005 and I can modify SSIS packages there with BI but it has Visual Studio loaded on it. Do I need Visual Studio to use BI?
I am writing a query to display the count of products when a month is the primary month (Month where large sales happened)..Please consider the following query..
With Member ProductRank as Rank([Date Due].[Calendar].currentmember,TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount])) Member PrimaryProd as Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1)) Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,bdesc) on 1 from [Sales]
Output:
It runs for 13 seconds on my laptop. I have modified the query to get the results fast like With
Set MySet as TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount]) Member ProductRank as Rank([Date Due].[Calendar].currentmember,MySet) Member PrimaryProd as Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1)) Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,Bdesc) on 1 from [Sales]
Output:
This query runs instantly and result is not the same. Am I missing something here?
We have a sales fact table that has sales by customers, products, date etc. and dimension tables such as customers, products etc.
We built a multi-dimensional cube on top of these tables. how do we support queries like "show me top 10 products by sales amount and then show customers that didn't buy these products"?
Hi, I tried to install sharepoint server 2007 as a standalone in my computer. When i run the sharepoint products and technologies configuration wizard, i get the following error,
Failed to create sample data.
An exception of type System.Data.Sqlclient.SqlException was thrown. Additional Exception information: Cannot open database "SharedServices2_DB_28aa01d8************************" requested by the login. The login failed. Login failed for user "domainAdministrator"
I am trying to pivot my data results so that instead of showing multiple rows for each product a client has it will show one line for each client and concatenate all of their products together.
For example, if I run the following query:
SELECT [Year], [Client_Name], [Product_Name] FROM My.dbo.Table GROUP BY [Year], [Client_Name], [Product_Name]
My site uses a text box to allow visitors to search products. I'm trying to design the SQL Statement to allow search's on full words, part words, and words/phrases regardless of the order the words are in.
E.g. megger meg mft megger (proper order in the database is megger mft) mft1710 (using 1710 should find the product)
This is my select statement (classic ASP)
Code: <% Dim RSResults__param5 RSResults__param5 = "xxx" If (Request("searchme") <> "") Then RSResults__param5 = Request("searchme") End If
A colleague of mine has a view that returns approx 100000 rows in about 60 seconds.
He wants to use the data returned from that view in an OLE DB Source component.
When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).
He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>
In this instance all the data was returned in approx 60 seconds (as expected).
This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.