We help IT Professionals succeed at work.

SQL script help with Group by

91 Views
Last Modified: 2019-04-22
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

Comment
Watch Question

yo_beeDirector of Information Technology
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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_beeDirector of Information Technology
CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.

Author

Commented:
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

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for your help John!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.