CODEWITHSUNDEEP
sql-servert-sql
marcinn
marcinn

Reputation: 1938

Year data report using t-sql

I have got simple project data

Project     Year    Sum
512-17      2011    3147625,79
512-43      2011    652935,22
512-43      2012    23982,68
512-52      2012    584541,35
512-52      2013    3353388,73
512-52      2014    3935164,39

How to init result data from 2010 to current year like that

Project     Year    Sum
512-17      2010    0 -- if not exist in current data init with year and zero Cost
512-17      2011    3147625,79
512-17      2012    0
512-17      2013    0
512-17      2014    0
512-17      2015    0
512-43      2010    0
512-43      2011    652935,22
512-43      2012    23982,68
512-43      2013    0
512-43      2014    0
512-43      2015    0
512-52      2010    0
512-52      2011    0
512-52      2012    584541,35
512-52      2013    3353388,73
512-52      2014    3935164,39
512-52      2015    0

Upvotes: 1

Views: 61

Answers (4)

Dgan
Dgan

Reputation: 10295

using Recursive CTE

 with
 CTE
 as
 (
 select N=1,0 as d
 union all
 select N+1,2009+N as d from CTE where N<7
 )select distinct Tab.Project,d ,ISNULL(Tab1.[Sum],0) from CTE 
 CROSS JOIN   (SELECT DISTINCT Project
            FROM dbo.MyTable)   Tab
    LEFT JOIN MyTable Tab1
 on CTE.d=Tab1.[Year] 
 and Tab.Project=Tab1.Project
 where d!=0
 ORDER BY Tab.Project,d

Upvotes: 0

Lamak
Lamak

Reputation: 70678

This should do:

;WITH Years AS
(
    SELECT 2010+number [Year]
    FROM master.dbo.spt_values
    WHERE type = 'P'
    AND 2010+number <= YEAR(GETDATE())
)
SELECT  P.Project,
        Y.[Year],
        ISNULL(T.[Sum],0) [Sum]
FROM Years Y
CROSS JOIN (SELECT DISTINCT Project
            FROM dbo.YourTable) P
LEFT JOIN dbo.YourTable T
    ON Y.[Year] = T.[Year]
    AND P.Project = T.Project

You need to start with a list of every year that you want (I did this using a numbers table, but you should have a table with this information somewhere)

Upvotes: 1

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

select 
  project
 ,y.year
 ,Sum
from (select 2010 as year
      union all
      select 2011
      union all
      select 2012
      union all
      select 2013
      union all
      select 2014
      union all
      select 2015) y
left join pd 
  on y.year = pd.year
order by Project
        ,y.year

Upvotes: 0

Vishal Gajjar
Vishal Gajjar

Reputation: 1029

Just posted a similar code on another thread:

DECLARE @StartYear INT = 2010
DECLARE @EndYear INT = 2014

;WITH MyCte ([Year])
AS
(
    SELECT  [Year] = @StartYear
    UNION ALL
    SELECT  [Year] + 1 FROM MyCte WHERE [Year] < @EndYear
)
,ProjectList ([Project], [Year], [Sum])
AS
(
    SELECT '512-17', 2011, '3147625,79' UNION
    SELECT '512-43', 2011, '652935,22' UNION
    SELECT '512-43', 2012, '23982,68' UNION
    SELECT '512-52', 2012, '584541,35' UNION
    SELECT '512-52', 2013, '3353388,73' UNION
    SELECT '512-52', 2014, '3935164,39'
)
SELECT  DISTINCT P.[Project], M.[Year], [Sum] = 0
FROM    ProjectList P
CROSS APPLY (SELECT [Year] FROM MyCTE WHERE [Year] NOT IN (SELECT [Year] FROM ProjectList WHERE [Project] = P.[Project])) M

Upvotes: 0

Related Questions