You must Sign In to post a response.
  • Category: SQL Server

    Need meaning for this select statements

    Hello,
    I have 5 select statements where paramters are setting here, need to know whats going on in each statement.

    SET @Pay = (SELECT MAX(Id) FROM dbo.PayFeCon WHERE Status = 'F')

    SET @CycDate = (SELECT CycleDate FROM dbo.PayFeCon WHERE ID = @Pay and Status = 'F')

    SET @PayCycle = (SELECT Pay_Cycle FROM dbo.Period WHERE End_Dt = @CycDate)

    SET @TimeProcessDate = (SELECT Process_Date FROM Calendar WHERE Payroll_Cycle = @PayCycle)

    SET @Begin_Dt = (SELECT Begin_Dt FROM dbo.Calendar WHERE Payroll_Cycle = @PayCycle)
  • #766398
    My suggestion is, you can make the following query into single query using inner join.
    Anyway following are the explanation

    SET @Pay = (SELECT MAX(Id) FROM dbo.PayFeCon WHERE Status = 'F')
    Selecting highest value of the id from the table PayFeCon of status "F"

    SET @CycDate = (SELECT CycleDate FROM dbo.PayFeCon WHERE ID = @Pay and Status = 'F')
    Selecting the "CycleDate" from the table "PayFeCon" for the selected above id. ( In this case "Status = 'F'" is not required)

    SET @PayCycle = (SELECT Pay_Cycle FROM dbo.Period WHERE End_Dt = @CycDate)
    selecting Pay_Cycle from the table "Period " for the above selected "CycleDate"

    SET @TimeProcessDate = (SELECT Process_Date FROM Calendar WHERE Payroll_Cycle = @PayCycle)
    selecting "Process_Date" from the table "Calendar " for the above selected "Pay_Cycle "

    SET @Begin_Dt = (SELECT Begin_Dt FROM dbo.Calendar WHERE Payroll_Cycle = @PayCycle)
    selecting "Begin_Dt " from the table "Calendar " for the above selected "Pay_Cycle "

    By Nathan
    Direction is important than speed

  • #766399
    1. first statement will select Maximum(greater) ID from 'PayFeCon ' table for which status is F, and then store that id in 'Pay' variable
    2. It will select 'CycleDate' column from PayFeCon table for which id is MaxID (from above query) and status is F and store that date in 'CycDate' variable
    3. it will select 'Pay_Cycle' column from Period table for which the 'End_Dt ' column should be ''CycDate' from above query and value to be set in 'PayCycle ' variable
    4. it will select 'Process_Date' column from 'Calendar' table for which 'Payroll_Cycle' column should be above 'TimeProcessDate' variable
    5. it will select 'Begin_Dt' column from calendar where 'Payroll_Cycle' column should be 'PayCycle' and store the result in 'Begin_Dt'
    hope it helps

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #766400
    Hi
    What is your issue for this Query ?

    you will expect every query output means try this Query



    CREATE TABLE PAYFECON
    (
    ID INT,
    CYCLEDATE DATETIME,
    STATUS VARCHAR(40)
    )


    CREATE TABLE PERIOD
    (
    PAY_CYCLE VARCHAR(30),
    END_DT DATETIME
    )


    CREATE TABLE CALENDAR
    (
    BEGIN_DT DATETIME,
    PROCESS_DATE DATETIME,
    PAYROLL_CYCLE VARCHAR(30)
    )


    Output Query



    DECLARE @PAY VARCHAR(40),@CYCDATE VARCHAR(40),@PAYCYCLE VARCHAR(40),@TIMEPROCESSDATE VARCHAR(40),@BEGIN_DT VARCHAR(40)
    SET @PAY = (SELECT MAX(ID) FROM DBO.PAYFECON WHERE STATUS = 'F')
    SET @CYCDATE = (SELECT CYCLEDATE FROM DBO.PAYFECON WHERE ID = @PAY AND STATUS = 'F')
    SET @PAYCYCLE = (SELECT PAY_CYCLE FROM DBO.PERIOD WHERE END_DT = @CYCDATE)
    SET @TIMEPROCESSDATE = (SELECT PROCESS_DATE FROM CALENDAR WHERE PAYROLL_CYCLE = @PAYCYCLE)
    SET @BEGIN_DT = (SELECT BEGIN_DT FROM DBO.CALENDAR WHERE PAYROLL_CYCLE = @PAYCYCLE)

    SELECT @PAY
    SELECT @CYCDATE
    SELECT @PAYCYCLE
    SELECT @TIMEPROCESSDATE
    SELECT @BEGIN_DT



    In this Query Execute shows separate output here.

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766409
    Hi,

    As per my understand the post you want to know the result of the query and how it is perform that action internally right?

    If you want the result of the query then use PRINT Statement, it will print the result in output window.

    Ex:

    PRINT @Pay


    If you want to know the execution plan of the query then refer below link

    https://www.mssqltips.com/sqlservertutorial/285/query-execution-plans/

    Here they were explain how to perform execution plan against the query.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #766411
    Hi,

    1. Firstofall 5 variables are declared
    declare @Pay int, @CycDate datetime, @PayCycle varchar(50), @TimeProcessDate datetime, @Begin_Dt datetime

    2. Each variable is assigned to a value using set statement.
    SET @Pay = (SELECT MAX(Id) FROM dbo.PayFeCon WHERE Status = 'F')
    Assigning greatest ID to @pay from PayFeCon table which have status 'F'
    SET @CycDate = (SELECT CycleDate FROM dbo.PayFeCon WHERE ID = @Pay and Status = 'F')
    Assigning CycleDate to @CycDate FROM PayFeCon table which have given ID and status 'F' i.e it should satisfy both conditions.
    SET @PayCycle = (SELECT Pay_Cycle FROM dbo.Period WHERE End_Dt = @CycDate)
    Assigning Pay_Cycle to @PayCycle from Period table which have given end date
    SET @TimeProcessDate = (SELECT Process_Date FROM Calendar WHERE Payroll_Cycle = @PayCycle)
    Assigning Process_Date to @TimeProcessDate from Calender table which have given Payroll_Cycle
    SET @Begin_Dt = (SELECT Begin_Dt FROM dbo.Calendar WHERE Payroll_Cycle = @PayCycle)
    Assigning Begin_Dt to @Begin_Dt from Calender table which have given Payroll_Cycle

    3. To select all the variables, Use this below select statement after running the set statement
    <pre>select @Pay, @CycDate, @PayCycle, @TimeProcessDate, @Begin_Dt</pre>

    4. For assigning a value to a variable both set and select statement can be used.
    However, Set accept scalar value only , whereas select statement accept multiple value.
    Hence, Set statement is recommended to assign a scalar value for a variable.

  • #766767
    HI,

    SET @Pay = (SELECT MAX(Id) FROM dbo.PayFeCon WHERE Status = 'F')
    Ans: Provide the Maximum of ID value from the table PayFeCon

    SET @CycDate = (SELECT CycleDate FROM dbo.PayFeCon WHERE ID = @Pay and Status = 'F')
    Ans: gives the Exact CycleDate where maximum Pay ID(Got from Previous Query) and the Status should be "F"

    SET @PayCycle = (SELECT Pay_Cycle FROM dbo.Period WHERE End_Dt = @CycDate)
    Ans: Gives the output of Pay_cycle based on the cycleDate (Got from Previous Query)

    SET @TimeProcessDate = (SELECT Process_Date FROM Calendar WHERE Payroll_Cycle = @PayCycle)
    Ans: Get the Output of Process_date based on Pay_cycle value

    SET @Begin_Dt = (SELECT Begin_Dt FROM dbo.Calendar WHERE Payroll_Cycle = @PayCycle)

    ans: Getting the Begin_dt from table calendar where the Payroll_Cycle value got from Previous Query

    Regards,
    Karunanidhi.K

  • #766797
    You are storing the value in that variable if you want to know what value in it you can use print command in that.
    SRI RAMA PHANI BHUSHAN KAMBHAMPATI

  • #766853
    HI,

    SET @Pay = (SELECT MAX(Id) FROM dbo.PayFeCon WHERE Status = 'F')
    Ans: Provide the Maximum of ID value from the table PayFeCon

    SET @CycDate = (SELECT CycleDate FROM dbo.PayFeCon WHERE ID = @Pay and Status = 'F')
    Ans: gives the Exact CycleDate where maximum Pay ID(Got from Previous Query) and the Status should be "F"

    SET @PayCycle = (SELECT Pay_Cycle FROM dbo.Period WHERE End_Dt = @CycDate)
    Ans: Gives the output of Pay_cycle based on the cycleDate (Got from Previous Query)

    SET @TimeProcessDate = (SELECT Process_Date FROM Calendar WHERE Payroll_Cycle = @PayCycle)
    Ans: Get the Output of Process_date based on Pay_cycle value

    SET @Begin_Dt = (SELECT Begin_Dt FROM dbo.Calendar WHERE Payroll_Cycle = @PayCycle)

    ans: Getting the Begin_dt from table calendar where the Payroll_Cycle value got from Previous Query

    Regards,
    Karunanidhi.K


Sign In to post your comments