Union / Join two tables for sales+payroll reporting

Hi,

I'm relatively new to this, so this may be convoluted, but I'm hoping someone can give me a hand on this. Trying to relate/join/union (not sure the best method here) two tables with select conditions.

Here is the layout, as I've got a few tables with sales-related data.

"Sales Ledger" is actual transactions (where there are multiple lines per TX, some that show individual items, some that summarize everything, and some that just have payment info). Importantly, each sale has a unique "receipt number" and a datetime.

"Item Details" is like a product catalog with item cost, supplier, etc. (related to Sales Ledger via item SKU field)

"Payroll" is a table of pay dates, amounts, employee, employee status (hourly or salaried), and a column of "pay periods" (basically just positive numbers from 1 to 26). There is no unique ID column in this file and the same employee may have multiple amounts listed in different rows for the same day.

There is another table called "Pay Periods" which lists the "Pay Period" number (basically an ID) in a column next to columns for "Start Date," "End Date," and "Pay Date". After failing to get the larger picture to work though I just manually copied over the pay period numbers to the "Payroll" table to try and make it easier. But the SQL below uses this table, as I have a feeling it is needed long-term, as data is added.

I'm trying to create a chart of total sales vs total payroll for a given month (or pay period, if that is the only way to make it work)...with the ability to user filter payroll amounts by employee type (so basically hide, or only show, salaried payroll amounts in the chart vs hourly).

1) The "Sales Ledger" select statement has a restriction WHERE "Sales Ledger"."Line Type" = 'Sale Line' so it doesn't double count sale amounts. Just an internal thing because the table contains lots of additional sale info.

2) I've built a query that works and the data returned in the simple table looks correct. However, no matter how I try, I can't get this data to Join/Union/Lookup/otherwise relate to payroll amount sums for the given Pay Period. That query is:

SELECT

"Sales Ledger"."Date" as S_Date,

"Sales Ledger"."Receipt Number" as 'S_Receipt Number',

"Sales Ledger"."Total" as S_Total,

"Pay Periods"."Pay Period" as 'Pay Period'

FROM "Sales Ledger",

"Pay Periods"

WHERE "Sales Ledger"."Line Type" = 'Sale'

AND"Sales Ledger"."Date" BETWEEN "Pay Periods"."Start" and "Pay Periods"."End"

That seems to return a correct list of individual "Receipt Numbers" (i.e. sales), their datetime, total amount, and the correct pay period each sale occurred in. So I can chart total Sales by Pay Period.

But I can't tie this data into payroll data. So I need to sum all "Sales Ledger"."Total" in a given "Pay Periods"."Pay Period", then sum all "Payroll"."Amount" within the same "Pay Periods"."Pay Period".

I'm guessing there is some sort of UNION or JOIN statement needed in the above query?

Sorry for the long post, but any help is appreciated!

Access your files securely from anywhere

Zoho Developer Community

New to Zoho LandingPage?

Access Zoho LandingPage

Zoho LandingPage Resources

New to Zoho Survey?

Access Zoho Survey

Latest Feature Updates

Explore our Pricing & Plans

Zoho Survey Resources

Android Mobile Surveys

Insurvey Blogs

Insight Blogs

Tips & Tricks

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Bigin?

Signup Now

Access Bigin

Latest Feature Updates

Zoho Desk Resources

Desk Community Learning Series
Digest
Functions
Meetups
Kbase
Resources
Glossary
Desk Marketplace
MVP Corner
Word of the Day

Tout forum

France ZUGs

Zoho SalesIQ Resources

Topic Participants
daniel
Vignesh Nehru

Sticky Posts
Announcing Zoho Analytics 6.0 Beta!
We are delighted to open up the next major version, Zoho Analytics 6.0 Beta! The new version comes packed with a wide range of functionalities for all persona, namely business users, data analysts, data engineers, and data scientists. Zoho Analytics team
What's New in Zoho Analytics - August 2024
Hello Users! We are back with the latest updates and enhancements made to Zoho Analytics. Keep reading to learn more about them. Connect to the data hosted in the cloud without allow-listing the IP addresses Utilize Zoho Databridge to connect to the data
We are coming to your city! Zoho Analytics Community Meetup
Hello, business leaders and data enthusiasts! We are delighted to announce that registrations are now open for the ZUG meetups, and we can't wait for you to be a part of them. Our in-house analytics experts are geared up to lead discussions on constructing
Zoho Analytics: 2021 Look Back
As we start a new year in 2022, here's some of our top moments from 2021. Zoho Analytics in 2021
[Customer Talk] PREMO Group's Analyst Interview at Zoho Day 2022
Premo Group, a 50 year old Spanish Manufacturing Company, has been our long-standing customer with #ZohoAnalytics. They've been using our platform for their end-to-end, unified business analytics solution. Hear more from Claudio Cabeza, Director at PREMO