+ Reply to Thread
Results 1 to 5 of 5

Thread: why am i getting a different values ?

  1. #1

    why am i getting a different values ?

    my problem is that Query #1 record`s value are less than Query #2 record`s :

    QUERY #1
    Code:
    with
    cte_biggie ([NPI],[Year Entered],[Month Entered],[Count],[Practice Name],[MLIS Code],[Practice Code],
            [Physician],[Sales Rep],[Date Established],[Address],[Address2],[City],[State],[Status]) as (
    select c.npi, DATEPART(yy, [DATE entered])  , 
    CASE WHEN DATEPART(mm, [DATE entered]) = 01 THEN 'Jan' 
               WHEN DATEPART(mm, [DATE entered]) = 02 THEN 'Feb'
               WHEN DATEPART(mm, [DATE entered]) = 03 THEN 'Mar'
               WHEN DATEPART(mm, [DATE entered]) = 04 THEN 'Apr'
               WHEN DATEPART(mm, [DATE entered]) = 05 THEN 'May'
               WHEN DATEPART(mm, [DATE entered]) = 06 THEN 'Jun'
               WHEN DATEPART(mm, [DATE entered]) = 07 THEN 'Jul'
               WHEN DATEPART(mm, [DATE entered]) = 08 THEN 'Aug'
               WHEN DATEPART(mm, [DATE entered]) = 09 THEN 'Sep'
               WHEN DATEPART(mm, [DATE entered]) = 10 THEN 'Oct'
               WHEN DATEPART(mm, [DATE entered]) = 11 THEN 'Nov'
               WHEN DATEPART(mm, [DATE entered]) = 12 THEN 'Dec'
               END
               ,COUNT([specimen id])  ,[practice name],b.[mlis practice id],a.[practice code],[Requesting Physician],c.salesrep,
       c.dateestablished , c.practiceaddress1, c.practiceaddress2,c.practicecity,c.practicestate,
        b.[Active Inactive]
    from quicklabdump a
        inner join qlmlismapping b
        on (b.[practice code] = a.[practice code])
        inner join PracticeandPhysician c
        on (a.[Requesting Physician]=c.doctorfirstname+' '+c.DOCTORLASTNAME
            and a.[practice code]=c.practicecode)
    where   ( [Date Entered] >= '20100101' AND [Date Entered] < '20120101')
    
    
    group by DATEPART(yy, [DATE entered]), DATEPART(mm, [DATE entered]), a.[practice name],b.[mlis practice id],a.[practice code],
        a.[Requesting Physician],c.salesrep,c.dateestablished, c.practicecity,c.practicestate,c.npi,c.practiceaddress1 ,c.practiceaddress2,
        b.[Active Inactive]
    
    
    )
    
    select [Original December Count]=SUM([count])  from cte_biggie
    where [Year Entered]=2011
    and [Month Entered]='Dec'
    Code:
    the result is
    79009
    whereas this query:
    QUERY# 2

    Code:
    ;with
    cte_biggie2 ([Year Entered],[Month Entered],[Count]) as (
    select  DATEPART(yy, [DATE entered])  , 
    CASE WHEN DATEPART(mm, [DATE entered]) = 01 THEN 'Jan' 
               WHEN DATEPART(mm, [DATE entered]) = 02 THEN 'Feb'
               WHEN DATEPART(mm, [DATE entered]) = 03 THEN 'Mar'
               WHEN DATEPART(mm, [DATE entered]) = 04 THEN 'Apr'
               WHEN DATEPART(mm, [DATE entered]) = 05 THEN 'May'
               WHEN DATEPART(mm, [DATE entered]) = 06 THEN 'Jun'
               WHEN DATEPART(mm, [DATE entered]) = 07 THEN 'Jul'
               WHEN DATEPART(mm, [DATE entered]) = 08 THEN 'Aug'
               WHEN DATEPART(mm, [DATE entered]) = 09 THEN 'Sep'
               WHEN DATEPART(mm, [DATE entered]) = 10 THEN 'Oct'
               WHEN DATEPART(mm, [DATE entered]) = 11 THEN 'Nov'
               WHEN DATEPART(mm, [DATE entered]) = 12 THEN 'Dec'
               END
               ,COUNT([specimen id]) 
    from quicklabdump 
    where   ( [Date Entered] >= '20100101' AND [Date Entered] < '20120101')
    
    
    group by DATEPART(yy, [DATE entered]), DATEPART(mm, [DATE entered])
    
    )
    
    select [Original December Count2]=SUM([count])  from cte_biggie2
    where [Year Entered]=2011
    and [Month Entered]='Dec'
    Code:
    is returning:
    108357
    i am returning the same thing in the above queries , any idea why am i getting this ?
    maybe it seems like the join that i am performing is actually limiting the results ?

  2. #2
    Junior Member
    Join Date
    Jan 2012
    Posts
    30
    Yes, it's the inner join clauses in the first query that are making the difference. With an inner join, the result set will only include rows from the left-hand table where there are matching rows in the right-hand table.

    You can include all rows from the left-hand table with a left join. Even with a left join, though, the number of rows in the final output is likely to be affected.

  3. #3
    Junior Member
    Join Date
    Jan 2012
    Posts
    40
    Here's a simple example of how an inner join can limit the rows in the resultset:
    Code:
    Authors:
    
    Author_ID    Name
    ---------    --------
    1            "John"
    2            "Jacky"
    3            "Parson"
    
    Posts:
    
    Post_ID      Author_ID     Abstract
    ---------    ----------    ----------
    1            1             "i love batman."
    2            2             "Who stand his boss ?"
    Selecting from only Authors (with no condition) returns all rows (obviously). But querying from both Authors and Posts with an inner join, like this:

    Code:
    SELECT a.Name, p.Abstract
    FROM Authors a
    JOIN Posts p ON p.Author_ID = a.Author_ID
    produces this result:

    Code:
    Name        Abstract
    --------    -----------
    "John"        "i love batman."
    "Jacky"       "Who stand his boss ?"
    Notice that even though we are querying the Authors table, "Bob" does not appear in the results...because he hasn't authored any posts (apparently). If we change the join to a left join, like this:

    Code:
    SELECT a.Name, p.Abstract
    FROM Authors a
    LEFT JOIN Posts p ON p.Author_ID = a.Author_ID
    the result will look like this:

    Code:
    Name        Abstract
    --------    -----------
    Fred        "Joel Spolsky is my hero..."
    Susan       "My co-worker is crazy..."
    Bob         NULL
    Note: The fact that I am returning a column (Abstract) from the second table in the join (Posts) makes no difference to the number of returned rows.

  4. #4
    oh , now i got it , thanks ryanve and jacklin , thumbs up for you .

  5. #5
    Junior Member
    Join Date
    Jan 2012
    Posts
    40
    you are welcome at any time

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts