Showing posts with label MIS. Show all posts
Showing posts with label MIS. Show all posts
Friday, June 12, 2020
Thursday, October 10, 2019
SQL Server constraint
CHECK constraint
A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.
After this if we used to insert the following query
insert into employees values(0,'Sawan','Chauan',24567)
Then following Output Message will be diplayed.
Second Example of Query
When we try to insert the following -
Then we will receive the above error
TRY----CATCH constructs
To handles exceptions by writing scripts inside the TRY block and error handling in the CATCH block
Thursday, July 25, 2019
SQL QUERY FOR TRACKING THE DETAILS OF A ITEM FROM MULTIPLE SUPPLIER- Programming Logic And Code
SQL QUERY FOR - Item Purchase Tracking
select
a.Pur_Order_No as [Purchase Order No],
convert(varchar,a.Pur_Order_Date,103) as [Purchase Order Date],
c.AC_NAME as [Supplier Name],
b.ALT_QTY as [PO QTY],
b.ALT_RATE as [PO Rate]
from Purchase_Order_mast a
left outer join Purchase_Order_Det b on a.urn_no=b.URN_No
left outer join ACCT_MAST c on a.Supp_Code=c.SR_NO
where IT_CODE=@it_code and
a.Pur_Order_Date>=@fromdate and
a.Pur_Order_Date<=@todate and
a.PO_Status='Approved'
order by a.Pur_Order_Date
Lot of SQL Programmer is facing the issue many Times-
When they have to add all columns value in last
I searched a lot to get the solution of this regarding to issue
Hear is the query-
Logic IS- I convert everything in Archaic format-
selectconvert(varchar,[Production Date]) as [Production Date],
count(prod_booking_heat_no) as [Total Heat Per Day],
sum([Production Qty]) as [Metal Production Per DAY]
from(
select a.sr_no,convert(varchar,a.Prod_Date,103) as [Production Date],
a.prod_booking_heat_no,
c.IT_NAME ,
A.prod_booking_item_no AS [Product Name],
A.prod_booking_laddle_no as [Laddle No],avg(b.alt_qty) [Production Qty],
from Direct_Prod_Mast a
left outer join Prod_Output_Det b on a.urn_no=b.urn_no
left outer join ItemMaster c on c.IT_CODE=b.IT_CODE
left outer join Prod_Input_Det d on d.urn_no=b.urn_no
left outer join ItemMaster e on e.IT_CODE=d.IT_CODE
where c.IT_NAME like '%Metal%' and a.co_code='GEN00A000001' and a.Prod_Date>=@fromdate and a.Prod_Date<=@todate
group by a.Prod_Date,a.Sr_No,a.prod_booking_heat_no,c.IT_NAME,A.prod_booking_item_no,A.prod_booking_laddle_no
) as b11
Monday, July 15, 2019
SQL Sale Register Complete Query
select
c.Full_Name,
c.cand_code,
f.Para_Description Department,
g.Para_Description Designation,
a.salary_Month,
c.Full_Name,
c.cand_code,
f.Para_Description Department,
g.Para_Description Designation,
a.salary_Month,
b.Basic,
b.current_basic,
e.Earning,
b.GrossSalary,
d.Deduction,
b.PT_Cal,
b.Net_salary
from Salary_DeptGen_Mst a
inner join Salary_DeptGen_Det b on a.co_Code = b.co_Code and a.urn_no = b.urn_no
left outer join HR_EMPLOYEEDETAIL_MASTER c on b.co_Code = c.co_Code and b.Emp_ID=c.URN_No
left outer join (
select round(sum(isnull(amount_e,0)),2) Earning,Employee_CodeE,URN_No,CO_CODE from Salary_Generation_Earning where co_code = @Prm_Co_Code group by Employee_CodeE,URN_No,co_Code) as E
on b.co_Code = e.co_code and b.urn_no = e.urn_no and b.Emp_ID = e.Employee_CodeE
left outer join (select round(sum(isnull(amount_d,0)),2) Deduction,Employee_Coded,URN_No,CO_CODE from Salary_Generation_Deduction where co_code = @Prm_Co_Code group by Employee_Coded,URN_No,co_Code) as d
on b.co_Code = d.co_code and b.urn_no = d.urn_no and b.Emp_ID = d.Employee_Coded
left outer join Parameter_Department f on c.co_Code = f.co_code and f.urn_no = c.dept_code
left outer join parameter_designation g on c.co_code = g.co_code and c.desi_code=g.URN_No
where a.co_Code = @Prm_Co_Code and a.salary_status = 'Approved'
Subscribe to:
Posts (Atom)