SQL Server : check if row exists in group

user_16914331 0
Viewed 276

Using SQL Server to extract some data, I want to get the rows which exist or not to do further analysis. With the following code I am not able to get the expected output. Assuming I want to add a column which give a flag if the price 19.17000 exists BY WEEK. The following code flag everything as exist but I would like only the weeks where the price exist to have a flag.

The code:

SELECT 
    (CASE WHEN EXISTS (SELECT 1 
                         FROM table1 WITH (NOLOCK)
                         WHERE Price = 19.170000
                         GROUP BY companyId, YEAR([calendarDate]), datepart(week, [calendarDate])) 
                THEN 'Record exists'
                ELSE 'Record doesn''t exist' 
     END) AS [Transaction],
    YEAR([calendarDate]) AS year_,  
    DATEPART (week, [calendarDate]) AS week_,
    calendarDate,
    Price
FROM 
    table1
WHERE 
    companyId = 18527

The output:

Transaction     year_   week_   calendarDate    stockPrice
----------------------------------------------------------
Record Exists   2010    7   2010-02-12  19.170000
Record Exists   2010    7   2010-02-13  19.170000
Record Exists   2010    8   2010-02-14  19.170000
Record Exists   2010    8   2010-02-15  19.220000
Record Exists   2010    8   2010-02-16  19.400000
Record Exists   2010    8   2010-02-17  19.770000
Record Exists   2010    8   2010-02-18  21.280000
Record Exists   2010    8   2010-02-19  21.340000
Record Exists   2010    8   2010-02-20  21.340000
Record Exists   2010    9   2010-02-21  21.340000
Record Exists   2010    9   2010-02-22  21.590000
Record Exists   2010    9   2010-02-23  21.370000
Record Exists   2010    9   2010-02-24  21.510000
Record Exists   2010    9   2010-02-25  21.520000
Record Exists   2010    9   2010-02-26  21.730000
Record Exists   2010    9   2010-02-27  21.730000

Expected output:

Transaction             year_ week_ calendarDate    stockPrice
--------------------------------------------------------------
Record Exists           2010    7   2010-02-12  19.170000
Record Exists           2010    7   2010-02-13  19.170000
Record Exists           2010    8   2010-02-14  19.170000
Record doesn''t Exists  2010    8   2010-02-15  19.220000
Record doesn''t Exists  2010    8   2010-02-16  19.400000
Record doesn''t Exists  2010    8   2010-02-17  19.770000
Record doesn''t Exists  2010    8   2010-02-18  21.280000
Record doesn''t Exists  2010    8   2010-02-19  21.340000
Record doesn''t Exists  2010    8   2010-02-20  21.340000
Record doesn''t Exists  2010    9   2010-02-21  21.340000
Record doesn''t Exists  2010    9   2010-02-22  21.590000
Record doesn''t Exists  2010    9   2010-02-23  21.370000
sql-server
2 Answers

You are not getting the record for a specific week, your inner query is returning some random top 1 record having Price = 19.170000, you need to add the following condition in your case statement to get the data for a specific week.

t2.companyId = 18527 AND T1.YEAR([calendarDate])=T2.YEAR([calendarDate]) 
AND T1.datepart(week, [calendarDate]) = T2.datepart(week, [calendarDate])

Your full query should look like the following.

SELECT (
        CASE 
            WHEN EXISTS (
                    SELECT 1
                    FROM table1 t2 WITH (NOLOCK)
                    WHERE Price = 19.170000 AND t2.companyId = 18527
                        AND T1.YEAR([calendarDate]) = T2.YEAR([calendarDate])
                        AND T1.datepart(week, [calendarDate]) = T2.datepart(week, [calendarDate])
                    GROUP BY companyId
                        ,YEAR([calendarDate])
                        ,datepart(week, [calendarDate])
                    )
                THEN 'Record Exists'
            ELSE 'Record doesn''t Exists'
            END
        ) AS [Transaction]
    ,YEAR([calendarDate]) AS year_
    ,datepart(week, [calendarDate]) AS week_
    ,calendarDate
    ,Price
FROM table1 t1
WHERE t1.companyId = 18527
user_2973220

You can use a conditional windowed count for this

SELECT 
    CASE WHEN COUNT(CASE WHEN Price = 19.170000 THEN 1 END)
        OVER (PARTITION BY companyId, YEAR([calendarDate]), datepart(week, [calendarDate])) > 0
      THEN 'Record exists'
      ELSE 'Record doesn''t exist' 
     END AS [Transaction],
    YEAR([calendarDate]) AS year_,  
    DATEPART (week, [calendarDate]) AS week_,
    calendarDate,
    Price
FROM 
    table1
WHERE 
    companyId = 18527

If you wanted to include data for other companyid values (like your original query), then place the count inside a derived table

SELECT 
    [Transaction],
    YEAR([calendarDate]) AS year_,  
    DATEPART (week, [calendarDate]) AS week_,
    calendarDate,
    Price
FROM (
    SELECT *,
        CASE WHEN COUNT(CASE WHEN Price = 19.170000 THEN 1 END)
            OVER (PARTITION BY companyId, YEAR([calendarDate]), datepart(week, [calendarDate])) > 0
          THEN 'Record exists'
          ELSE 'Record doesn''t exist' 
         END AS [Transaction]
    FROM table1
) table1
WHERE 
    companyId = 18527

Side note: do not use NOLOCK unless you really know what you are doing, it can cause completely incorrect results

user_148689970
Related