Not sure if this is possible 1

[Snakeroot]

I'm trying to get an average weight (stored in the quantity column of itemkey 'refr%') of the contents of a particular line item(TS:3050) and also the count of TS:3050. I have one table that lists credit memo header info and that joins to another table that shows transaction line details.

That part is easy enough.

The part I'm having difficulty with is I need to calculate the average weight of line item type TS:3050 ONLY when it's the only TS:# type on each invoice.

There are numerous other types of TS:# types, for instance TS:92125, TS:50, TS:240, etc. not to mention other non-related line item types, such as recov, other, disposal, etc.

So, if one particular invoice has TS:3050 AND TS:240, then I need to exclude that invoice from my calculation. If the invoice has only TS:3050 (and any other non TS: type line item) then it needs to be part of my calculation to find the average weight. The weight column is identified as "quantity".

So a valid credit memo that I want to calculate based off of might have this information:

Invoice #1505
12/3/07

(Itemkey) (Description) (Quantity)
Location Description of location
TS:3050 TS 3050 done 1
Refr:134 4

In the example above, I would use TS:3050's quantity of 1 as part of my average of the count of TS:3050 and I would use Refr:134's quantity of 4 to use as part of my average of 'Refr:%' quantity (I don't care what the number is after Refr. It could be Refr:22, Refr:12, Refr:410... it doesn't matter).



An invoice I wouldn't want as part of my calculation would look something like this:

Invoice #1504
12/2/07

(Itemkey) (Description) (Quantity)
Location Description of location
TS:3050 TS 3050 done 1
TS:240 TS 240 done 300
Refr:12 1,300

In the example above, because there's another TS:# type (TS:240) in addition to TS:3050, I can't use the quantity of TS:3050 in my calculation. I can ONLY use the invoices where there's ONLY TS type of TS:3050.


In the end, I would like an average number of TS:3050 and an average number of quantity of line item type 'Refr:%'.

Can someone give me a clue on how I might go about getting the results I'm looking for?


To get a list of all invoices within the date range that are listed on 2 possible CM templates, and that have the item key TS:3050 in them at all, I would do this:

SELECT CM.Refnumber, CM.franchise, CMLD.itemref_fullname
FROM CreditMemo CM JOIN CreditMemoLineDetail CMLD ON 
CM.IDKey = CMLD.TxnID
WHERE (CM.txnDate >='1/1/2007' AND CM.txnDate <= '12/31/2008')
      AND 
      (CM.templateref_fullname = 'CM-TS' OR CM.templateref_fullname = 'CM-WS')
      AND 
      (itemref_fullname = 'TS:3050')

I'm unclear about how I would ONLY use the creditmemos that ONLY have the TS type of TS:3050 in the invoice.

Any nudges in the right direction would be greatly appreciated.

 

[gmmastros]

First, let's think about COUNT. Count returns the number of rows, right?

If you COUNT(*), you will get a pure row count. If you COUNT(ColumnName), you will get a count of the columns where 'ColumnName' is not null.

We can use this to our advantage!

The following query should return ONLY those invoices??? where all of the itemref_fullname = 'TS:3050'

[tt][blue]
Select TxnId
From CreditMemoLineDetail
Group By TxnId
Having Count(Case When itemref_fullname = 'TS:3050' Then 1 Else NULL End) = Count(*)
[/blue][/tt]

Let's take a closer look at the having clause.

[tt]
Having [!]Count([/!][blue]Case When itemref_fullname = 'TS:3050' Then 1 Else NULL End[/blue][!])[/!] = Count(*)
[/tt]

If the itemref_fullname = 'TS:3050', then we use the value 1, otherwise, NULL. Since we are counting these, when the value is 1, count will increment by 1, otherwise it won't. We then compare this to Count(*). If the numbers are the same, we found an invoice that ONLY has 'TS:3050'.

I encourage you to run that query. Once you are satisfied that it is ONLY returning the rows you want, then you can make that query become a derived table which is used to filter out all the rest of the data.

Like this...

SELECT CM.Refnumber, CM.franchise, CMLD.itemref_fullname
FROM   CreditMemo CM 
       JOIN CreditMemoLineDetail CMLD 
          ON CM.IDKey = CMLD.TxnID
       [!]Inner Join ([/!]
          [blue]Select TxnId
          From   @CMLD
          Group By TxnId
          Having Count(Case When itemref_fullname = 'TS:3050' Then 1 Else NULL End) = Count(*)[/blue]
          [!]) As AliasName
          On CM.IDKey = AliasName.TxnId[/!]
WHERE (CM.txnDate >='1/1/2007' AND CM.txnDate <= '12/31/2008')
      AND 
      (CM.templateref_fullname = 'CM-TS' OR CM.templateref_fullname = 'CM-WS')
      AND 
      (itemref_fullname = 'TS:3050')

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

That last query has a table variable that I was playing around with when I was testing this. It should really be...

SELECT CM.Refnumber, CM.franchise, CMLD.itemref_fullname
FROM   CreditMemo CM 
       JOIN CreditMemoLineDetail CMLD 
          ON CM.IDKey = CMLD.TxnID
       Inner Join (
          Select TxnId
          From   [!]CreditMemoLineDetail [/!]
          Group By TxnId
          Having Count(Case When itemref_fullname = 'TS:3050' Then 1 Else NULL End) = Count(*)
          ) As AliasName
          On CM.IDKey = AliasName.TxnId
WHERE (CM.txnDate >='1/1/2007' AND CM.txnDate <= '12/31/2008')
      AND 
      (CM.templateref_fullname = 'CM-TS' OR CM.templateref_fullname = 'CM-WS')
      AND 
      (itemref_fullname = 'TS:3050')

Sorry about the confusion.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Snakeroot]

Okay, I see what you're doing... however, that is showing the credit memos that ONLY have TS:3050 and nothing else.

I need to find the credit memos that have TS:3050 and no other kind of TS:#. It can and usually will still have some other line items such as recov, other, disposal, etc.

I'm wondering if your solution would work with different qualifiers in the case statement. I'm not very familiar with case statements in SQL, so I'll try that and see what I come up with.

Thanks for your help!

 

[Snakeroot]

Also, I meant to say, if you see my refer to invoice, I meant to type creditmemo. Because of a software limitation, we have to use credit memo templates in certain scenarios for invoices. So in my mind they're invoices, but in SQL they're credit memos

 

[gmmastros]

Try this...

Select txnId
From   CreditMemoLineDetail 
Group By TxnId
Having Count(Case When itemref_fullname = 'TS:3050' Then 1 
                  When itemref_fullname Not Like 'TS:%' Then 1
                  Else NULL End) = Count(*)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Snakeroot]

Thanks George! That worked great. I was able to pull the numbers I needed and I learned a new SQL technique... bonus!