Data source (Query)

An overview of how to extract desired output of data from tables using the power of SQL Query

This data source is created using the SQL queries which are commands used to retrieve specific information from data tables. They allow you to search, sort, filter, and summarize data stored in tables by specifying criteria, such as keywords or conditions, that must be met for the data to be returned. SQL queries can be used to extract data for a variety of purposes, such as generating reports, creating charts, and performing data analysis.

Let's understand how to play the tables with a query and extract desired output with a simple example:

Assume two tables "Orders" & "Order Items"

Table 1 - Orders Table

Table 2 - Order Items

Now, to query the data, you can use a SQL SELECT statement. For example, to get the total quantity of each product sold, you can use the following query:

SELECT "Product Name", SUM("Quantity") AS Total_Quantity
FROM {{Order Items}}
GROUP BY "Product Name"

The result will be as given below:

The above example is a simple one. However, the developers can use advanced queries as well, as given below.

WITH t_account_info AS (
  select 
    h."BS Name", 
    c."GL Account Category Name", 
    h."Flag Name", 
    g."GL Account Group Name", 
    a."GL Account Name", 
    a."Company GL Account ID ", 
    a."Opening Balance"
  from 
    {{Finance.BS Heads}} as h, 
    {{Finance.GL Accounts Category}} as c, 
    {{Finance.GL Accounts Groups}} as g, 
    {{Finance.GL Accounts}} as a
 where h."Company BS Head ID" = c."Company BS Head ID"  
   AND c."Company GL Account Category ID" = g."Company GL Account Category ID" 
   AND CAST(g."Company GL Account Groups ID" as varchar) = a."Company GL Account Group ID"  
   and a."GL Account Name" = @"Account"
), 
t_opening AS (
  select 
    a."GL Account Name",
    a."Company GL Account ID ",
    sum(i."Dr Amount")::Numeric as "dr_amount", 
    sum(i."Cr Amount")::Numeric as "cr_amount", 
    sum(
      CASE 
        WHEN h."Flag Name" = 'Dr' then i."Dr Amount" - i."Cr Amount" 
        WHEN h."Flag Name" = 'Cr' then i."Cr Amount" - i."Dr Amount" 
      END
    )::Numeric as "Account Balance" 
  from 
    t_account_info as h,
    {{Finance.GL Accounts}} as a, 
    {{Finance.JV Items}} as i, 
    {{Finance.Journal Vouchers}} as j 
 where CAST(a."Company GL Account ID " as varchar) = i."GL Account ID"
   and i."JVI ID" = j."JV ID App" 
   and a."GL Account Name" = @"Account" 
   and j."Created Date" < @"From Date"
GROUP BY 
   a."GL Account Name",
   a."Company GL Account ID "
),
t_opening_balance AS (
  select 
    ROW_NUMBER() over() as "seq", 
    h."BS Name", 
    h."GL Account Category Name", 
    h."Flag Name", 
    h."GL Account Group Name", 
    h."GL Account Name", 
    'Opening Balance' as "Particulars", 
    to_date(@"From Date" , 'YYYY-MM-DD')-1 as "txn_date", 
    '' as "JV Item ID", 
    coalesce(o."dr_amount",0) as "dr_amount", 
    coalesce(o."cr_amount",0) as "cr_amount", 
    coalesce(o."Account Balance",0) + coalesce(h."Opening Balance", 0) as "Account Balance"
  from t_account_info h
       left join t_opening o
       on h."Company GL Account ID " = o."Company GL Account ID "
), 
t_txn_details AS (
  select 
    * 
  from 
    t_opening_balance 
  union all 
  select 
    ROW_NUMBER() over() + 1 as "seq", 
   h."BS Name", 
    c."GL Account Category Name", 
    h."Flag Name", 
    g."GL Account Group Name", 
    a."GL Account Name", 
    j."Voucher Type" as "Particulars", 
    j."Created Date" as "txn_date", 
    i."JVI ID" as "JV Item ID", 
    i."Dr Amount" as "dr_amount", 
    i."Cr Amount" as "cr_amount", 
    CASE 
        WHEN h."Flag Name" = 'Dr' then coalesce(i."Dr Amount", 0) - coalesce(i."Cr Amount", 0)
        WHEN h."Flag Name" = 'Cr' then coalesce(i."Cr Amount", 0) - coalesce(i."Dr Amount", 0)
    END as "Account Balance"
 from 
    {{Finance.BS Heads}} as h, 
    {{Finance.GL Accounts Category}} as c, 
    {{Finance.GL Accounts Groups}} as g, 
    {{Finance.GL Accounts}} as a, 
    {{Finance.JV Items}} as i, 
    {{Finance.Journal Vouchers}} as j 
where h."Company BS Head ID" = c."Company BS Head ID"  
  AND c."Company GL Account Category ID" = g."Company GL Account Category ID" 
  AND CAST(g."Company GL Account Groups ID" as varchar) = a."Company GL Account Group ID"  
  AND CAST(a."Company GL Account ID " as varchar) = i."GL Account ID"  
  and i."JVI ID" = j."JV ID App" 
  and a."GL Account Name" = @"Account"
  and j."Created Date" between @"From Date" and @"To Date" 
ORDER by 
    1, 8
), 
t_closing as (
  select 
    seq, 
    "GL Account Name", 
    txn_date, 
    "JV Item ID", 
    "Particulars", 
    sum(coalesce(cr_amount,0)) credit, 
    sum(coalesce(dr_amount,0)) debit, 
    sum(
        sum("Account Balance")
--      sum(CASE WHEN "Flag Name" = 'Dr' then coalesce(dr_amount,0) - coalesce(cr_amount,0) WHEN "Flag Name" = 'Cr' then coalesce(cr_amount,0) - coalesce(dr_amount,0) END)
    ) 
      over (partition by "GL Account Name" order by "GL Account Name", seq
    ) closing 
  from 
    t_txn_details 
  group by 
    seq, 
    "GL Account Name", 
    txn_date, 
    "JV Item ID", 
    "Particulars"
) 
select 
  seq, 
  "GL Account Name", 
  "JV Item ID", 
  case when seq = 1 then null else to_date(to_char(txn_date, 'dd-mm-yyyy'),'dd-mm-yyyy') end as "Transaction Date", 
  "Particulars", 
  CASE when seq = 1 then null else lag(closing, 1, 0) over (
    partition by "GL Account Name" 
    order by 
      "GL Account Name", 
      seq
  ) END as "Opening", 
  case when seq = 1 then null else credit end as "Credit", 
  case when seq = 1 then null else debit end as "Debit", 
  closing as "Balance"
from 
  t_closing

This above query joins four tables from a financial database and creates several derived tables to generate a report showing the opening balance, transaction details, and closing balance for a particular account over a specified time period.

The first derived table (t_account_info) retrieves metadata about the account such as its name, group, category, and opening balance. The second derived table (t_opening) summarizes journal voucher items related to the account to get the total debits, credits, and account balance as of the specified start date.

The third derived table (t_opening_balance) uses the results of the first two derived tables to create a row with the opening balance. The fourth derived table (t_txn_details) retrieves transaction details for the account, including the journal voucher item ID, debit amount, credit amount, and account balance.

Finally, the t_closing derived table groups the transaction details by account and calculates the closing balance as the sum of all previous account balances plus the current debits and credits.

NOTE

1. The initial step for referencing a data table in SQL involves specifying the workspace and providing the name of the corresponding data table. This can be accomplished by using the format {{workspace.data table}}.
2. When defining a parameter in SQL, including the "@" symbol at the beginning of the parameter is essential. This helps to indicate that the following text represents a parameter rather than a regular piece of data.

Ask Caddie to Generate and Refine Lengthy Queries

Admins can use AI(Ask Caddie) to generate lengthy queries by providing plain text instructions, simplifying the query-writing process. If the initial result isn't satisfactory, they can refine their instructions iteratively until they get the desired query. When creating a data source, just enter your prompt in the AI option at the top of the query data source creation page, and the AI will generate the query for you.