Help with SQL 1

[primagic]

I have the following sql:

select  c.FullCodeDescription,
            l.[Date],
            COUNT(l.Curr1) as Volume,
            d.clientcode,
            sum(l.Curr1) as value
from openquery([bcw-apps],'select bcwref,clientcode,code from dial_attempts_js where clientcode in (''SWWAGED'',''SWWAGEDSME'') and code in (26,59,63) and jobdate between cast(floor( cast( dateadd(day,-7,getDate()) AS float) )AS datetime) and cast(floor( cast( dateadd(day,-1,getDate()) AS float) )AS datetime) ') d
inner join BusinessSupportProfiling..CompletionCodes c ON d.code = c.Code
inner join BusinessSupportProfiling..DailyOutboundCallingList l ON d.bcwref COLLATE DATABASE_DEFAULT = l.BCWRef COLLATE DATABASE_DEFAULT
where CONVERT(datetime,substring(l.[Date],7,4) + substring(l.[Date],4,2) + substring(l.[Date],1,2)) between cast(floor( cast( dateadd(day,-7,getDate()) AS float) )AS datetime) and cast(floor( cast( dateadd(day,-1,getDate()) AS float) )AS datetime)
group by c.FullCodeDescription,l.[date],d.clientcode

ORDER BY Date Desc

Which gives me these results:

Description Date Vol Client Sum
------------
Call back scheduled 24/03/2014 2 SWWAGED 4516.04
Data Protection Act Refusal - RPC 24/03/2014 1 SWWAGED 2083.13
Data Protection Act Refusal - RPC 21/03/2014 1 SWWAGED 2083.13

What I need is:

Description Date Vol Client Sum
------------
Call back scheduled 24/03/2014 2 SWWAGED 4516.04
Data Protection Act Refusal - RPC 24/03/2014 1 SWWAGED 2083.13

I need to retrieve the most recent record (by date) and group.

Essentially from my data I need to group by description and group by date.

Then I need to get only the latest records by group with the most recent dates, count them and sum the values

 

[imex]

Try:

with CTE_RN as
(
    select  c.FullCodeDescription,
            l.[Date],
            COUNT(l.Curr1) as Volume,
            d.clientcode,
            sum(l.Curr1) as value,
            ROW_NUMBER() OVER(PARTITION BY c.FullCodeDescription ORDER BY l.[Date] DESC) as RN
    from openquery([bcw-apps],'select bcwref,clientcode,code from dial_attempts_js where clientcode in (''SWWAGED'',''SWWAGEDSME'') and code in (26,59,63) and jobdate between cast(floor( cast( dateadd(day,-7,getDate()) AS float) )AS datetime) and cast(floor( cast( dateadd(day,-1,getDate()) AS float) )AS datetime) ') d
    inner join BusinessSupportProfiling..CompletionCodes c ON d.code = c.Code
    inner join BusinessSupportProfiling..DailyOutboundCallingList l ON d.bcwref COLLATE DATABASE_DEFAULT = l.BCWRef COLLATE DATABASE_DEFAULT
    where CONVERT(datetime,substring(l.[Date],7,4) + substring(l.[Date],4,2) + substring(l.[Date],1,2)) between cast(floor( cast( dateadd(day,-7,getDate()) AS float) )AS datetime) and cast(floor( cast( dateadd(day,-1,getDate()) AS float) )AS datetime)
    group by c.FullCodeDescription,l.[date],d.clientcode
)

select * from CTE_RN
where RN = 1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[primagic]

Thank you. That is spot on.

So now I have used your code for another statement and I want to combine them together with UNION.

How do I combine these two statements

with CTE_RN as
(
    select  c.FullCodeDescription,
            l.[Date],
            COUNT(l.Curr1) as Volume,
            d.clientcode,
            sum(l.Curr1) as value,
            ROW_NUMBER() OVER(PARTITION BY c.FullCodeDescription ORDER BY l.[Date] DESC) as RN
    from openquery([bcw-apps],'select bcwref,clientcode,code from dial_attempts_js where clientcode in (''SWWAGED'',''SWWAGEDSME'') and code in (26,59,63) and jobdate between cast(floor( cast( dateadd(day,-7,getDate()) AS float) )AS datetime) and cast(floor( cast( dateadd(day,-1,getDate()) AS float) )AS datetime) ') d
    inner join BusinessSupportProfiling..CompletionCodes c ON d.code = c.Code
    inner join BusinessSupportProfiling..DailyOutboundCallingList l ON d.bcwref COLLATE DATABASE_DEFAULT = l.BCWRef COLLATE DATABASE_DEFAULT
    where CONVERT(datetime,substring(l.[Date],7,4) + substring(l.[Date],4,2) + substring(l.[Date],1,2)) between cast(floor( cast( dateadd(day,-7,getDate()) AS float) )AS datetime) and cast(floor( cast( dateadd(day,-1,getDate()) AS float) )AS datetime)
    group by c.FullCodeDescription,l.[date],d.clientcode
)

select * from CTE_RN
where RN = 1

with CTE_RN as
(

select  'No Contact' As FullCodeDescription, Max(l.[Date]) As [Load Date], COUNT(l.Curr1) AS Volume, d.clientcode, SUM(l.Curr1) AS value, ROW_NUMBER() OVER(PARTITION BY 'No Contact' ORDER BY l.[Date] DESC) as RN
from openquery([bcw-apps],'select bcwref,clientcode,code from dial_attempts_js where clientcode in (''SWWAGED'',''SWWAGEDSME'') and jobdate between cast(floor( cast( dateadd(day,-7,getDate()) AS float) )AS datetime) and cast(floor( cast( dateadd(day,-1,getDate()) AS float) )AS datetime) ') d
inner join BusinessSupportProfiling..CompletionCodes c ON d.code = c.Code
inner join BusinessSupportProfiling..DailyOutboundCallingList l ON d.bcwref COLLATE DATABASE_DEFAULT = l.BCWRef COLLATE DATABASE_DEFAULT
where CONVERT(datetime,substring(l.[Date],7,4) + substring(l.[Date],4,2) + substring(l.[Date],1,2)) between cast(floor( cast( dateadd(day,-7,getDate()) AS float) )AS datetime) and cast(floor( cast( dateadd(day,-1,getDate()) AS float) )AS datetime)

group by l.[date],d.clientcode
)
select * from CTE_RN
where RN = 1

Normally I just added union in between both statements but this doesnt work with the CTE code added.

 

[primagic]

Got it working. thanks for your help