SQL With

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;

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s