SQL query task

chandan m
chandan m used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
please post your table + sample data
and the result you are looking for...
Megan BrooksSQL Server Consultant

Commented:
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.

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
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...
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
chandan, is this issue solved?

Author

Commented:
Thank you...
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
chandan, please don't choose my comment as an answer. I didn't provide any solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial