Use BruceDBTest2

–select * from invoice;
–select * from customer;
–select * from Product;

— Get referred by
–select * from BruceDBtest2..customer as c1 join brucedbtest2..customer as c2 on c1.referredby = c2.custid

— Get Raw dump of invoice joined with product table
–select * from invoice as i join product as p on i.productId = p.productid
;

— COMMON TABLE EXPRESSION EXAMPLE
with cte1 as
(
select invoiceid,
salesdate,
i.custid,
Name,
p.productId,
quan,
cost,
productName ,
quan * cost as InvoiceTotal
from invoice as i
join customer as c
on i.custid = c.custid
join product as p
on i.productId = p.productId
)

— SIMPLE XML SUMMARY EXAMPLE
select count(*) NumberOfSales,sum(InvoiceTotal) as TotalSales, Max(InvoiceTotal) as HighestSale from cte1 for xml path(‘Totals’),elements, ROOT(‘SalesSummary’)
GO

— ANOTHER CTE
with cte2 as
(
select
c.custid,
Name,
quan,
cost,
p.ProductName ,
quan * cost as InvoiceTotal
from invoice as i
join customer as c
on i.custid = c.custid
join product as p
on i.productId = p.productId
)
select Name, sum(invoicetotal) from cte2 group by Name, Invoicetotal
GO
–************ CUSTOMERS ******************
with cte3 as
(
select custid , sum(quan) as quantity, sum(quan * cost) as amount from invoice as i join product as p on i.ProductId = p.ProductId group by i.custid
)
select
c.custid as [@CustomerId],
name,
quantity as [PurchaseTotals/Quanity],
amount as [PurchaseTotals/Total]
from cte3 as c join customer as cus on c.custid = cus.custid
–for xml Path(‘Customer’), Root(‘Customers’)

–select sum(iif(productName = ‘Duck’, 1, null)) , sum(Iif(productName = ‘Frog’,1 , null)) from invoice as i join product as p on i.productid = p.productId
–select Sum(quan), cost, productname from invoice as i join product as p on i.ProductId = p.ProductId group by quan,cost, productname

GO

— TOTAL SALES and QUANTITY BY PRODUCT
select
duckQuantity
DuckSales,
FrogQuantity,
frogsales,
IsNull(pigquantity, 0) as PigQuan,
IsNull(pigSales, 0.0) as PigSales,
IsNull(monkeyquantity, 0) as MonkeyQuan,
IsNull(monkeySales, 0.0) as MonkeySales
–iif(pigQuantity is not null, PigQuantity,0) as [Pig Quantity],
–iif(pigSales is not null, PigSales,0) as [Pig Sales]
from
(
select
sum(iif(productName = ‘Duck’, 1, null) * quan) as duckQuantity,
sum(iif(productName = ‘Duck’, 1, null) * quan * cost) as DuckSales,
sum(iif(productName = ‘Frog’, 1, null) * quan) as FrogQuantity,
sum(iif(productName = ‘Frog’, 1, null) * quan * cost) as FrogSales,
IsNull(sum(iif(productName = ‘Pig’, 1, null) * quan),0) as pigQuantity,
IsNull(sum(iif(productName = ‘Pig’, 1, null) * quan * cost),0) as PigSales,
IsNull(sum(iif(productName = ‘Monkey’, 1, null) * quan),0) as MonkeyQuantity,
IsNull(sum(iif(productName = ‘Monkey’, 1, null) * quan * cost),0) as MonkeySales
from
invoice as i join product as p on i.productid = p.productId
) as salestable
— SIMPLE DATE SEARCH
select * from invoice where salesdate > ’12/06/2016′