July 5, 2016 at 1:04 am
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
July 5, 2016 at 2:59 am
Hi,
Why are you maintaining separate db table for each region / country?
July 5, 2016 at 3:17 am
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
July 5, 2016 at 3:40 pm
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".
July 5, 2016 at 6:26 pm
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]
July 5, 2016 at 9:59 pm
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