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!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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 G1.DebtorNo,G1.ReportName,
(
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
(
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
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
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...
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
;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
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...
Premium Content
You need an Expert Office subscription to comment.Start Free Trial