m******t
发帖数: 6905 | 1 Hi all
I created a table and tried the solution above but it failed. who can help
--Creat Table and loading data by using bulk insert
Drop Table Test20140317
Create Table Test20140317
(SalesDate date,
Sales Decimal)
Bulk insert Test20140317
from 'C:\filepath\Book1.txt'
with
(ROWTERMINATOR = '\n',
firstrow = 2)
select * from Test20140317
--Here is the above solution, I used DateName function to display week days.
select
Month(SalesDate),
case when DATENAME(WEEKDAY,(SalesDate)) = 0 then ... 阅读全帖 |
|
c*******y
发帖数: 8 | 2 厄
尝试一下
SQL Server 2008 Enterprise
declare @YY int;
declare @MM int;
set @YY = 2009;
set @MM = 11;
With CalenderCTE (DayNum,[Date],[WeekDay])
as(
select 1 as DayNum,
CONVERT(datetime,convert(nvarchar(8),@YY * 10000 + @MM * 100 + 1)) [
Date],
DATENAME(WEEKDAY,CONVERT(datetime,convert(nvarchar(8),@YY * 10000 +
@MM * 1
00 + 1))) [WeekDay]
union all
select Day(DATEADD(dd,1,[Date])) as DayNum,
DATEADD(dd,1,[Date]) as [Date],
DATENAME(WEEKDAY,DATE |
|
m*********u
发帖数: 1491 | 3 这是老土的学生们做出来的答案, 用的是SQL Server：
=========================================
use test1
go
if object_id('t1') is not null
drop table t1
create table t1(
saleDate smalldatetime,
sales money)
insert into t1 values('11/01/2012',110), ('12/01/2012', 200), ('01/03/2013',
120),('01/04/2014',300)
select Month, [Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun]
from (select datepart(month, saleDate) as Dmonth,
left(datename(M,saleDate), 3) as Month,
left(datename(dw,saleDate),3) as day,
sales... 阅读全帖 |
|
d***e
发帖数: 793 | 4 sum（case when DATENAME(WEEKDAY,(SalesDate)) = 0 then sales else 0 end） |
|
A*******n
发帖数: 625 | 5 改一下 table t里面就可以了
select m,[Monday],[Tusday],[Wednesday],[Thursday],[Friday],[Saturday],[
Sunday]
from
(
select month('06/05/2014') as m,DATENAME(weekday,('06/05/2014')) as da,100
as sales) as t
pivot
(
sum(sales)
for t.da in ([Monday],[Tusday],[Wednesday],[Thursday],[Friday],[Saturday],[
Sunday])
) as pivottable |
|
