In this example, cross-tabulation, or crosstab, on financial accounting of a company is considered.On financial accounting of a company, journal voucher is a unit that manages financial statement of a company.

In SQL, journal voucher can be considered as a table entry. That is , journal voucher table is composed of following columns; “ID”, “name of an account item”, “price of the item” and “transaction date”. Journal voucher table may contain a flag or type column that shows an item is recorded as credit, deposit, payment, and so on. The structure of journal voucher table can be figured as follow;

IDDatePriceItemType

For example,  an entry of the above journal voucher table is recorded as “10, 2019-04-01, 1,000 JPY, traveling fee, expense payment” , that means “Voucher ID 10: traveling fee  1,000 JPY was paid as expense payment in April 1st in 2019”.

Cross-tabulation, or crosstab query can calculate monthly total from such journal voucher table. In other words, crosstab query of monthly total converts a table such as

IDDatePriceItemType

to another table like

ItemAprilMayJune…March

Crosstab query

Crosstab query of monthly total in an JP fiscal year can be described as follow:

select item, 
    sum(case when m='04' then price else 0 end) as April, 
    sum(case when m='05' then price else 0 end) as May, 
    sum(case when m='06' then price else 0 end) as June, 
    sum(case when m='07' then price else 0 end) as July, 
    sum(case when m='08' then price else 0 end) as August, 
    sum(case when m='09' then price else 0 end) as September, 
    sum(case when m='10' then price else 0 end) as October, 
    sum(case when m='11' then price else 0 end) as November, 
    sum(case when m='12' then price else 0 end) as December, 
    sum(case when m='01' then price else 0 end) as January, 
    sum(case when m='02' then price else 0 end) as February, 
    sum(case when m='03' then price else 0 end) as June, 
    sum(price) as price
from
(
  SELECT
   (
     case when
       month(t.date) < 4
     then
       year(t.date)-1
     else
       year(t.date) end
   ) as jpfiscal,
   month(t.date) as m,
   t.price,
   t.item
  FROM
   vhrtransfer as t
  where
   t.isCredit=0
   having jpfiscal=2014
   order by year(t.date),m
 ) as s

group by item;

The procedure of this crosstab query is

  • The sub query labeled “s” generates a table of JP fiscal year 2014 from “vhrtransfer” table, where each account item is not labeled as credit.
  • “Group” by item, then, all columns of same account item are grouped in sub query “s”.
  • At last, the main query divides grouped items into each month column by case sentences.

Detail of Main / Sub queries

Sub query 1:  pulling data of the target JP Fiscal year from a sorted list

In Japan, fiscal year is composed of 2Q, 3Q, 4Q and 1Q of next year. The SELECT query labeled “jpfiscal” can be written by “case” and “having”, that
pulls data of a target JP fiscal year from a sorted list ; 

select
  (
   case when
     month(datecol) < 4
   then
     year(datecol)-1
   else
     year(datecol) end
 ) as jpfiscal
from
 sortedlist
having jpfiscal=2014

The format of “case” sentence is as follow:

case when condition then action1 else action2

If condition is True, action1 is extecuted, else, action2 is executed.

Sub query 2:  pulling data of the target JP fiscal year from an original accounting table.

Suppose, “vhrtransfer” table contains columns as follow:

IDisCredit 
(flag of credit)		price
(price of item)		date
(transaction date)		item
(account item)

“vhrtransfer” table contains “isCredit” column. If the isCredit flag is set, the account item is recorded as credit.

A sorted list for jpfiscal sub query should be created from “vhrtransfer” table by pulling such entries whose isCredit column is set as 0. The SQL query for creating “sortedlist” is as follow:

  FROM
   vhrtransfer as t
  where
   t.isCredit=0
   having jpfiscal=2014
   order by year(t.date),m

Main query: crosstab output of monthly total

Here, the table created by jpfiscal sub query would be calculated and sorted as a crosstab table. The image of crosstab table is as follow: 

itemApril
2014		May
2014		June
2014		…February
2015		March
2015

Pseudo SQL query for crosstab output of monthly total can be written as follow:

select  item,
　sum in April,
　sum in May,
　　　：
　sum in March
from
  table

Thus, the sum of each month should be calculated from each entry of the table generated by sub queries.

As an example of the case sentence of each month, the query for sum in April would be described as follow:

   sum(case when m='04' then price else 0 end) as April,

this sum calculation query means:

When the timestamp of an entry is in April, calculate sum of prices on the entry and use the sum of prices as a column, that is labeled as April. If the month of the timestamp is not April, add 0.   

Crosstab output query of monthly total would be made by writing such sum calculation query for each month.