I am having a whole bunch of trouble trying to design a certain query. I
have two tables (see a representation of them here:
http://www.plankmeister.org.uk/tables.html ) one called menu_data and one
called page_data.
menu_data contains a list of defined menus (menu_group) which may be sorted
according to the menu_group_display_order column.
page_data contains a list of page definitions. Each page belongs to a
menu_group. All the pages in a given group may be sorted according to the
page_display_order column. (for instance, a query to order all pages with a
menu_group equal to 1 in ascending order would produce:
Cats
Dogs
Fish
Squirrels)
What I am trying to do is to design a query that will tell me the
page_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 the
menu_group_display_order colum, then comparing menu_data.menu_group to
page_data.menu_group, will return the 'first' row after being ordered by
page_display_order, so that in the case of the data given, the return would
be:
Horses
Cats
Birds
Rabbits
I've tried UNION, GROUP BY, TOP 1, INNER JOIN, OUTER JOIN, SLIGHTLY TO THE
LEFT JOIN, ONLY IF ITS A WEDNESDAY JOIN... etc etc... But I just can't get
it to work! It either returns 40 rows, or one. I've spent a total of about
10 hours trying to design this query, and am losing what precious little
hair I have left at an alarming rate.
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) :)
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 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.
Im stumped on my SQL connection string when using the "SQLGridSelectedView" module. The tables im accessing reside in the same database as DNN on the local machine. My current string is "Provider=SQLOLEDB;Server=ADVANCED02;DATABASE=XP;Trusted_Connection=TRUE"My SQL statement as a test was a simple select all ...... "SELECT * FROM atm info"The error im getting is that ASPNET is not a trusted user"System.Data.OleDb.OleDbException: Login failed for user 'ASPNET'. Reason: Not associated with a trusted SQL Server connection. at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider() at System.Data.OleDb.OleDbConnection.Open() at DNNAdditional.SQLGridSelectedView.DisplayGrid(String psSortExpr, Boolean pbExport)"/TIA
I am migrating from MS Access and MySql to MS SQL 2000. I guess the first step is to load MSDE on my computer. My platform is Win XP Office
I am not sure what to expect, but this sure appears to be one of the most complicated procedures I have ever encountered. So I assume that I some how got off on a tangent.
Basically I just want a simple ability to create databases on Websites and transfer the data from existing databases on another Website.
What am I doing wrong?
What am I missing here?
Here is what I have done.
I downloaded the MSDE file MSDE2000A.exe
When I executed MSDE2000A.exe that installed a files in a directory MSDERelA
When I tried to run setup.exe in the directory MSDERelA, the process was aborted with the following comment
"A strong password is required for security reasons."
Then following the instructions on "How to Chane Your SA Password" I received the following.
'osql' is not recognized as an internal or external command, operable program or batch file.
This one has the expert in SQL Express stumped and he suggested I try posting here. My problem is that I am trying to load SQL Express and can not do so.
Mike Wachal - MSFT wrote:
mnn2501 wrote:
I am having a similar problem, I first used the uninstall program which failed, then went to add remove programs finding nothing, then went to windows explorer and deleted what looked to be obvious choices. I still can't get sql express to install - any idea's?
Here are the logs:
Product "{2AFFFDD7-ED85-4A90-8C52-5DA9EBDC9B8F}" versioned 9.00.1116 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1399.00 The Product Name is "Microsoft SQL Server 2005 Express Edition CTP"
Message displayed to user SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Framework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme.
This error indicates that you have a pre-RTM CTP version of Express on your computer that you did not remove correctly. You will need to remove all pre-release software before you can install the released software. Try to remove components from Add/Remove programs first. If that fails, or if nothing is listed, manually remove the product using the product id listed in the log like this:
Thank you for your answer, I tried doing the msiexec.exe /x {2AFFFDD7-ED85-4A90-8C52-5DA9EBDC9B8F} but came up with this pop up error message when I did and I am lost.
The setup has encountered an unexpected error in datastore. The action is RestoreSetupParams. The error is" Failed to read property "Installlds" {"MachineConfiguration","","BEAST"} from cache Unable to write property into cache. IsClustered
Unable to write property in cache:"IsClustered": XmlRW Error: Failure loading xmlre.dll CheckAllProcedures() returned:2
Any more ideas?
That one has me stumpt. I would suggest that you post this question into the Setup and Upgrade forum so the folks that specialize in the technology can offer suggestions.
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.
Here is the table:CREATE TABLE [child]([pk_child_id] [int] NOT NULL ,[fk_parent_id] [int] NOT NULL ,[code] [char] (2)NOT NULL ,[dt] [datetime] NOT NULL ,[newcode] [int] NULL)There is a situation where there will be more than one record with thesame [fk_parent_id] value, but different values for the [code]field.If one of those records has a [code]= 5, but the [dt] is AFTER asimilar record where [code]= 6 or [code]= 7 (but same [fk_parent_id]value), I need to set [newcode] = 10. How can I pull this off? Again,the group of records can have different [code] values, different [dt]values, but a common [fk_parent_id].Help!
SCENARIO:SQL Server 2000 Back End.The customer calls in with a problem and a QUERY is raised. The dateof creation of the record is in the fldDateQuery column.The user responds to the customer and a RESPONSE is raised. Itcontains the ID of the associated QUERY and the date of creation ofthe RESPONSE record is in the fldDateResponse column.The user wishes to know how many queries in the last month wereresponded to on the same day.CANDIDATE SQL:SELECT COUNT(*) AS NumResponsesFROM tblQueryINNER JOIN tblResponses ON tblQuery.fldQueryID =tblResponses.fldQueryIDWHERE (tblQuery.fldDateQuery < CONVERT(DATETIME,'2003-9-8 00:00:00',102))AND (tblQuery.fldDateQuery > CONVERT(DATETIME,'2003-8-8 00:00:00',102))AND (DATEDIFF(Day, tblQuery.fldDateQuery,tblResponses.fldDateResponse) <= 1)PROBLEM:If a query has more than one response raised on it within a day of thequery being logged, it counts all those responses. In other words,the SQL counts the number of matching RESPONSES, and not the number ofQUERIES.TIAEdward
Because of the single quotation mark in YEAR'S the insert fails. I know this has got be one helluva dumb question. but I'm still learning. How is it done??
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, I'm getting the error Syntax error (missing operator) in query expression 'dbPWork.id = dbPWorkWord.work_id LEFT JOIN words ON dbPWorkWord.word_id = words.id'." on the query below. Everything looks in place to me. Do you see anything that shouldn't work?
Query:
Code:
Code:
SELECT DISTINCT dbPWork.id, description_e as description, w_id , dateStart, dateEnd FROM dbPWork LEFT JOIN dbPWorkW ON dbWork.id = dbPWorkW.work_id LEFT JOIN words ON dbPWorkW.word_id = words.id
Everything works fine until I add the second LEFT JOIN statement. Any ideas? I'm stumped.
create table holding ( id1 int, id2 int, datefield datetime, holding int, quantity int ) go
insert into holding select 1,1,'20031210',10,null union select 1,1,'20031211',30,null union select 1,1,'20031212',70,null union select 1,1,'20031213',60,null union select 1,2,'20031210',100,null union select 1,3,'20031210',100,null union select 1,3,'20031210',10,null
go
Quantity is based on Holding for the day - Holding for the previous transaction involving same id1 ,id2.
I was hoping somebody could help me out with a query I've been trying to solve.
My Table Structure:
[UserMessages]
MessageID int ToUserID int FromUserID int Subject varchar(200) Message varchar(max) isNew bit
[UserMessageReplies]
ReplyMessageID int MessageID int ToUserID int FromUserID int Message varchar(max) isNew bit
Sample Data:
[UserMessages]
MessageID | ToUserID | FromUserID | Subject | Message | isNew ------------------------------------------------------------------------------------------------------------- 1 1 2 test subject Message Body 0 2 2 1 test subject Message Body 0 3 1 4 test subject Message Body 1 4 1 5 test subject Message Body 1
[UserMessageReplies]
ReplyMessageID | MessageID | ToUserID | FromUserID | Message | isNew --------------------------------------------------------------------------------------------------------------------------- 1 1 2 1 re: Message Body 0 2 1 1 2 re: Message Body 0 3 1 2 1 re: Message Body 0 4 1 1 2 re: Message Body 0 5 1 2 1 re: Message Body 1 6 2 1 2 re: Message Body 1
Explanation:
ReplyMessageID = 1-4 signfies that there is a thread response to MessageID 1 but they have been opened (isNew =0). ReplyMessageID = 5 signfies that there is a new thread response to MessageID 1. ReplyMessageID = 6 signifies that there is a reply from a message UserID=1 sent out (MessageID 2)
There needs to be a filter to check UserMessages.TOUserID=1 OR UserMessageReplies.ToUserID=1 to ensure that we can capture a msg that UserID=1 had sent out but now there is a reply to the Message by the User (in this example data i've setup MessageID = 2 to handle that possibility)
My Goal:
To select all messages for a ToUserID and order them by UserMessages.isNew or UserMessageReplies.isNew. This must include a message the user has sent but now has received a reply. (This means the first filter; UserMessages.ToUserID, must be overwritten with UserMessageReplies.ToUserID)
I have been looking at this for over a day now. I cannot see why this procedure does not work, its so simple. No matter what happens it always returns 0. If it locates a record, it doesnt update it, yet it still returns 0. It should not be returning 0 if its not updating so I can't figure out why it does. Why does this always return 0? [pre]Create Procedure CreateNewCategory @title nvarchar(100), @description nvarchar(1000), @displayOrder intAS DECLARE @Result as int IF EXISTS(SELECT categoryTitle FROM categories WHERE categoryTitle = @title) BEGIN SELECT @Result = 1 ENDELSE BEGIN INSERT INTO categories(categoryTitle, categoryDescription, displayOrder) VALUES(@title, @description, @displayOrder) /* If no error was encountered, 0 will be returned. */ SELECT @Result = @@Error ENDGO[/pre] Thanks!
It goes like the following:Use AdventureWorks database and HumanResources.Department table.Create a stored procedure called spDepartmentAddUpdate. This procedureaccepts two parameters: Name, and GroupName. The data types areVarChar(50), and VarChar(50) respectively. Define logic in thisprocedure to check for an existing Department record with the same Name.If the department record exists, update the GroupName and ModifiedDate.Otherwise, insert a new department record.A.Execute your stored procedure to show that the insert logic works.B.Execute your stored procedure to show that the update logic works.Any hints from the wizards out there would be greatly appreciated!*** Sent via Developersdex http://www.developersdex.com ***
I need to produce a result that has 1 column that is the concatenation of each COORD separated by a '.' by increasing LEVEL
EX: The result should look like this 1.1.1 1.2.1 1.2.2 1.2.3 1.2.4 1.2.5 1.3.1 1.3.2 1.3.3 1.3.4 1.3.5
Please note that the answer must be flexible enough to handle any number of levels and coordinates (although levels will most likely be less than 5 and coordinates less than 100)
Here is the situation I'm trying to set-up a package to ftp some files down if that part fails email me but if successful rename the 2 files to something else.
so what I set-up was a FTP tasks with the remote path set as a expresion since I needed to Download todays files. no problem there have that part working.
so then I set a notifaction event with failure constraint to email me. pretty easy so far.
then I set 2 tasks to rename the 2 files downloaded I'm sure I could do this part with a loop but what seperate tasks so I knew if any failed or could set-up notifaction tasks if I wanted. in the 2 rename tasks I set the sourcepath to an expression so it would know to read todays files and they could change from day to day.
then the destination part is hard coded for now to a file name.
now here is were the issue comes in I go to save it and it says the files don't exist from the rename piece. well of course not they haven't been FTPed yet.
so I tried delayevaluation on both the file system tasks but a no go still errors.
What I need it to do is basically say wait till the ftp is run these files won't be there. any help in the right direction would be appreciated.
I've seem some good posts similair to this, but I haven't been able to find my exact issue.
I have the following table:
ID Name Location Start End
1 Joe NY 2000 2001
1 Joe CA 2002 2004
1 Joe MA 2005 2008
2 Sue NJ 2003 2004
2 Sue FL 2004 2008
3 Bob CA 1999 2000
3 Bob WA 2001 2004
3 Bob OR 2005 2006
3 Bob MI 2007 2008
As you can see, the Location, Start and End dates can vary for each person and I don't know how many rows a single person might have.
The result I want, is a "pivot like" table.
ID Name Location1 Start1 End1 Location2 Start2 End2 Location3 Start3 End3 Location4 Start4 End4
1 Joe NY 2000 2001 CA 2002 2004 MA 2005 2008
2 Sue NJ 2003 2004 FL 2004 2008
3 Bob CA 1999 2000 WA 2001 2004 OR 2005 2006 MI 2007 2008
I assume I can first do a count of the maximum rows for an individual and that is greatest number of columns I would need. But doing that and trying to figure out the rest has really stumped me.
Any thoughts, ideas and suggestions would be greatly appreciated.
