Click here to Skip to main content
15,846,004 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
hi,
first i'm sorry if i cant write a good title and welcome to any recommendations
i have "st_items" that have "stitems_Type" field , this field determine if the item is "stock" or "stock with custom serial".

st_items data rows examples :-
----------------------------------------------------
stitems_ID | stitems_Type | stitems_MinBalance | ---
1          | 0            | 10                 | ---
2          | 0            | 20                 | ---
3          | 1            | 5                  | ---
4          | 1            | 15                 | ---
----------------------------------------------------

if "stitems_Type = 0" , the item is "stock"
in this case all stocks information that related to this item will stored in table called "stock_noserials" with this rows example :
---------------------------------
id | stitems_ID | StockQnty | ---
1  | 1          | 5         | ---
2  | 1          | 3         | ---
3  | 2          | 500       | ---
4  | 2          | 150       | ---
---------------------------------

as "stock_noserials " table , the stock of item 1 = 8 (sum of StockQnty colmun) and the stock of item 2 = 650 (sum of StockQnty colmun)


if "stitems_Type = 1" , the item is "stock with serials"
in this case all stocks information that related to this item will stored in table called "purchases_item_seriels" with this rows example :
-------------------------------------
pis_ID| stitems_ID | pis_Statues| ---
1     | 3          | 1          | ---
2     | 3          | 2          | ---
3     | 3          | 4          | ---
4     | 3          | 5          | ---
5     | 4          | 2          | ---
-------------------------------------

pis_Statues (1 = in stock , 2 = sold , 4 = lost , 5 = returned sales) so , to get the stock will count the item that have this pis_Statues values (1,5,6)

as "purchases_item_seriels" table , the stock of item 3 = 2 (count of records where pis_Statues colmun value is (1,5,6)) and the stock of item 4 = 0

i want to create VIEWS that will provide me with the Shortages Items as this results :
--------------------------------------------------
stitems_ID | stitems_MinBalance | stock      | ---
1          | 10                 | 8          | ---
3          | 5                  | 2          | ---
4          | 15                 | 0          | ---
--------------------------------------------------


so i can also create Stored Procedure that will based on this VIEW ..

What I have tried:

I'm thinking of creating a View1 to join st_itemswith stock_noserials and creating a View2 to join st_itemswith purchases_item_seriels . but i don't know how i can create view to combine the two views


now i get shortages with Entity Framework code but it is very slow ..

st_itemsBindingSource.DataSource = nDB01.st_items
               .GroupBy(x => x.stitems_ID)
               .Select(grp => new
               {
                   itemID = grp.Key,
                   sumNoSerial = nDB01.stock_noserials.Where(u => u.stitems_ID == grp.Key).Sum(s => s.StockQnty),
                   sumWithSerial = nDB01.purchases_item_seriels.Where(u => (u.stitems_ID == grp.Key && u.pis_Statues == 0) ||
                                                                           (u.stitems_ID == grp.Key && u.pis_Statues == 5) ||
                                                                           (u.stitems_ID == grp.Key && u.pis_Statues == 6))
                                                                           .Count(),
                   minBalance = grp.Select(s => s.stitems_MinBalance).FirstOrDefault()
               })
                   .Where(u => u.minBalance >= (u.sumNoSerial+u.sumWithSerial)).ToList();


also i get its count with this code :
//Shortages النواقص
            int Shortages_StockNoSer = nDB01.stock_noserials
                .GroupBy(x => x.stitems_ID)
                .Select(grp => new
                {
                    itemID = grp.Key,
                    sum = grp.Sum(x => x.StockQnty),
                    min = grp.Select(s => s.st_items.stitems_MinBalance).FirstOrDefault()
                }).Where(u => u.sum < u.min && u.min != 0)
                .Count();
            int Shortages_StockWithSer = nDB01.purchases_item_seriels
               .GroupBy(x => x.stitems_ID)
               .Select(grp => new
               {
                   itemID = grp.Key,
                   sum = grp.Count(),
                   min = grp.Select(s => s.st_items.stitems_MinBalance).FirstOrDefault()
               }).Where(u => u.sum < u.min && u.min != 0)
               .Count();

            int CountShortagesItems = Convert.ToInt32( Shortages_StockNoSer) + Convert.ToInt32(Shortages_StockWithSer);
Posted
Updated 28-Dec-18 11:02am
v3

Not sure if I understood the question correctly, but you could try something like
SQL
WITH CurrentAmounts AS (
   SELECT sns.stitems_ID, 
          SUM(sns.StockQnty) AS Amount
   FROM stock_noserials sns
   GROUP BY sns.stitems_ID
) 
SELECT si.stitems_ID,
      si.stitems_MinBalance,
      COALESCE(ca.Amount,0) AS Stock
FROM st_items si,
     LEFT JOIN CurrentAmounts ca ON ca.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount,0) < si.stitems_MinBalance

And to create a view for the query above, just place it inside a CREATE VIEW statement[^]

ADDITION:

Without CTE
SQL
SELECT si.stitems_ID,
      si.stitems_MinBalance,
      COALESCE(ca.Amount,0) AS Stock
FROM st_items si,
     LEFT JOIN (SELECT sns.stitems_ID, 
                       SUM(sns.StockQnty) AS Amount
                FROM stock_noserials sns
                GROUP BY sns.stitems_ID) ca 
     ON ca.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount,0) < si.stitems_MinBalance


If needs to be added into amount, something like
SQL
SELECT si.stitems_ID,
      si.stitems_MinBalance,
      COALESCE(ca.Amount + pa.Amount,0) AS Stock
FROM st_items si,
     LEFT JOIN (SELECT sns.stitems_ID, 
                       SUM(sns.StockQnty) AS Amount
                FROM stock_noserials sns
                GROUP BY sns.stitems_ID) ca 
     ON ca.stitems_ID = si.stitems_ID
     LEFT JOIN (SELECT pis.stitems_ID, 
                       COUNT(*) AS Amount
                FROM purchases_item_seriels pis
                WHERE pis.pis_Statues IN (1, 5, 6)
                GROUP BY pis.stitems_ID) pa
     ON pa.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount + pa.Amount, 0) < si.stitems_MinBalance
 
Share this answer
 
v3
Comments
Golden Basim 28-Dec-18 16:43pm    
thanks for reply , 1- it say unexpected 'with' (with)
2- i went to this table "purchases_item_seriels" to your query
Wendelius 28-Dec-18 17:14pm    
For the item 1. Perhaps you're using an older version of MySQL which doesn't recognize CTE. I updated the question to use an inline view for the query.

What comes to the item 2, not sure if I understand the meaning of the table purchases_item_seriels. Should those rows be added to the amount in stock? If that is the case, have a look at the third variation in the answer.
Golden Basim 29-Dec-18 4:53am    
yes the last query i think that it what i mean .. i will try it now
thanks
Golden Basim 29-Dec-18 4:56am    
i have error with syntax again in (LEFT ,and SUM) , i use MySQL server 5.7.21

CREATE OR REPLACE VIEW `shortages` as
SELECT si.stitems_ID,
si.stitems_MinBalance,
COALESCE(ca.Amount + pa.Amount,0) AS Stock
FROM st_items si,
LEFT JOIN (SELECT sns.stitems_ID,
SUM(sns.StockQnty) AS Amount
FROM stock_noserials sns
GROUP BY sns.stitems_ID) ca
ON ca.stitems_ID = si.stitems_ID
LEFT JOIN (SELECT pis.stitems_ID,
COUNT(*) AS Amount
FROM purchases_item_seriels pis
WHERE pis.pis_Statues IN (1, 5, 6)
GROUP BY pis.stitems_ID) pa
ON pa.stitems_ID = si.stitems_ID
WHERE COALESCE(ca.Amount + pa.Amount, 0) < si.stitems_MinBalance;
Wendelius 29-Dec-18 5:08am    
Sorry there's an extra comma after si. Try with
CREATE OR REPLACE VIEW `shortages` as
   SELECT si.stitems_ID,
          si.stitems_MinBalance,
          COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0) AS Stock
   FROM st_items si
   LEFT JOIN (SELECT sns.stitems_ID,
                     SUM(sns.StockQnty) AS Amount
              FROM stock_noserials sns
              GROUP BY sns.stitems_ID) ca
   ON ca.stitems_ID = si.stitems_ID
   LEFT JOIN (SELECT pis.stitems_ID,
                     COUNT(*) AS Amount
              FROM purchases_item_seriels pis
              WHERE pis.pis_Statues IN (1, 5, 6)
              GROUP BY pis.stitems_ID) pa
   ON pa.stitems_ID = si.stitems_ID
   WHERE (COALESCE(ca.Amount, 0) + COALESCE(pa.Amount,0)) < si.stitems_MinBalance;


But before making the view, you can try the SELECT statement on itself, just to test if it's giving correct results
You should create the View on the database server to take advantage of indexing and database server performance. I would be surprised if LINQ was designed to handle complex scenarious like this even though you can formulate them in theory (or practice).
 
Share this answer
 
Comments
Golden Basim 28-Dec-18 15:43pm    
thank you , i checked this documentation and others .. but i can't create the required query

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