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
CodePudding user response:
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