I have a Stored procedure which fetches data according to the date paramters. here is a preview
Now I want to add one more column which gets the MAX and MIN of doc_no from the inward_doc_tracking_hdr table.
Below is the SP
ALTER PROCEDURE [dbo].[GET_RECORDS_FORDATE]
@From_date Datetime,
@To_date Datetime
AS
BEGIN
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, Returned int
)
INSERT INTO #temp
(date, Total, Inward, First_Level_Transfer,
Data_Entry_Transfer,
Second_Level_Transfer, Outward_Transfer,
Closed, Communication_Transfer, Returned)
SELECT
doc_date, COUNT(*),
SUM(
CASE
WHEN status_flag in ('6','23') 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 in ('18','27') 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),
SUM(
CASE
WHEN status_flag='14' 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
END
Select * from #temp
How to get that MAX and MIN document with respective to the TOTAL column.
Aucun commentaire:
Enregistrer un commentaire