Hi Guys,
I need your advise on a 12 months rolling report.
I wrote something that gets some of the work done, I am already cleansing the results and creating the front end with SSRS, but I would like to know if there is a better and more efficient way to do it. especially as I will have to do the same with the commented "Mail Order Group"
I appreciate all your inputs, please find the code below:
ALTER PROC [dbo].[RollingMonths]
as
Begin
SELECT Distinct Clients.ClientName, Clients.CustCateg, Clients.ClntGroup, CORDDocs.DocType, CORDDocs.Status,sum(CORDLines.TransSell) as TransSell, CORDDocs.DateOpn,
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 13
)THEN CORDLines.TransSell END) AS 'Sales13',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 12
)THEN CORDLines.TransSell END) AS 'Sales12',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 11
)THEN CORDLines.TransSell END) AS 'Sales11',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 10
)THEN CORDLines.TransSell END) AS 'Sales10',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 9
)THEN CORDLines.TransSell END) AS 'Sales9',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 8
)THEN CORDLines.TransSell END) AS 'Sales8',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 7
)THEN CORDLines.TransSell END) AS 'Sales7',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 6
)THEN CORDLines.TransSell END) AS 'Sales6',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 5
)THEN CORDLines.TransSell END) AS 'Sales5',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 4
)THEN CORDLines.TransSell END) AS 'Sales4',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 3
)THEN CORDLines.TransSell END) AS 'Sales3',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 2
)THEN CORDLines.TransSell END) AS 'Sales2',
(CASE WHEN (CORDDocs.Status = 'Posted'or CORDDocs.Status = 'Closed') and (CORDLines.TransSell > '0') AND (CORDDocs.DocType = 'CINV')
AND (
DATEDIFF(MONTH, CORDDocs.DateOpn, GetDate()) = 1
)THEN CORDLines.TransSell END) AS 'Sales1',
/*(CASE WHEN CORDDocs.Status = 'Open' AND CORDDocs.DocType = 'CINV' THEN CORDLines.TransSell END) AS InvNotProcessed,
(CASE WHEN CORDDocs.Status = 'Open' AND CORDDocs.DocType = 'CORD' THEN CORDLines.TransSell END) AS OpenOrders,
(CASE WHEN CORDDocs.Status = 'Posted' AND CORDDocs.DocType = 'CORD' THEN CORDLines.RemVal END) AS PostedOrders,*/
--Mail Order Group
/* (CASE WHEN (
Clients.ClntGroup = 'Mail Order - Export' or
Clients.ClntGroup = 'Mail Order - Local' or
Clients.ClntGroup = 'Mail Order - Prop' or
Clients.ClntGroup = 'Web Sales - Export' or
Clients.ClntGroup = 'Web Sales - Local' ) and (
CORDDocs.DocType = 'CASR' or
CORDDocs.DocType = 'Cord' or
CORDDocs.DocType = 'Mord' or
CORDDocs.DocType = 'CASW') AND (
DATEDIFF(MONTH,CORDDocs.DateOpn,GETDATE()) = 0 and CORDDocs.DateOpn <= GETDATE()
)THEN CORDLines.TransSell END) AS MailSales, */
(CASE WHEN Clients.ClntGroup = 'Web Sales - Local' THEN Clients.ClientName WHEN Clients.ClntGroup = 'Mail Order' THEN Clients.ClientName WHEN Clients.ClntGroup = 'Web Sales - Export' THEN Clients.ClientName END) AS WebClient,
(CASE WHEN Clients.ClntGroup = 'Local Market' THEN Clients.ClientName WHEN Clients.ClntGroup = 'Local Market - Pharm' THEN Clients.ClientName WHEN Clients.ClntGroup = 'Export Market - UK' THEN Clients.ClientName WHEN Clients.ClntGroup = 'Export Market' THEN Clients.ClientName END) AS CustomerName,
CORDDocs.DocumentName, Continents.name AS Region, Countries.name AS Countries
FROM dbo.Documents AS CORDDocs INNER JOIN
Clients ON CORDDocs.ObjectID = dbo.Clients.ClntID LEFT Outer JOIN
Continents INNER JOIN
Countries ON Continents.code = Countries.continent_code ON Clients.Country = Countries.name Left Outer JOIN
dbo.DocumentsLines AS CORDLines ON CORDDocs.DocID = CORDLines.DocID AND CORDLines.TrnType = 'L' AND CORDLines.LneType <> 'Fght' AND CORDLines.LneType <> 'Manf' LEFT OUTER JOIN
dbo.DocumentsLines AS CDSPLines ON CORDLines.TranID = CDSPLines.SourceID AND CDSPLines.TrnType = 'L' AND (CDSPLines.Status = 'Posted' OR
CDSPLines.Status = 'Closed') LEFT OUTER JOIN
dbo.Documents AS CDSPDocs ON CDSPLines.DocID = CDSPDocs.DocID
WHERE (CORDDocs.DocType <> 'REQN') AND (CORDDocs.DocType <> 'SORD') AND (CORDDocs.DocType <> 'manf') AND (CORDDocs.DocType <> 'BORD') AND
(CORDDocs.DocType <> 'PROD') AND (CORDDocs.DocType <> 'SDEU') AND (CORDDocs.DocType <> 'PMNT') AND (CORDDocs.DocType <> 'PRST') AND
(CORDDocs.DocType <> 'CQUO') AND (CORDDocs.DocType <> 'SDEP') AND (CORDDocs.DocType <> 'JRNL') AND (CORDDocs.DocType <> 'BORD') AND (CORDDocs.DocType <> 'SDEV') AND (CORDDocs.DocType <> 'RCPT') AND
(CORDDocs.DocType <> 'PRSR') AND (CORDDocs.DocType <> 'REQU') AND (CORDDocs.DocType <> 'SINX') AND (CORDDocs.DocType <> 'ASEX') AND
(CORDDocs.DocType <> 'PRTR') AND (CORDDocs.DocType <> 'SINV') AND (CORDDocs.DocType <> 'JRNR') --AND (CORDDocs.DocType <> 'CDSP')
AND (CORDDocs.DocType <> 'PROX') AND (CORDDocs.DocType <> 'MANX') AND (CORDDocs.DocType <> 'INSP') And (Clients.ClntGroup is not NULL)
AND (CORDDocs.DocType <> 'HIVE') AND (CORDDocs.DocType <> 'REQP')
GROUP BY CORDDocs.CreateDate, Clients.CustCateg, Continents.name, Countries.name, CORDDocs.Status, Clients.ClientName, Clients.ClntGroup, CORDDocs.DateOpn, CORDDocs.DateDue,
CORDDocs.DocumentName, CORDDocs.DocType, CORDLines.RemVal, Clients.Country, CORDLines.TransSell, CORDLines.TrnType, CORDDocs.DocID
Order by Clients.ClientName, Clients.CustCateg, Clients.ClntGroup, CORDDocs.DocType, CORDDocs.Status--, CORDDocs.DateOpn,
end
GO
Thnk you guys
Eric
No comments:
Post a Comment