AdventureWorks2014_Queries.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2aaf75f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pyodbc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "da177033",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    SaleMonth  SaleYear    TotalSales\n",
      "0           1      2012  3.970627e+06\n",
      "1           1      2013  2.087872e+06\n",
      "2           1      2014  4.289818e+06\n",
      "3           2      2012  1.475427e+06\n",
      "4           2      2013  2.316922e+06\n",
      "5           2      2014  1.337725e+06\n",
      "6           3      2012  2.975748e+06\n",
      "7           3      2013  3.412069e+06\n",
      "8           3      2014  7.217531e+06\n",
      "9           4      2012  1.634601e+06\n",
      "10          4      2013  2.532266e+06\n",
      "11          4      2014  1.797174e+06\n",
      "12          5      2011  5.038059e+05\n",
      "13          5      2012  3.074603e+06\n",
      "14          5      2013  3.245624e+06\n",
      "15          5      2014  5.366675e+06\n",
      "16          6      2012  4.099354e+06\n",
      "17          6      2013  5.081069e+06\n",
      "18          7      2011  2.044600e+06\n",
      "19          7      2012  3.417954e+06\n",
      "20          7      2013  4.896354e+06\n",
      "21          8      2011  2.495817e+06\n",
      "22          8      2012  2.175637e+06\n",
      "23          8      2013  3.333964e+06\n",
      "24          9      2012  3.454152e+06\n",
      "25          9      2013  4.532909e+06\n",
      "26         10      2011  4.588762e+06\n",
      "27         10      2012  2.544091e+06\n",
      "28         10      2013  4.795813e+06\n",
      "29         11      2012  1.872702e+06\n",
      "30         11      2013  3.312130e+06\n",
      "31         12      2011  1.309863e+06\n",
      "32         12      2012  2.829405e+06\n",
      "33         12      2013  4.075487e+06\n"
     ]
    }
   ],
   "source": [
    "cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=DESKTOP-M687F4C;DATABASE=AdventureWorks2014;UID=eo89;Trusted_Connection=yes')\n",
    "sql = \"\"\"\n",
    "WITH IndivSalesData AS\n",
    "(\n",
    "    SELECT\n",
    "       SaleMonth = MONTH(oh.OrderDate),\n",
    "\t   SaleYear = YEAR(oh.OrderDate),\n",
    "       SalesForMonth = SUM(od.LineTotal) \n",
    "    FROM \n",
    "        AdventureWorks2014.Sales.SalesOrderDetail od \n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Sales.SalesOrderHeader oh ON od.SalesOrderID = oh.SalesOrderID\n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Sales.Customer c ON oh.CustomerID = c.CustomerID\n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Person.Person p ON c.PersonID = p.BusinessEntityID\n",
    "    WHERE\n",
    "        p.PersonType = 'IN'\n",
    "    GROUP BY\n",
    "       MONTH(oh.OrderDate),\n",
    "\t   YEAR(oh.OrderDate)\n",
    "), \n",
    "\n",
    "CorporateSalesData AS\n",
    "(\n",
    "    SELECT\n",
    "       SaleMonth = MONTH(oh.OrderDate),\n",
    "\t   SaleYear = YEAR(oh.OrderDate),\n",
    "       SalesForMonth = SUM(od.LineTotal) \n",
    "    FROM \n",
    "        AdventureWorks2014.Sales.SalesOrderDetail od \n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Sales.SalesOrderHeader oh ON od.SalesOrderID = oh.SalesOrderID\n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Sales.Customer c ON oh.CustomerID = c.CustomerID\n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Person.Person p ON c.PersonID = p.BusinessEntityID\n",
    "    WHERE\n",
    "        p.PersonType = 'CS'\n",
    "    GROUP BY\n",
    "       MONTH(oh.OrderDate),\n",
    "\t   YEAR(oh.OrderDate)\n",
    ")\n",
    "-- Determine total sales\n",
    "SELECT \n",
    "    Indiv.SaleMonth,\n",
    "\tIndiv.SaleYear,\n",
    "    TotalSales = Indiv.SalesForMonth + CS.SalesForMonth\n",
    "FROM \n",
    "    IndivSalesData Indiv\n",
    "INNER JOIN \n",
    "    CorporateSalesData CS ON Indiv.SaleMonth = CS.SaleMonth AND Indiv.SaleYear = CS.SaleYear\n",
    "ORDER BY \n",
    "    Indiv.SaleMonth,\n",
    "\tIndiv.SaleYear\n",
    ";\"\"\"\n",
    "\n",
    "data = pd.read_sql(sql,cnxn)\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "print(data)\n",
    "\n",
    "df.to_excel (r'C:\\Users\\eo89\\Documents\\monthly_sales.xlsx', index = False, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "811899a8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Country_Region_or_City  Total\n",
      "0                       US   8668\n",
      "1                       AU   3632\n",
      "2                       GB   1954\n",
      "3                       FR   1851\n",
      "4                       DE   1821\n",
      "..                     ...    ...\n",
      "576       North Sioux City      1\n",
      "577               Westport      1\n",
      "578                  Boise      1\n",
      "579                Augusta      1\n",
      "580             San Ysidro      1\n",
      "\n",
      "[581 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "sql = \"\"\"SELECT COALESCE(addre.City, stateprov.CountryRegionCode) as Country_Region_or_City,\n",
    "       Count(*) AS Total\n",
    "FROM AdventureWorks2014.Person.Address addre INNER JOIN\n",
    "     AdventureWorks2014.Person.StateProvince stateprov\n",
    "     ON addre.StateProvinceID = stateprov.StateProvinceID\n",
    "GROUP BY GROUPING SETS ((addre.City), (stateprov.CountryRegionCode))\n",
    "ORDER BY Total DESC;\"\"\"\n",
    "data = pd.read_sql(sql,cnxn)\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "df.to_excel (r'C:\\Users\\eo89\\Documents\\regional_sales.xlsx', index = False, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "64c5ab52",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   SaleYear  PercentToIndividuals  PercentToCorporate    TotalSales\n",
      "0      2011               30.5586             69.4413  1.264167e+07\n",
      "1      2012               19.0625             80.9374  3.352430e+07\n",
      "2      2013               24.6022             75.3977  4.362248e+07\n",
      "3      2014               41.7432             58.2567  2.005793e+07\n"
     ]
    }
   ],
   "source": [
    "sql = \"\"\";WITH IndivSalesData AS\n",
    "(\n",
    "    SELECT\n",
    "       SaleYear = YEAR(oh.OrderDate),\n",
    "       SalesForYear = SUM(od.LineTotal) \n",
    "    FROM \n",
    "        AdventureWorks2014.Sales.SalesOrderDetail od \n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Sales.SalesOrderHeader oh ON od.SalesOrderID = oh.SalesOrderID\n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Sales.Customer c ON oh.CustomerID = c.CustomerID\n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Person.Person p ON c.PersonID = p.BusinessEntityID\n",
    "    WHERE\n",
    "        p.PersonType = 'IN'\n",
    "    GROUP BY\n",
    "       YEAR(oh.OrderDate)\n",
    "), \n",
    "\n",
    "CorporateSalesData AS\n",
    "(\n",
    "    SELECT\n",
    "       SaleYear = YEAR(oh.OrderDate),\n",
    "       SalesForYear = SUM(od.LineTotal) \n",
    "    FROM \n",
    "        AdventureWorks2014.Sales.SalesOrderDetail od \n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Sales.SalesOrderHeader oh ON od.SalesOrderID = oh.SalesOrderID\n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Sales.Customer c ON oh.CustomerID = c.CustomerID\n",
    "    INNER JOIN \n",
    "        AdventureWorks2014.Person.Person p ON c.PersonID = p.BusinessEntityID\n",
    "    WHERE\n",
    "        p.PersonType = 'CS'\n",
    "    GROUP BY\n",
    "       YEAR(oh.OrderDate)\n",
    ")\n",
    "SELECT \n",
    "    Indiv.SaleYear, \n",
    "    TotalSales = Indiv.SalesForYear + CS.SalesForYear\n",
    "FROM \n",
    "    IndivSalesData Indiv\n",
    "INNER JOIN \n",
    "    CorporateSalesData CS ON Indiv.SaleYear = CS.SaleYear\n",
    "ORDER BY \n",
    "    Indiv.SaleYear\"\"\"\n",
    "data = pd.read_sql(sql,cnxn)\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "df.to_excel (r'C:\\Users\\eo89\\Documents\\export_dataframe.xlsx', index = False, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4f54e7ed",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57c36241",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "107bd6b8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22e854f0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ab7b5c2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c38bfc0",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}