September 16, 2009 at 7:48 am
Hi All,
I am trying the add the date difference using subquery and sum function but getting the error.
Query is :--
select
age.COMPANY_NAME,
age.CUSTOMER_NUM,
c.business_unit_desc,
c.GEO_LEVEL3 as 'Region',
c.business_group_desc,
c.segment_desc,
p.PAYCODE_DESC,
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) < 1 then age.ar else 0 end) as [DLQ_EOM_CURRENT],
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) between 1 and 30 then age.ar else 0 end) as [DLQ_EOM_1_30],
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) between 31 and 60 then age.ar else 0 end) as [DLQ_EOM_31_60],
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) between 61 and 90 then age.ar else 0 end) as [DLQ_EOM_61_90],
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) between 91 and 120 then age.ar else 0 end) as [DLQ_EOM_91_120],
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) between 121 and 150 then age.ar else 0 end) as [DLQ_EOM_121_150],
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) between 151 and 180 then age.ar else 0 end) as [DLQ_EOM_151_180],
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) between 181 and 360 then age.ar else 0 end) as [DLQ_EOM_181_360],
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) > 0 then age.ar else 0 end) as [DLQ_EOM_PAST_DUE],
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) > 90 then age.ar else 0 end) as [DLQ_EOM_90+],
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) > 150 then age.ar else 0 end) as [DLQ_EOM_150+],
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) > 360 then age.ar else 0 end) as [DLQ_EOM_360+],
age.CREDIT_LIMIT_CURRENCY_CODE,
age.LOCAL_CREDIT_LIMIT
from dbo.ar_aging_denormal age with(nolock)
inner join dbo.PAYCODE_DESCRIPTIONS p on age.payment_code_1 = p.payment_code_1 and age.business_unit_id = p.business_unit_id
inner join dbo.channels_liberty c on age.business_unit_id = c.business_unit_id and age.local_channel_customer = c.local_channel
where
(c.region in ('EE') AND c.business_group_desc = 'GP' )
group by
age.COMPANY_NAME,
age.CUSTOMER_NUM,
c.business_unit_desc,
c.GEO_LEVEL3,
c.business_group_desc,
c.segment_desc,
p.PAYCODE_DESC,
age.CREDIT_LIMIT_CURRENCY_CODE,
age.LOCAL_CREDIT_LIMIT
September 16, 2009 at 9:57 am
What is the error you are getting? Or does the query runs successfully but doesn't give you the expected results?
September 16, 2009 at 10:10 am
getting the error on :
sum(case when (age.days_dlq + datediff(dd,(select max(invoice_date) from AR_HEADER WHERE BUSINESS_UNIT_ID IN (SELECT BUSINESS_UNIT_ID FROM CHANNELS_LIBERTY WHERE REGION = 'EMEA')),'2009-september-25')) < 1 then age.ar else 0 end) as [DLQ_EOM_CURRENT],
i am trying to find the differnce between 25th sept and max invoice date availbale in table and then add this value to the age.days_dlq field.
I hope this clarifies your doubbt.
September 16, 2009 at 10:14 am
Error :-
Cannot perform an aggregate function on an expression containing an aggregate or a subquery
September 16, 2009 at 10:39 am
Your syntax is off in many different ways. Take a look at this thread, I just fixed a very similar query for someone else last week.
http://www.sqlservercentral.com/Forums/Topic785369-338-1.aspx
September 16, 2009 at 11:01 am
Seth is right, the query is way off and will be really slow.
But I was wondering why can't you put the difference between 25th sept and max invoice date available in an integer variable and use that variable in the query. It seems there is no relationship between the dbo.AR_HEADER and dbo.ar_aging_denormal tables and the date difference is a constant. Is that the case or am I reading it wrong?
-Supriya
September 16, 2009 at 11:58 pm
I tried doing it by using variables... but i need run this query behind excel sheet using MS query...
when i run the query using integer variables, it's running fine in MS Query but date is getting exported to excel sheet.. i think this is bacause of using variable.
due to this i had to combine this query...
Any suggestion...............?
September 17, 2009 at 12:01 am
I tried doing it by using variables... but i need run this query behind excel sheet using MS query...
when i run the query using integer variables, it's running fine in MS Query but date is getting exported to excel sheet.. i think this is bacause of using variable.
due to this i had to combine this query...
Any suggestion...............?
September 17, 2009 at 11:49 am
I'm not sure I understand what you're saying. Did you have a look at the link I posted? Your query wasn't even close to a running state before, so if you've fixed all that, repost what you have now and what exactly your current problem is.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply