none
northwind sample database product purchase and sales on credit how to deal it, payment are on installments RRS feed

  • Question

  • hi

    IN THIS DIAGRAM CUSTOMER PURCHASE AND SUPPLIER SALE EVERY PRODUCT IN CASH, BUT IN MY CASE CREDIT IS INVOLVE, HOW CAN I HANDLE, SOME TIME CUSTOMER PAY BILL INSTALLMENT, AND SOME TIME PAY THE ALL BILL

    AND SAME I DID WITH SUPPLIER.

    WHAT ARE CHANGES MAKE IN DATABASE TO HANDLE THE CREDIT PURCHASE AND SALE


    MUHAMMAD ANZAR E-mail : muhammadanzar@hotmail.com Mobile # :0092-3215096959

    Wednesday, September 18, 2019 10:07 AM

Answers

  • Here is the script for the entire database with sample data.

    https://onedrive.live.com/?cid=A3D5A9A9A28080D1&id=A3D5A9A9A28080D1%211743&parId=A3D5A9A9A28080D1%211742&o=OneUp

    Relations, payments tables that are basic. Currently payments are setup for customers, to do others add their primary key in as another field.

    Note Employee table is not shown but is included

    Some sample SQL statements

    -- get payments by customer and order
    DECLARE @CustomerId AS INT= 1;
    SELECT PaymentTransactions.TransactionIdentifier,
           PaymentTransactions.CustomerIdentifier,
           PaymentTransactions.OrderID,
           PaymentTransactions.PaymentTypeIdentifier,
           PaymentTypes.PaymentDescription,
           PaymentTransactions.PaymentDate,
           PaymentTransactions.Amount
    FROM PaymentTransactions
         INNER JOIN PaymentTypes ON PaymentTransactions.PaymentTypeIdentifier = PaymentTypes.PaymentTypeIdentifier
    WHERE PaymentTransactions.CustomerIdentifier = @CustomerId;
    
    -- get order data by customer and order id
    DECLARE @CustomerId AS INT= 56
    DECLARE @OrderId AS bigint = 10248
    SELECT Orders.OrderID,
           Orders.CustomerIdentifier,
           Orders.EmployeeID,
           Employees.Title+''+Employees.FirstName+' '+Employees.LastName AS employeeName,       
    	   FORMAT(Orders.OrderDate, 'MM-dd-yyyy') As OrderDate,
           Orders.RequiredDate,
           Orders.ShippedDate,
           Orders.ShipVia,
           Orders.Freight,
           Orders.ShipAddress,
           Orders.ShipCity,
           Orders.ShipPostalCode,
           Orders.ShipCountry,
           Shippers.CompanyName AS SupplerName,
           Products.ProductName,
           Suppliers.CompanyName AS SupplierName
    FROM Orders
         INNER JOIN Shippers ON Orders.ShipVia = Shippers.ShipperID
         INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
         INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
         INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
         INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
    WHERE Orders.CustomerIdentifier = @CustomerId
          AND Orders.OrderID = @OrderId;
    
    -- get customer by id
    DECLARE @CustomerId AS INT= 56
    SELECT Cust.CustomerIdentifier,
           Cust.CompanyName,
           Cust.ContactId,
           Contacts.FirstName,
           Contacts.LastName,
           Cust.ContactTypeIdentifier,
           CT.ContactTitle,
           Cust.Address AS Street,
           Cust.City,
           Cust.PostalCode,
           Cust.CountryIdentifier,
           Countries.Name AS CountryName,
           Cust.ModifiedDate
    FROM Customers AS Cust
         INNER JOIN ContactType AS CT ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
         INNER JOIN Contacts ON Cust.ContactId = Contacts.ContactId
         INNER JOIN Countries ON Cust.CountryIdentifier = Countries.CountryIdentifier
    WHERE Cust.CustomerIdentifier = @CustomerId;      
    
    -- getting products by category and supplier via IN clause
    SELECT Products.ProductID,
           Products.ProductName,
           Products.SupplierID,
           Suppliers.CompanyName AS SupplierName,
           Products.CategoryID,
           Categories.CategoryName,
           Products.QuantityPerUnit,
           Products.UnitPrice,
           Products.UnitsInStock,
           Products.UnitsOnOrder,
           Products.ReorderLevel,
           Products.Discontinued,
           Products.DiscontinuedDate
    FROM Products
         INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
         INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
    WHERE dbo.Products.CategoryID = 1 AND dbo.Suppliers.SupplierID IN (1,16)


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Wednesday, September 18, 2019 1:55 PM
    Moderator

All replies

  • Hello,

    Give me a chance to get into work and I will provide a script for this as I'm just getting ready to go to work.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, September 18, 2019 11:09 AM
    Moderator
  • Here is the script for the entire database with sample data.

    https://onedrive.live.com/?cid=A3D5A9A9A28080D1&id=A3D5A9A9A28080D1%211743&parId=A3D5A9A9A28080D1%211742&o=OneUp

    Relations, payments tables that are basic. Currently payments are setup for customers, to do others add their primary key in as another field.

    Note Employee table is not shown but is included

    Some sample SQL statements

    -- get payments by customer and order
    DECLARE @CustomerId AS INT= 1;
    SELECT PaymentTransactions.TransactionIdentifier,
           PaymentTransactions.CustomerIdentifier,
           PaymentTransactions.OrderID,
           PaymentTransactions.PaymentTypeIdentifier,
           PaymentTypes.PaymentDescription,
           PaymentTransactions.PaymentDate,
           PaymentTransactions.Amount
    FROM PaymentTransactions
         INNER JOIN PaymentTypes ON PaymentTransactions.PaymentTypeIdentifier = PaymentTypes.PaymentTypeIdentifier
    WHERE PaymentTransactions.CustomerIdentifier = @CustomerId;
    
    -- get order data by customer and order id
    DECLARE @CustomerId AS INT= 56
    DECLARE @OrderId AS bigint = 10248
    SELECT Orders.OrderID,
           Orders.CustomerIdentifier,
           Orders.EmployeeID,
           Employees.Title+''+Employees.FirstName+' '+Employees.LastName AS employeeName,       
    	   FORMAT(Orders.OrderDate, 'MM-dd-yyyy') As OrderDate,
           Orders.RequiredDate,
           Orders.ShippedDate,
           Orders.ShipVia,
           Orders.Freight,
           Orders.ShipAddress,
           Orders.ShipCity,
           Orders.ShipPostalCode,
           Orders.ShipCountry,
           Shippers.CompanyName AS SupplerName,
           Products.ProductName,
           Suppliers.CompanyName AS SupplierName
    FROM Orders
         INNER JOIN Shippers ON Orders.ShipVia = Shippers.ShipperID
         INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
         INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
         INNER JOIN Products ON [Order Details].ProductID = Products.ProductID
         INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
    WHERE Orders.CustomerIdentifier = @CustomerId
          AND Orders.OrderID = @OrderId;
    
    -- get customer by id
    DECLARE @CustomerId AS INT= 56
    SELECT Cust.CustomerIdentifier,
           Cust.CompanyName,
           Cust.ContactId,
           Contacts.FirstName,
           Contacts.LastName,
           Cust.ContactTypeIdentifier,
           CT.ContactTitle,
           Cust.Address AS Street,
           Cust.City,
           Cust.PostalCode,
           Cust.CountryIdentifier,
           Countries.Name AS CountryName,
           Cust.ModifiedDate
    FROM Customers AS Cust
         INNER JOIN ContactType AS CT ON Cust.ContactTypeIdentifier = CT.ContactTypeIdentifier
         INNER JOIN Contacts ON Cust.ContactId = Contacts.ContactId
         INNER JOIN Countries ON Cust.CountryIdentifier = Countries.CountryIdentifier
    WHERE Cust.CustomerIdentifier = @CustomerId;      
    
    -- getting products by category and supplier via IN clause
    SELECT Products.ProductID,
           Products.ProductName,
           Products.SupplierID,
           Suppliers.CompanyName AS SupplierName,
           Products.CategoryID,
           Categories.CategoryName,
           Products.QuantityPerUnit,
           Products.UnitPrice,
           Products.UnitsInStock,
           Products.UnitsOnOrder,
           Products.ReorderLevel,
           Products.Discontinued,
           Products.DiscontinuedDate
    FROM Products
         INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
         INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
    WHERE dbo.Products.CategoryID = 1 AND dbo.Suppliers.SupplierID IN (1,16)


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Wednesday, September 18, 2019 1:55 PM
    Moderator
  • Keep in mind that the Northwind sample database is a sample created how things can be done with MS-Access around .  

    In real practise you will see quick that things likewise an ID which is technical the same as the by humans used ID are unworkable in practise. (This is not the only thing). 

    Therefore see it as sample how to use your tools, not a framework for how to create an application. 

    By the way, it was and is a very good sample, it lacks a lot but therefore it helps you to get a quick insight how a database can be used, probably the reason that all attempts afterwards have failed. 


    Success
    Cor


    • Edited by Cor Ligthert Wednesday, September 18, 2019 2:15 PM
    Wednesday, September 18, 2019 2:14 PM