SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 IN (SELECT)?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

techsupport14
Starting Member

USA
18 Posts

Posted - 08/05/2014 :  00:04:04  Show Profile  Reply with Quote
I think I need an IN (SELECT) statement ...

What I need to do:

An invoice can have one line or many lines.

In some of the invoices there is the item and a processing fee.

Sometimes there's just a processing fee on a single invoice.


I do not want to see the single processing fee invoices however I need to see single transaction invoices (as long as it's not the processing fee only).

How do I write this to show me all invoice detail records EXCEPT when the invoice has only the processing fee on it?

I was thinking the IN (SELECT), though don't know how to right it really, but to use that to see something like
ONLY when the ITEM = FEE the count of the InvNo has to be greater than 1 else don't show the invoice.
something like this (though it's not working and why I'm seeking help)
CODE
(dbo.AE_DeferredMemberships.InvNo IN
(SELECT InvNo
FROM dbo.AE_DeferredMemberships AS AE_DeferredMemberships_1
WHERE ClassID = ‘ADMINFEE’
GROUP BY InvNo
HAVING (COUNT(1) > 1))) AND (dbo.AE_DeferredSalesMembers.YearEnd > dbo.AE_DeferredMembershipsDynamics.PaidYear)

As well as there are invoices with the same ItemDescription but a return invoice starting with RTN for refunding the original invoice.
How I can tell so far is that the ItemDescription is the same along with the obvious customber number is the same, but the InvNo field is different with the original invno and the RTN#.

What would be the correct syntax to handle this scenario?


Thank you. I am very new to SQL coding.

gbritton
Flowing Fount of Yak Knowledge

1109 Posts

Posted - 08/05/2014 :  08:53:55  Show Profile  Reply with Quote
Please post:

1. CREATE TABLE statements to create test tables for your query
2. INSERT INTO statements to populate the tables
3. The results of your query executed against the test tables
4. The desired results of your query.
Go to Top of Page

techsupport14
Starting Member

USA
18 Posts

Posted - 08/05/2014 :  11:02:43  Show Profile  Reply with Quote

SELECT RTRIM(USFSA.dbo.SOP30200.SOPNUMBE) AS InvNo, RTRIM(USFSA.dbo.IV00101.ITMCLSCD) AS ClassID, RTRIM(USFSA.dbo.SOP30300.ITEMNMBR) 
                  AS Item, CASE WHEN RTRIM(USFSA.dbo.IV00101.ITMCLSCD) = 'ADMINFEE' THEN 'ADMIN FEE' ELSE MemberGroup END AS MbrGroup, 
                  CASE WHEN RTRIM(USFSA.dbo.IV00101.ITMCLSCD) = 'ADMINFEE' THEN 'Processing Fee' ELSE Description END AS Type, RTRIM(USFSA.dbo.SOP30300.ITEMDESC) 
                  AS InvItemDesc, CASE WHEN USFSA.dbo.SOP30200.SOPNUMBE LIKE 'RTN%' THEN XTNDPRCE * - 1 ELSE XTNDPRCE END AS ExtPrice, 
                  USFSA.dbo.SOP30200.DOCDATE AS PaymentDate, YEAR(USFSA.dbo.SOP30200.DOCDATE) AS PaidYear, MONTH(USFSA.dbo.SOP30200.DOCDATE) AS PaidMonth, 
                  RTRIM(USFSA.dbo.SOP30200.CUSTNMBR) AS CustNo, CASE WHEN RTRIM(USFSA.dbo.SOP30200.CUSTNMBR) 
                  LIKE 'PER%' THEN ' Individual' ELSE RTRIM(USFSA.dbo.SOP30200.CUSTNAME) END AS OrgName, CASE WHEN CONVERT(INT, 
                  dbo.GetNumeric(RTRIM(USFSA.dbo.SOP30300.ITEMDESC))) = 0 THEN MembershipNumber ELSE CONVERT(INT, dbo.GetNumeric(RTRIM(USFSA.dbo.SOP30300.ITEMDESC))) 
                  END AS MbrNoGP, entity.Person.LastName + ', ' + entity.Person.FirstName AS Name, entity.Person.MembershipNumber AS USFSANo, 
                  RTRIM(USFSA.dbo.SOP30200.CUSTNAME) AS GPName
FROM     USFSA.dbo.SOP30300 INNER JOIN
                  USFSA.dbo.IV00101 ON USFSA.dbo.SOP30300.ITEMNMBR = USFSA.dbo.IV00101.ITEMNMBR INNER JOIN
                  USFSA.dbo.SOP30200 ON USFSA.dbo.SOP30300.SOPNUMBE = USFSA.dbo.SOP30200.SOPNUMBE LEFT OUTER JOIN
                  entity.Person ON USFSA.dbo.SOP30200.CUSTNMBR = entity.Person.Id LEFT OUTER JOIN
                  lookup.MemberTypes ON USFSA.dbo.IV00101.ITEMNMBR = lookup.MemberTypes.ProductCode
WHERE  (lookup.MemberTypes.Id BETWEEN 1 AND 10 OR
                  lookup.MemberTypes.Id BETWEEN 22 AND 31 OR
                  lookup.MemberTypes.Id BETWEEN 41 AND 44 OR
                  lookup.MemberTypes.Id IS NULL) AND (RTRIM(USFSA.dbo.IV00101.ITMCLSCD) IN ('DUES-MEMB', 'DUES BSCOA', 'DUES-BSMEM', 'DUES-FOFS', 'DUES-COACH', 'ADMINFEE')) 
                  AND (YEAR(USFSA.dbo.SOP30200.DOCDATE) >= YEAR(USFSA.dbo.SOP30200.DOCDATE) - 1) AND (MONTH(USFSA.dbo.SOP30200.DOCDATE) < 7) AND 
                  (NOT (RTRIM(USFSA.dbo.SOP30200.CUSTNMBR) LIKE 'TEA%')) AND EXISTS dbo.SOP30200.SOPNUMBE IN
                      (SELECT dbo.SOP30200.SOPNUMBE AS InvNo
                       FROM   dbo.SOP30200 AS SOP30200_1
                       WHERE  IV00101.ITMCLSCD = 'ADMINFEE'
                       GROUP BY dbo.SOP30200.SOPNUMBE
                       HAVING  (COUNT(*) > 1)))


Incorrect syntax near dbo (on the last part where I am trying to do the IN SELECT statement.

Edited by - techsupport14 on 08/05/2014 11:03:03
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1109 Posts

Posted - 08/05/2014 :  11:36:42  Show Profile  Reply with Quote
OK -- first off, this is really hard to read. the main reason is that you are not using table aliases. FOr example, if, in the first FROM clause, you said:


FROM     USFSA.dbo.SOP30300 SOP303


Then you can replace ever occurrence of 'USFSA.dbo.SOP30300' with 'SOP303', thereby shortening all the column references and the whole query text

Second, try moving the 'INNER JOIN' to the start of the line like this:


FROM     USFSA.dbo.SOP30300 SOP303 
  INNER JOIN USFSA.dbo.IV00101 IV101
     ON SOP303.ITEMNMBR = IV101.ITEMNMBR
...


Third, (this may be the cause of the error), in your query, you join on 'USFSA.dbo.SOP30200.SOPNUMBE' but try to refer to the column without the linked server name '... AND EXISTS dbo.SOP30200.SOPNUMBE '

Aliasing your table names will definitely help with that.

Fourth, try stacking the Where clauses. e.g. not this


WHERE x AND y ANX z

but

WHERE x
  AND y
  AND z


As you do this, you might find missing parentheses and other problems

Finally, without some test data (see my earlier post), It's well-nigh impossible to debug your query.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
410 Posts

Posted - 08/05/2014 :  12:57:43  Show Profile  Reply with Quote
To do that, you use <> {type = 'fee' OR IN_statement}.

Here's the shell of the full statement, but without the details on the IN statement, as I don't fully understand what you need. The table name in the original IN doesn't match the last one you posted.


SELECT RTRIM(SOP30200.SOPNUMBE) AS InvNo, RTRIM(IV00101.ITMCLSCD) AS ClassID, RTRIM(SOP30300.ITEMNMBR) AS Item, 
    CASE WHEN RTRIM(IV00101.ITMCLSCD) = 'ADMINFEE' THEN 'ADMIN FEE' ELSE MemberGroup END AS MbrGroup, 
    CASE WHEN RTRIM(IV00101.ITMCLSCD) = 'ADMINFEE' THEN 'Processing Fee' ELSE Description END AS Type, 
    RTRIM(SOP30300.ITEMDESC) AS InvItemDesc, 
    CASE WHEN SOP30200.SOPNUMBE LIKE 'RTN%' THEN XTNDPRCE * - 1 ELSE XTNDPRCE END AS ExtPrice, 
    SOP30200.DOCDATE AS PaymentDate, YEAR(SOP30200.DOCDATE) AS PaidYear, MONTH(SOP30200.DOCDATE) AS PaidMonth, 
    RTRIM(SOP30200.CUSTNMBR) AS CustNo, 
    CASE WHEN RTRIM(SOP30200.CUSTNMBR) LIKE 'PER%' THEN ' Individual' ELSE RTRIM(SOP30200.CUSTNAME) END AS OrgName, 
    CASE WHEN CONVERT(INT, dbo.GetNumeric(RTRIM(SOP30300.ITEMDESC))) = 0 THEN MembershipNumber 
         ELSE CONVERT(INT, dbo.GetNumeric(RTRIM(SOP30300.ITEMDESC))) 
         END AS MbrNoGP, 
    Person.LastName + ', ' + Person.FirstName AS Name, Person.MembershipNumber AS USFSANo, 
    RTRIM(SOP30200.CUSTNAME) AS GPName
FROM     USFSA.dbo.SOP30300 SOP30300 INNER JOIN
                  IV00101 IV00101 ON SOP30300.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
                  SOP30200 SOP30200 ON SOP30300.SOPNUMBE = SOP30200.SOPNUMBE LEFT OUTER JOIN
                  entity.Person Person ON SOP30200.CUSTNMBR = Person.Id LEFT OUTER JOIN
                  lookup.MemberTypes ON IV00101.ITEMNMBR = lookup.MemberTypes.ProductCode
WHERE  (lookup.MemberTypes.Id BETWEEN 1 AND 10 OR
                  lookup.MemberTypes.Id BETWEEN 22 AND 31 OR
                  lookup.MemberTypes.Id BETWEEN 41 AND 44 OR
                  lookup.MemberTypes.Id IS NULL) AND 
       (IV00101.ITMCLSCD IN ('DUES-MEMB', 'DUES BSCOA', 'DUES-BSMEM', 'DUES-FOFS', 'DUES-COACH', 'ADMINFEE')) AND
       (YEAR(SOP30200.DOCDATE) >= YEAR(SOP30200.DOCDATE) - 1) AND 
       (MONTH(SOP30200.DOCDATE) < 7) AND 
       (NOT (RTRIM(SOP30200.CUSTNMBR) LIKE 'TEA%')) AND 
       (IV00101.ITMCLSCD <> 'ADMINFEE' OR
        {<IN statement goes here>})

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000