Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jacque
Starting Member
6 Posts |
 Posted - 2003-05-30 : 16:30:08
|
| I have a pretty complex query so far and I need to add one more thing. I have two tables: tblQuotes and tblQuoteItems. tblQuotes has all of the general stuff (contact info, etc.). tblQuoteItems has all of the specifics about the quote. Here are the fields (QuoteID, Type, Revision, DisplayStat, ModelNo, TextValue, Price, Qty) The type field is the key to what is stored in the other fields. If the type = 0 then I know the other data is a line item. if the type = 1 then the data is the discount... If the type = 7 then the quote is locked and the other data has to do about when the quote was locked.Here is my problem. I need the line item price quoted (along with other stuff) where the quote is not locked. Here is my query so far.SELECT DISTINCTROW [tblQuotes].[QuoteNumber] & IIf([tblQuotes].[Revision]="Initial","",[tblQuotes].[Revision]) AS [Bid#], Format([LITotal],"$#,###.00") AS [Total$], Format([tblQuotes].[Date],"mm/dd/yyyy") AS [Entry Date], [First Name] & " " & [Last Name] AS Issuer, tblOppLog.EndCustomer, tblQuoteItems.ModelNo, tblQuoteItems.Price AS [Price Quoted], Format(IIf([tblQuoteItems].[Type]=0,IIf([tblQuoteItems].[DisplayStat]=-1,[Products].[US Price],IIf([tblQuoteItems].[DisplayStat]=0,[Products].[Export Price],IIf([tblQuoteItems].[DisplayStat]=1,[Products].[Cost],0))),""),"$#,###.00") AS [Database Price], [Price]-IIf([tblQuoteItems].[Type]=0,IIf([tblQuoteItems].[DisplayStat]=-1,[Products].[US Price],IIf([tblQuoteItems].[DisplayStat]=0,[Products].[Export Price],IIf([tblQuoteItems].[DisplayStat]=1,[Products].[Cost],0))),"") AS [Price Difference]FROM tblOppLog INNER JOIN (Employees INNER JOIN (tblQuotes INNER JOIN (Products INNER JOIN tblQuoteItems ON Products.[Model No] = tblQuoteItems.ModelNo) ON tblQuotes.ID = tblQuoteItems.QuoteID) ON Employees.[Employee ID] = tblQuotes.EmployeeID) ON tblOppLog.QuoteID = tblQuotes.IDWHERE ((tblQuotes.Date Between #01/1/2003# And Now()) AND (tblQuoteItems.Type=0));This is giving me the correct data for ALL quotes, even if they are locked. How do I add that last condidtion where type <> 7 (the quote is not locked). |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-05-31 : 03:42:57
|
First, this needs to go to the Access forum or you'll get some brilliant answers which will unfortunately work with SQL Server only Secondly, the table structure is inconsistent. If I understand it right, "Locked" is an attribute of a Quote and not a QuoteItem. You re-use the Type attribute on QuoteItems and apply it to its parent Quote, creating redundancy and an added layer of complexity in reference. My suggestion is add another column to the Quote table, could be as simple as a Yes/No column named "IsLocked". That way you dont need to loop into the QuoteItem table and then backwards into the Quote table to identify (or exclude) a "locked quote".To get this query working with the existing structure, try this:SELECT DISTINCTROW [tblQuotes].[QuoteNumber] & IIf([tblQuotes].[Revision]="Initial","",[tblQuotes].[Revision]) AS [Bid#], Format([LITotal],"$#,###.00") AS [Total$], Format([tblQuotes].[Date],"mm/dd/yyyy") AS [Entry Date], [First Name] & " " & [Last Name] AS Issuer, tblOppLog.EndCustomer, tblQuoteItems.ModelNo, tblQuoteItems.Price AS [Price Quoted], Format(IIf([tblQuoteItems].[Type]=0,IIf([tblQuoteItems].[DisplayStat]=-1,[Products].[US Price],IIf([tblQuoteItems].[DisplayStat]=0,[Products].[Export Price],IIf([tblQuoteItems].[DisplayStat]=1,[Products].[Cost],0))),""),"$#,###.00") AS [Database Price], [Price]-IIf([tblQuoteItems].[Type]=0,IIf([tblQuoteItems].[DisplayStat]=-1,[Products].[US Price],IIf([tblQuoteItems].[DisplayStat]=0,[Products].[Export Price],IIf([tblQuoteItems].[DisplayStat]=1,[Products].[Cost],0))),"") AS [Price Difference] FROM tblOppLog INNER JOIN (Employees INNER JOIN (tblQuotes INNER JOIN (Products INNER JOIN tblQuoteItems ON Products.[Model No] = tblQuoteItems.ModelNo) ON tblQuotes.ID = tblQuoteItems.QuoteID) ON Employees.[Employee ID] = tblQuotes.EmployeeID) ON tblOppLog.QuoteID = tblQuotes.ID WHERE ((tblQuotes.Date Between #01/1/2003# And Now()) AND (tblQuoteItems.Type=0)AND tblQuotes.QuoteNumber NOT IN (SELECT tblQuoteItems.QuoteNumber FROM tblQuoteItems WHERE type = 7)) ;OwaisEdited by - mohdowais on 05/31/2003 03:45:40 |
 |
|
|
|
|
|
|
|
subselect query