Thursday, May 30, 2013

[SQL Server] Problem adding to a query

[SQL Server] Problem adding to a query

Problem adding to a query

Posted: 30 May 2013 10:05 AM PDT

Hello, I am trying to change the below query to add two new columns. Territory and Customer Sold To.I have tried several different ways of pulling the information and get nothing but errors.The information that I need is contained in the tblarInvoiceline table.Any help would greatly appreciated.Thanks,DavegDECLARE @SPC varchar(6)SET @SPC = '{[Report]!B7}'Selectsp.Item, sp.itemdescription,sp.SellingPrice,sp.EffDate,vp.PurchasePrice,sp.pricedesc, (select max(ar.InvoiceDate) from tblarInvoice ar inner join tblarInvoiceLine ari on ar.InvoiceNumber = ari.InvoiceNumber Where sp.Item in (ari.Item)) as 'Last Date Sold',(select distinct(il.VendorPurchaseFrom) from tblimItemLoc il inner join tblapVendorPurchaseFrom apf on il.VendorPurchaseFrom = apf.VendorPurchaseFrom Where sp.Item = il.Item and vp.Vendor = apf.Vendor) as 'Vendor Purchase From'From vwimCurrentItemPriceAOP2 sp left join vwimCurrentItemVendorPrice vp on sp.Item = vp.ItemWhere sp.SPC = @SPC

SQL Server SSMS Doesn't like the ORDER BY Clause. What??

Posted: 30 May 2013 12:35 AM PDT

OK. I have the most basic of queries and SSMS is barking at me. I created this as a View in SSMS.SELECT TOP (100) PERCENT AddressTypeID, AddressTypeDescFROM dbo.AddressTypesWHERE (Active = 1)ORDER BY AddressTypeDescRuns just fine. I can save it as a saved View but when I do, SSMS says:[i]Warning: The ORDER BY clause is used only to dertermine the rows that are returned by the TOP Clause in the View Definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.[/i]OK. I've read this several times and I'm still failing to understand what it's trying to tell me. Can someone please specify? I can ofcourse simply create a Query and it works just fine.PS - does anyone know how to disable the default "TOP PERCENT" SSMS insists on inserting into the queries/views?

Need help with Pseduo code for arequirement

Posted: 30 May 2013 06:16 AM PDT

Hi friends,I'm basically a Java guy into TSQL sometimes as and when needed- I need a help with a requirement on how to code the below req in SQL - even a Pseduo will help please//cenario 2:You'll need to leverage the SLII database for scenario 2. Include requests in these statuses (for Job Postings, Work Orders and Work Order Revisions): Pending Approval (anything), Approval Paused, Rejected (bottom line: use the same exact method you use in the SLII audit). - If "Delivery Staffing Justification Audit Flag" = SLII Request and "Miscellaneous Notes" = blank then compare the "ORC entry AC Tenure Exception #" field in the Req Audit (can't do exact match – need to look at first 12 char) to the "SLII ID" from the SLII Database.1. If no match then, marka. "Owner for Next Action" = WFP/Businessb. "Passed Audit" = Noc. "Audit Failure Summary" = See Miscellaneous Notesd. "Miscellaneous Notes" = FLAG – Invalid SmartLaborII Job Posting/Work Order e. "WFP Business Action Items" = Correct Flags2. If created in SLII before 12:00 AM GMT on June 3, 2013 then, marka. "Miscellaneous Notes" = SLII pre June 3, 20133. If created in SLII on or after 12:00 AM GMT on June 3, 2013 then, mark a. "Owner for Next Action" = WFP/Businessb. "Passed Audit" = Noc. "Audit Failure Summary" = See Miscellaneous Notesd. "Miscellaneous Notes" = FLAG - This request was created in SmartLaborII on or after 12:00 AM GMT on June 3, 2013, the ORCR and Taleo request are not required, please cancel them. e. "WFP Business Action Items" = Correct Flags//thanksDJ

Schema Design: Many-To-Many Self Join

Posted: 30 May 2013 03:13 AM PDT

Here's the situation:I need to structure a DB to facilitate "relationship discovery" between people and business entities. Simply put it should show from data collected people who own businesses, are executives/employees of businesses, businesses that are subsidiaries of other businesses, businesses that are partners of other businesses, and people who are somehow 'related' (eg, blood relative, through marriage, business associates, friends, etc.) I just need a little validation on my current design and possibly some guidance on how to handle the relationships in queries. current Design:[u]tblPerson[/u]PersonID - PK (Identity Col)FNameLName...[u]tblBusEntity[/u]BusEntityID - PK (Identity Col)BusNameEIN (Tax ID)Addr...[u]tblPersToBus[/u] - Bridge entity for many to manyPersonID -PK - FK to tblPersonBusEntID - PK - FK to tblBusEntityRelType[u]tblPersToPers[/u] - Bridge entity for self join many to manylfPersID - PK - FK to tblPerson.PersonIDrtPersID - PK - FK to tblPerson.PersonIDRelationType[u]tblBusToBus[/u] - Bridge entity for self join many to manylfBusEntityID - PK - FK to tblBusEntity.BusEntityIDrtBusEntityID - PK - FK to tblBusEntity.BusEntityIDReltypeFrom there I will use the bridge tables to store the "transaction" records that define the relationships. The query question I have is if I want to show a persons 'network' of relationships between other people and businesses or a business' relationship 'network' between people and other businesses how do I accomplish this? I figure I will have to use both the "lf..." and "rg..." sides of the relationship bridge entity tables to get multiple levels out from the central network point (a person or business I am interested in.) Any help is greatly appreciated

Read Log from Windows 2003 Task Scheduler

Posted: 30 May 2013 12:13 AM PDT

I am looking for help in reading Windows 2003 task scheduler log history to find out if a task has run and send an email via SQL. Or any other tricks for that purpose.Your help will be very much appreciated.Regards

Comparing rows between two tables

Posted: 29 May 2013 09:59 PM PDT

I have two tables that I have to compare to get the differences.Table DesignService varchar(40)CustomerName varchar(50)ConnectDate datetimeDisconnect Date datetimeThere is no key nor index - I need to find the records that are in table1 that are not in table2 and vice versa. Any recommendations on how to do this ?Thanks in advance to all who help.
Posted by at

1 comment:

  1. mtomSeptember 12, 2018 at 5:05 AM

    I love once you talk about this kind of stuff inside your posts. Perhaps might you continue this? Supplier Feedback Audits

    ReplyDelete

Subscribe to: Post Comments (Atom)

Search This Blog