How to join two different table in a stored procedure and use the alias name in the respective column as value of datafield

Question

0.00/5 (No votes)

See more:

, +

Hi I am working on a grid view which has to get data from two different table.

How to join two different table in a stored procedure and use the alias name in the respective column as value of Datafield

What I have tried:

SQL

<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

My design page

 <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>

Posted 2-May-17 23:44pm

Member 12605293

Add a Solution

Comments

ZurdoDev 3-May-17 7:56am

Very simple.

SELECT field1 AS aliasName
FROM table1 t1
INNER JOIN table2 t2 ON t1.Something = t2.Something

And then bind to aliasName.

Member 12605293 3-May-17 8:53am

Hi RyanDev
I am Joining but the thing is that I need to get the total at the end Using Separate Column
My Modified Query

;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

Note: The Issue is the alias names below Union is not taking

Another Way I followed below union like this i need as a column

 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 3-May-17 8:11am

You are already doing that?

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)