Invoice Prepared before 30 days only by Item description

SELECT 
a.CardCode as 'Code',
a.Cardname as 'Customer Name' ,
a.Docentry ,
a.Docnum as 'Inv.No' ,
a.Docdate as 'Inv.dt' ,
b.U_part_no as 'PartNo.', 
b.Dscription,
b.U_inv_f_text as 'Inv.Txt',
b.Price as 'Unit SP',
b.Quantity,
b.linetotal as 'Total SP'  ,
 Case
when a.DocType = 'I' then 'INVOICE'
when a.DocType = 'S' then 'INVOICE'
End 'Doctype'
from OINV a inner join INV1 b on a.docentry = b.docentry
where b.Dscription like '%Rental Charges%'
and  (a.docdate =  CONVERT(VARCHAR(10),DATEADD(MONTH,-1,GETDATE()+1),120) )

Invoice With Serial Number , Part No , ItemGroup

Select
 P2.docentry , P2.docnum as [InvNo.],
 P1.DocDate,
 P2.cardcode ,P2.cardname as 'CustomerName' ,
 P1.U_part_no as 'PartNo',
 P1.Itemcode, P1.Dscription,
 P1.price as 'UnitPrice' ,
 P1.Quantity ,
 P1.linetotal as 'TotalSP' ,
 case when P2.Doctype = 'I' then 'Invoice' End 'Doctype' ,
 (select T6.FirmName  from omrc T6 where T20.FirmCode = T6.FirmCode) 'Mfg',
 T5.itmsgrpnam as 'ItemGroup',
 Case When P1.BaseType=15 then
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=15  and SN1.BaseEntry=P1.BaseEntry and SN1.BaseLinNum=P1.BaseLine
FOR XML PATH('')))
else
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=13  and SN1.BaseEntry=P1.DocEntry and SN1.BaseLinNum=P1.LineNum
FOR XML PATH(''))) end AS SerialNo ,
P1.U_INV_F_TEXT as 'Inv.Text'
  from  OINV P2
INNER JOIN INV1 P1 on P2.DocEntry = P1.DocEntry
INNER JOIN OITM T20 ON T20.ItemCode = P1.ItemCode
LEFT OUTER JOIN OITB T5 ON T5.ItmsGrpCod = T20.ItmsGrpCod
where P2.DocDate >= '[%1]' and P2.DocDate <= '[%2]'
and P2.DocType = 'I' and
T5.ItmsGrpNam in ( 'Server' , 'Iss ACcessory', 'Enterprise Server', 'Blade Server' , 'Blade Accessory', 'NAS' , 'Storage' , 'Storage Accessory')

union all

 Select
 P2.docentry , P2.Docnum as [InvNo.],
   P1.DocDate,
 P2.cardcode ,P2.cardname as 'CustomerName' ,
 --Cast(P1.DocDate as Varchar(25)) [Docdate],
 P1.U_part_no as 'PartNo',
 P1.Itemcode, P1.Dscription ,
 -P1.price as 'UnitPrice' ,
 -P1.Quantity ,
 -P1.linetotal as 'TotalSP' ,
 case when P2.Doctype = 'I' then 'Creditmemo' End 'Doctype' ,
 (select T6.FirmName  from omrc T6 where T20.FirmCode = T6.FirmCode) 'Mfg',
 t5.itmsgrpnam as 'ItemGroup',
 Case When P1.BaseType=15 then
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=15  and SN1.BaseEntry=P1.BaseEntry and SN1.BaseLinNum=P1.BaseLine
FOR XML PATH('')))
else
'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', '
From SRI1 SN1
Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial
Where SN1.BaseType=14  and SN1.BaseEntry=P1.DocEntry and SN1.BaseLinNum=P1.LineNum
FOR XML PATH(''))) end AS SerialNo ,
P1.U_INV_F_TEXT as 'Inv.Text'
 from  ORIN P2
INNER JOIN RIN1 P1 on P2.DocEntry = P1.DocEntry
INNER JOIN OITM T20 ON T20.ItemCode = P1.ItemCode
LEFT OUTER JOIN OITB T5 ON T5.ItmsGrpCod = T20.ItmsGrpCod
where P2.DocDate >= '[%1]' and P2.DocDate <= '[%2]'
and P2.DocType = 'I' and
T5.ItmsGrpNam in ( 'Server' , 'Iss ACcessory', 'Enterprise Server', 'Blade Server' , 'Blade Accessory', 'NAS' , 'Storage' , 'Storage Accessory')
order by P2.docnum

One Year Sales Analysis Report

SELECT T0.[CardCode], T0.[CardName], (SUM(T1.Debit) – sum(T1.Credit)) AS ‘January’, (SUM(T2.Debit) – sum(T1.Credit)) AS ‘February’, (SUM(T3.Debit) – sum(T1.Credit)) AS ‘March ‘, (SUM(T4.Debit) – sum(T1.Credit)) AS ‘April’, (SUM(T5.Debit) – sum(T1.Credit)) AS ‘May ‘, (SUM(T6.Debit) – sum(T1.Credit)) AS ‘June’, (SUM(T7.Debit) – sum(T1.Credit)) AS ‘July ‘, (SUM(T8.Debit) – sum(T1.Credit)) AS ‘August’, (SUM(T9.Debit) – sum(T1.Credit)) AS ‘September ‘, (SUM(T10.Debit) – sum(T1.Credit)) AS ‘October ‘, (SUM(T11.Debit) – sum(T1.Credit)) AS ‘November ‘, (SUM(T12.Debit) – sum(T1.Credit)) AS ‘December ‘ FROM dbo.OCRD T0 LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND Month(T1. Duedate) = 1 AND Year(T1.Duedate) = Year(GetDate()) AND T1.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T2 ON T2.ShortName = T0.CardCode AND Month(T2. Duedate) = 2 AND Year(T2.Duedate) = Year(GetDate()) AND T2.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T3 ON T3.ShortName = T0.CardCode AND Month(T3. Duedate) = 3 AND Year(T3.Duedate) = Year(GetDate()) AND T3.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T4 ON T4.ShortName = T0.CardCode AND Month(T4. Duedate) = 4 AND Year(T4.Duedate) = Year(GetDate()) AND T4.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T5 ON T5.ShortName = T0.CardCode AND Month(T5. Duedate) = 5 AND Year(T5.Duedate) = Year(GetDate()) AND T5.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T6 ON T6.ShortName = T0.CardCode AND Month(T6. Duedate) = 6 AND Year(T6.Duedate) = Year(GetDate()) AND T6.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T7 ON T7.ShortName = T0.CardCode AND Month(T7. Duedate) = 7 AND Year(T7.Duedate) = Year(GetDate()) AND T7.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T8 ON T8.ShortName = T0.CardCode AND Month(T8. Duedate) = 8 AND Year(T8.Duedate) = Year(GetDate()) AND T8.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T9 ON T9.ShortName = T0.CardCode AND Month(T9. Duedate) = 9 AND Year(T9.Duedate) = Year(GetDate()) AND T9.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T10 ON T10.ShortName = T0.CardCode AND Month(T10. Duedate) = 10 AND Year(T10.Duedate) = Year(GetDate()) AND T10.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T11 ON T11.ShortName = T0.CardCode AND Month(T11. Duedate) = 11 AND Year(T11.Duedate) = Year(GetDate()) AND T11.TransType in (’13’,’14’) LEFT JOIN dbo.JDT1 T12 ON T12.ShortName = T0.CardCode AND Month(T12. Duedate) = 12 AND Year(T12.Duedate) = Year(GetDate()) AND T12.TransType in (’13’,’14’) WHERE T0.CardType = ‘C’ Group By T0.[CardCode], T0.[CardName]

Deliveries done on Previous Day

 SELECT 
T0.[DocNum] as ‘Delivery Note No.’, 
T0.[DocDate],T0.[DocDueDate] as ‘Delivery Date’, 
T0.[CardName] as ‘Customer Name’, 
T1.[ItemCode], 
T1.[Dscription], 
T1.[Quantity]
 FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry 
WHERE T0.[DocDate] =DATEADD(DAY, -1, CONVERT(char,GETDATE(),101)) 
order by T0.[CardName] , T0.[DocDate] 

Stock By Warehouse

SELECT 
T1.WhsName,
T0.ItemCode,
SUM(T0.InQty – T0.OutQty) as ‘On Hand Changed’
FROM dbo.OINM T0
INNER JOIN dbo.OWHS T1 ON T0.Warehouse = T1.WhsCode
WHERE T0.DocDate >= ‘[%0]’ and
T1.WhsName like ‘[%1]%’ and
T0.ItemCode like ‘[%2]%’
GROUP BY T1.WhsName, T0.ItemCode
Having SUM(T0.InQty – T0.OutQty) != 0

Active item list Query

SELECT T0.ItemCode

FROM dbo.OITM T0

WHERE T0.ITEMCODE IN (SELECT DISTINCT CODE FROM dbo.ITT1) OR

T0.ITEMCODE IN (SELECT DISTINCT CODE FROM dbo.OITT) OR T0.ITEMCODE

IN (SELECT DISTINCT ITEMCODE FROM dbo.OINM) OR T0.ITEMCODE IN (SELECT

DISTINCT ITEMCODE FROM dbo.ADO1)