This is a migrated thread and some comments may be shown as answers.

grouping

0 Answers 18 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
ramya kannan
Top achievements
Rank 1
ramya kannan asked on 24 May 2010, 02:04 PM
hi,
 i have  created report without using report wizard... here my coding is

 

private void ShiftReport_NeedDataSource(object sender, EventArgs e)

 

{

 

this.DataSource = null;

 

 

this.shifttab.DataSource = null;

 

 

DateTime StartDate = Convert.ToDateTime(this.ReportParameters["StartDate"].Value);

 

 

DateTime EndDate = Convert.ToDateTime(this.ReportParameters["EndDate"].Value);

 

 

string UserName = Convert.ToString(this.ReportParameters["UserName"].Value);

 

 

OleDbConnection con = new OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["FMS_1_0_0"].ToString());

 

 

OleDbCommand sqlCmd = new OleDbCommand("Shift_Report");

 

 

DataSet dsGetSearchResults = new DataSet();

 

sqlCmd.Connection = con;

sqlCmd.CommandType =

CommandType.StoredProcedure;

 

sqlCmd.Parameters.Add(

"Start_date", OleDbType.Date).Value = StartDate;

 

sqlCmd.Parameters.Add(

"End_date", OleDbType.Date).Value = EndDate;

 

sqlCmd.Parameters.Add(

"UserName", OleDbType.VarChar).Value = UserName;

 

con.Open();

 

OleDbDataAdapter Sqlda = new OleDbDataAdapter(sqlCmd);

 

 

try

 

{

 

Sqlda.Fill(dsGetSearchResults);

 

this.shifttab.DataSource = dsGetSearchResults;

 

 

this.DataMember = "";

 

 

}

 

catch (Exception ex)

 

{

}

}
and
/*the stored procedure is*/

create or replace PROCEDURE Shift_Report
(
       Start_date IN  DATE default null,
       End_date IN DATE default null,
       UserName IN Varchar default null,
       cv_1 IN OUT SYS_REFCURSOR
)
as 
tmp_userid number(10,0);
BEGIN
tmp_userid:=0;

 BEGIN
 select user_id into tmp_userid from ztuiusers where legacy_user_name = UserName ;
  EXCEPTION
  WHEN OTHERS THEN
         NULL;
       
  END;
OPEN cv_1 FOR
select  distinct
   CASE
      WHEN to_char(dol.datecreated,'HH24:MI:SS') BETWEEN '08:00:01' AND '16:45:00' THEN '08:00 - 04:45'
      WHEN to_char(dol.datecreated,'HH24:MI:SS') BETWEEN '16:45:01' AND '23:59:59' THEN '04:45 - 12:00'
      WHEN to_char(dol.datecreated,'HH24:MI:SS') BETWEEN '00:00:00' AND '08:00:00' THEN '12:00 - 08:00'
      END AS ShiftTime,
     -- to_char(dol.datecreated,'HH24:MI:SS') datecreated,
       f.fax_id,
       dol.title,
       Users.legacy_user_name
   from fax f
  JOIN data_object_log dol
          ON dol.entity_id = f.fax_id
 JOIN ztuiusers Users
         ON Users.user_id = f.user_id
 JOIN data_object_log_types dolt
        ON dolt.data_object_id = dol.data_object_id
 JOIN data_objects do
        ON do.data_object_id = dol.data_object_id
WHERE
(( UserName is null) or( Users.user_id = tmp_userid))and (f.received_date_time between Start_date and End_date)
order by ShiftTime ;
 END;


i want to group  the time column (i.e giving in case condition)
plz help me.. and post  the results as soon as possible...
thank u,
ramya

No answers yet. Maybe you can help?

Tags
General Discussions
Asked by
ramya kannan
Top achievements
Rank 1
Share this question
or