I have a query which gives me the result as
the query is
select count(*) tot_count,'Total' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
union all
select count(*) tot_count,'Inward' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103) and status_flag in ('6')
union all
select count(*) tot_count,'1st Level Transfer' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag in ('4','26','24')
union all
select count(*) tot_count,'Data Entry Transfer' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag in ('15','20')
union all
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')
union all
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'
union all
select count(*) tot_count,'Close' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag='5'
/*union all
select count(*) tot_count,'Return to Creator' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag='6'*/
union all
select count(*) tot_count,'Communication Transfer' stage from inward_doc_tracking_hdr where doc_date >=convert(datetime,'24/05/2016',103)
and status_flag='16'
So, what I want is, I want to use this query in the Stored procedure with two parameters such as
from_date and To_date which will be dynamic in the above query.
Also, I want to display result as below.
SR_NO Total Inward 1stLevelTransfer
1 8 0 2
I am using sql-server-2005
kindly suggest how to do
Aucun commentaire:
Enregistrer un commentaire