July 14, 2010 at 9:37 am
I need to be able to step through row by row a list of statuses to see if a status of "filed" is there then return a Yes if it is and No if not. The status Filed can be anywhere in the list.
I will be using this in a report as a dataset passing in a unique key in and returning the Yes or No to the main report.
I think (being new to this) I need a loop but have not written one for T-SQL before...
This is what I have so far and test data:
--=============================================================
-- temp table for test data
--========================================================
IF OBJECT_ID('TempDB..#case_pick','u') IS NOT NULL
DROP TABLE #case_pick
CREATE TABLE #case_pick
(
case_sk int,
group_code varchar(6),
item_sk int,
pick_date datetime,
)
INSERT INTO #case_pick (case_sk, group_code, item_sk, pick_date)
SELECT 34568, 'CSTATU', 4241, NULL UNION ALL
SELECT 34568, 'CSTATU', 1040, '2009-03-16' UNION ALL
SELECT 34568, 'CSTATU', 4247, '2009-01-28' UNION ALL
SELECT 34568, 'CSTATU', 4219, '2009-06-16' UNION ALL
SELECT 34568, 'CSTATU', 4220, '2009-07-16' UNION ALL
SELECT 34560, 'CSTATU', 4221, '2009-08-16' UNION ALL
SELECT 34560, 'CSTATU', 4222, '2009-12-16' UNION ALL
SELECT 34560, 'CSTATU', 4223, '2009-12-25'
-- select * from #case_pick
-- DROP TABLE #case_pick
--===============================================================
--temp table for test data
--===============================================================
IF OBJECT_ID('TempDB..#group_items','u') IS NOT NULL
DROP TABLE #group_items
CREATE TABLE #group_items
(
item_sk int,
description varchar(30),
)
INSERT INTO #group_items (item_sk, description)
SELECT 4241, 'New Case'UNION ALL
SELECT 1040, 'Treating'UNION ALL
SELECT 4247, 'Released'UNION ALL
SELECT 4219, 'Submitted'UNION ALL
SELECT 4220, 'Filed'UNION ALL
SELECT 4221, 'New Case'UNION ALL
SELECT 4222, 'Treating'UNION ALL
SELECT 4223, 'Released'
-- select * from #group_items
-- drop table #group_items
--======================================================================================================
-- Code to return the date the status was entered, the status, and the days in that status calculated
-- by difference in previous status. Ex: row 2 datediff row 3
--=======================================================================================================
IF OBJECT_ID('TempDB..#Filed','u') IS NOT NULL
DROP TABLE #Filed
CREATE TABLE #Filed
(
casesk int,
status varchar(30),
rownum int
)
INSERT INTO #Filed
SELECT case_sk, gi.description, ROW_NUMBER() OVER (ORDER BY cp.pick_date) AS rownum
FROM #case_pick AS cp INNER JOIN
#group_items AS gi ON cp.item_sk = gi.item_sk
WHERE (cp.group_code = 'cstatu') AND (case_sk = 34568)
--test case_sk from table #case_pick the 2nd number does not have filed in list
ORDER BY pick_date
-- code to return "Yes" or "No"
SELECT f.status
FROM #Filed AS f LEFT JOIN
#Filed as nextd ON nextd.rownum = f.rownum + 1
-- Select * from #Filed
As always any help would be appreciated....
July 14, 2010 at 9:54 am
declare @item_sk int
set @item_sk = 4220
select @item_sk as item_sk, CASE when exists (select 1 from #group_items where item_sk = @item_sk and description = 'filed')
then 'Yes'
else 'No'
end as filed
You could modify that case statement to be included in the query that produces your result set, rather than calling the above a line at a time from your report.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 14, 2010 at 9:55 am
-- code to return "Yes" or "No"
IF EXISTS(SELECT f.status FROM #Filed f WHERE LOWER(f.status) LIKE 'filed')
SELECT 'YES'
ELSE
SELECT 'NO'
_________________________________
seth delconte
http://sqlkeys.com
July 14, 2010 at 10:11 am
You were too quick for me, Seth.
Paul, whenever you do something in SQL, it is almost always preferable to NOT use a loop. That is procedural thinking that usually produces poor performance in SQL. While SQL supports procedural constructs, they are tools of last resort. CASE expressions, EXISTS clauses, and other set-based techniques run MUCH faster in the vast majority of cases.
Conceptually, you will need to unlearn procedural thinking to get the job done well in SQL. While you are unlearning, always be sure to ask for help whenever you are faced with a problem and think "This needs a loop." Chances is are someone can show you technique that is much simpler and will run much faster.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 14, 2010 at 10:14 am
The Dixie Flatline (7/14/2010)
You were too quick for me, Seth.
LOL
_________________________________
seth delconte
http://sqlkeys.com
July 14, 2010 at 1:13 pm
Well Dixie, I ended up using yours with the idea of incorporating it into the main report 😀
Sorry Seth....
Final version (I didn't include all the Main query):
CASE WHEN EXISTS
(
SELECT 1
FROM case_pick cp INNER JOIN
group_items gi ON cp.item_sk = gi.item_sk
WHERE(cp.group_code = 'cstatu') AND (gi.description = 'filed') AND (cp.case_sk = cpick.case_sk)
)
THEN 'Yes'
ELSE 'No'
END AS Filed
added the AS Filed, cpick.case_sk is from the main query.
So basically added the above CASE statment to the Select statement of the Main query...
Thanks for the help.... and I didn't even think to use CASE like that.... I am such a newb at this..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply