SQL Server Performance

How to Join a Query in sql server 2008

Discussion in 'ALL SQL SERVER QUESTIONS' started by kreddyav, May 11, 2013.

  1. kreddyav New Member

    AT PRESENT I GET THE REPORT PERFECTLY BY USING THE BELLOW QUERY BUT IT TAKES TOO MUCH TIME FOR EXECUTION. SO I NEED TO SIMPLIFY THE QUERY FOR FAST RESULT USING JOINS OR ANY ELSE

    "SELECT ROW_NUMBER() OVER (ORDER BY A.EMPID) As SLNO,A.EMPID,A.EMPNAME,

    (ISNULL((SELECT TOP(1)B.STATUS+'-'+B.PTIME FROM TBL_PUNCH B WHERE B.BADGENUMBER=A.EMPID AND A.SN=B.SN AND CONVERT(VARCHAR(10),B.PDATE,105)='01-04-2013' AND B.PUNCHID=1 AND B.STATUS LIKE '%%' ORDER BY PTIME ASC),'')+'/'+ISNULL((SELECT TOP(1)B.STATUS+'-'+B.PTIME FROM TBL_PUNCH B WHERE B.BADGENUMBER=A.EMPID AND A.SN=B.SN AND CONVERT(VARCHAR(10),B.PDATE,105)='01-04-2013' AND B.PUNCHID=2 AND B.STATUS LIKE '%%' ORDER BY PTIME ASC),'')) AS [Apr 01],

    (ISNULL((SELECT TOP(1)B.STATUS+'-'+B.PTIME FROM TBL_PUNCH B WHERE B.BADGENUMBER=A.EMPID AND A.SN=B.SN AND CONVERT(VARCHAR(10),B.PDATE,105)='02-04-2013' AND B.PUNCHID=1 AND B.STATUS LIKE '%%' ORDER BY PTIME ASC),'')+'/'+ISNULL((SELECT TOP(1)B.STATUS+'-'+B.PTIME FROM TBL_PUNCH B WHERE B.BADGENUMBER=A.EMPID AND A.SN=B.SN AND CONVERT(VARCHAR(10),B.PDATE,105)='02-04-2013' AND B.PUNCHID=2 AND B.STATUS LIKE '%%' ORDER BY PTIME ASC),'')) AS [Apr 02],

    (ISNULL((SELECT TOP(1)B.STATUS+'-'+B.PTIME FROM TBL_PUNCH B WHERE B.BADGENUMBER=A.EMPID AND A.SN=B.SN AND CONVERT(VARCHAR(10),B.PDATE,105)='03-04-2013' AND B.PUNCHID=1 AND B.STATUS LIKE '%%' ORDER BY PTIME ASC),'')+'/'+ISNULL((SELECT TOP(1)B.STATUS+'-'+B.PTIME FROM TBL_PUNCH B WHERE B.BADGENUMBER=A.EMPID AND A.SN=B.SN AND CONVERT(VARCHAR(10),B.PDATE,105)='03-04-2013' AND B.PUNCHID=2 AND B.STATUS LIKE '%%' ORDER BY PTIME ASC),'')) AS [Apr 03],

    A.DEPT FROM TBL_EMP A WHERE A.SN='4550962200036' AND A.DEPT='All Departments'
    THIS IS MY QUERY PRESENTLY USING.
    IF ANY OF YOU WANTS TO CONNECT TO THE DATABASE I WILL GIVE YOU THE TEST DB CREDENTIALS
    PLS HELP ME...

    Thnaks in Advance
    Kreddy
  2. FrankKalis Moderator

    Welcome to the forums!
    Instead of posting your query, it would have been more beneficial if you've posted a description of your problem, your table structures, some sample data and the requested output. Looks like there a quite a few things to be optimised, although it suspiciously looke like some kind of homework assignment...
  3. sonnysingh Member

    Share your problems as problem analysis will be better manner and so solution(s). As it's looks you should be having calculative data for each day (day wise data) in the table and run this query from it. This way you can reduce so many overheads on network, server and speed up this query. of course depend on query usage and data requirement frequency.

Share This Page