Weekly data comparison, how often do we?

Category: MS-SQL Server -> Applications Author: xuteng123 Date: 2001-07-08 10:49:04
 
xuteng123
2001-07-08 10:49:04
Example, using this year's weekly data and weekly data last year compared
example
week last year
1 10000 11000
2 99999 22222
...
52 11111 ; 11112
53 22222 11111

the first week of each year is calculated from the day began,
is From January 1 to the end of this week.
example: January 1, 2010 began on Saturday, Sunday, Jan. 2 to the end.
or the first day of the first week of the year
example: January 3, 2011, a Monday, which is the first day of the first week of the year. To end Sunday, January 9.

last year data comparisons. Last year's data, whether it is according to nature to compare it

weeks per year the last week of how the data processing.
example, December 31, 2010 was a Friday
considered when comparing data this week is actually the first 53 weeks, that I was not put data together to January 2 do?
while hoping to give sql statement.
can only send up to 100 points, that little can additionally open paste
xinzhenhuaxinzhenhua
2001-07-08 10:58:19


IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'TEst')
DROP TABLE TEst
SELECT '2010-1-1 'Dt, '100' Qt
INTO TEst
UNION ALL SELECT '2010-2-1 ', '200'
UNION ALL SELECT '2009-2-1 ', '300'
UNION ALL SELECT '2009-3-1 ', '400'
UNION ALL SELECT '2010-2-1 ', '500'

IF EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE name = '# # TEMP')
DROP TABLE # # TEMP
SELECT TOP 0'' [week], Qt [this year], Qt [last year]
INTO # # TEMP
FROM dbo.test

INSERT INTO # # TEMP ([Week], [year], [year])
SELECT DATEPART (week, dt), Qt, 0 FROM dbo. test
WHERE Dt> = '2009-1-1 'AND Dt <'2010-1-1'

INSERT INTO # # TEMP ([Week], [year], [year])
SELECT DATEPART (week, dt), 0, Qt FROM dbo.test
WHERE ; Dt> = '2010-1-1 'AND Dt <'2011-1-1'
- comparison results
SELECT * ; FROM # # TEMP
DROP TABLE # # TEMP
gubinger
2001-07-08 11:06:28
Compare by week not as chronological comparison.

annually on the first day of the week may not be the same, but the first day of every year is January 1.

weather forecast is higher than a year earlier date.

1 1st to January 7th count the first week, so unified annually, except for leap year!

mention ideas, for reference only.
toddyfan
2001-07-08 11:12:17
General Different companies have their own definition of the cycle, such as financial accounting month, 28 days or 30 days a year of 13 months to statistics, by nature like you date, very troublesome, and bad time control.
shiqillx
2001-07-08 11:32:14
If that years ago, years after the docking of the two weeks in question
cross week because it is easy, is not recommended as a natural Week.
nan081207
2001-07-08 11:38:02
you to customize a calendar

ratio consequently

think how reliable than on how than
helong022042
2001-07-08 11:53:34
are generally on a monthly basis to statistical comparison, if we, following week, the head and tail than two weeks removed again, because there is no comparable.
hyn5252
2001-07-08 12:08:28
first calculate the week, and then the two subqueries connected
w137650207
2001-07-08 12:26:43

good way. .
ZXCZXC1234567890
2001-07-08 12:45:36

DATEPART(wk,date)--  。
 select DATEPART(wk,date),sum(id) from tb
zl16885168
2001-07-08 12:55:44
is indeed a lot of trouble just trying to write a bit sql find basic, it seems, simply to achieve can not.