SQL Server Performance

getting multiple records instead of one

Discussion in 'SQL Server 2005 Reporting Services' started by rai, May 10, 2007.

  1. rai New Member

    Can anyone solve my problem please. Iam very new to this sql query stufff.
    I got 4 views.
    View1 result (code and income)
    Code Income
    3180.9064949238
    6907.6083412962
    741.3846458392
    114404.7250236475
    41021.1001552847
    262.2729940619
    83922.1734928514
    View2 result (code and OpeningBalance)
    Code opening Balance
    31535.7278
    68792.8247
    7451.1157
    1145108.1791
    415287.5795
    2581.4349
    524439.4521
    861151.7488
    View3 result (code and closingBalance)
    Code Closing Balance
    31586.8213050762
    67998.6163587038
    7409.7310541608
    1147811.0747763525
    416433.9105447153
    2497.0023059381
    57014.6987000000
    857264.2453071486
    View4 result (code ,RE,CN,MC,PR)
    code update RE CN MC PR
    1118524.640.000.000.00
    110.000.000.00-1070.27
    110.00-346.74930.000.00
    110.000.000.000.00
    210.00-22.15960.000.00
    31232.000.000.000.00
    410.000.000.000.00
    410.00-79.88880.000.00
    410.000.000.00-59.74
    412307.060.000.000.00
    61113.400.000.000.00
    8134.670.000.000.00

    I have created a stored procedure
    Periondend
    here is my query
    SELECT
    PublicationControl.PubCode,
    PublicationControl.PubName,
    View2.SumOfOpeningBal,
    View4.RE ,
    View4.CN,
    View4.MC,
    View4.PR,
    View1.Income,
    View3.OS_AMT
    FROM (((PublicationControl
    LEFT JOIN View1
    ON PublicationControl.PubCode = View1.Pub)
    LEFT JOIN View4 ON PublicationControl.PubCode = View4.PubCode)
    LEFT JOIN View3 ON PublicationControl.PubCode = View3.PubCode)
    INNER JOIN View2 ON PublicationControl.PubCode = View2.PubCode
    WHERE (((PublicationControl.PubCode)=@PublicationCode))

    The problem is that i want the result to be in one raw only.
    Instead iam getting 4 rows of data
    Result iam getting is this
    1Local Subs145108.17910.00-346.74930.000.0014404.7250236475147811.0747763525
    1(Local Subs145108.17910.000.000.00-1070.2714404.7250236475147811.0747763525
    1Local Subs145108.17910.000.000.000.0014404.7250236475147811.0747763525
    1Local Subs145108.179118524.640.000.000.0014404.7250236475147811.0747763525

    but instead i want only
    1 Local Subs 145108.1791 18524.64 -346.7493 0.00 -1070.27 14404.7250236475 147811.0747763525

    I tried to use group by to get only 1 row but it didnt help me.
    Please can someone giv me any idea hoe to solve this problem.

    Waiting for you peoples opinion....

    Thanks in advance
    Rai


  2. techbabu303 New Member

    Did you try the same query with "HAVING" instead of "WHERE" Clause with GROUP BY ?

    Cheers
    Sat

Share This Page