SQL Server Performance

query help needed

Discussion in 'General Developer Questions' started by newsqldev, Jul 20, 2006.

  1. newsqldev New Member

    I need a help in the below query.

    declare @sin table (SID tinyint, SEID smallint, FMID smallint, TP char(1),SDT datetime)
    insert @sin
    select 126, 4000, 5107, 'A','2006-07-29 00:00:00.000' union all
    select 115, NULL, 5107, 'B','2006-07-30 00:00:00.000'union all
    select 116, NULL, 5107, 'B','2006-07-30 00:00:00.000' union all
    select 136, NULL, 5108, 'A','2006-07-30 00:00:00.000'


    declare @HIT table (RNRSNP char(2), SID tinyint, RSLT tinyint )

    insert @HIT
    select '08', 116,01

    declare @GX table (x tinyint, SID tinyint)

    insert @GX
    select 01,126 union all
    select 01,136

    DECLARE @FRMDT DATETIME
    DECLARE @TODT DATETIME
    DECLARE @X INT
    DECLARE @SEID INT
    DECLARE @RSLT INT


    SET @FRMDT =NUll
    SET @TODT =NULL
    SET @X =0
    SET @SEID =0
    SET @RSLT =0




    SELECT COUNT(GX.X) AS X_Count
    FROM (
    SELECT s.SID,
    s.sdt sid_dt,
    w.SEID,
    s2.SID sid2
    FROM (
    SELECT FMID,
    MAX(SEID) SEID
    FROM @SIN
    GROUP BY FMID
    ) w
    INNER JOIN @sin s ON s.FMID = w.FMID
    INNER JOIN @sin s2 ON s2.FMID = w.FMID AND s2.SEID = w.SEID
    ) z

    INNER JOIN @HIT h ON h.SID = z.SID
    INNER JOIN @GX gx ON gx.SID = z.SID2

    WHERE (z.SID_DT Between ISNULL(@FRMDT, convert(datetime, 'January 1, 1753')) And ISNULL(@TODT, convert(datetime, 'December 31, 9999')))
    AND (@X = 0 OR GX.X = @X)
    AND (@SEID = 0 OR Z.SEID = @SEID)
    AND (@RSLT = 0 OR H.RSLT = @RSLT)


    Need to display count 2 if no parameters are passed..
    OUTPUT:
    X_Count
    -------
    2

    Thanks for help in advance!!!!!!!
  2. ramkumar.mu New Member

    IF @FRMDT IS NUll AND @TODT IS NULL AND @X =0 AND @SEID =0 AND @RSLT =0
    BEGIN
    SELECT 2 AS X_Count
    RETURN
    END

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  3. newsqldev New Member

    Thanks for the reply!
    I need the result count 2.. based on the table gx which contains 2 rows.
  4. ramkumar.mu New Member

    I didnt get you. did you get the desired output? if not, explain in detail.

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  5. patel_mayur New Member

    Seems not possible...

    Your data is filtered out at "INNER JOIN @HIT h ON h.SID = z.SID "

    as

    insert @HIT
    select '08', 116,01

    @HIT have only one row and you are joining with the derived table that have four rows,
    resulting in single row with SID = 116

Share This Page