June 24, 2011 at 8:37 am
Hello all,
I'm struggling with how to get the results I need and could use some help.
Need to assign a priority based on file status for customers that have both
forms SIG1 (only type A) and ALT1 (types B, M, R) filed on the same day,
B = 'priority 1'
M = 'priority 2'
R = 'priority 3'
Some sample data is below
File_number Cust_id Form_type File_status Date_filed
200587438752SIG1A2011-03-11
200600938752ALT1B2011-03-11
200618526547SIG1A2011-03-11
200685226547ALT1M2011-03-11
200697535613SIG1A2011-03-11
200786535613ALT1R2011-03-11
200715618764SIG1A2011-03-11
200736518764ALT1B2011-02-25
2008274 39875 SIG1 C 2011-03-11
June 24, 2011 at 10:03 am
sqlgirlatty (6/24/2011)
Hello all,I'm struggling with how to get the results I need and could use some help.
Need to assign a priority based on file status for customers that have both
forms SIG1 (only type A) and ALT1 (types B, M, R) filed on the same day,
B = 'priority 1'
M = 'priority 2'
R = 'priority 3'
Some sample data is below
File_number Cust_id Form_type File_status Date_filed
200587438752SIG1A2011-03-11
200600938752ALT1B2011-03-11
200618526547SIG1A2011-03-11
200685226547ALT1M2011-03-11
200697535613SIG1A2011-03-11
200786535613ALT1R2011-03-11
200715618764SIG1A2011-03-11
200736518764ALT1B2011-02-25
2008274 39875 SIG1 C 2011-03-11
Does "priority" means sorting order?
For the multiple criteria part check AND and OR operators on WHERE clause.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 24, 2011 at 10:55 am
I'm not sure I fully understand, but maybe this will get you going:SELECT
*
FROM
(
SELECT *
FROM Table
WHERE Form_type = 'SIG1' AND File_staus = 'A'
) AS A
INNER JOIN
(
SELECT *
FROM Table
WHERE Form_type = 'ALT1' AND File_staus IN ('B', 'M', 'R')
) AS B
ON A.Cust_id = B.Cust_id
AND A.Date_filed = B.Date_filed
June 24, 2011 at 11:00 am
Thanks! That's what I needed.
June 24, 2011 at 11:25 am
Full solution that worked for me
SELECT
*
FROM
(
SELECT *
FROM Table
WHERE Form_type = 'SIG1' AND File_staus = 'A'
) AS A
INNER JOIN
(
SELECT File_number,Cust_id,Form_type,File_status,Date_filed,Priority =
CASE File_status
WHEN 'B' THEN 'Priority 1'
WHEN 'M' THEN 'Priority 2'
WHEN 'R' THEN 'Priority 3'
END
FROM Table
WHERE Form_type = 'ALT1' AND File_staus IN ('B', 'M', 'R')
) AS B
ON A.Cust_id = B.Cust_id
AND A.Date_filed = B.Date_filed
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply