今天做的供货商缺货率列表

sql：

select
suppliercode,
count(*) as short,
(
select count(*)
from YM_products
where 1=1 and oldOrderDate >='2010-05-09' and oldOrderDate <'20101018' and Status>0 and suppliercode=b.suppliercode
)
as [normal],
( case
when (
    select count(*)
    from YM_products
    where 1=1 and oldOrderDate >='2010-05-09' and oldOrderDate <'20101018' and Status >0  and suppliercode=b.suppliercode)=0
then 0
else
cast ((select count(*)
     from YM_products
    where 1=1 and oldOrderDate >='2010-05-09' and oldOrderDate <'20101018' and jhstatus='缺货' and suppliercode=b.suppliercode)*1.0*100/(
    select count(*)
    from YM_products
    where 1=1 and oldOrderDate >='2010-05-09' and oldOrderDate <'20101018' and Status >0  and suppliercode=b.suppliercode)*1.0 
as numeric(10, 2)) end)
as rate
from YM_products b
where 1=1 and oldOrderDate >='2010-05-09' and oldOrderDate <'20101018' and jhstatus='缺货' and suppliercode is not null and suppliercode !='' 
group by SupplierCode

 

后台绑定：

protected void BindRepeater()
    {
        string startDate = "0";
        string endDate = "0";
        try
        {
            string sqlwhere = " 1=1 ";
            string suppliercode = " and  1=1  ";

 


            //上传日期
            if (this.ipstartDate.Value.Trim() != "")
            {
                startDate = this.ipstartDate.Value;
                sqlwhere += " and oldOrderDate >='" + startDate + "' ";
            }
            if (this.ipendDate.Value.Trim() != "")
            {
                DateTime d = DateTime.Parse(this.ipendDate.Value).AddDays(1);
                string dMonth = d.Month >= 10 ? d.Month.ToString() : "0" + d.Month.ToString();
                string dDay = d.Day >= 10 ? d.Day.ToString() : "0" + d.Day.ToString();
                endDate = d.Year.ToString() + dMonth + dDay;
                sqlwhere += " and oldOrderDate <'" + endDate + "' ";
            }

            if (ipSupplierCode.Value.Trim() != "")
            {
                suppliercode += " and SupplierCode='" + ipSupplierCode.Value + "'";

            }
            DataSet ds = new DataSet();
            string ds_sql = "select suppliercode,count(*) as short,(select count(*) from YM_products where  " + sqlwhere + " and Status>0 and suppliercode=b.suppliercode) as [normal],( case when (select count(*) from YM_products where " + sqlwhere + " and Status >0  and suppliercode=b.suppliercode)=0 then 0 else cast ((select count(*) from YM_products where " + sqlwhere + " and jhstatus='缺货' and suppliercode=b.suppliercode)*1.0*100/(select count(*) from YM_products where " + sqlwhere + " and Status >0  and suppliercode=b.suppliercode)*1.0  as numeric(10, 2)) end) as rate from YM_products b where" + sqlwhere + " and jhstatus='缺货' and suppliercode is not null and suppliercode !='' " + suppliercode + " group by SupplierCode";
            ds_sql = string.Format(ds_sql, ipSupplierCode.Value);
            // Response.Write(ds_sql);
            ds = DB.getdataset(ds_sql);
            this.rep2.DataSource = ds;
            this.rep2.DataBind();

            DataSet ds2 = new DataSet();
            string ds_sql2 = "select (select count(*) from YM_products where" + sqlwhere + " and jhStatus='缺货') as shortage,(select count(*) from YM_products where " + sqlwhere + " and Status>0 )as [normal], cast((select count(*) from YM_products where" + sqlwhere + " and jhStatus='缺货')*1.0*100/(select count(*) from YM_products where " + sqlwhere + " and Status>0 )*1.0 as numeric(10, 2)) as rate";
            //Response.Write(ds_sql);
            ds2 = DB.getdataset(ds_sql2);
            rep1.DataSource = ds2;
            rep1.DataBind();

        }
        catch
        {
            MessageBox.ShowAndClose(this.Page, "初始化失败~请重新进入~");
        }

    }