Link to home
Start Free TrialLog in
Avatar of chandan m
chandan m

asked on

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
Avatar of HainKurt
HainKurt
Flag of Canada image

please post your table + sample data
and the result you are looking for...
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.
Avatar of chandan m
chandan m

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
chandan, is this issue solved?
Thank you...
chandan, please don't choose my comment as an answer. I didn't provide any solution.