Buna seara,

as avea si eu o intrebare.
Lucrez cu sql server 2005. Am de realizat un Raport in Reporting Services (un matrix):
An/Luna/Saptamana/Zi an curent - An/Luna/Saptamana/Zi  anul trecut.

Cu ajutorul unui Assambly  - sincronizez: Luni a.c. cu Luni anul trecut (Luni 18.Oct.2010 Vanzari:10 000 - Luni 19.Oct.2009 Vanzari: 9 200)

  A.C.                           A.T.                        Vanzari
Vineri 01.10.2010       Vineri 02.10.2010         5 000 (a.c)
Luni    04.10.2010       Luni   03.10.2010         6 200 (a.c)
...                                    ...                            ...
...                                    ...                            ...
...                                    ...                            ...
Luni 19.10.2010          Luni 18.10.2009          6 900 (a.c)
Marti 20.10.2010        Marti19.10.2009          9 000 (a.c)

Problma este:
trebuie sa compar Luna a.c. cu Luna a.t.
ParallelPeriod (mdx) nu functioneaza, deoarece am o sincronizare de zile.
Pe intelesul tuturor:

A.C.                           A.T.                          Vanzari
                                Joi      01.10.2010       6 100 <- aceasta pozitie imi lipseste din raport. Numai asa pot compara Oct.2010 cu Oct.2009
Vineri 01.10.2010       Vineri 02.10.2010         5 000 (a.c)
Luni    04.10.2010       Luni   03.10.2010         6 200 (a.c)
...                                    ...                            ...
...                                    ...                            ...
...                                    ...                            ...
Luni 19.10.2010          Luni 18.10.2009          6 900 (a.c)
Marti 20.10.2010        Marti19.10.2009          9 000 (a.c)




Are cineva vreo Idee cum as putea rezolva aceasta prolema cu ajutorulu unui script MDX?
Sau o alta idee?

Va multumesc anticipat
Ysa

Eu aş face toate calculele în Transact-SQL, de exemplu (în baza de date AdventureWorks):

DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate='20031001'
SET @EndDate='20031231'

SELECT * FROM (
  SELECT YEAR(OrderDate) AS YearNumber, MONTH(OrderDate) AS MonthNumber, DAY(OrderDate) AS DayNumber,
    OrderDate, WeekDayName, CurrentSales, PreviousYearDate,
    (SELECT SUM(TotalDue) FROM Sales.SalesOrderHeader WHERE OrderDate=PreviousYearDate) AS PreviousYearSales
  FROM (
    SELECT OrderDate, WeekDayName, CurrentSales,
      DATEADD(DAY,(DATEPART(WEEKDAY,OrderDate)-DATEPART(WEEKDAY,DATEADD(YEAR,-1,OrderDate))+7)%7,DATEADD(YEAR,-1,OrderDate)) AS PreviousYearDate
    FROM (
      SELECT OrderDate, DATENAME(WEEKDAY,OrderDate) AS WeekDayName, SUM(TotalDue) AS CurrentSales
      FROM Sales.SalesOrderHeader
      WHERE OrderDate BETWEEN @StartDate AND @EndDate
      GROUP BY OrderDate
    ) t1
  ) t2
  UNION ALL
  SELECT YearNumber, MonthNumber, NULL AS DayNumber, NULL AS OrderDate, NULL AS WeekDayName, CurrentSales, NULL AS PreviousYearDate,
    (SELECT SUM(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate)=YearNumber-1 AND MONTH(OrderDate)=MonthNumber) AS PreviousYearSales
  FROM (
    SELECT YEAR(OrderDate) AS YearNumber, MONTH(OrderDate) AS MonthNumber, SUM(TotalDue) AS CurrentSales
    FROM Sales.SalesOrderHeader
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
  ) t3
) t4
ORDER BY YearNumber, MonthNumber, -DayNumber DESC

Răzvan

Salut Razvan,

Mersi mult!!

Voi incerca sa implementez acest cod in ETL.

(Ai tinut in evidenta si faptul ca, in acelasi timp, zilele saptamanii trebuie sa fie sincronizate, si faptul ca numai pentr anul trecut am nevoie de acea zi in plus (01. a lunii)?) :)

Dupa un test voi afla si eu raspunsul! :)

Mersi!!
Ysa

Salut Razvan,

imi poti spune te rog care veziune AdventureWorks ai folosit?
Eu am incercat cu AdwenturWork2005 si 2008R2 dar (nume), coloane nu coincid... :(
Sa incerc AdwenturWork2000??

Mersi
Ysa

Mie îmi merge şi cu varianta 2005, şi cu varianta 2008 şi cu varianta 2008R2 a bazei de date AdventureWorks. Singura diferenţă este că în varianta 2008R2 nu returnează nimic deoarece datele se referă la perioade ceva mai recente (ar trebui înlocuit la început anul 2003 cu anul 2007, de exemplu).

Dacă ţie îţi dă eroare "Invalid object name 'Sales.SalesOrderHeader'", vezi să nu fie vorba de AdventureWorksDW sau AdventureWorksLT, ci de AdventureWorks simplu (OLTP). Personal, prefer varianta Case Insensitive, deci am instalat fişierul AdventureWorksDBCI_x64.msi de la http://msftdbprodsamples.codeplex.com/releases/view/4004. Oricum, ar trebui să meargă la fel de bine şi cu varianta Case Sensitive (adică fără CI în denumire), precum şi cu varianta de 32 de biţi.

Răzvan

[1] Cu titlu de exemplu:
DECLARE    @AnCurent INT = 2010;

DECLARE    @Facturi TABLE
(
      IDFactura    INT IDENTITY(1,1) PRIMARY KEY,
      Data      DATE NOT NULL,
      Valoare      INT NOT NULL
);
INSERT    @Facturi
--2009
SELECT    '2009-01-01', 100
UNION ALL
SELECT    '2009-01-01', 101
UNION ALL
SELECT    '2009-01-11', 111
UNION ALL
SELECT    '2009-02-02', 200
UNION ALL
SELECT    '2009-02-23', 222
UNION ALL
--2010
SELECT    '2010-01-01', 1000
UNION ALL
SELECT    '2010-02-01', 2000
UNION ALL
SELECT    '2010-02-22', 2002
UNION ALL
SELECT    '2010-02-22', 2022;

SELECT    f.*
FROM    @Facturi f;

WITH    Rezultate
AS
(
      SELECT      f.Data,
              YEAR(f.Data)      AS An,
              MONTH(f.Data)      AS LunaDinAn,
              DATEPART(wk, f.Data)  AS SaptamanaDinAn,
              DATEPART(dw, f.Data)  AS ZiDinSaptamana,
              SUM(f.Valoare)      AS Valoare
      FROM      @Facturi f
      GROUP BY    f.Data,
              YEAR(f.Data),
              MONTH(f.Data),
              DATEPART(wk, f.Data),
              DATEPART(dw, f.Data)
),      RezultateAC
AS
(
      SELECT      *
      FROM      Rezultate r
      WHERE      r.An = @AnCurent
),      RezultateAT
AS
(
      SELECT      *
      FROM      Rezultate r
      WHERE      r.An = @AnCurent - 1
)
SELECT    ac.Data                      AS DataAC,
      at.Data                      AS DataAT,
      COALESCE(ac.LunaDinAn, at.LunaDinAn)      AS LunaDinAn,
      COALESCE(ac.SaptamanaDinAn, at.SaptamanaDinAn)  AS SaptamanaDinAn,
      COALESCE(ac.ZiDinSaptamana, at.ZiDinSaptamana)  AS ZiDinSaptamana,
      COALESCE(ac.Valoare, 0)              AS ValoareAC,
      COALESCE(at.Valoare, 0)              AS ValoareAT
FROM    RezultateAC ac
FULL JOIN  RezultateAT at ON ac.LunaDinAn = at.LunaDinAn
      AND ac.SaptamanaDinAn = at.SaptamanaDinAn
      AND ac.ZiDinSaptamana = at.ZiDinSaptamana
ORDER BY  COALESCE(ac.LunaDinAn, at.LunaDinAn),
      COALESCE(ac.SaptamanaDinAn, at.SaptamanaDinAn),
      COALESCE(ac.ZiDinSaptamana, at.ZiDinSaptamana)

[2] Non Recursive Common Table Expressions - Performance ... [1] & [2]

Buna Bogdan,

mersi mult pentru raspunsul tau.

Ysa

Thanks interesting information+1)

---

I generally trigger to my tender friends to order essay stealthily, because mostly they have bulky dubieties with doing of other constructive tasks.