Using mssql:

pulling data out of air from the system tables:

select top 5 ROW_NUMBER() over(order by a.name) + 2014 as yr from sys.all_objects a

above query gives you 5 rows with column yr with values 2015-2019

WITH ds (idx, FISCAL_YR_VAL, FISCAL_YY) as

(

select

ROW_NUMBER() over (order by years.yr) as idx, yr as FISCAL_YR_VAL , yr-2000 as FISCAL_YY

from (select top 5 ROW_NUMBER() over(order by a.name) + 2014 as yr from sys.all_objects a) years

where years.yr < 2017 +1

union

select ROW_NUMBER() over (order by years.yr) as idx, yr+8 as FISCAL_YR_VAL, yr-2000 + 8 as FISCAL_YY

from (select top 5 ROW_NUMBER() over(order by a.name) + 2014 as yr from sys.all_objects a) years

where years.yr < 2017 +1

)

Select * from ds ;

Create more data

select CONCAT(‘ABC’, CONVERT(varchar(10), (ROW_NUMBER() over (order by years.yr)) )) as code,

CASE WHEN yr % 2 = 0 THEN ‘Apple’ WHEN yr % 2 = 1 THEN ‘Android’ END as typ

, yr as ItemCount , years.yr + 14 as amt

from (select top 5 ROW_NUMBER() over(order by a.name) as yr from sys.all_objects a) years

where years.yr < 2017 +1

union

select CONCAT(‘ABC’, CONVERT(varchar(10), (ROW_NUMBER() over (order by years.yr)) )) as code

,

CASE WHEN (yr+7) % 2 = 0 THEN ‘Android’ WHEN (yr+7) % 2 = 1 THEN ‘Apple’ END as typ

, yr + 8 as ItemCount , years.yr + 14 as amt

from (select top 5 ROW_NUMBER() over(order by a.name) as yr from sys.all_objects a) years

where years.yr < 2017 +1

The following grouping

To sum up the totals per code

SELECT code,SUM(ItemCount) AS ItemCount,Typ,SUM(amt) AS Amount

FROM

ds

GROUP BY code, typ;

To sum up the total amt of bill , but give total number of items per code

SELECT

Code, SUM(ItemCount) ItemCount,Typ,

(SELECT SUM(Amt) FROM ds) Amount

FROM

ds

GROUP BY Code, Typ;

### Like this:

Like Loading...