CODEWITHSUNDEEP
sqljoin
Pankaj Gupta
Pankaj Gupta

Reputation: 53

Getting Latest Date with field count

I have 2 Tables to Join

Subscription having column CustomerID
Report Pull having columns CustomerId and ReportDt

I want to have result as columns 

CustomerId   LastReportPullDt    ReportCount

Where

LastReportPullDt = Latest Report Pulled Date
ReportCount = Report Count for Customer

My Condition is to check for customers who have a record created for today in Report Pull Table and also that customer should have atleast another record present so that the report pulled today is 2nd time or nth time report has been pulled where n>1.

Example Data
Table Subscription

CustomerId 

C1
C2
C3
C4

Report Pull Table
CustomerId ReportDt

C1            19-Oct-2016
C1            01-Oct-2016
C1            17-Sep-2016
C2            18-Oct-2016
C2            01-Sep-2016
C3            19-Oct-2016

The query returns only C1 as record, latest date as 19th Oct and 3 as count. This is because only C1 satisfies that report date is today, and count >1 for no. of reports.

Upvotes: 1

Views: 412

Answers (4)

connectedsoftware
connectedsoftware

Reputation: 7097

This query will only return customers who last ran the report today and have run the report more than once in total: 

SELECT C.Id,
       MAX(RP.PullDt) as LastReportPullDt, 
       COUNT(*) AS ReportCount
 FROM Customer C
 INNER JOIN ReportPull RP ON C.CustomerId = RP.CustomerId
 GROUP BY C.CustomerId
 HAVING COUNT(*) > 1 -- MORE THAN ONE ATTEMPT AT RUNNING THE REPORT
 AND MAX(RP.PullDt) >= dateadd(dd,0, datediff(dd,0,GETUTCDATE())) -- THE LAST ATTEMPT IS TODAY

Just for extra info, this part:

dateadd(dd,0, datediff(dd,0,GETUTCDATE()))

simply removes the time from the date/time returned from GETUTCDATE() so that you are comparing any reports run from the start of the today (i.e. midnight).

Upvotes: 1

McNets
McNets

Reputation: 10807

Try something like this.

If you don't need customer_name, you can avoid using join part.

select
    reports.customer_id
    , count(reports.*) as num_records
    , max(ReportDt)
    , customers.customer_name
from reports
    left join customers
        on reports.customer_id = customers.customer_id
group by 
    reports.customer_id, customers.customer_name
having 
    count(*) >= min_required_reports

Upvotes: 0

Alex H
Alex H

Reputation: 300

Try: 

select * from (
  select 
    customerid, 
    max(reportdt) as LastReportPullDt, 
    count(*) as ReportCount 
  from "Report Pull" 
  group by customerid
) where LastReportPullDt='2016-10-19' and ReportCount>1

Upvotes: 0

Shrikant Kumbhar
Shrikant Kumbhar

Reputation: 46

; with temp as 
(
    select  s.CustomerId, ReportDt, Rank() over (partition by s.CustomerId order by ReportDt desc) r
    from    Subscription    s
    join    ReportPull  r   on s.CustomerId = r.CustomerId
)
    select  *
    from    temp
    where   r = 1

Upvotes: 0

Related Questions