Query on unique records with multiple critierias

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query on unique records with multiple critierias

Author

Topic

d3LL
Starting Member

4 Posts

Posted - 2013-10-19 : 20:03:51

Hi,
I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required. Really appreciate your expertise. Sorry if my explaination below is quite lengthy.

SCENARIO :

I am required to query from 2 tables for those unique record that meets both conditions below:-
1. Status is 1 @ max (trans_id), paychnl = CC
2. Status is 2 @ max (trans_id), paychnl = A or B

FYR, 2 tables and respective columns to query are as below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL

FYI, status refers to the paychnl method status:-
==> 1 means the current paychnl method
==> 2 means the previous paychnl method

paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.

Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.

Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE
table CHFF --> col STATUS,PAYCHNL

SELECT DISTINCT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.PAYCHNL
    
    FROM DBO.PTFF PTFF
    JOIN DBO.CHFF CHFF
    ON CHFF.ID = PTFF.ID
    WHERE
    PTFF.TRANSDATE BETWEEN 130501 AND 130831
    AND PTFF.TRANSCODE='T522'
    AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))

However, the script above returns :-
1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2,
2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B
3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID

Samples of the result:-

ID STATUS TRANS_ID PAYCHNL TRANSDATE EFFDATE TRANSCODE
5188 1 712 CC 130829 20130920 T522
9361 1 64 CC 130816 20140813 T522
7853 1 153 CC 130820 20130814 T522
8949 1 51 CC 130812 20130801 T522
8949 2 51 B 130812 20130801 T522
1908 1 455 CC 130516 20131129 T522
1908 2 455 A 130516 20131129 T522
1908 1 409 CC 111019 20111129 T522
1908 2 409 A 111019 20111129 T522
1908 1 404 CC 110929 20111129 T522
1908 2 404 B 110929 20111129 T522

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 03:30:56

as per your explanation, this might be what you're after


SELECT ID,
STATUS,
TRANS_ID,
PAYCHNL,
TRANSDATE,
EFFDATE,
TRANSCODE
FROM
(
SELECT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.PAYCHNL,
ROW_NUMBER() OVER (PARTITION BY PTFF.ID ORDER BY TRANS_ID DESC) AS Seq
    
    FROM DBO.PTFF PTFF
    JOIN DBO.CHFF CHFF
    ON CHFF.ID = PTFF.ID
    WHERE PTFF.TRANSDATE BETWEEN '20130501' AND '20130831'
    AND PTFF.TRANSCODE='T522'
    AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC') OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B'))
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

d3LL
Starting Member

4 Posts

Posted - 2013-10-20 : 04:35:55

quote:
Originally posted by visakh16

as per your explanation, this might be what you're after

SELECT ID,
STATUS,
TRANS_ID,
PAYCHNL,
TRANSDATE,
EFFDATE,
TRANSCODE
FROM
(
SELECT PTFF.TRANSCODE,PTFF.ID,PTFF.TRANS_ID,PTFF.TRANSDATE,PTFF.EFFDATE, CHFF.STATUS,CHFF.PAYCHNL,
ROW_NUMBER() OVER (PARTITION BY PTFF.ID ORDER BY TRANS_ID DESC) AS Seq

FROM DBO.PTFF PTFF
JOIN DBO.CHFF CHFF
ON CHFF.ID = PTFF.ID
WHERE PTFF.TRANSDATE BETWEEN '20130501' AND '20130831'
AND PTFF.TRANSCODE='T522'
AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC') OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B'))
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi visakh16,
Thank you for your prompt effort. However, when I tried it out, the refresh was taking more than 20mins for just 7 days TRANSDATE period, so I cancelled it because I think it exhausts our resources. Is thr any simpler way to get the same result, please?

Probably my additional info below may help you to understand better.

I would only need to see the query return those records with same ID when
1. status is 1 @ MAX(TRANS_ID) while paychnl='CC'
2. status is 2 @ MAX(TRANS_ID) while paychnl='A' or 'B'

so, from the sample result I gave earlier, I should only be having below result as a genuine and unique result for period between
1/5/2013 until 31/8/2013.

ID STATUS TRANS_ID PAYCHNL TRANSDATE EFFDATE TRANSCODE
8949 1 51 CC 130812 20130801 T522
8949 2 51 B 130812 20130801 T522

Apart from above, please take note that our dbase structure won't recognize date as this format --> 'yyyymmdd'
it will only recognize this format instead --> yymmdd
for TRANSDATE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 04:41:49

Are you using SQL Server?
please take note that our dbase structure won't recognize date as this format --> 'yyyymmdd'
it will only recognize this format instead --> yymmdd

this is not true for SQL Server. So far as datatype is datetime it will accept yyyymmdd
Also to see why query is slower analyze execution plan and see costly steps.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

d3LL
Starting Member

4 Posts

Posted - 2013-10-20 : 04:56:18

quote:
Originally posted by visakh16

Are you using SQL Server?
please take note that our dbase structure won't recognize date as this format --> 'yyyymmdd'
it will only recognize this format instead --> yymmdd

this is not true for SQL Server. So far as datatype is datetime it will accept yyyymmdd
Also to see why query is slower analyze execution plan and see costly steps.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi visakh16,
I believe it should be SQL server. but I'm doing the query via Excel data connection. The earlier tasks I've been assigned to do which involves date also having same scenario. It failed to give result when we use format 'yyyymmdd' but succeeded when using yymmdd. The weird part is that some of the columns use 'yyyymmdd' and some of them use yymmdd. You can see this from data in TRANSDATE and EFFDATE.
What I notice is I need to query following the original format as what in the dbase else I won't have any result or error will pop-up.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 06:13:09

Then date fields are not of datetime datatype i guess. Otherwise formats wont be a prblm.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

d3LL
Starting Member

4 Posts

Posted - 2013-10-20 : 06:28:10

quote:
Originally posted by visakh16

Then date fields are not of datetime datatype i guess. Otherwise formats wont be a prblm.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi visakh16,

ABt an hour ago I managed to get a workarnd to come out with the result I need. However, it's not a clean solution as it's not solely based on the SQL script. However, since I've cracked my head for couple of mths already for the script which basically I've never had SQL education bckgrnd in the first place, I had alternatively use a combination of some excel formulas to look for duplicates (same ID and TRANS_ID) then count how many genuine records. Then I do a pivot table to display only those genuine records to the requester/user.

I know that this solution still gonna cost me a long list of records which also causing the slow speed for a longer TRANSDATE period and lead to a very big file size (since it also gonna return all other non-duplicate records with status 1 & paychnl='CC') but I would need to submit this solution first to the mgmt since d bottom line is it able to come out with the result as required. However, I would still be very much appreciate if you or any expert out there to help me to gv the best solution and improve my SQL skills over time.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 06:33:48

I'm happy to help you so far as you give use some sample data and then show us what you want as output. Otherwise I can only give you suggestions.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources