asked on

SQL script help with Group by

This is something that I've had trouble with over the years. In the script below I need to move the date script in the where statement into the select. so I want to the the last 365 days for po_count and line_count. This way I see all suppliers even if they have a 0. The issue is the group by as I don't want to add date_created into the group by or it make a different line for each date and I want the sum so one line per supplier.

This is my case statment but requires me to put po_hdr.date_created into the group by which is not what I want.

case when ( po_hdr.date_created >= Getdate() - 365 ) then  Count(DISTINCT po_hdr.po_no) else 0 end AS po_count

Open in new window

case when ( po_hdr.date_created >= Getdate() - 365 ) then  Count(DISTINCT po_line.po_line_uid) else 0 end AS po_count

Open in new window

SELECT DISTINCT p21_view_contacts.first_name + ' '
                + p21_view_contacts.last_name       AS buyer,
                p21_view_supplier.supplier_id,
                p21_view_supplier.supplier_name,
                Count(DISTINCT po_hdr.po_no)        AS po_count,
                Count(DISTINCT po_line.po_line_uid) AS line_count
FROM   p21_view_supplier
       INNER JOIN p21_view_contacts
               ON p21_view_supplier.buyer_id = p21_view_contacts.id
       INNER JOIN po_hdr
               ON p21_view_supplier.supplier_id = po_hdr.supplier_id
       INNER JOIN po_line
               ON po_hdr.po_no = po_line.po_no
WHERE  ( p21_view_supplier.delete_flag = 'n' )
      -- AND ( po_hdr.date_created >= Getdate() - 365 )  
       AND ( po_hdr.requested_by IN (SELECT buyer_id
                                     FROM   abix_buyer_list) )
       AND ( po_line.delete_flag <> 'y' )
       AND ( po_line.cancel_flag <> 'y' )
       AND ( po_hdr.delete_flag <> 'y' )
GROUP  BY p21_view_supplier.supplier_id,
          p21_view_supplier.supplier_name,
          p21_view_contacts.first_name + ' '
          + p21_view_contacts.last_name
ORDER  BY buyer,
          po_count DESC

Open in new window

yo_bee

You can use DATEDIFF function

Datediff(day,po_hdr.date_created,Getdate()) >= 365

Open in new window

SELECT DISTINCT p21_view_contacts.first_name + ' '
                + p21_view_contacts.last_name       AS buyer,
                p21_view_supplier.supplier_id,
                p21_view_supplier.supplier_name,
                Count(DISTINCT po_hdr.po_no)        AS po_count,
                Count(DISTINCT po_line.po_line_uid) AS line_count
FROM   p21_view_supplier
       INNER JOIN p21_view_contacts
               ON p21_view_supplier.buyer_id = p21_view_contacts.id
       INNER JOIN po_hdr
               ON p21_view_supplier.supplier_id = po_hdr.supplier_id
       INNER JOIN po_line
               ON po_hdr.po_no = po_line.po_no
WHERE  ( p21_view_supplier.delete_flag = 'n' )
      AND ( DateDiff(day,po_hdr.date_created,getdate()) >= 365 )  
       AND ( po_hdr.requested_by IN (SELECT buyer_id
                                     FROM   abix_buyer_list) )
       AND ( po_line.delete_flag <> 'y' )
       AND ( po_line.cancel_flag <> 'y' )
       AND ( po_hdr.delete_flag <> 'y' )
GROUP  BY p21_view_supplier.supplier_id,
          p21_view_supplier.supplier_name,
          p21_view_contacts.first_name + ' '
          + p21_view_contacts.last_name
ORDER  BY buyer,
          po_count DESC

Open in new window

abarefoot

ASKER

Not sure how this is getting me what I want. SO this script would be getting me the difference between date_created and getdate() where they are greater then 365? Not sure
I really want to pull all suppliers no mater if they have a po or po line created. and I want count the last 365 days of PO count and line count.

yo_bee

I like to work with two tables first and then work my way out. So for you query I would just use po_hdr and po_line
So all I am doing here is counting distinct po_no and po_line_uid where the date _created was within the last year and grouping by po_line.po_line_uid. Once I like what I see I will start building out the inner join

SELECT distinct  po_line.po_line_uid, count(DISTINCT po_hdr.po_no)        AS po_count,
                Count(DISTINCT po_line.po_line_uid) AS line_count
FROM   po_hdr inner join  po_line
               ON po_hdr.po_no = po_line.po_no
WHERE  (DateDiff(day,po_hdr.date_created,getdate()) <= 365 )  
         AND ( po_line.delete_flag <> 'y' )
       AND ( po_line.cancel_flag <> 'y' )
       AND ( po_hdr.delete_flag <> 'y' )
group by po_line.po_line_uid
ORDER  BY count(DISTINCT po_hdr.po_no) DESC

Open in new window

John_Vidmar

If you wrap a field in a function-call then any index on that field is ignored. Better performance if you compare a field to a constant:

AND	po_hdr.date_created >= DATEADD( day, -365, GETDATE() )

Open in new window

abarefoot

ASKER

Yo_Bee
The script you have would give me the same count for line and po. Not sure I can do that. I maybe can split them up and use a CTE for line and then a different CTE for PO. Not really sure.

abarefoot

ASKER

I did the below but still not getting what I want. This only shows suppliers that have something in the last 365 days but if they don't they are left off the report. This is why I wanted to do the case statement. So if there was a po in the last 365 then count it if not then put a 0. but either way they still show up in the report. Make since?

WITH CTE_count(supplier_id, po_no, po_line_uid, po_count, line_count)
     AS (SELECT DISTINCT po_hdr.supplier_id,
                         po_hdr.po_no,
                         po_line.po_line_uid,
                         Count(DISTINCT po_hdr.po_no)        AS po_count,
                         Count(DISTINCT po_line.po_line_uid) AS line_count
         FROM   po_hdr
                INNER JOIN po_line
                        ON po_hdr.po_no = po_line.po_no
         WHERE  ( Datediff(day, po_hdr.date_created, Getdate()) <= 365 )
                AND ( po_line.delete_flag <> 'y' )
                AND ( po_line.cancel_flag <> 'y' )
                AND ( po_hdr.delete_flag <> 'y' )
         GROUP  BY po_hdr.supplier_id,
                   po_hdr.po_no,
                   po_line.po_line_uid)
SELECT DISTINCT p21_view_contacts.first_name + ' '
                + p21_view_contacts.last_name AS buyer,
                p21_view_supplier.supplier_id,
                p21_view_supplier.supplier_name,
                Count(CTE_count_1.po_count)   AS Expr1,
                Count(CTE_count_1.line_count) AS Expr2
FROM   p21_view_supplier
       INNER JOIN p21_view_contacts
               ON p21_view_supplier.buyer_id = p21_view_contacts.id
       INNER JOIN CTE_count AS CTE_count_1
               ON p21_view_supplier.supplier_id = CTE_count_1.supplier_id
WHERE  ( p21_view_supplier.delete_flag = 'n' )
GROUP  BY p21_view_supplier.supplier_id,
          p21_view_supplier.supplier_name,
          p21_view_contacts.first_name + ' '
          + p21_view_contacts.last_name

Open in new window

ASKER CERTIFIED SOLUTION

John_Vidmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

abarefoot

ASKER

Thanks for your help John!