mercredi 25 mai 2016

Create procedure with UNION ALL query and pass date as parameters

I have a query which gives me the result as

SQL result

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