Booking and Sales Summary MAR021

USE [mandb101]
GO
/****** Object: StoredProcedure [dbo].[sp_OMAR_021]    Script Date: 03/15/2009 18:39:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Eric Panorel>
-- ALTER date: <11/19/2007>
-- Description:    <Stored Procedure for OMAR - 021 created from scratch>
-- =============================================
CREATE PROCEDURE [dbo].[sp_OMAR_021]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

declare @temp TABLE
(
ma_bilname varchar(200),
ma_bilno varchar(200),
ma_bilctry varchar(200),
[Total Sales 2007] money,
[Total Sales 2005] money,
[Total Sales 2006] money
)

select ma_bilname, ma_bilno, ma_bilctry,
sum(adjustedpricebase * ma_indqty) as 'Total Sales 2007',
( select sum(adjustedpricebase * ma_indqty) from dbo.datamart_s_sales_details v2
Where fiscalDate >= '1/1/2005 12:00:00 AM'
and fiscalDate < '1/1/2006 12:00:00 AM'
and v1.ma_bilno = v2.ma_bilno
Group By ma_bilname, ma_bilno, ma_bilctry) as 'Total Sales 2005',
(select sum(adjustedpricebase * ma_indqty) from dbo.datamart_s_sales_details v3
Where fiscalDate >= '1/1/2006 12:00:00 AM'
and fiscalDate < '1/1/2007 12:00:00 AM'
and v1.ma_bilno = v3.ma_bilno
Group By ma_bilname, ma_bilno, ma_bilctry) as 'Total Sales 2006'
INTO #tmpSales567
from dbo.datamart_s_sales_details v1
Where fiscalDate >= '1/1/2007 12:00:00 AM'
and fiscalDate < '1/1/2008 12:00:00 AM'
--and MA_BILNO='99084' /* debug */
Group By ma_bilname, ma_bilno, ma_bilctry

/* hold the 2007 ma_bilno */
insert into @temp
select *
from #tmpSales567

INSERT INTO #tmpSales567
/* OLD Data from ARC */
select ma_bilname, ma_bilno, ma_bilctry,
sum(0) as 'Total Sales 2007',
( select sum(adjustedpricebase * ma_indqty) from mandb101_arc.envymgr.datamart_s_sales_details av2
Where fiscalDate >= '1/1/2005 12:00:00 AM'
and fiscalDate < '1/1/2006 12:00:00 AM'
and v1.ma_bilno = av2.ma_bilno
Group By ma_bilname, ma_bilno, ma_bilctry) as 'Total Sales 2005',
(select sum(adjustedpricebase * ma_indqty) from mandb101_arc.envymgr.datamart_s_sales_details av3
Where fiscalDate >= '1/1/2006 12:00:00 AM'
and fiscalDate < '1/1/2007 12:00:00 AM'
and v1.ma_bilno = av3.ma_bilno
Group By ma_bilname, ma_bilno, ma_bilctry) as 'Total Sales 2006'
from @temp v1
/*
Where fiscalDate >= '1/1/2007 12:00:00 AM'
and fiscalDate < '1/1/2008 12:00:00 AM'
*/
Group By ma_bilname, ma_bilno, ma_bilctry

-- SUM 'em all

select ma_bilname, ma_bilno, ma_bilctry ,
sum([Total Sales 2007]) as 'Total Sales 2007',
sum([Total Sales 2006]) as 'Total Sales 2006',
sum([Total Sales 2005]) as 'Total Sales 2005'
from #tmpSales567
group by ma_bilname, ma_bilno, ma_bilctry
order by [Total Sales 2007] desc
DROP TABLE #tmpSales567

END