locked
SQL, Select in Looping returning repeated value RRS feed

  • Question

  • User1170663586 posted

    I have been working on a project for a very long time and I can't seem to find out how to do it. I have a looping code and a function combined but can't seem to get the output I wanted to see.

    My code is like this:

    WITH CTE AS
    (
    SELECT 1 as Day
    UNION ALL
    SELECT Day+1 FROM CTE
    WHERE Day < 15
    ), Name as (Select * from fn_logs(@Month, @Year,@date_from,@date_to)

    )

    SELECT CTE.Day,
    CASE WHEN Name.DAtee != CTE.Day THEN Name.Fullname ELSE Name.Fullname END as Fullname,
    CASE WHEN Name.DAtee != CTE.Day THEN ' ' ELSE Name.AMIN END as AMIN,
    CASE WHEN Name.DAtee != CTE.Day THEN ' ' ELSE Name.AMOUT END as AMOUT,
    CASE WHEN Name.DAtee != CTE.Day THEN ' ' ELSE Name.PMIN END as PMIN,
    CASE WHEN Name.DAtee != CTE.Day THEN ' ' ELSE Name.PMOUT END as PMOUT
    FROM CTE, Name

    group by CTE.Day,Name.Fullname,Name.AMIN,Name.AMOUT,Name.PMIN,Name.PMOUT

    where the **Name.DAtee** gets the day of the date
    The output I wanted to happen was like:

    Day Fullname   AM-IN   AM-OUT   PM-IN   PM-OUT
    1   Ara Ast    8:00    12:00    12:03    5:00
    2   Ara Ast    7:51    12:22    12:23    5:10
    3   Ara Ast
    1   Clara Est  8:01    12:12    12:25    5:07
    2   Clara Est
    3   Clara Est  7:41    12:02    12:15    5:00


    I wanted to show all the days a single employees has logged and when no date was logged, it will automatically show no value. The output I get was like:

    Day Fullname  AM-IN  AM-OUT  PM-IN  PM-OUT
    1   Ara Ast   8:00   12:00   12:03  5:00
    2   Ara Ast
    3   Ara Ast
    1   Ara Ast
    2   Ara Ast   7:51   12:22   12:23   5:10
    3   Ara Ast
    1   Ara Ast
    2   Ara Ast
    3   Ara Ast

    The values seem to repeat and whenever the value was not equal to the date, it prints the no value. Sorry I am kinda still in the learning stage of the sql server coding.

    Thanks in advance.

    Wednesday, March 14, 2018 3:42 AM

All replies

  • User1120430333 posted

    How is what you have posted a Web service issue? It's not a Web service issue. You have T-SQL issues on how to execute T-SQL, which is a database issue.

    You could post to the Transact-SQL forum (T-SQL) and seek help.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    Wednesday, March 14, 2018 12:19 PM
  • User77042963 posted

    Instead of cross join (FROM CTE, Name), you should use left join.

    Post your table and sample data and your function if you want a working query.

    Thursday, March 15, 2018 3:14 PM