I am looking thru SQL Books, browsing the forum, but still unclear of the best method to perform the following calculation. I am really struggling with this and any helpful hints would be greatly appreciated.
Situation
Table 1 = customer information. Contains customer #, name, address, etc.
Table 2 = default price list. Contains item #, item desc, price
Table 3 = Allowed discounts based on customer types. Hospital gets $1 off, Doctors get 25 cents off, individuals get no discount.
Further discounts are given for multiple purchases. The 1st item is 100%, 2nd item is 75% and 3rd and subsequent items get 50% off purchase that day.
Here is a basic query result set of customer number, price, item ordered, item description and customer type.
Cust Price ItemNo Item Type
1 $5.50 23432 Moisturizer Hospital
2 $5.55 23453 Moisturizer 2 Indvidual
2 $29.00 23243 Lotion Pkg Indvidual
3 $29.00 23243 Lotion Pkg Doctor
3 $21.25 23232 Gloves Doctor
3 $3.00 23235 Drape Doctor
3 $2.00 27821 Lube Doctor
Calculation
Customer 1 ordered 1 item @ 5.50, but because they are a hospital they get a $1.00 discount. They only ordered 1 item so they pay 100% of the discounted price.
(5.50 - 1.00) * 100% = 4.50
Customer 2 ordered 2 items @ 5.55 and 29.00 respectively. They get no discount because they are an individual client, but do qualify for the multiple item discount of 25% for the second item.
29.00 + 5.55 * 75% = $33.16
Customer 3 ordered 4 items @ 29.00, 21.25, 2.00 and 3.00 respectively. They get a 25 cent discount off each item for being a doctor. They also get a multiple item discount of 25% off the second item and and 50% off items 3 and 4.
The math ideally will look like this
Problem
For customer 2 if someone entered the lower valued item first the math would look like this
5.55 + 29.00 * 75% = $27.30 (loss of revenue)
Possible Solution
The prices need to be sorted in descending order to maximize revenue prior to discounts being taken for a given day.
Should I use a "rank by clause" statement?
Then I need to multiply the first item by 100%, the 2nd by 75% and the remaining items by 50% to get a grand total of money due. Any pointers would be greatly appreciated.
I am not at all sure how to accomplish this.
I think I have got the hang of queries and stuff like that but here is what I want to do. I am using the 2008 express freebie from microsoft with Sql server 8 freebie.
Here is waht I want to do. Place a load of buttons on a screen each one with a list in it. so for example box one will specify all the days of the week,the others all the colors of the rainbow and the others all the month in the years. So the the boxes will feed through to a tiny little sql server database with three tables...days..colours...months. I want somebody to be able top come along and then click on a particular day of the week...let's say thursday...then click on a colur.lets say pink...then click on a month,lets say september.. Then a table will appear with that info in it..i.e. thursday,yellow september.
I know I could write a little prog in VB or C++ to do this but I am trying to relate it to a database.
I think the bottom line here is that I am trying to get the user to create the query and get the result of that query out onto a page.
As you can see I am just below novice level and I think the question I am really asking is that is it possible that the person using my program is actually making up a query(based on my criteria)and can get a result,rather than me have top store a query(or stored procedure) that just responds to a click on the box.
If this lot makes any sense what so ever,then could someone kind soul point me it the right direction(or any direction) :)
Hi there...I am having a whole bunch of trouble trying to design a certain query. Ihave two tables (see a representation of them here:http://www.plankmeister.org.uk/tables.html ) one called menu_data and onecalled page_data.menu_data contains a list of defined menus (menu_group) which may be sortedaccording to the menu_group_display_order column.page_data contains a list of page definitions. Each page belongs to amenu_group. All the pages in a given group may be sorted according to thepage_display_order column. (for instance, a query to order all pages with amenu_group equal to 1 in ascending order would produce:CatsDogsFishSquirrels)What I am trying to do is to design a query that will tell me thepage_user_name of all the pages that sit at the 'top' of their menu group,but ordered by the menu_group_display_order column.So basically, it will order the data in menu_data by themenu_group_display_order colum, then comparing menu_data.menu_group topage_data.menu_group, will return the 'first' row after being ordered bypage_display_order, so that in the case of the data given, the return wouldbe:HorsesCatsBirdsRabbitsI've tried UNION, GROUP BY, TOP 1, INNER JOIN, OUTER JOIN, SLIGHTLY TO THELEFT JOIN, ONLY IF ITS A WEDNESDAY JOIN... etc etc... But I just can't getit to work! It either returns 40 rows, or one. I've spent a total of about10 hours trying to design this query, and am losing what precious littlehair I have left at an alarming rate.Hope someone can provide some insight!Plankmeister.
I know that the answer to my problem is something that lies right infront my eyes but I got totally stuck. I cant understand why, when I deploy a database application I've made, when the application is run for the second time, the database doesn't have the data I have entered the first time... Is it something I have to tell my application?
It must be something trivial but I just can't put my finger on it.
This is a shameless plug. My new book, SQL PROGRAMMING STYLE is outnow.Joe Celko's SQL Programming Styleby Joe CelkoSave 20% and get Free Shipping if you order by Friday May 6.Place your order now or learn more below_______________________________________If you're looking for a book with a collection of tricks & tips andrules for formatting and writing portable, readable, maintainable SQLcode, you'll find what you need here!Are you an SQL programmer that, like many, came to SQL after learningand writing procedural or object-oriented code? Or have switched jobsto where a different brand of SQL is being used, or maybe even beentold to learn SQL yourself?If even one answer is yes, then you need this book. A "Manual of Style"for the SQL programmer, this book is a collection of heuristics andrules, tips, and tricks that will help you improve SQL programmingstyle and proficiency, and for formatting and writing portable,readable, maintainable SQL code. Based on many years of experienceconsulting in SQL shops, and gathering questions and resolving hisstudents' SQL style issues, Joe Celko can help you become an evenbetter SQL programmer. More specifically, this book will give youexpert advice from a noted SQL guru and award-winning columnist.Table of Contents_______________________________________FeaturesHelps you write Standard SQL without an accent or a dialect that isused in another programming language or a specific flavor of SQL, codethat can be maintained and used by other people.Enables you to give your group a coding standard for internal use, toenable programmers to use a consistent style.Gives you the mental tools to approach a new problem with SQL as yourtool, rather than another programming language-one that someone elsemight not know!Place your order here:http://books.elsevier.com/us//mk/us...r=&community=mkVolume discounts available, contact Join Bytes!
I've got a .dtproj project along with 10 dtsx packages. Up to here everything is fine, every package have its own source files, sql destinations and so on.. Issue comes when I try create a flat file source connection for the eleven one.
I get this message:
The component has detected potential metadata corruption during validation. Error at Data Flow Task [Flat File Source [1]]: Failed to find any non-special error column in output "Flat File Source Error Output" (3).
It may not be possible to recover to a valid state using a component-specific editor. Do you want to use the Advanced Editor dialog box for editing this component?
Otherwise if I'm gonna to open a new .dtproj project separately no problem at all, I mean, appears the suitable window, "Connection Manager", "Column", "Error output" on the left and so on..
It's very strange. The rest of the packages, the same situation. Fortunately, all of them are stored on the server.
I don't get the point at all. Could you please tell me what the hell is happening?
Hello everyone. I need some assistance here. I have a web form to get information about user details. The web form has two parts. the first part is user personal information where there is few textboxes, dropdownlist , radiobuttons , checkbox etc. this is where user inputs his personal information. the second part contains the office information where there is few textboxes, dropdownlist , radiobuttons , checkbox etc too. but the second part is automatically populated by data from the database(lets say table1). this happens as soon as user selects an option from dropdownlist in second part(here an option for dropdownlist is name of the office) . there is also a save and cancel button at the end of the form.now what i want here is, whenever i click a save button in my webform,i want the data of first part of my webform (ie user personal information) to get stored in new table(say table2).but i dont want the data of secont part of my webform(ie office information) to get stored in table2.(coz its already stored in table1.thats from whereit got populated earlier).But i want a reference of second part of my web form(ie office info) in table2.(only the reference id of related data from table1.not the whole data)and finally i want to display data from both the tables in one single details view.(both personal and office information)i hope u people are gettin it?i know i have to use foriegn keys.but i dont know how to use in this case.(sql express)and can u plz explain how to implement this in cs file with example including source code.(c#, vwd2005 express, sql express). thanks. jack.
I downloaded MS SQL Server 2005 Express Edition. The MS site said that it was "easy to install, use and manage." However I'm totally confused. I'm not a total neophyte but I can't even seem to launch the program.
I have an MS SQL database powering my website. I view the database on my home computer using MS Access 2000 with an ODBC connection. MS Access 2000 however, does not let me make changes to the structure of the database. I can't add, delete or modify fields in my current tables. I can't add or delete tables. Is MS SQL Server 2005 Express Edition the proper software to perform these functions? If it is, is there a guide to helping me launch the program, connect to my MS SQL database and perform these functions? If this is not the right software, what is?
I would greatly appreciate any guidance anyone can offer.
okay so i have query which sure does run fine if i drop it into a SQL server's console. however i try and put this into ACCESS so the accounting department can use it and it returns no values.
not sure if i should drop in the vb forum or what but im about to build a page in c# and have them get it that way.
Any one have any suggestions
Code:
select Doc.Dat, Doc.DocNo, Doc.IdPers, Pos.ItemNo, Pos.[Description], Pos.Quantity, Pos.SinglePrice, Pos.Discount from Pos, Doc where Doc.Dat like '%2005%' AND IdPers = (select IdPers from Pers where match = 'Aec One Stop Group, Inc.') AND Pos.IdDoc in ( select idDoc from Doc where Type = 8 ) ORDER by Dat, DocNo DESC
I've been parachuted into a place with a few dozen servers, several thousand external users (and scheduled to grow). Right now the whole place - IT and non-IT alike - l logs in with SA.....
(where's the 'Puking' icon??)
They've got Windows logins, but that's about as tight as it gets. Honestly I've never seen anywhere as insecure....and not sure where to start.
Hi all......well ill try and explain what I dont understand :-(
I am completely new to SQL. I have downloaded SQL Server Express 2005. I guess I need to download another program now like SQL Query Analyaer so that I can query a database - can anyone recommend a free download that will do the job?? I had a play around with one that I downloaded but to be honest, I have no idea on how to get the two programs talking.
Is someone able to guide me through how to initially set up SQl Server and how to get evrything working??
I get multiples of one record. i dont want multiples. but i need to have all the records that are the latest and i dont know how. can you help me. thanks
SELECT TOP 1 [sn].[WrkstaId] ,[sn].[Computer Model] ,[sn].[Serial Number] ,[sn].[Asset Tag] ,[sn].[Computer Type] ,[id].[Name] ,[id].[Domain] ,[id].[OS Name] ,[id].[OS Type] ,[id].[Last Logon User] ,[id].[Last Logon Domain] ,[id].[Client Date] ,[pu].[_id] ,[pu].[Month] ,[pu].[User] FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number] sn INNER JOIN [Altiris].[dbo].[AeXInv_AeX_AC_Identification] id ON [sn].[WrkstaId]=[id].[WrkstaId] INNER JOIN [Altiris].[dbo].[AeXInv_AeX_AC_Primary_User] pu ON [sn].[WrkstaId]=[pu].[WrkstaId] WHERE [sn].[WrkstaId]=(SELECT MAX([WrkstaId]) FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number] WHERE [Serial Number]='68659') ORDER BY [pu].[_id] DESC
68659Latitude D62028LGGD1Data not accessiblePortableWL-28LGGD1AZCORPMicrosoft Windows 2000Professionaluswclh04AZCORP2008-01-16 17:20:37.000864649Januaryuswclh04
68659Latitude D62028LGGD1Data not accessiblePortableWL-28LGGD1AZCORPMicrosoft Windows 2000Professionaluswclh04AZCORP2008-01-16 17:20:37.000864648Decemberuswclh04
After much hard work and effort I finally got SQL Server downloaded and opened. I am at a lost since the "Dummies" books, SQL & SQL Servier 2005 Reporting Services, I have don't (I can't find or understand) explain what I am trying to do. I have been able to create a couple of table, but can't see them. I can see the queries but not the actual table unless I run a query. How can I open the table it self?
Would to import an Access table in the SQLEXPRESS but can't figure out how. Can I get some guidence?
Any help I can get would be helpful. Thank you in advance.
Hello,I am using visual studio express edition. I making a nice website that uses the ASPNETDB.MDF for all those cool asp 2.0 membership and roles stuff.. This uses a SQL express database. OK so far. I have tried and tried to get my website live so I can further test it, but I can't People have been helpful, escpecially Bruce from this forum. I need more than help I guess. I really need to get this website up. Can someone please tell me where I can go to hire someone who will talk me through this process. I am not a programmer, I am a hobbyist. I understand SQL to select and update my database, but I can't do the stuff I read in FAQ's. It seems to be written for professionals. I have tried 3 different hosting services none seem to work. Please someone send me a private msg. we can work out terms. I am sure with someone over the phone that knows sql express and visual web developer expres will have me up and running in a jiffy. I can send payment via paypal.Jason
This procedure is missing one importent piece is I need to have a customerID added to the invoice table. The CustomerID comes from another table called Customer_Invoice_cart. I don't know how to convert Customer_Invoice_Cart.CustomerID to a @CustomerID to add to the table. If there is anyone out there who is good at stored procedures (unlike me who is learning) could help me out. FYI I realy cannot get the CustomerID for the .aspx page, it is not called any where.
procedure [dbo].[InvoiceAdd]( @EmployeeID int, @CartID nvarchar(50), @OrderDate datetime, @OrderID int OUTPUT)AS BEGIN TRAN AddInvoice /* Create the Order header */ INSERT INTO invoice( employee_number, invoice_date)VALUES( @EmployeeID, @OrderDate) SELECT @OrderID = @@Identity /* Copy items from given shopping cart to OrdersDetail table for given OrderID*/ INSERT INTO invoice_books( invoiceID, ProductID, TaxID, Quanatity, UnitCost, UnitPrice ) SELECT @OrderID, Inventory_Invoice_Cart.InventoryID, Inventory_Invoice_Cart.TaxID, Inventory_Invoice_Cart.Quanity, products.UnitCost, products.UnitPrice FROM Inventory_Invoice_Cart INNER JOIN products ON Inventory_Invoice_Cart.InventoryID = products.ProductID WHERE Invoice_CartID = @CartID INSERT INTO invoice_books( invoiceID, ServiceID, Service_TaxID, Service_Quanatity, ServiceCost) SELECT @OrderID, Service_Invoice_Cart.ServiceID, Service_Invoice_Cart.TaxID, Service_Invoice_Cart.Quanity, services.price_leval1FROM Service_Invoice_Cart INNER JOIN services ON Service_Invoice_Cart.ServiceID = services.serviceID WHERE Invoice_CartID = @CartID /* Removal of items from user's shopping cart will happen on the business layer*/EXEC Customer_Invoice_Cart_Empty @CartIDEXEC Inventory_Invoice_Cart_Empty @CartIDEXEC Service_Invoice_Cart_Empty @CartID COMMIT TRAN AddInvoice
I used sql 2000 create a data base at home call 'Mysite' several months ago, there are about eight tables and 30 stored procedures. When I run my asp.net program at home yesterday, and found can't connect to database. The error as follow: "Cannot open database requested in login 'Mysite'. Login fails. Login failed for user 'MINGYANG-MSONXHASPNET'. "
When I open the database, very surpriced to found that my database was suspect and not any item inside it. The suspect database include CommunityStarterKit. But Northwind and pubs database still there?
What happen? Last week I just download new Norton antivirus software, does have any relationship with it? Does data really gone? How to solve this problem?
What is the quivalent of EM for SQLExpress2005? With the old SQL 2000 I used EM to manage tables, SPs and such. Now, I cannot even find the databases - I installed the Club Starter Kit and I want to view and modify some tables. How do I do that?
Hello all let me first start out by saying I suck at SQL. I can do quite a bit with ASP.NET but SQL Server is on area that has haunted me and I have finally decided to bite the bullet and figure this behamouth out.Well here is my first problem, which is the biggest reason I can't seem to understand SQL Server or dataases in general.To have a realtional database you set up tables with foriegn keys and primary keys referenceing the id's like product table has a product name and one of the columns is a user_id displaying the id number of the user in the user table. From what I can tell you are supposed to set up the user_id in the products table a foriegn key to the Primary Key in the user table. I may be totally wrong on this though.Now my biggest question is how do you retrive this information the proper way to get it ready to be displayed in a gridveiw or a datalist? I have been reading SQL Server 2005 for developers and reading online tutorials and it seems like they say you need to set up a relationship and because of the realtionship you don't need to do a join, but I never hear how to use the relationship at all.Please someone help I am so totally lost that i feel like i will never understand. I have spent the last week trying to figure this out and I guess I am just googleing this wrong completely or i am incompentent one of the two.Thank you.
My company took over a project that another company was building. The other company switched all the passwords for the system and the database right before they left. No one has the passwords to the system or the database. Is there a way I can dump the data, or re-mount the drives into a different machine and get the data? Or am I just screwed? I have physical access to the machine and I can do anything needed I just need this data asap!!
Hi, maybe someone has some clues to the following situation. Suppose from a SQL 7 database, only the log .ldf files are lost. All datafiles are present. Suppose there is no backup of this database. SQL Server will put the database in the SUSPECT status. The following question is very interesting to me: Can one recover from this situation??? I ask this, because when datafiles are lost, it's obvious that you have lost data, but since the transaction log files are a "bit special", I just wonder how one can survive such a situation. I know that IF a database has only ONE log file associated with it, and this one log file gets lost, SQL Server will create on startup a new log file. But with multiple log files, it does not behave in this sympathic manner. Is there perhaps any way to recover (for example, an undocumented dbcc command or so) ? I surely will appreciate any tip or suggestion !! Thanks!!!
We lost all the data we had in a table. We restored the db and the transaction log up to a point in time, but could still not recover the table. We eventually only do db restore without the transaction log. We recovered the data in the table in question, but lost all the data entered that particular day. Does any body have a solution or suggestion that can help? I will be greatly appreciated.
Hi, I face a different behaviour between SQL Server 6.5 and 7.0 regarding the global variable @@identity. I insert a row into a table with an identity column. This table has an insert trigger attached which performs another insert into a second table. Now I seem to lose the identity value (@@identity is NULL).
Example: Take the following 2 tables:
create table a (aaintnot nullidentity, bbintnot null )
create table b (bintnot null ) go
Table a has a trigger attached:
create trigger a_trigger on a for insert as insert b select aa from inserted go
After this I execute the following SQL statement:
insert a (bb) values (1) select @@identity
On 6.5 I get the result: ------------------------------------------ 1
On 7.0 I get: ------------------------------------------ (null)
Which means that outside the trigger I have lost my identity value. This feature of Version 6.5 I usually use to maintain event logs. Using a trigger hides this maintenance nicely from the application. My question is now, is this a new feature of Version 7.0 or is it a bug? BTW: The books online describe this behaviour in the case when the trigger inserts value into a table with an identity property, which is not the case in my example.
Regards Marco
Marco Ruggli ruggli_m@simultan.ch Simultan P&I Kantonsstrasse 1 6246 Altishofen, Switzerland
templog2.DAT and templog3.DAT were deleteed and now sql wont start. how do I recreate them with out a backup do I have any options? I will continue to monitor this site while I work on it. Please help.
I have lost the system administrator password for my test server which contains inf. which I cannot afford to loose. The SQL server documents state that if the password is lost , then a SQL re-installation will have to be done.
Is anyone aware of a way to reset the password for SA without reinstalling the SQL server.
Hey every one...I'm new here, and I feel rather bad just jumping in with out at least formally saying hi. However, I have a problem that I'm struggling with that I'm not finding an answer to via searches or through my library of books.
I'm sure it's basic, but I'm still new to the world of MS SQL, so please allow my naivety to MS SQL be forgiven.
I've been using MS SQL 2000 and 2005 on a Windows 2003 machine. However, I've been needing to be more mobile, I've been transferring some projects over to an XP pro lap top. There was a mild learning curve with .net with II5, because I've gotten spoiled with II6. However, that's fine.
It's just that when going through the procedure of attaching my databases from my Windows 2003 machine to my XP machine, I'm no longer able to log in, even though I've changed all the Connection string information.
On one database, it's not allowing me to use a stored procedure:
EXECUTE permission denied on object 'ViewSubscriptions', database 'la_market', schema 'dbo'
I know of general information concerning Owners of Databases and what not. However, when I tried to fiddle with granting dbo ownership (which i though DBO was just a general all purpose owner), it gave me other errors.
And on the other Database I'm getting absolutely no access to it what so ever (Once again, I've changed my connection string and everything)
Cannot open database "catalog" requested by the login. The login failed. Login failed for user 'BUCKYCOREASPNET'.
Now, the problem isn't what I'm showing you here. Normally I have what it takes to find answers to these sort of problems. But with this one, I can't seem to phrase the problem correctly. So, advice would be wonderful. However, just phrasing the problem is good enough. So far I was told that my databases aren't mapped to my current Windows authentication. So, once again, just having some advice as to how to understand the problem will help. I may be new, but I'm enthusiastic and confident.
I have lost LOG file, and I can not open database, (Error 5105) enterprise manager can not create database is it possible to not rewrite database ?? br
I've run into a fairly big problem. Apparently, the automatic backup has not been working since September, and that backup is of no use to me.
What I'm trying to do is restore the database without using the transaction log file. I don't care about a dirty database or lost data. The data is all I need, and the database structure of course.
I've tried several things.
Right click on the <database> go to tasks->attach-> pick MDF file etc.
Following this guide The only problem is I get a different error message. It doesn't ask a yes no question it just says the file doesn't exist and it can't create the database.
I've tried running it from other SQL management software and removing the LOG file when attaching, and that doesn't work. I tried:
Code:
MS SQL
EXEC sp_detach_db @dbname = <my db name> EXEC sp_attach_single_file_db @dbname = <my db name>, @physname = <my db mdf file>
and I get the same message pretty much.
I tried this but I can't seem to get the database into emergency mode.
Am at the end of my rope and I really need this up and running ASAP.
Although I'm not sure of the proper syntax for setting a database into emergency mode, I think the last link I provided might do the trick.
I also tried the following: http://www.spaceprogram.com/knowled...file-on_12.html But when I get to step 8, the database is not in a suspect mode. When I click on it it says the database is in accessible.
When I right click and click on properties I get: Quote: TITLE: Microsoft SQL Server Management Studio Express ------------------------------
Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Database 'BridgeTrak_Central2' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0194&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------ I checked the server and there is enough memory and disk space, about 2GB on each drive. Should be plenty for my 32MB database.
It's not my neck on the line but I would like to see my coworker stay here.
If anyone here knows anything about this, that would be great.
MS SQL 2000 Windows Server 2000 SP 4
_Any_ help or suggestions, other than the obvious, make sure backups are working, will be greatly appreciated....
Did I mention greatly appreciated?
I think the issue is that the original database was lost when someone else was trying what I'm trying now. They're very delete happy.
I think they may be SOL... glad it's not me, then again I wouldn't use the delete key without making 100% sure it wouldn't screw things up...I especially wouldn't empty the recycling bin. I probably would have deleted a database already burnt to CD, or burned some to CD....or made sure my backups were working. Messy messy network state, last guy left it in a mess. New network/DBA/ if it's got a network cable attached you're responsible. :S I don't like this new company that much. Too stingy... Dual P2 server?!?!?! Isn't that like, antiquated? No wonder there have been 3 dba/network admins/"if it's got a network cable..." that have left in the past year.
