Aggregating Correlated Sub-Queries

  • Hi All,

    I have two queries I am combining with a UNION ALL, it is two sets of invoice data pretty simple. But I need a final SUM column based on the two sets column [>7Days] when it has '1' (it is a case statement). But you can't do a aggregate function on a sub query, so I'm stuck and not sure what the answer.

    SELECT

    f.Country

    ,f.CompanyName

    ,f.InvoiceID

    ,f.SupplierCode

    ,f.InvoiceNumber

    ,f.[Scan Date]

    ,f.[Approval Date]

    ,f.[Days to Final Approval]

    ,f.[<7 Days]

    ,f.[>7 Days]

    --,SUM(case when [>7 Days] = 1 Then count(invoiceid) else 0 end) as [count] --this one

    --,SUM(case when [>7 Days] = 1 Then 1 else 0 end) as [count] -- or this one

    ,@@ROWCOUNT

    ,f.Date

    FROM(

    SELECT

    Country

    ,Companyname

    ,invoiceid

    ,suppliercode

    ,invoicenumber

    ,CONVERT(varchar,scandate,103) as [Scan Date]

    ,CONVERT(varchar,BCCUploadDate,103) as [Approval Date]

    ,DATEDIFF (day, scandate, BCCUploadDate) - (2 * DATEDIFF(week, scandate, BCCUploadDate)) - CASE WHEN DATEPART(weekday, scandate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END

    - CASE WHEN DATEPART(weekday, BCCUploadDate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END as[Days to Final Approval]

    , CASE WHEN (DATEDIFF (day, scandate, BCCUploadDate) - (2 * DATEDIFF(week, scandate, BCCUploadDate)) - CASE WHEN DATEPART(weekday, scandate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END

    - CASE WHEN DATEPART(weekday, BCCUploadDate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END) <7 THEN 1 else 0 end as[<7 Days]

    , CASE WHEN (DATEDIFF (day, scandate, BCCUploadDate) - (2 * DATEDIFF(week, scandate, BCCUploadDate)) - CASE WHEN DATEPART(weekday, scandate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END

    - CASE WHEN DATEPART(weekday, BCCUploadDate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END) >=7 THEN 1 else 0 end as[>7 Days]

    --,@ausgreat7 as [greater7]

    --,datepart(YEAR,bccuploaddate) as [Year]

    --,datepart(Month,bccuploaddate) as [Month]

    --,datename(month,bccuploaddate) as [Month2]

    ,datename(YEAR,bccuploaddate) +' '+ datename(Month,bccuploaddate) as [Date]

    from [dbo].[AUS] h

    inner join [dbo].[Companies] c

    on h.company_id = c.company_id

    UNION

    SELECT

    Country

    ,Companyname

    ,invoiceid

    ,suppliercode

    ,invoicenumber

    ,CONVERT(varchar,scandate,103) as [Scan Date]

    ,CONVERT(varchar,BCCUploadDate,103) as [Approval Date]

    --,@acount as [Count1]

    ,DATEDIFF (day, scandate, BCCUploadDate) - (2 * DATEDIFF(week, scandate, BCCUploadDate)) - CASE WHEN DATEPART(weekday, scandate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END

    - CASE WHEN DATEPART(weekday, BCCUploadDate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END as[Days to Final Approval]

    , CASE WHEN (DATEDIFF (day, scandate, BCCUploadDate) - (2 * DATEDIFF(week, scandate, BCCUploadDate)) - CASE WHEN DATEPART(weekday, scandate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END

    - CASE WHEN DATEPART(weekday, BCCUploadDate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END) <7 THEN 1 else 0 end as[<7 Days]

    , CASE WHEN (DATEDIFF (day, scandate, BCCUploadDate) - (2 * DATEDIFF(week, scandate, BCCUploadDate)) - CASE WHEN DATEPART(weekday, scandate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END

    - CASE WHEN DATEPART(weekday, BCCUploadDate + @@DATEFIRST) = 1 THEN 1 ELSE 0 END) >=7 THEN 1 else 0 end as[>7 Days]

    --,@apacgreat7 as [greater7]

    --,datepart(YEAR,bccuploaddate) as [Year]

    --,datepart(Month,bccuploaddate) as [Month]

    --,datename(month,bccuploaddate) as [Month2]

    ,datename(YEAR,bccuploaddate) +' '+ datename(Month,bccuploaddate) as [Date]

    from [dbo].[APAC] h

    inner join [dbo].[Companies] c

    on h.company_id = c.company_id

    )f

    Group by country, companyname, invoiceid,suppliercode,invoicenumber,[Scan Date],[Approval Date],[Days to Final Approval],[<7 Days],[>7 Days],[date]

    order by country,CompanyName,InvoiceID,SupplierCode

    CountryCompanyNameInvoiceIDSupplierCodeInvoiceNumberScan DateApproval DateDays to Final Approval<7 Days>7 Days(No column name)Date

    Australia2D47236AMXTRA747786316/05/201630/05/2016 1001537222016 May

    Australia2D47237AMXTRA747786216/05/201630/05/2016 1001537222016 May

    Australia2D47266ORDEIN4054616/05/201630/05/2016 1001537222016 May

    SingaporeARC35453IHK9060P4803422/04/201626/04/2016 210537222016 April

    Thanks

  • Hi,

    Why are you maintaining separate db table for each region / country?

  • Is it as simple as this?

    ,CASE WHEN [>7 Days] = 1 THEN COUNT(invoiceid) ELSE 0 END AS InvoiceCount

    ,SUM(CASE WHEN [>7 Days] = 1 THEN 1 ELSE 0 END) as RowCount

    John

  • I don't think the need the GROUP BY on the outer query, just a SUM() OVER(), so maybe this?:

    SELECT

    f.Country

    ,f.CompanyName

    ,f.InvoiceID

    ,f.SupplierCode

    ,f.InvoiceNumber

    ,f.[Scan Date]

    ,f.[Approval Date]

    ,f.[Days to Final Approval]

    ,f.[<7 Days]

    ,f.[>7 Days]

    ,SUM([>7 Days]) OVER(PARTITION BY f.Country ,f.CompanyName ,f.InvoiceID ,f.SupplierCode ,f.InvoiceNumber

    ,f.[Scan Date] ,f.[Approval Date] ,f.[Days to Final Approval] ,f.Date ) AS [>7 Days Total]

    --,SUM(case when [>7 Days] = 1 Then count(invoiceid) else 0 end) as [count] --this one

    --,SUM(case when [>7 Days] = 1 Then 1 else 0 end) as [count] -- or this one

    ,@@ROWCOUNT

    ,f.Date

    FROM(

    SELECT

    ...

    UNION

    SELECT

    ...

    )f

    order by country,CompanyName,InvoiceID,SupplierCode

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thanks for the replies.

    None of these work and the @@rowcount is also only returning '1'

    ,SUM([>7 Days]) OVER(PARTITION BY f.Country ,f.CompanyName ,f.InvoiceID ,f.SupplierCode ,f.InvoiceNumber

    ,f.[Scan Date] ,f.[Approval Date] ,f.[Days to Final Approval] ,f.Date ) AS [>7 Days Total]

    ,SUM(case when [>7 Days] = 1 Then count(invoiceid) else 0 end) as [count]

    ,SUM(case when [>7 Days] = 1 Then 1 else 0 end) as [count]

  • Will some of these work for you?

    COUNT(case when [>7 Days] = 1 Then invoiceid else NULL end) as [Total >7 Days]

    ,COUNT(case when [>7 Days] = 1 Then NULL ELSE invoiceid end) as [Total Not >7 Days]

    ,COUNT(case when [<7 Days] = 1 Then invoiceid else NULL end) as [Total <7 Days]

    ,COUNT(case when [<7 Days] = 1 Then NULL ELSE invoiceid end) as [Total NOT <7 Days]

    I included different criteria combinations to let you verify if your <> 7 Days bits are computed correctly.

    _____________
    Code for TallyGenerator

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply