N K N K - 6 months ago 19
SQL Question

Add the count of select in temporary table

I want to run this SP but it is not working and giving error as


A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


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


I want to add the Count as per the column in temporary table with SR_NO and date.

NOTE For time being i want to add for date between 24 - 26

I am using
sql-server-2005

Answer

If I've understood

Try this:

First step:

Creating of table #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

I think, because you want counter, these are not linked with a single SR_NO.

Tell me if it's OK, otherwise we can tune the query