MIS Queries Sql Server

This post have all the Impoortant Queries which an organizastion Management requires to analysis the Organization Grouth and culture.

 Top 100+ Sql Server Interview Questions

 

Employee List  MIS Query

Select
   s.Subtrans_Name,
   b.Full_Name,
   b.cand_code,
   c.Para_Description Department,
   d.Para_Description Designation,
   e.Para_Description Leave_Type,
    a.fullHalf_Leave,
    convert(nvarchar,a.Fromdt,103),
    convert(nvarchar,a.Todt,103),
    isnull(convert(nvarchar,a.fromtime,108),''),
    isnull(convert(nvarchar,a.totime,108),'')
    from main_Account s,
    Employee_register a
    inner join Employee_Master b on a.co_Code = b.co_Code and a.empno = b.ID
    left outer join Parameter_Department c on b.dept_code = c.ID
    left outer join parameter_designation d on b.desi_code = d.urn_no
    left outer join Parameter_Leave_Type e on a.Leavetype_ID = e.URN_No
 
 where
    s.co_Code = a.co_code and s.Subtrans_Code=a.DB_CODE
    and a.Fromdt>=@fromdate and a.Todt<=@todate

SQL MIS Query for Account Ledger

SELECT A.AC_NAME,
A.AC_SHORTNAME,
A.AC_TYPE,
A.CITY,
A.PERSON_STATE,
A.CONTRY,
A.GST_NO
FROM
ACCOUNT_MASTER A,
BALANCESHEETGROUP_MASTER B
WHERE
 A.GR_CODE=B.Code
AND A.ACCOUNT_STATUS = 'APPROVED'

SQL QUERY Pending dispatch instruction

select
 e.CO_NAME as "Unit",
a.OA_Status as "Status",
d.AC_NAME as"Customer Name",
d.Ac_ShortName as "Bill to (Customer Code)",
'' as "Shipped to (Customer Code)",
f.DESCRIPTION as "Customer Group",
 a.OA_NO as"Sale Order No",
CONVERT(nvarchar,a.OA_DATE,103) as "Sale Order Date",
c.ALT_NAME as "Item Code",
c.IT_NAME as "Item Description",
g.Para_Description as "UOM",
'' as "Warehouse",b.ALT_QTY as "Ordered Quantity",
b.ALT_RATE as "Item Rate",
'' as "Shipped Quantity",
'' as "Back Order Quantity",
'' as "Required Date"
 from
order_mast a,
order_details b,
Item_Master c,
account_master d,
co_mast e,
Account_category f,
Measurement_Unit g
where a.urn_no=b.urn_no
and b.it_code=c.it_code
and a.oa_custco=d.sr_no
and a.co_code=e.co_code
and d.Party_Category_ID=f.id
and b.ALT_UNIT_ID=g.ID
and a.oa_date>=@fromdate and a.oa_date<=@todate

SQL Query Pending Purchase Order Details

@fromdate datetime=null,
@todate datetime=null,



SELECT
 C.CO_NAME AS 'Unit',
a.PO_Status as 'Status',
a.Pur_Order_No as 'PO No',
convert(nvarchar,a.Pur_Order_Date,103) as 'PO Date',
F.DESCRIPTION AS 'Supplier Group',
D.AC_NAME AS 'Supplier Name',
E.ALT_NAME AS'Item Code',
E.IT_NAME AS 'Item Description',
B.ALT_UNIT AS 'UOM',B.ALT_QTY AS'Order Quantity',
(
    select isnull(sum(alt_qty),0) from
 Purchase_Inward_Det
where From_URN_No=b.URN_No and From_Item_Sr_No=b.pur_order_srno )
AS 'Received Quantity',
b.ALT_QTY-(
select
isnull(sum(alt_qty),0) from
Purchase_Inward_Det
where From_URN_No=b.URN_No and From_Item_Sr_No=b.pur_order_srno) AS 'Pending Quantity',B.ALT_RATE AS 'Rate',B.Final_Amt AS 'Value',isnull(convert(nvarchar,b.Req_Date,103),'') as 'Required Date',
case when b.ALT_QTY-(select isnull(sum(alt_qty),0)
from Purchase_Inward_Det where
From_URN_No=b.URN_No and
From_Item_Sr_No=b.pur_order_srno)<0 then 0
else isnull(
(datediff(day,b.Req_Date,(SELECT GETDATE()))),0) end as 'Delays'

FROM
PURCHASE_ORDER_MASTER A,
PURCHASE_ORDER_DETAILS B ,
ACCOUNT_MASTER D,
ITEM_MASTER,
PARTY_CATEGORY F
WHERE A.URN_NO=B.ID
AND A.Supp_Code=D.SR_NO
AND B.IT_CODE=E.IT_CODE
AND D.PARTY_CATEGORY_ID=F.ID
and a.CO_CODE
and a.Pur_Order_Date>=@fromdate and a.Pur_Order_Date<=@todate

SQL Query to track the details of any item

declare @it_code varchar(50)
set @it_code='A005123'
select * from (
select type,type_date,type_no,Location_Name,ALT_QTY,Entry_Status,Item_Stock_No,Para_Code,item_code from Item_Stock
union
select  'Request' as type,a.Req_Date as type_date,a.URN_No as type_no,'Request for issue' as Location_Name,b.ALT_QTY as [ALT_QTY],a.Req_Status as [Entry_Status],'0' as [Item_Stock_No],'Para_Code' ,b.IT_CODE as [item_code]
from Requisition_Detail b
left outer join Requisition_mast a on a.URN_No=b.URN_No
left outer join ItemMaster c on b.it_code=c.IT_CODE

) as A where item_code=@it_code order by type_date

SQL Query for checking the mismatch of common data in two tables

select * from Item_Stock a
left outer join Purchase_GRN_Det b on a.type_no=b.URN_No and a.item_code=b.IT_CODE
where a.para_code<>b.Para_Code

SQL Query to make GST Report HSN Code Wise 

select 'Sale' as [Sales Account],
sum(case when a.ac_code='ACC00B001069' Then a.Credit_Amount else 0 end) as [Basic],
sum(case when a.ac_name like '%SGST%OUTPUT%' then a.Credit_Amount else 0 end) as [SGST],
sum(case when a.ac_name like '%CGST%OUTPUT%' then a.Credit_Amount else 0 end) as [CGST],
sum(case when a.ac_name like '%IGST%OUTPUT%' then a.Credit_Amount else 0 end) as [IGST]
 from
ledr_data  a
left outer join acct_mast b on a.AC_CODE=b.SR_NO
 where b.ac_type='General' and urn_no like 'MSI%'  and Trans_Type='S'
union
select 'Purchase' as [Sales Account],
sum(case when (a.ac_name like '%Purchase Account%' or a.ac_name like '%Invention Toolings%') Then a.DEBIT_Amount else 0 end) as [Basic],
sum(case when a.ac_name like '%SGST%INPUT%' then a.DEBIT_Amount else 0 end) as [SGST],
sum(case when a.ac_name like '%CGST%INPUT%' then a.DEBIT_Amount else 0 end) as [CGST],
sum(case when a.ac_name like '%IGST%INPUT%' then a.DEBIT_Amount else 0 end) as [IGST]
 from
ledr_data  a
left outer join acct_mast b on a.AC_CODE=b.SR_NO
 where a.urn_no like 'MPF%' and  (b.ac_type='General' or b.sr_no='ACC00A000042')  and Trans_Type='P'
 union
select 'Debit Note' as [Sales Account],
sum(case when (b.ac_type like '%General%') Then a.DEBIT_Amount else 0 end) as [Basic],
sum(case when a.ac_name like '%SGST%OUTPUT%' then a.DEBIT_Amount else CASE WHEN  a.ac_name like '%SGST%input%' THEN (-a.DEBIT_Amount) ELSE 0 end END ) as [SGST],
sum(case when a.ac_name like '%CGST%OUTPUT%' then a.DEBIT_Amount else CASE WHEN  a.ac_name like '%CGST%input%' THEN (-a.DEBIT_Amount) ELSE 0 end END ) as [CGST],
sum(case when a.ac_name like '%IGST%OUTPUT%' then a.DEBIT_Amount else CASE WHEN  a.ac_name like '%IGST%input%' THEN (-a.DEBIT_Amount) ELSE 0 end END ) as [IGST]
 from
ledr_data  a
left outer join acct_mast b on a.from_ledger_code=b.SR_NO
 where a.urn_no like 'DRN%' and  (b.ac_type='General' )
 --select * from LEDR_DATA  where   urn_no like 'drn01A000266%'
  union
select 'Credit Note' as [Sales Account],
sum(case when (b.ac_type like '%General%') Then a.DEBIT_Amount else 0 end) as [Basic],
sum(case when a.ac_name like '%SGST%OUTPUT%' then a.DEBIT_Amount else CASE WHEN  a.ac_name like '%SGST%input%' THEN (-a.DEBIT_Amount) ELSE 0 end END ) as [SGST],
sum(case when a.ac_name like '%CGST%OUTPUT%' then a.DEBIT_Amount else CASE WHEN  a.ac_name like '%CGST%input%' THEN (-a.DEBIT_Amount) ELSE 0 end END ) as [CGST],
sum(case when a.ac_name like '%IGST%OUTPUT%' then a.DEBIT_Amount else CASE WHEN  a.ac_name like '%IGST%input%' THEN (-a.DEBIT_Amount) ELSE 0 end END ) as [IGST]
 from
ledr_data  a
left outer join acct_mast b  on a.from_ledger_code=b.SR_NO
 where a.urn_no like 'CRN%' and  (b.ac_type='General' )
 --select * from LEDR_DATA  where   urn_no like 'drn01A000001%'

SQL Query to track Invoice with multiple join full code 

select a.urn_no,i1.alt_name,b.URN_No,i2.alt_name,c.URN_No,i3.alt_name,d.URN_No,i4.alt_name,e.URN_No,i5.alt_name from Invoice_DET a
left outer join Challan_DET b on a.from_urn_no=b.urn_no and a.From_Item_Sr_No=b.CHALLAN_SRNO
left outer join Dispatch_DET c on b.from_urn_no=c.urn_no and b.From_Item_Sr_No=c.DIS_SRNO
left outer join Order_Delivery_Schedule_Det d on c.from_urn_no=d.urn_no and c.From_Item_Sr_No=d.Sr_No
left outer join Order_Delivery_Schedule_Mast d1 on d.URN_No=d1.URN_No
left outer join OA_DET e on d1.OA_NO_ID=e.urn_no
left outer join itemmaster i1 on a.IT_CODE=i1.IT_CODE
left outer join itemmaster i2 on b.IT_CODE=i2.IT_CODE
left outer join itemmaster i3 on c.IT_CODE=i3.IT_CODE
left outer join itemmaster i4 on d.item_id=i4.IT_CODE
left outer join itemmaster i5 on e.IT_CODE=i5.IT_CODE
where a.urn_no='A000167'

SQL Query for tracking the details of particular transaction in log table 

select b.First_Name,b.Last_Name,a.Action_Title,a.Curr_DateTime from Web_User_Log_Details a
left outer join user_master b on a.ur_code=b.ur_code where urn_no='A005964'

SQL Query for Purchase Order details 

select distinct 

a.URN_No
,a.Pur_Order_Date  as "Date"
,isnull(a.Pur_Order_No,'') as "Doc No"
,c.ac_name as "Account Name"
,a.Ref_No as "Ref No",
s.Subtrans_Name as "Category",
a.Grand_Total as "Amount",
c.city as "City",
"Currency"=(g.Para_Description)
from
Purchase_Order_Mast a left outer join
Purchase_Order_Det b on a.CO_CODE = b.CO_CODE and a.URN_No = b.URN_No
left outer join
(select co_code,From_URN_No,From_Item_Sr_No,sum(alt_qty) alt_qty from Purchase_GRN_Det
group by co_code,From_URN_No,From_Item_Sr_No ) as forwrd 
on b.CO_CODE = forwrd.CO_CODE and b.URN_No = forwrd.From_URN_No
left outer join
(select co_code,From_URN_No,From_Item_Sr_No,sum(alt_qty) alt_qty from purchase_inward_det
group by co_code,From_URN_No,From_Item_Sr_No ) as forwrdInward
on b.CO_CODE = forwrdInward.CO_CODE and b.URN_No = forwrdInward.From_URN_No 
left outer join acct_mast c on a.CO_CODE =c.CO_CODE and a.Supp_Code =c.SR_NO  and c.acc_status = 'Approved'
left outer join LEDR_DATA f on (f.Ac_Code=a.Supp_Code or f.From_Ledger_Code=a.Supp_Code) and f.fyear=a.fyear and f.CO_CODE=a.CO_CODE
left outer join Parameter_Currency g on g.CO_CODE =a.CO_CODE and g.URN_No =a.SALE_CURRCO
left outer join Sub_Trans s on a.CO_CODE=s.CO_CODE and a.DB_CODE=s.Subtrans_Code
where  a.CO_CODE=@Prm_Co_Code
and a.Pur_Order_Date>=@fromdate and a.Pur_Order_Date<=@todate