0

Can anyone please help me on below to get the consolidated ServiceID with the sum of File Size associated with it over an year.

Complete Code (For reference)

If(OBJECT_ID('tempdb..#temp1') Is Not Null)
Begin
    Drop Table #Temp1
End

GO

If(OBJECT_ID('tempdb..#temp2') Is Not Null)
Begin
    Drop Table #Temp2
End
GO

If(OBJECT_ID('tempdb..#temp3') Is Not Null)
Begin
    Drop Table #Temp3
End

GO

If(OBJECT_ID('tempdb..#temp4') Is Not Null)
Begin
    Drop Table #Temp4
End
GO

SELECT *
INTO #Temp1
FROM
(
SELECT 
SUBSTRING(ServiceName,2,3) AS ServiceID 
      , FileTotalSize as FileSize 
      ,year = '1980' from table1 
    ) a

--1981
SELECT *
INTO #Temp2
FROM
(
SELECT 
SUBSTRING(ServiceName,2,3) AS ServiceID 
      , FileTotalSize as FileSize 
      ,year = '1981'
        from table2 
    ) b

--1982
SELECT *
INTO #Temp3
FROM
(
SELECT 
SUBSTRING(ServiceName,2,3) AS ServiceID 
      , FileTotalSize as FileSize
      ,year = '1982' from table3
) c

--1983

SELECT *
INTO #Temp4
FROM
(
SELECT 
SUBSTRING(ServiceName,2,3) AS ServiceID 
      , FileTotalSize as FileSize 
      ,year = '1983' from table4 
    ) d


select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize1 from #Temp1
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize2 from #Temp2
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize3 from #Temp3
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize4 from #Temp4

Below are the 4 queries existing in #temp tables I finally worked and their outputs:

select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize1 from #Temp1
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize2 from #Temp2
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize3 from #Temp3
select ROW_NUMBER() OVER(ORDER BY year ASC) AS ID,ServiceID,year,FileTotalSize AS FileSize4 from #Temp4

Output of the above 4 queries:

enter image description here

My Question : How can I get the below output using the above 4 queries ? Please help me , the way I need to join . I tried my way to join them but sure it is wrong so reached out for help.

enter image description here

Thank you

  • 2
    Is your data static for years 1980, 1981, 1982 and 1983 or will you query serve up random years? – Scott Hodgin Aug 21 '18 at 14:12
  • Hi @ScottHodgin , data is not static , the values for filesize differs for years . Can be more entries of ServiceID as well. – DBOn Aug 21 '18 at 14:15
  • Please update your question to include actual table definitions and sample data in the form of INSERT statements so we don't have to type this stuff up ourselves. To be clear, your data can have more than 4 distinct years? For example, you could have unlimited number of years? – Scott Hodgin Aug 21 '18 at 14:19
  • Sorry for that , I will include now . and there are only 4 distinct years but the data can differ , I mean the filesizes over each year because of multiple service IDs each year. – DBOn Aug 21 '18 at 14:23
  • @ScottHodgin Updated my question with complete code block for reference – DBOn Aug 21 '18 at 14:35
1

Here is an example of how this could be done.

Declare @Query1 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query2 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query3 table (id int, ServiceID int, Year int, FileSize1 int)
Declare @Query4 table (id int, ServiceID int, Year int, FileSize1 int)

insert into @Query1(id,ServiceID,Year,FileSize1) values
(1,121,1980,10),
(2,122,1980,20),
(3,123,1980,30),
(4,121,1980,40)

insert into @Query2(id,ServiceID,Year,FileSize1) values
(1,121,1981,10),
(2,122,1981,20),
(3,123,1981,30),
(4,123,1981,40)

insert into @Query3(id,ServiceID,Year,FileSize1) values
(1,121,1982,10),
(2,122,1982,20),
(3,124,1982,30),
(4,125,1982,40)

insert into @Query4(id,ServiceID,Year,FileSize1) values
(1,123,1983,10),
(2,121,1983,20),
(3,122,1983,30),
(4,121,1983,40)

;With _cte as
(
select ServiceID,Year,FileSize1 as FileSize1 from @Query1 
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query2 
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query3 
union all
select ServiceID,Year,FileSize1 as FileSize1 from @Query4 
)

select ServiceID,
sum(case when year = 1980 then FileSize1 end) as FileSize_1980,
sum(case when year = 1981 then FileSize1 end) as FileSize_1981,
sum(case when year = 1982 then FileSize1 end) as FileSize_1982,
sum(case when year = 1983 then FileSize1 end) as FileSize_1983
 from _cte
group by ServiceID

| ServiceID | FileSize_1980 | FileSize_1981 | FileSize_1982 | FileSize_1983 |
|-----------|---------------|---------------|---------------|---------------|
| 121       | 50            | 10            | 10            | 60            |
| 122       | 20            | 20            | 20            | 30            |
| 123       | 30            | 70            | NULL          | 10            |
| 124       | NULL          | NULL          | 30            | NULL          |
| 125       | NULL          | NULL          | 40            | NULL          |
  • Thank you @Scott Hodgin , I will check on this code and update . Thanks for your help. – DBOn Aug 21 '18 at 15:20
  • My miss @Scott , thank you much for the help , it fixed my issue and accepted it as answer, – DBOn Aug 28 '18 at 8:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.