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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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