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
Country CompanyName InvoiceID SupplierCode InvoiceNumber Scan Date Approval Date Days to Final Approval <7 Days >7 Days (No column name) Date
Australia 2D 47236 AMXTRA 7477863 16/05/2016 30/05/2016 10 0 1 53722 2016 May
Australia 2D 47237 AMXTRA 7477862 16/05/2016 30/05/2016 10 0 1 53722 2016 May
Australia 2D 47266 ORDEIN 40546 16/05/2016 30/05/2016 10 0 1 53722 2016 May
Singapore ARC 35453 IHK9060 P48034 22/04/2016 26/04/2016 2 1 0 53722 2016 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