Click here to Skip to main content
14,927,434 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have two table (sales_main, sales_sreturned_main) , based on this tables i created two views to group every one of them by (day,weak,month,year).

view_sales_invoices_summery: sales
C#
------------------------------------------------------------------------
smain_Date --- YEAR_smain_Date_ --- (others columns )--- Qunt --- TotalValue -- (others columns )
------------------------------------------------------------------------


view_rsales_invoices_summery: returns sales
C#
------------------------------------------------------------------------
sretmain_Date --- YEAR_sretmain_Date_ --- (others columns )--- Qunt --- TotalValue -- (others columns )
------------------------------------------------------------------------


But how to create a view that show all (sales and returns) in one row if the date the same and zero if there is no data like (Briefly):
if I have this data
Sales
C#
------------------------------------------------------------------------
date ------- Sales Value -- others columns
2021/2/1 --- 3000        --
2021/2/1 --- 2000        --
2021/2/2 --- 3500        -- 
2021/2/2 --- 1500        -- 
2021/2/2 --- 2000        --
------------------------------------------------------------------------

Returns
C#
------------------------------------------------------------------------
date ------- Sales Value -- others columns
2021/2/1 --- 2000        --
2021/2/3 --- 500         --
------------------------------------------------------------------------



i want to show them like :
C#
------------------------------------------------------------------------
date -- year -- month -- weak -- day -- sales Qunt -- Sales Value -- ret.qunt -- ret.value --- 
date1-- 2021 -- 2     -- 1    -- 1   -- 2          -- 5000        -- 1        -- 2000      --- 
date2-- 2021 -- 2     -- 1    -- 2   -- 3          -- 7000        -- 0        -- 0         --- 
date3-- 2021 -- 2     -- 1    -- 3   -- 0          -- 0           -- 1        -- 500         --- 
------------------------------------------------------------------------


What I have tried:

I created the views to group sales table and returns table but I don't how to create one view to group both of them

sales
CREATE OR REPLACE VIEW `view_sales_invoices_summery` AS SELECT 
smain_Date,
YEAR(smain_Date), MONTH(smain_Date), WEEK(smain_Date), DAY(smain_Date),
r_user_ID,
r_branche_ID,
cust_ID,
COUNT(smain_ID) as Qunt,
smain_TotalValue as TotalValue,
smain_ExtraValue as ExtraValue,
smain_DiscValue as DiscValue,
smain_NetValue as NetValue,
smain_PaidValue as PaidValue,
smain_ReminValue as ReminValue,
sTotalProfits as TotalProfits
FROM sales_main
GROUP BY YEAR(smain_Date), MONTH(smain_Date), WEEK(smain_Date), DAY(smain_Date),r_user_ID,
r_branche_ID,
cust_ID,
smain_Date,
TotalValue,
ExtraValue,
DiscValue,
NetValue,
 PaidValue,
ReminValue,
TotalProfits



returns

CREATE OR REPLACE VIEW `view_rsales_invoices_summery` AS SELECT 
	YEAR(sretmain_Date), MONTH(sretmain_Date), WEEK(sretmain_Date), DAY(sretmain_Date),
	sretmain_Date,
    r_user_ID,
r_branche_ID,
vendor_ID,
    COUNT(sretmain_ID) as Qunt,
	sretmain_TotalValue as TotalValue,
	sretmain_ExtraValue as ExtraValue,
	sretmain_DiscValue as DiscValue,
	sretmain_NetValue as NetValue,
	sretmain_PaidValue as PaidValue,
	sretmain_ReminValue as ReminValue
	FROM sales_sreturned_main
	GROUP BY YEAR(sretmain_Date), MONTH(sretmain_Date), WEEK(sretmain_Date), DAY(sretmain_Date),r_user_ID,
r_branche_ID,
vendor_ID,
sretmain_Date,
TotalValue,
ExtraValue,
DiscValue,
NetValue,
 PaidValue,
ReminValue
Posted
Updated 17-Mar-21 7:26am

1 solution

I created this view and it work good at this time

CREATE OR REPLACE VIEW `view_sales_and_returns` as
SELECT
sales.smain_Date as FullDateTime,
date(sales.smain_Date) as OpDate,
YEAR(sales.smain_Date) as OpYear, 
DATE_FORMAT(sales.smain_Date, '%m %Y')  as OpMonth,  
WEEK(sales.smain_Date) as OpWeek,  
DAY(sales.smain_Date) as OpDay, 
sales.r_user_ID as userID,
sales.r_branche_ID as BranchID,
sales.cust_ID as CustID,
1 as Qnt,
sales.smain_TotalValue as TotalValue,
sales.smain_ExtraValue as ExtraValue,
sales.smain_DiscValue as DiscValue,
sales.smain_NetValue as NetValue,
sales.smain_PaidValue as PaidValue,
sales.smain_ReminValue as ReminValue,
sales.sTotalProfits as TotalProfits,
0 as rQnt,
0 as rTotalValue,
0 as rExtraValue,
0 as rDiscValue,
0 as rNetValue,
0 as rPaidValue,
0 as rReminValue,
0 as rTotalProfits
FROM sales_main AS sales

union all
SELECT
returnssales.sretmain_Date as FullDateTime,
date(returnssales.sretmain_Date) as OpDate,
YEAR(returnssales.sretmain_Date) as OpYear,
DATE_FORMAT(returnssales.sretmain_Date, '%m %Y')  as OpMonth,
WEEK(returnssales.sretmain_Date) as OpWeek,  
DAY(returnssales.sretmain_Date) as OpDay, 
returnssales.r_user_ID as userID,
returnssales.r_branche_ID as BranchID,
returnssales.vendor_ID as CustID,
0 as Qnt,
0 as TotalValue,
0 as ExtraValue,
0 as DiscValue,
0 as NetValue,
0 as PaidValue,
0 as ReminValue,
0 as TotalProfits,
1 as rQnt,
returnssales.sretmain_TotalValue as rTotalValue,
returnssales.sretmain_ExtraValue as rExtraValue,
returnssales.sretmain_DiscValue as rDiscValue,
returnssales.sretmain_NetValue as rNetValue,
returnssales.sretmain_PaidValue as rPaidValue,
returnssales.sretmain_ReminValue as rReminValue,
0 as rTotalProfits
FROM sales_sreturned_main AS returnssales
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900