SQL script help with Group by

abarefoot
abarefoot used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
yo_beeDirector of Information Technology

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

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

CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

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

Here is the case-statement that does not restrict data:
WITH CTE_count AS
(	SELECT	po_hdr.supplier_id
	,	po_count	= COUNT(DISTINCT CASE WHEN po_hdr.date_created >= DATEADD( day, -365, GETDATE() ) THEN po_hdr.po_no END)
	,	line_count	= COUNT(DISTINCT CASE WHEN po_hdr.date_created >= DATEADD( day, -365, GETDATE() ) THEN po_line.po_line_uid END)
	FROM	po_hdr
	JOIN	po_line	ON po_hdr.po_no = po_line.po_no
	WHERE	po_line.delete_flag <> 'y'
	AND	po_line.cancel_flag <> 'y'
	AND	po_hdr.delete_flag <> 'y'
	GROUP
	BY	po_hdr.supplier_id
)
SELECT	buyer	= p21_view_contacts.first_name + ' ' + p21_view_contacts.last_name
,		p21_view_supplier.supplier_id
,		p21_view_supplier.supplier_name
,		po_count = ISNULL(CTE_count.po_count,0)
,		line_count = ISNULL(CTE_count.line_count,0)
FROM	p21_view_supplier
JOIN	p21_view_contacts	ON p21_view_supplier.buyer_id = p21_view_contacts.id
JOIN	CTE_count		ON p21_view_supplier.supplier_id = CTE_count.supplier_id
WHERE	p21_view_supplier.delete_flag = 'n'

Open in new window

Author

Commented:
Thanks for your help John!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial