|
Comparari date calendaristice
Last post 05-15-2011, 3:26 PM by sibilla. 7 replies.
-
10-25-2010, 9:45 PM |
-
Ysabet
-
-
-
Joined on 10-25-2010
-
-
db_datawriter
-
-
|
Comparari date calendaristice
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
|
|
-
10-26-2010, 11:16 AM |
-
rsocol
-
-
-
Joined on 10-03-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: Comparari date calendaristice
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
|
|
-
10-27-2010, 12:05 PM |
-
Ysabet
-
-
-
Joined on 10-25-2010
-
-
db_datawriter
-
-
|
Re: Comparari date calendaristice
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
|
|
-
10-29-2010, 9:41 PM |
-
Ysabet
-
-
-
Joined on 10-25-2010
-
-
db_datawriter
-
-
|
Re: Comparari date calendaristice
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
|
|
-
10-30-2010, 5:26 PM |
-
rsocol
-
-
-
Joined on 10-03-2006
-
Bucuresti
-
sysadmin
-
-
|
Re: Comparari date calendaristice
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
|
|
-
10-31-2010, 12:36 AM |
-
B_gd_n[ ]Sahlean
-
-
-
Joined on 07-17-2007
-
Bucuresti
-
sysadmin
-
-
|
Re: Comparari date calendaristice
[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]
|
|
-
12-01-2010, 3:04 PM |
-
Ysabet
-
-
-
Joined on 10-25-2010
-
-
db_datawriter
-
-
|
Re: Comparari date calendaristice
Buna Bogdan,
mersi mult pentru raspunsul tau.
Ysa
|
|
-
05-15-2011, 3:26 PM |
-
sibilla
-
-
-
Joined on 05-10-2011
-
-
db_datareader
-
-
|
Re: Comparari date calendaristice
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.
|
|
|
|
|