Hi
I want to use Join in SQL Server in Stored Procedure
1st Table
create table JobCard
(
JobCard_Id int primary key not null,
Machine_Id bigint foreign key references Machine (Machine_Id) not null,
Estimated_Date date not null,
Breakdown_Date_Time datetime not null,
Priority_Id tinyint foreign key references Priority_Table (Priority_Id) not null,
created_by_Employee_Id smallint foreign key references Employee_Details (Employee_Id) not null,
JobCard_Status tinyint not null, ---1 Pending ,2 Processing ,3 Waiting For Approval , 4 Reject , 5 Close , 6 Cancel
Comments varchar(5000)
)
2nd Table
Create Table Job_Card_Approve
(
JobCard_Id int foreign key references JobCard (JobCard_Id) not null,
Employee_Id smallint foreign key REFERENCES Employee_Details(Employee_Id) not null,
Approve_Date Datetime not null,
Repair_Cost money not null,
)
Procedure that I create
create procedure retrive_closed_job_card_analytsis_procedure(@year int) as
SELECT *,DATEPART(yyyy,Breakdown_Date_Time) AS Year,DATEPART(mm,Breakdown_Date_Time) AS Month,DATEPART(dd,Breakdown_Date_Time) AS Day FROM JobCard where job_card_status=1 and YEAR(breakdown_time) = @year
go
I want Breakdown_Date_Time from 1st table and Approve_Date, Repair_Cost from 2nd table then this query SELECT *,DATEPART(yyyy,Breakdown_Date_Time) AS Year,DATEPART(mm,Breakdown_Date_Time) AS Month,DATEPART(dd,Breakdown_Date_Time) AS Day FROM JobCard where job_card_status=1 and YEAR(breakdown_time) = @year
How I do this