How to find the average of certain records T-SQL

Question

I have a table variable that I am dumping data into:

DECLARE @TmpTbl_SKUs AS TABLE
(
    Vendor VARCHAR  (255),
    Number VARCHAR(4),
    SKU VARCHAR(20),
    PurchaseOrderDate DATETIME,
    LastReceivedDate DATETIME,
    DaysDifference INT
)

Some records don't have a purchase order date or last received date, so the days difference is null as well. I have done a lot of inner joins on itself, but data seems to take too long, or comes out incorrect most of the time.

Is it possible to get the average per SKU days difference? how would I check if there is only 1 record of that SKU? I need the data, if there is only 1 record, then I have to find it at a champvendor level the average.

Here is the structure:

Vendor has many Numbers and Numbers has many SKUs

Any help would be great, I can't seem to crack this one, nor can I find anything related to this online. Thanks in advance.

Here is some sample data:

Vendor       Number   SKU     PurchaseOrderDate              LastReceivedDate       DaysDifference

OTHER PMDD       1111         OP1111   2009-08-21 00:00:00.000    2009-09-02 00:00:00.000    12
OTHER PMDD        1111         OP1112   2009-12-09 00:00:00.000    2009-12-17 00:00:00.000    8
MANTOR             3333         MA1111   2006-02-15 00:00:00.000    2006-02-23 00:00:00.000    8
MANTOR             3333         MA1112   2006-02-15 00:00:00.000    2006-02-23 00:00:00.000    8

I'm sorry I may have written this wrong. If there is only 1 SKU for a record, then I want to return the DaysDifference (if it's not null), if it has more than 1 record and they are not null, then return the average days difference. If it is all nulls, then at a vendor level check for the average of the skus that are not null, otherwise it should just return 7. This is what I have tried:

SELECT t1.SKU, ISNULL
(
    AVG(t1.DaysDifference), 
    (
        SELECT ISNULL(AVG(t2.DaysDifference), 7)
        FROM @TmpTbl_SKUs t2
        WHERE t2.SKU=t1.SKU
        GROUP BY t2.ChampVendor, t2.VendorNumber, t2.SKU
    )
)
 FROM @TmpTbl_SKUs t1
 GROUP BY t1.SKU

Keep playing with this. I somewhat have what I got, but just don't understand how I would check if it has multiple records, and how to check at a vendor level.

I don't really follow your descriptive text. Can you provide some rows of sample data and desired results? — Martin Smith
And also would you please post your attempts at solving the problem? So we didn't try to offer you solutions that didn't work for you. Or so we could see what could be done about improving yours. — Andriy M
I'm sorry. I wrote it partly wrong. I hope that makes more sense. My queries are failing at some points because then it returns too many records to retrieve. It's driving me nuts. — pqsk
I just updated that. Wow I am just super tired. I do want a group by. I've got another solution working, it's just taking so long. Not so sure if there would just be a better approach to this. — pqsk

Andriy M Andriy M · Accepted Answer · 2011-02-07T22:19:24

Try this:

EDITED: added NULLIF(..., 0) to treat 0s as NULLs.

SELECT
  t1.SKU,
  COALESCE(
    NULLIF(AVG(t1.DaysDifference), 0),
    NULLIF(t2.AvgDifferenceVendor, 0),
    7
  ) AS AvgDiff
FROM @TmpTbl_SKUs t1
  INNER JOIN (
    SELECT Vendor, AVG(DaysDifference) AS AvgDifferenceVendor
    FROM @TmpTbl_SKUs
    GROUP BY Vendor
  ) t2 ON t1.Vendor = t2.Vendor
GROUP BY t1.SKU, t2.AvgDifferenceVendor

EDIT 2: how I tested the script.

For testing I'm using the sample data posted with the question.

DECLARE @TmpTbl_SKUs AS TABLE
(
    Vendor VARCHAR  (255),
    Number VARCHAR(4),
    SKU VARCHAR(20),
    PurchaseOrderDate DATETIME,
    LastReceivedDate DATETIME,
    DaysDifference INT
)

INSERT INTO @TmpTbl_SKUs
      (Vendor,       Number, SKU,      PurchaseOrderDate,         LastReceivedDate,    DaysDifference)
SELECT 'OTHER PMDD', '1111', 'OP1111', '2009-08-21 00:00:00.000', '2009-09-02 00:00:00.000', 12
UNION ALL
SELECT 'OTHER PMDD', '1111', 'OP1112', '2009-12-09 00:00:00.000', '2009-12-17 00:00:00.000', 8
UNION ALL
SELECT 'MANTOR',     '3333', 'MA1111', '2006-02-15 00:00:00.000', '2006-02-23 00:00:00.000', 8
UNION ALL
SELECT 'MANTOR',     '3333', 'MA1112', '2006-02-15 00:00:00.000', '2006-02-23 00:00:00.000', 8;

First I'm running the script on the unmodified data. Here's the result:

SKU                  AvgDiff
-------------------- -----------
MA1111               8
MA1112               8
OP1111               12
OP1112               8

AvgDiff for every SKU is identical to the original DaysDifference for every SKU, because there's only one row per each one.

Now I'm changing DaysDifference for SKU='MA1111' to 0 and running the script again. Ther result is:

SKU                  AvgDiff
-------------------- -----------
MA1111               4
MA1112               8
OP1111               12
OP1112               8

Now AvgDiff for MA1111 is 4. Why? Because the average for the SKU is 0, and so the average by Vendor is taken, which has been calculated as (0 + 8) / 2 = 4.

Next step is to set DaysDifference to 0 for all the SKUs of the same Vendor. In this case I'm setting it for SKUs MA1111 and MA1112. Here's the result of the script for this change:

SKU                  AvgDiff
-------------------- -----------
MA1111               7
MA1112               7
OP1111               12
OP1112               8

So now AvgDiff is 7 for both MA1111 and MA1112. How has it become so? Both have DaysDifference = 0. That means that the average by Vendor should be taken for each one. But Vendor average is 0 too in this case. According to the requirement, the average here should default to 7, which is what the script has returned.

So the script seems to be working correctly. I understand that it's either me having missed something or you having forgotten to mention some details. In any case, I would be glad to see where this script fails to solve your problem.

How to find the average of certain records T-SQL

1 Answers