Most useful SQL Query examples. I would like now to review my own direct knowledge and experiences with SQL and Queries. First we will start with more than one table. 1. Table Joining. For this example, we will use the Customer and Orders tables. The Customer table has Customer_ID, a unique key for each customer. Customer Table: Customer_ID Name Street City State Zip Country Phone Email Social1 (we will store several social media accounts as Social1-10.) Orders_Header: Customer_ID Order# Total order$ Order_date Ship_date Discount1 (We might have more than 1 discount per order) Promo1 Invoice# Orders_detail Customer_ID Order# Order_ext (this will be a counter of every order per the customer) Part# (this is the item #) Price Part_Discount1 Part_Promo1 there will be multiple rows usually. Shipping charge, will be a separate row. Even if there is a promo for free shipping. Now we want to see which customers bought the most products in a given month. We want to show who had most orders per month, and highest Total$. We would also like to see amongst those who used a Discount code, bought which part#

Writing a small simple excel program and store the data on the cloud

We need to track the staff hours in a more coherent way than currently. There needs to be data available such as current monthly hours scheduled by department staff by entering a monthly work schedule. Data Analysis needs are both for planning and monitoring the present. We get a feed from the Healthcare ERP when clinical needs are at risk, and there is a business rule, the substance is, more clinicians needed means more support staff needed. This information can be of a feed nature or an api call. We would want to have both passages, when we are low and when we need more staff scheduling and augmentation. So we start with a simple excel sheet. How simple can we make it? I will create 2 solutions. This often happens in certain of the real world. First get a simple edition up and then work on the enhancements. I wrote a solution in Access. That allows for entry of data with multi view, by calendar; monthly and weekly views. Let's see what the Customer wants shall we?

SQL to determine which Practitioner a patient saw the most times.

select  distinct  A.Patient_id,   A.Appt_DateTime, P.Patient_name,  A.Patient_number,  P.Patient_Street1, P.Patient_DOB, P.Patient_Street2, P.Patient_City, P.Patient_State, v.service_date, P.Patient_Zip_Code, P.Patient_Home_Phone, D.Diagnosis_Code, b.Appt_resource_id,  b.appt_resource_descr from [PM].[vwGenPatApptInfo] A inner join  ( select top 100 percent  patient_id, Appt_resource_id, appt_resource_descr,  count(Appt_resource_id) as DR_count, row_number() over (partition by patient_id order by count(*) desc) as seqnum from [PM].[vwGenPatApptInfo] where Patient_ID is not NULL     group by patient_id  ,Appt_resource_id, appt_resource_descr order by patient_id, seqnum  ) B    on B.Patient_ID = A.Patient_ID   and  B.seqnum = 1  inner join [PM].[vwGenPatInfo] P  on A.Patient_id = P.Patient_id Left Join [PM].[Vouchers] V ON A.Patient_id = V.Patient_ID Inner Join [PM].[Services] S ON V.Voucher_ID = S.Voucher_ID Inner Join [PM].[Service_Diagnoses] SD ON S.Service_ID = SD.Service_ID Inner Join [PM].[Diagnosis_Codes] D ON SD.Diagnosis_Code_ID = D.Diagnosis_Code_ID where A.Appt_Cancelled_Date is NULL and A.Appt_Noshow_date is NULL and A.Appt_status = 'A'

QTY USAGE & VENDOR PO Forecast

Forecast report. The import department wanted to forecast what they need to order based on past years as well as materials already on order. The company does import raw materials and parts but does the assembling, and engraving, as well as custom embellishments and design. We are talking about many 10's of thousands of pieces for each of our major parts. QTY USAGE + VENDOR PO Forecast. I used sub-reports for this because it's a long and complex display row. Sub-reports are useful when there is a lot of data from various tables and you just need to branch off and pick up a passenger. In this case, past years data to show against current year data, showing the projected difference, so they can place an order for more parts. We show usage in 3 month increments and this includes our marked data, the PO and inventory on order to get a rolling 3 months. The main formula here is pulling for the next 3 months. We need to format the date because it is stored as YYYYMMDD. numbervar mm1:=0; numbervar dd1:=0; numbervar yy1:=0; numbervar newmm1:=0; numbervar newyy1:=0; mm1:= tonumber(Mid (CStr ({@S1AckFromDate2}),6 ,1 )& Mid (CStr ({@S1AckFromDate2}),8 ,1 )); dd1:= tonumber(Mid (CStr ({@S1AckFromDate2}),9 ,2 ) ); yy1:= tonumber(Mid (CStr ({@S1AckFromDate2}),1 ,2 )& Mid (CStr ({@S1AckFromDate2}),4 ,2 )); if mm1+1>12 then newmm1:=1; if mm1+1>12 then newyy1:= yy1+1; if mm1+1<=12 then newmm1:=mm1+1; if mm1+1<=12 then newyy1:= yy1; tonumber(cstr(newyy1,0) & right("00" & cstr(newmm1,0),2) & right("00" & cstr(dd1,0),2))

Crystal Reports Cross tab date processing

The task here was to create a report that has the number of orders created by hour. The time stamp for orders is on the transaction table from trans code 001 = order created and the time stamp. The system has an Orders table, header and detail and the transaction table is a little sketchy, but it has orders and transaction codes. Every order, has work done on it, from taking a phone call about a product, or via a web chat, to the sales process, then the work on the order, the planning, engraving, extras, the picking, packing etc. This allows senior management to monitor the flow at any given time. So, basically we want to see the number of orders for each day of the week by hour.2 week span. I used Crystal Reports Cross tab function. I used functions to get the count. if {@date} IN [Minimum(lastfullweek)+1 TO Maximum(lastfullweek)+1] OR {@date} IN [Maximum(lastfullweek)+2 TO Maximum(lastfullweek)+8] then 1 else 0; Set the week to start Monday: (datepart('w',{@date},crMonday)) Time set and edit: Time(Picture(Right('0000000000'&totext({OETRA99.OTTRT},'#'),6),'xx:xx:xx')) Set date for count: stringvar dd := picture(totext({OETRA99.OTTRND},'#'),'xxxx/xx/xx'); if isdate(dd) then date(dd) and the result is showing orders entered by hour: Orders Created by Hour Monday Tuesday Wednesday Last Week Current Week Last Week Current Week 12:00AM 28 33 22 26 15 22 5:00AM 39 48 31 43 52 56 6:00AM 7 6 6 9 7 7 7:00AM 12 18 16 24 11 19