Home How can I get the days from latest to oldest
Reply: 0

How can I get the days from latest to oldest

user3233
1#
user3233 Published in July 17, 2018, 7:22 pm

first time using something like this for assistance so I am not sure if I am doing this correctly but here goes...

I am trying to get my dates to go from the most recent to the oldest. When I try Order by [Day] Desc or Asc it doesnt display correctly.

here is a link to my current display as it is right now

https://imgur.com/a/9P9hz

can anyone help please?

DECLARE @StartDate DATETIME = '2018/03/13'
DECLARE @NumDays INT = 11


DECLARE @cols AS NVARCHAR(MAX),
             @query  AS NVARCHAR(MAX),
             @query1  AS NVARCHAR(MAX),
             @query2  AS NVARCHAR(MAX),
             @query3 AS NVARCHAR(MAX),
             @query4 AS NVARCHAR(MAX),
             @query5 AS NVARCHAR(MAX) 



;with cte as (          
    SELECT 
    CAST(DAY(SentTime) AS VARCHAR(MAX)) [Day]
                 --,CAST((DATENAME(DAY, SentTime)) AS INT) as SentDay 
                 ,isnull(count(id),0) 'Total'
                 ,Sum(Case when isnull(IsDeleted,'') <> '' Then 1 else 0 end) Deleted
                 ,Sum(Case when isnull(CallLogTime,'') = '' and isnull(IsDeleted,'')=''  Then 1 else 0 end) 'Not Logged'
                ,Sum(Case when isnull(SentTime,'') = '' and isnull(IsDeleted,'')=''  Then 1 else 0 end) 'Received'
                 ,Sum(Case when isnull(CallLogTime,'') <> '' and isnull(IsDeleted,'')=''  Then 1 else 0 end) Logged

    FROM tblMailItems
    WHERE DATEDIFF(dd, SentTime, GETDATE()) <= @NumDays
    group by DAY(SentTime) --,CAST((DATENAME(DAY, SentTime)) AS INT) 

),
cte2 as (
    select * from cte
)

select * 
into #X
from cte
union
select 'Row Total', sum(Total), sum(Deleted), sum([not logged]), sum(Received), sum(Logged) from cte2

--drop table #X

select @cols = STUFF((SELECT ',' + QUOTENAME([Day]) 
                    from #X
                    group by DAY
                    order by DAY asc

                                   FOR XML PATH('')
            ), 1, 1, '')

                    --select * from #x 

set @query1 = 'select 1 Sequence, ''Total Logged'' [Description], ' + @cols + '
from 
(
  select [day], total
  from #X
) src
pivot
(
  sum(total)
  for Day in (' + @Cols + ')
) p'



set @query2 = ' UNION select 2 Sequence, ''Deleted'' [Description], ' + @cols + '

from 
(
  select [day], Deleted
  from #X
) src
pivot
(
  sum(Deleted)
  for Day in (' + @Cols + ')
) p'


Select  @query = @query1 + @query2



set @query3 = ' UNION select 3 Sequence, ''Not Logged'' [Description], ' + @cols + ' 
from 
(
  select [day], [Not Logged]
  from #X
) src
pivot
(
  sum([Not Logged])
  for Day in (' + @Cols + ')
) p'

select  @query = @query1 + @query2 + @query3


set @query4 = ' union select 4 sequence, ''Received'' [description], ' + @cols + ' 
from 
(
  select [day], [Received]
  from #x
) src
pivot
(
  sum([Received])
  for day in (' + @cols + ')
) p'

select  @query = @query1 + @query2 + @query3 + @query4


set @query5 = ' UNION select 5 Sequence, ''Logged'' [Description], ' + @cols + ' 
from 
(
  select [day], [Logged]
  from #X
) src
pivot
(
  sum([Logged])
  for Day in (' + @Cols + ')
) p'

select  @query = @query1 + @query2 + @query3 + @query5

execute  (@query)
drop table #x
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.581175 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO