SQL query task
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
Sr. System Analyst
and the result you are looking for...
SQL Server Consultant
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.
(
select a.debtorno,b.Reportname,a.
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.
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
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy 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.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
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