troubleshooting Question

SQL script help with Group by

Avatar of abarefoot
abarefoot asked on
Microsoft SQL ServerMicrosoft Server OSSSRSSQL* Sql server Report builder
8 Comments1 Solution102 ViewsLast Modified:
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
case when ( po_hdr.date_created >= Getdate() - 365 ) then  Count(DISTINCT po_line.po_line_uid) else 0 end AS po_count


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 
ASKER CERTIFIED SOLUTION
John_Vidmar

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros