Как объединить две разные таблицы в хранимой процедуре и использовать псевдоним в соответствующем столбце в качестве значения поля данных
Привет , я работаю над сеточным представлением, которое должно получать данные из двух разных таблиц.
Как объединить две разные таблицы в хранимую процедуру и использовать псевдоним в соответствующем столбце в качестве значения поля данных
Что я уже пробовал:
<pre>Alter procedure [dbo].[Sampe4](@years int,@months int)
as
;WITH CTE AS(
SELECT TotalEmployee=case when (datepart(yyyy,DOJ) between 2005 and 2017)and datepart(mm,DOJ) <='12' and status=0 THEN COUNT(EmpID) ELSE 0 END,
Resigned=case when (DATEPART(yyyy, deactivate) = 2000) AND (DATEPART(mm, deactivate) <= 11) and Status=1 THEN COUNT(EmpID) ELSE 0 END,
NewJoinees=case when (DATEPART(yyyy, DOJ) = 2017) AND (DATEPART(mm, DOJ) = 02) and datepart(mm,DOJ)<='02' and status=0 THEN COUNT(EmpID) ELSE 0 END
FROM EmployeeDetails AS EmployeeDetails_7
group by DOJ,deactivate,status
)
SELECT 'ZTotal' as DepartmentTotals,SUM(TotalEmployee) TotalEmployee,SUM(NewJoinees) NewJoinees, SUM(Resigned)Resigned,'' AS Tobehired,'' As Position,'' as Status FROM CTE
Union
SELECT T.DeptID As DeptID ,SUM(CASE WHEN datepart(yyyy,T.DOJ) between 2005 and 2017 and T.Status =0 then 1 else 0 END) As TotalHeadCount,
SUM(CASE when T.DOJ >= cast('2017-01-12' as datetime) and T.Status =0 then 1 else 0 END) As NewJoinees1,
SUM (CASE When T.deactivate>= Cast('2017-01-12' as datetime) and T.Status =0 then 0 else 1 END) As Resigned1,
SUM(CASE WHEN T1.Tobehired IS NULL THEN '0' ELSE Tobehired END) AS Tobehired ,
SUM(CASE WHEN T1.Openposition IS NULL THEN '0' ELSE Openposition END) As Openposition ,T1.Status As Status
FROM EmployeeDetails T LEFT JOIN RecruitmentDetails1 T1 on T.DeptID = T1.DeptID
Where T.Status=0
GROUP BY T.DeptID
, T1.Tobehired
, T1.Openposition
, T1.Status
Моя страница дизайна
<pre><table align="center">
<tr>
<td>
<span id="printcontent1">
<asp:DataList ID="DataListHeadCount" HorizontalAlign="Center" DataSourceID="SqlDataSourceHeadCount" runat="server">
<ItemTemplate>
<table align="center" class="table table-bordered">
<tr align="center">
<td>Data
</td>
<td>COUNT
</td>
</tr>
<tr>
<td>NEW JOINEES
</td>
<td align="center">
</td>
</tr>
<tr>
<td>RESIGNATIONS
</td>
<td align="center">
</td>
</tr>
<tr>
<td>TOTAL HEADCOUNT
</td>
<td align="center">
</td>
</tr>
</table>
<div>
<table >
<tr>
<td
colspan="7" align="center">
<%=ddlMonths.Text %><%=ddlyear.Text %></td>
</tr>
<tr>
<td>
<asp:GridView ID="grdvEmployeeLeaveUpdate" runat="server" DataKeyNames=""
class="table table-bordered table-striped table-hover" DataSourceID="SqlDataSourceHeadCount"
OnRowDataBound="grdvEmployeeLeaveUpdate_RowDataBound" AutoGenerateColumns="False" Width="100%"
OnRowDeleting="grdvEmployeeLeaveUpdate_RowDeleting"
OnRowEditing="grdvEmployeeLeaveUpdate_RowEditing"
OnRowUpdating="grdvEmployeeLeaveUpdate_RowUpdating"
OnRowCancelingEdit="grdvEmployeeLeaveUpdate_RowCancelingEdit"
OnPageIndexChanging="grdvEmployeeLeaveUpdate_PageIndexChanging">
<Columns>
<asp:BoundField DataField="DeptID" HeaderText="Cost Center" ReadOnly="true" />
<asp:BoundField DataField="TotalHeadCount" HeaderText="HeadCount" ReadOnly="true" />
<asp:BoundField DataField="NewJoinees1" HeaderText="Additions" ReadOnly="true" />
<asp:BoundField DataField="Resigned1" HeaderText="Separations" ReadOnly="true" />
<asp:BoundField DataField="Tobehired" HeaderText="To Be Hired" ControlStyle-Width="80px" />
<asp:BoundField DataField="Openposition" HeaderText="Open Position" ControlStyle-Width="80px" />
<asp:BoundField DataField="Status" HeaderText="Status" ControlStyle-Width="80px" />
<asp:CommandField HeaderText="Update" ShowEditButton="True" ControlStyle-Width="80px" />
</Columns>
<HeaderStyle></HeaderStyle>
</asp:GridView>
</td>
</tr>
</table>
</div>
</ItemTemplate>
</asp:DataList>
</span>
</td>
</tr>
</table>
<table align="center">
<tr valign="middle" align="center">
<td align="center">
<asp:Button ID="btnExport" runat="server" CssClass="btn btn-wide btn-dark-grey" Text="Export To Excel" OnClick="btnExport_Click" />
<input id="Button2" class="btn btn-wide btn-dark-grey" onclick="return doprintcontent1();" type="button" value="Print Page" />
</td>
</tr>
</table>
<asp:SqlDataSource ID="SqlDataSourceHeadCount" runat="server" ConnectionString="<%$ ConnectionStrings:TimeSheetConnectionString2 %>"
SelectCommandType="StoredProcedure" SelectCommand="Sampe4"
UpdateCommand="UPDATE RecruitmentDetails1 SET Tobehired = @Tobehired, Openposition = @Openposition, Status=@Status WHERE DeptID = @DeptID">
<SelectParameters>
<asp:ControlParameter ControlID="ddlMonths" Name="months" Type="Int32" />
<asp:ControlParameter ControlID="ddlyear" Name="years" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
</asp:View>
ZurdoDev
Очень просто.
Выберите field1 в качестве псевдонима
Из табл. 1 Т1
Внутреннее соединение table2 t2 на t1. Something = t2. Something
А затем привязать к aliasName.
Member 12605293
Привет Ряндев
Я присоединяюсь, но дело в том, что мне нужно получить общую сумму в конце, используя отдельный столбец
Мой Измененный Запрос
;with t1 as ( SELECT DeptID, COUNT(EmpID) AS TotalHeadCount FROM EmployeeDetails WHERE (datepart(yyyy,DOJ) between 2005 and 2017) and Status =0 group by DeptID ), t2 as ( SELECT DeptID,COUNT(EmpID) AS NewJoinees FROM EmployeeDetails AS EmployeeDetails_7 WHERE (DATEPART(yyyy, DOJ) = 2017) AND (DATEPART(mm, DOJ) = 03) and datepart(mm,DOJ)>= 01 and Status = 0 group by DeptID ), t3 as ( SELECT DeptID,COUNT(EmpID) AS Resigned FROM EmployeeDetails AS EmployeeDetails_7 WHERE (DATEPART(yyyy, deactivate) = 2017) AND (DATEPART(mm, deactivate) = 02) and datepart(mm,deactivate)>= 05 and Status =1 group by DeptID ) Select t1.DeptID, CASE WHEN TotalHeadCount IS NULL THEN '0' ELSE TotalHeadCount END AS TotalHeadCount,CASE WHEN NewJoinees IS NULL THEN '0' ELSE NewJoinees END AS NewJoinees,CASE WHEN Resigned IS NULL THEN '0' ELSE Resigned END AS Resigned, '' as ToBeHired, '' as OpenPositions, '' as Status from t1 full outer join t2 on t1.DeptID = t2.DeptID full outer join t3 on t1.DeptID = t3.DeptID Union Select 'ZTotal' as total, '' as TotalHeadCount, '' as NewJoinees, '' as Resigned, '' as ToBeHired, '' as OpenPositions, '' as Status
Примечание: проблема заключается в том, что псевдонимы ниже Union не принимают
Еще один способ, которым я следовал ниже, такой, как этот, мне нужен в качестве столбца
WITH CTE AS(
SELECT TotalEmployee=case when (datepart(yyyy,DOJ) between 2005 and 2017)and datepart(mm,DOJ) <='12' and status=0 THEN COUNT(EmpID) ELSE 0 END,
Resigned=case when (DATEPART(yyyy, deactivate) = 2017) AND (DATEPART(mm, deactivate) <= '04') and Status=1 THEN COUNT(EmpID) ELSE 0 END,
NewJoinees=case when (DATEPART(yyyy, DOJ) = 2017) AND (DATEPART(mm, DOJ) = 02) and datepart(mm,DOJ)<='02' and status=0 THEN COUNT(EmpID) ELSE 0 END
FROM EmployeeDetails AS EmployeeDetails_7
group by DOJ,deactivate,status
)
SELECT 'Z' as DepartmentTotals,SUM(TotalEmployee) As TotalEmployee1,SUM(NewJoinees)AS NewJoinees1, SUM(Resigned) As Resigned1,'' AS Tobehired,'' As Position,'' as Status FROM CTE
CHill60
Вы уже это делаете?