|
First Create a new table with using select command and then you can fatch data from those tables. And after that you can add any column in new table.
|
|
|
|
|
I have a table named as depot_mstr having attributes, are the following---
regn,
depot_code,
depot_name,
city
I have also an another table as dealer_list having attributes, are the following---
depot_no,
depot_code,
dealer_name,
dealer_category
how i get the output as---
regn| depot_code| dealer_name| GC| SC| STA| OTH
where,
GC, SC, STA, OTH are the elements of dealer_category
|
|
|
|
|
Assuming you are using sql server:
SELECT regn, depot_code, dealer_name, GC, SC, STA, OTH
FROM
(SELECT regn, depot_mstr.depot_code, dealer_name, dealer_category FROM depot_mstr LEFT
JOIN dealer_list ON depot_mstr.depot_code = dealer_list.depot_code
) AS sourcetable
PIVOT
(
COUNT (dealer_category)
FOR dealer_category IN
(GC, SC, STA, OTH)
) AS pivottable
|
|
|
|
|
Please also send me the code as early as possible, if i solve it through function in sql server...
Thank u...
|
|
|
|
|
I can't find any case using in the query....!!!
|
|
|
|
|
Hi,
I am planning to move my existing client database from MySQl to Oracle.
I will go for Express (because it's FREE)
I want to ask is the 11 GB Express is proving enough? I mean is it just 11 GB for my data or includes database sys data the it will be easily full?
please advise...
Technology News @ www.JassimRahma.com
|
|
|
|
|
|
|
Did you notice that is not the Express edition? But in either case the documentation provides the information you are looking for.
Veni, vidi, abiit domum
|
|
|
|
|
it is Express Edition.
Oracle Database Express Edition at the top and it's FREE and limited to 11 GB..!
It's confusing now..!
Technology News @ www.JassimRahma.com
|
|
|
|
|
From version 11g2 it's 11GB, before that it's 4GB.
|
|
|
|
|
|
If you go to the next page[^] they specify it as 11GB of user data. (emphasis mine)
|
|
|
|
|
What about the size of your current MySQL database? The space required for storing the data and the indexes does not differ a lot between different databases.
|
|
|
|
|
I am going to define a table for Book's Publisher so I want to give a name like Book_Publisher_Mapping. But when I will map this with EF then It creates a partial class that name is Book_Publisher_Mapping. So it is not good name for class. I think class name should be like BookPublisherMapping. So I should define table like BookPublisherMapping. It's a good naming convention for table name in Pascal case without _.
Thanks
|
|
|
|
|
It is an excellent naming convention underscores (_) were invented because the devil (Oracle) could not work out what lower case is.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i am receiving Emplid in this action,
public ActionResult showDDL(int? EmplID = null)
{
ViewBag.EmplID = EmplID;
if (EmplID == null)
{
IEnumerable<GetAtdRecord_SpResult> EmployeeAtd_2 = DataContext.GetAtdRecord_Sp(0).ToList();
return View(EmployeeAtd_2);
}
else if (EmplID != null)
{
IEnumerable<GetAtdRecord_SpResult> EmployeeAtd_2 = DataContext.GetAtdRecord_Sp(EmplID).ToList();
return View(EmployeeAtd_2);
}
return View();
}
View:
@{
var grid = new WebGrid(ViewData.Model, defaultSort: "EmplID", rowsPerPage: 20);
}
@if (Model.Count > 0)
{
<div id="AllEmpGrid_ByName">
@grid.GetHtml(columns: grid.Columns(
grid.Column("EmplID", "Employee ID"),
grid.Column("EmplName", "Employee Name"),
grid.Column("ShiftID", "Shift ID"),
grid.Column("DateVisited", "Date of Visit"),
grid.Column("InTime", "In Time"),
grid.Column("TimeOut", "Time Out"),
grid.Column("OverTime", "Over Time"),
grid.Column("TotalWorkingTime", "Total Working Time")
))
</div>
using (Html.BeginForm("ToExcel", "Home", FormMethod.Get))
{
<button type="submit" class="button_form button_download" >Download in Excel</button>
}
}
else
{
<h2 class="error" >No Data Found</h2>
}
In same View you can see, button DOWNLOAD IN EXCEL, i want to pass this emplID to ToExcel method
public ActionResult ToExcel(int? empid )
{
var DataContext = new EmployeeRecordDataContext();
var grid = new GridView();
grid.DataSource = DataContext.GetAtdRecord_Sp(null).ToList();
grid.DataBind();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=AttendanceSheet.xls");
Response.ContentType = "application/ms-excel";
Response.Charset = "";
StringWriter sw = new StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
return RedirectToAction("index");
}
i can't figure out that how to pass EmplID recieved in Action 'showDDL' to EmpID when i click Button "Download in Excel" ?
|
|
|
|
|
And what has this got to do with databases?
You'll stand more chance of getting an answer if you post your question in the correct forum[^].
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This query shows emplName,emplID, totalworking time, InTime, OutTime, DateVisited, Overtime for an employee based on his InTime and Outime, that's OK. Now i am trying to modify it to show only emplID, EmplName, Total Working hours(Per month), total overtime (per month).
e.g.
Empid EmplName TotalWorkingHours TotalOvertime Month
00001 John 77:00 05:55 2013-02
00002 Masn 57:00 04:56 2013-02
Query:
with times as (
SELECT t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, t4.ShiftId as ShiftID
, t4.StAtdTime as ShStartTime
, t4.EndAtdTime as ShEndTime
, cast(min(t1.RecTime) as datetime) AS InTimeSub
, cast(max(t2.RecTime) as datetime) AS TimeOutSub
, t1.RecDate AS [DateVisited]
FROM AtdRecord t1
INNER JOIN
AtdRecord t2
ON t1.EmplID = t2.EmplID
AND t1.RecDate = t2.RecDate
AND t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON t3.EmplID = t1.EmplID
inner join AtdShiftSect t4
ON t3.ShiftId = t4.ShiftId
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
, t4.ShiftId
, t4.StAtdTime
, t4.EndAtdTime
)
SELECT
EmplID
,EmplName
,ShiftId As ShiftID
,InTime
,[TimeOut]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) TotalWorkingTime
,[DateVisited]
,CASE WHEN [InTime] IS NOT NULL AND [TimeOut] IS NOT NULL THEN
CONVERT(char(5),CASE WHEN CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S002' Then LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME),CAST([TimeOutSub] AS DATETIME)),0), 108),5)
WHEN CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S001' Then LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME), CAST([TimeOutSub] AS DATETIME)),0), 108),5)
ELSE '00:00' END, 108)
ELSE 'ABSENT' END AS OverTime
FROM times order by EmplID, ShiftID, DateVisited
|
|
|
|
|
Just remove the columns you don't need and you should be good to go. Are you facing any issues?
|
|
|
|
|
sorry what ? i need sum of time per month sir
|
|
|
|
|
|
i have written this query to show total working time and overtime of an employee on particular date (if he has came) otherwise it doesn't show record for a person if his INTIME and TIMEOUT are empty but i don't want this now, i want if for a particular date person's INtime and OutTime are empty then put 00:00 in his intime, outtime, totalworkingtime, overtime.
e.g.
EmplID EmplName ShiftID intime Outtime totalworking overtime dateVisited
0000001 John S001 00:00 00:00 00:00: 00:00 2013-12-01
Query:
with times as (
SELECT t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, t4.ShiftId as ShiftID
, t4.StAtdTime as ShStartTime
, t4.EndAtdTime as ShEndTime
, cast(min(t1.RecTime) as datetime) AS InTimeSub
, cast(max(t2.RecTime) as datetime) AS TimeOutSub
, t1.RecDate AS [DateVisited]
FROM AtdRecord t1
INNER JOIN
AtdRecord t2
ON t1.EmplID = t2.EmplID
AND t1.RecDate = t2.RecDate
AND t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON t3.EmplID = t1.EmplID
inner join AtdShiftSect t4
ON t3.ShiftId = t4.ShiftId
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
, t4.ShiftId
, t4.StAtdTime
, t4.EndAtdTime
)
SELECT
EmplID
,EmplName
,ShiftId As ShiftID
,InTime
,[TimeOut]
,convert(char(5),cast([TimeOutSub] - InTimeSub as time), 108) TotalWorkingTime
,[DateVisited]
,CASE WHEN [InTime] IS NOT NULL AND [TimeOut] IS NOT NULL THEN
CONVERT(char(5),CASE WHEN CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S002' Then LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME),CAST([TimeOutSub] AS DATETIME)),0), 108),5)
WHEN CAST([TimeOutSub] AS DATETIME) >= ShEndTime And ShiftID = 'S001' Then LEFT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, CAST(ShEndTime AS DATETIME), CAST([TimeOutSub] AS DATETIME)),0), 108),5)
ELSE '00:00' END, 108)
ELSE 'ABSENT' END AS OverTime
FROM times order by EmplID, ShiftID, DateVisited
|
|
|
|
|
Change your inner joins to outer joins
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
When building these type of query I break it down to getting the primary list (employees) and getting the data you are reporting (timesheet information) then I use a left outer join between the primary and data queries and use ISNULL to display the default values (00:00).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|