MS-SQL Tips

The scripts below demonstrates some of the projects that i was involved in a Microsoft SQL Server environment.

This is a sample of scripts that i employ to draw information from various tables to report on sales figures within a Point-Of-Sales environment.

SELECT

/**********Demonstrate the SUMMING Technique****************

SUM(l.Quantity), SUM(l.Quantity * (v.UnitFinalPriceExcTax + v.UnitDiscountIncTax))

************************************************************/
s.TransactionOrderID, s.TransactionShipmentID, l.Quantity,
o.OrderDate, g.description, g.Code, v.InventoryItemCode, m.Code, m.Description, v.InventoryItemDescription,
l.Quantity * (v.UnitFinalPriceExcTax + v.UnitDiscountIncTax) AS ‘Amount’,
v.UnitFinalPriceExcTax,
v.UnitDiscountIncTax,
v.UnitFinalPriceTax,
v.UnitFinalPriceIncTax

FROM dbo.TransactionShipment s INNER JOIN dbo.TransactionOrder o
ON s.TransactionOrderID = o.TransactionOrderID

INNER JOIN dbo.vwTransactionOrderLine v
ON o.TransactionOrderID = v.TransactionOrderID

INNER JOIN dbo.TransactionShipmentLine l
ON v.TransactionOrderLineID = l.TransactionOrderLineID

INNER JOIN dbo.InventoryItem m
ON v.InventoryItemCode = m.Code

INNER JOIN dbo.InventoryGroup g
ON m.InventoryGroupID = g.InventoryGroupID

WHERE
g.Code NOT IN (‘2999’)
AND o.OrderDate > getdate()-10

/***Demonstrate the DATEPART Technique******

DATEPART(year,o.OrderDate) = 2014
AND DATEPART(month,o.OrderDate) = 3
AND DATEPART(day,o.OrderDate) = 17

*******************************************/

ORDER BY o.OrderDate

SELECT
budget_code AS ‘JournalType’,
DATEADD(dd,(b.period_end_date-(SIGN(b.period_end_date)*693596)),’19000101′) AS ‘Applied Date’,
net_change AS ‘Amount’,
” AS ‘JournalNum’,
b.seg2_code AS ‘seg2_code’,
‘Drawings’ AS ‘ChartOfAccount’, *

FROM dbo.glbuddet b INNER JOIN dbo.glchart c
ON b.account_code = c.account_code

WHERE b.seg2_code = ‘61002’
AND DATEADD(dd,(b.period_end_date-(SIGN(b.period_end_date)*693596)),’19000101′)
BETWEEN ‘2013-07-01’ AND ‘2014-06-30’