SQL query task

Hi ,
how to add a field for the month so we can see a trend month by month since Jan 2016. for the same query i changed  
DATEDiff to month but i need a output grouping in month by month average so i could get it as below format

Row Labels      Average of DaysDiff
2016                       2.7
Qtr1                       3.1
Jan                               3.0
Feb                               2.9
Mar                       3.3
chandan mAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
please post your table + sample data
and the result you are looking for...
0
Megan BrooksSQL Server ConsultantCommented:
It doesn't sound difficult to do, but it is a little hard to visualize exactly what you are looking for from just that information.

You can often add an extra column to a result set without affecting the existing rows by using a correlated subquery -- SELECT col = ( SELECT  xxx FROM yyy WHERE innerKey = outerKey) -- or a window function -- SELECT aggregate() OVER ( PARTITION BY ... ORDER BY ... ), or some such thing.
0
chandan mAuthor Commented:
select G1.DebtorNo,G1.ReportName,G1.CD,G1.SetupDate,G2.CancelDate,DATEDIFF(DAY,G1.SetupDate,G2.CancelDate) as DaysDiff  FROM
(
select a.debtorno,b.Reportname,a.cd,a.Setupdate,a.loginid
from #Debtor a join #client b
on a.Client = b.PK_Client
Where a.Client like 'CH%'
and a.SetupDate >= '2016-01-01'
and a.LoginID like 'ACS2'
) G1 Join
(
select a.debtorno,b.Reportname,a.cd,a.CancelDate,a.loginid
from #Debtor a join #client b
on a.Client = b.PK_Client
Where a.Client like 'CH%'
and a.LoginID like 'ACS'
) G2 on G1.ReportName = G2.ReportName and G1.CD = G2.CD


For the above query i need to  see a trend month by month since Jan 2016 with below format,

Row Labels      Average of DaysDiff
2016                       2.7
Qtr1                       3.1
Jan                               3.0
Feb                               2.9
Mar                       3.3
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark WillsTopic AdvisorCommented:
Interesting query... Some aspects seem to be a little redundant / repetitive, and so, I have to assume that it has been simplified to hide (obfuscate) the real query... And in saying that, I mean no disrespect whatsoever... Maybe some dummy data would help as well.

Also, you haven't mentioned what version of SQL Server you are running. The samples below will work from SQL2008 onwards.

For your needs, you should look at a few new functions to add into your query. They are :
1) datename
2) group by ... with rollup
3) grouping_id
4) avg
5) common table expression ( CTE )

Explanations for AVG and GROUPING_ID can be found at : https://docs.microsoft.com/en-us/sql/t-sql/functions/grouping-id-transact-sql and you can also check/search the other functions there as well

And as an example, I will over simplify your query by way of an example query

But first, let us create and populate a sample table
if object_id('tempdb..#table','U') is not null drop table #table
go

create table #table
(
  PK_Name varchar(10),
  SetupDate datetime,
  CancelDate datetime
)

go

insert into #table values
('Jack',  '20160101','20160103'),
('Jill',  '20160201','20160205'),
('Fred',  '20160301','20160303'),
('Wilma', '20160101','20160103'),
('Barney','20160201','20160207'),
('Betty', '20160301','20160309'),
('Fred',  '20160401','20160402'),
('Wilma', '20160401','20160403'),
('Jack',  '20170101','20170102'),
('Jill',  '20170101','20170103'),
('Barney','20170201','20170207'),
('Betty', '20170301','20170309')

go

Open in new window

Now we have some sample data, we can look at those new functions :
;with mth_cte as
(
  select PK_Name,SetupDate, CancelDate
       , datediff(day,SetupDate,CancelDate) as DaysDiff
       , datename(month,SetupDate) as Month_Name
       , datename(year,SetupDate) as Year_Name
       , datename(quarter,SetupDate) as Quarter_Name
  From #table

) select Case when grouping_id(Year_Name, Quarter_Name, Month_Name) = 1 then 'Qtr '+ Quarter_Name
              when grouping_id(Year_Name, Quarter_Name, Month_Name) = 3 then Year_Name
              when grouping_id(Year_Name, Quarter_Name, Month_Name) = 7 then 'Grand Total'
         else Month_Name end as 'Period'
       , avg(DaysDiff) as AVG_DaysDiff
  From mth_cte
  Group by Year_Name, Quarter_Name, Month_Name with rollup
 -- Having grouping_id(Year_Name, Quarter_Name, Month_Name) < 7  -- to filter out the grand total if needed
  Order by Year_Name, Quarter_Name, Month_Name

Open in new window


See how you go with the above example and then we can discuss in more detail. For example, do you need to accommodate missing periods ? You might need to fabricate months first (or use a calendar table). Also, the AVG function shows as INT because datediff() returns INT and might want to convert to a decimal  basis...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
chandan, is this issue solved?
0
chandan mAuthor Commented:
Thank you...
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
chandan, please don't choose my comment as an answer. I didn't provide any solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.