I have an OLD SP which is working fine as per the requirement. Here is what it looks like.
and the SP is below:-
ALTER PROCEDURE [dbo].[GET_RECORDS_FORDATE]
@From_date Datetime,
@To_date Datetime
AS
BEGIN
CREATE TABLE #temp(
date datetime, Total int,doc_From_To varchar(50),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,doc_From_To, Inward, First_Level_Transfer,
Data_Entry_Transfer,
Second_Level_Transfer, Outward_Transfer,
Closed, Communication_Transfer, Returned)
SELECT
doc_date, COUNT(*),
(select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select min(mkey) FROM inward_doc_tracking_hdr jj
where jj.doc_date =convert(datetime,aa.doc_date,103) ) )
+ ' - '+
(select kk.doc_no FROM inward_doc_tracking_hdr kk where mkey in (select max(mkey) FROM inward_doc_tracking_hdr jj
where jj.doc_date =convert(datetime,aa.doc_date,103) ) )
,SUM(
CASE
WHEN status_flag in ('6','23') THEN 1 ELSE 0
END)
,SUM(
CASE
WHEN status_flag in ('4','26','24','19') 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 aa
WHERE doc_date between @From_date and @To_date
--AND status_flag <> '6'
GROUP BY doc_date
END
Select * from #temp
So, now what I want is, I want to create a another
SPwhich will have following column as my output and only with one parameter
I am using SQL-server-2005 Please help
Aucun commentaire:
Enregistrer un commentaire