We help IT Professionals succeed at work.

SQL query task

chandan m
chandan m asked
on
80 Views
Last Modified: 2017-07-13
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

HainKurtSr. System Analyst
CERTIFIED EXPERT

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
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
chandan, is this issue solved?

Author

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

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions