--组织结构表
USE [yc_hw_ems]
/****** 对象:  Table [dbo].[t_ems_organization]    脚本日期: 06/29/2012 15:38:21 ******/
CREATE TABLE [dbo].[t_ems_organization](
    [id] [varchar](255) COLLATE Chinese_PRC_CI_AS NOT NULL primary key,
    [dep_name] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--部门名称
    [dep_no] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--部门编号
    [remarks] [text] COLLATE Chinese_PRC_CI_AS NULL,--备注
    [parent_id] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--上级部门ID
    [state] [int] NULL,--记录状态 0，1，2 。0为已删除
    [indexs] [int] NULL,索引编号
）
--设备表
USE [yc_hw_ems]
/****** 对象:  Table [dbo].[t_ems_equi_accounting]    脚本日期: 06/29/2012 15:41:16 ******/
CREATE TABLE [dbo].[t_ems_equi_accounting](
    [id] [varchar](255) COLLATE Chinese_PRC_CI_AS NOT NULL primary key,
    [code] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--设备编码
    [model] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--型号
    [brand] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--品牌
    [unit] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--数量
    [number] [int] NULL,
    [install_company] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,
    [position] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,
    [install_date] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [money] [float] NULL,
    [warranty] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,
    [remarks] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,
    [dept_id] [varchar](255) COLLATE Chinese_PRC_CI_AS NULL,--部门id
)

DECLARE @Name VARCHAR(800)
SET @Name = '运城公司'

;WITH T AS
(
  SELECT ID , PID , dep_name ,dep_no,indexs
  FROM dbo.t_ems_organization
  WHERE dep_name = @Name and state=1
  
  UNION all
  
  SELECT A.ID , A.PID , A.dep_name ,A.dep_no,A.indexs
  FROM dbo.t_ems_organization AS A 
    JOIN T AS B 
        ON B.ID = A.PID and A.state =1
)

SELECT  equi_name,code,brand,model,unit,ISNULL(money,0) AS 'money',install_date,install,remarks,station_num 
FROM dbo.t_ems_equi_accounting as a 
    inner join dbo.t_ems_equi_accounting a1
        on a.id = a1.id
    inner join T t1
        on a1.station_num=t1.dep_no 
where a.state <> 0    
    and create_date between '2011-01-11' and dateadd(millisecond, -3, cast('2012-11-11' as datetime)+1)