62,046
社区成员
发帖
与我相关
我的任务
分享
/*
author:旋风
date:2008-9-12 21:21:00
version:V1.0
memo:SQL自动生成月份天数列
*/
--> 测试数据: [T]
if object_id('[T]') is not null drop table [T]
create table [T] ([用户] varchar(4),[时间] datetime,[考勤类型] varchar(4))
insert into [T]
select '张三','2008-8-3','出差' union all
select '张三','2008-8-4','请假' union all
select '李四','2008-8-5','请假' union all
select '王五','2008-8-6','出差' union all
select '王五','2008-8-7','请假' union all
select '赵六','2008-8-2','请假'
select * from [T]
go
create procedure createmdays
@month int--月份
as
declare @sql varchar(8000),
--@month int,--月份
@mcount int,--月天数
@mdate datetime,--月初始值
@i int--计数
begin
--set @month=8
set @i=1
set @mdate=ltrim(str(datepart(yy,getdate()))+'-'+ltrim(@month)+'-'+'1')
select @mcount=datediff(dd,@mdate,dateadd(mm,1,@mdate))
--print @mcount
set @sql='select 用户'
while @i<=@mcount
begin
set @sql=isnull(@sql+',','')+'max(case datepart(dd,时间) when '+ltrim(@i)+' then [考勤类型] else '''' end) as ['+ltrim(@i)+']'
set @i=@i+1
end
select @sql=@sql+' from T where datepart(mm,时间)='+ltrim(@month)+' group by 用户 '
--print @sql
exec(@sql)
end
go
exec createmdays 8--执行
go
--删除测试
drop table T
drop procedure createmdays
/*
用户 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
李四 请假
王五 出差 请假
张三 出差 请假
赵六 请假
(4 行受影响)
*/
void databind()
{
DataTable dt = new DataTable();
int month=DateTime.Now.Month;
dt.Columns.Add("姓名");
if (month == 1 || month == 3 || month == 5 || month == 7 || month == 8 || month == 10 || month == 12)
{
for (int i = 1; i < 32; i++)
{
dt.Columns.Add("第"+i+"天");
}
}
else
{
for (int i = 1; i < 31; i++)
{
dt.Columns.Add("第" + i + "天");
}
}
DataRow row = dt.NewRow();
row[0] = "张三";
row[30] = "111";
dt.Rows.Add(row);
GridView1.DataSource = dt;
GridView1.DataBind();
}
//取得月份的天数
public int GetMdays(int month)
{
DateTime d1 = new DateTime(DateTime.Now.Year, month, 1);//刚这里有错
int tdays = (d1.AddMonths(1) - d1).Days;
return tdays;
}
//动态生成DataTable,根据每月的天数生成如1-31天的列,再根据查询的表数据进行赋值
private void button5_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("name",typeof(string));
int tdays = GetMdays(9);//取天数
for (int i = 1; i <=tdays; i++)//添加列
{
dt.Columns.Add(i.ToString(), typeof(string));
}
//循环赋值
int count = 4;//假设是SQL查询出的姓名行数
for (int i = 0; i < count; i++)
{
DataRow dr = dt.NewRow();
dr["name"] = "name"+(i+1).ToString();//假设是SQL查询出的姓名值
for (int j = 1; j <= tdays; j++)
{
dr[j.ToString()] = j.ToString();//假设是SQL查询出列的值
}
dt.Rows.Add(dr);
}
GridView3.DataSource = dt;//绑定
GridView3.DataBind();
}
//取得月份的天数
public int GetMdays(int month)
{
DateTime d1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
int tdays = (d1.AddMonths(1) - d1).Days;
return tdays;
}