在临时表中添加select计数

Question

我想运行这个SP，但是它没有工作，并且给出了错误

不能将为变量赋值的SELECT语句与数据检索操作相结合。

ALTER PROCEDURE GET_RECORDS_FORDATE

            @From_date Datetime,
            @To_date Datetime

            AS
            BEGIN


                SELECT * 
            INTO #temp 
            FROM ( 
                    select 0 SR_NO, 0 date, 0 Total, 0 Inward, 0 First_Level_Transfer, 0 Data_Entry_Transfer, 
                            0 Second_Level_Transfer, 0 Outward_Transfer,
                            0 Closed, 0 Communication_Transfer
                    from inward_doc_tracking_hdr
            ) AS x 


            select count(*) tot_count,'Total' stage from inward_doc_tracking_hdr  
            where 
            doc_date between convert(datetime, @From_date ,103) 
                and convert(datetime, @To_date, 103)  

            select count(*) tot_count,'Inward' stage from inward_doc_tracking_hdr  
            where
            doc_date between convert(datetime, '24/05/2016',103) 
             and convert(datetime, '26/05/2016', 103)  
             and status_flag in ('6')

            select count(*) tot_count,'1st Level Transfer' stage from inward_doc_tracking_hdr  
            where doc_date between convert(datetime, '24/05/2016',103) 
            and convert(datetime, '26/05/2016', 103)  
            and status_flag in ('4','26','24')

            select count(*) tot_count,'Data Entry Transfer' stage from inward_doc_tracking_hdr  
            where doc_date between convert(datetime, '24/05/2016',103) 
            and convert(datetime, '26/05/2016', 103)  
            and status_flag in ('15','20')

            select count(*) tot_count,'2nd Level Transfer' stage from inward_doc_tracking_hdr  
            where doc_date >=convert(datetime,'24/05/2016',103)
            and status_flag in ('17','21')

            select count(*) tot_count,'Outward Transfer' stage from inward_doc_tracking_hdr 
            where doc_date >=convert(datetime,'24/05/2016',103)
            and status_flag='18'

            select count(*) tot_count,'Close' stage from inward_doc_tracking_hdr  
            where doc_date between convert(datetime, '24/05/2016',103) 
                and convert(datetime, '26/05/2016', 103)  
            and status_flag='5'


            select count(*) tot_count,'Communication Transfer' stage from inward_doc_tracking_hdr 
             where doc_date between convert(datetime, '24/05/2016',103) 
                and convert(datetime, '26/05/2016', 103)  
            and status_flag='16'
  END


    SELECT * FROM #temp 
  END 

我想按照SR_NO和date临时表中的列添加计数。

备注表示我想在24-26之间添加日期

我正在使用sql-server-2005

Answer 1

如果我能理解

试试这个：

第一步：

表#temp的创建

CREATE TABLE #temp(
    date datetime, Total int, Inward int, First_Level_Transfer int,
    Data_Entry_Transfer int, 
    Second_Level_Transfer int, Outward_Transfer int,
    Closed int, Communication_Transfer int
)

INSERT INTO #temp
(date, Total, Inward, First_Level_Transfer,
    Data_Entry_Transfer, 
    Second_Level_Transfer, Outward_Transfer,
    Closed, Communication_Transfer)
SELECT
    doc_date, COUNT(*),
    SUM(
    CASE
        WHEN status_flag = '6' THEN 1 ELSE 0
    END),
    SUM(
    CASE
       WHEN status_flag in ('4','26','24') THEN 1 ELSE 0
    END),
    SUM(
    CASE
       WHEN status_flag in ('15','20') THEN 1 ELSE 0
    END),
    SUM(
    CASE
       WHEN status_flag in ('17','21') THEN 1 ELSE 0
    END),
    SUM(
    CASE
       WHEN status_flag='18' THEN 1 ELSE 0
    END),
    SUM(
    CASE
       WHEN status_flag='5' THEN 1 ELSE 0
    END),
    SUM(
    CASE
       WHEN status_flag='16' THEN 1 ELSE 0
    END)
FROM inward_doc_tracking_hdr
WHERE doc_date between @From_date and @To_date AND status_flag <> '6'
GROUP BY doc_date

我想，因为您想要计数器，所以这些都不是与单个SR_NO链接的。

告诉我是否可以，否则我们可以调优查询