Need to select where count more than 1 in criteria one and in that more than 1 .
-
Hi,
I need to make a select and display of ITEMID, TRANSID and POSRECORDID, where TRANSID count is higher than 1, and in that result where POSRECORDID count is higher than 1. How can I achive that?
My initial SELECT is like this :
SELECT TRANSID, ITEMID, POSRECORDID
FROM JournalTrans
WHERE STOREID = 99999 AND TRANSDATE = '2020-02-24 00:00:00.000';
- This topic was modified 4 years, 10 months ago by sk 939.
-
Take a look at "GROUP BY" and "HAVING" clauses, if you have any issues please post back
-
Are you looking for something like this?
SELECT JT.TRANSID
,JT.ITEMID
,JT.POSRECORDID
FROM JournalTrans JT
inner join (
SELECT TRANSID, POSRECORDID
FROM JournalTrans
group by TRANSID,POSRECORDID
having count(*) > 1
)
FI
on JT.TRANSID= FI.TRANSID
and JT.POSRECORDID = FI.POSRECORDIDI want to be the very best
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was - Ant-Green wrote:
Take a look at "GROUP BY" and "HAVING" clauses, if you have any issues please post back
Hmm
Now i am struggling a bit on how to deal with TSQL Questions like this.
What i have just done and what others do is often post a possible query that might deliver the wanted result.
Obviously it is not a good solution since it does not help the developement and learning process of the person asking.
In hindsight ur approach is better, just a bit bare bone with lack of some hints.
Has there been a discussion in this forum on this topic? I mean there is multiple posts on how to ask questions correctly, but is there some kind of guideline on how you should post answers?
I want to be the very best
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
Like no one ever was -
Hi Ten,
Thank you for your code 🙂 .. it looks it will do the trick, but the Query have now run for +5 min and I have not gotten the result yet, so cannot verify yet. I guess my WHERE clauses should be like this?
SELECT JT.TRANSID
,JT.ITEMID
,JT.POSRECORDID
FROM RCMJournalItemTrans JT
inner join (
SELECT TRANSID, POSRECORDID
FROM RCMJournalItemTrans
group by TRANSID,POSRECORDID
having count(*) > 1
)
FI
on JT.TRANSID= FI.TRANSID
and JT.POSRECORDID = FI.POSRECORDID
WHERE STOREID = 42023 AND TRANSDATE = '2020-02-24 00:00:00.000'; -
@ktflash, no standard on how to answer a question. Me personally like to point in a research direction first. Simply handling a solution will most usually result in someone taking it and not understanding how it works. Then when it comes to make a change the poster will be back asking for another query rather than investing in themselves for self development.
@sk939, could you supply an estimated plan, or better the actual plan if you manage to get the query to complete?
Could you also please post the definition of the table along with any indexes.
It may be best to follow this link http://www.sqlservercentral.com/articles/Best+Practices/61537/ on assistance on how to post the code so we can look at it easily to see what is going on.
-
@anthony.green : The query take more than 10 minutes to run using WHERE Clause at outer select, and approcimately 3 minutes to complete placing inside the inner join.
Here is a list of the Indexes on the table, what info do you need from these? 🙂
-
Unfortunately not, I could hazard a guess as to what columns are in those indexes but that's it a guess. Without the execution plan also its hard to say whats going on.
Could you go through the link I posted and knock up some sample create tables with sample data and an expected outcome, that will help to write the query to meet what you need, then it will be a case of you transposing that into your environment to work how it needs to.
Base the information on what is already there, may be best to just create the table as is and put in sample data for the bits your after to make it easier to transport into something workable for your server.
-
@anthony.green :
Actually the below code is doing exactly what it needs to do, exect I have difficult getting it to ORDER by TRANSID, it says The ORDER BY clause is invalid in views:
SELECT JT.TRANSID
,JT.ITEMID
,JT.POSRECORDID
FROM RCMJournalItemTrans JT WITH(INDEX(ITR_RCMJOURNALITEMTRANS_ITEMID2_COVERING))
inner join (
SELECT TRANSID, POSRECORDID
FROM RCMJournalItemTrans WITH(INDEX(ITR_RCMJOURNALITEMTRANS_ITEMID2_COVERING))
WHERE STOREID = 42023 AND TRANSDATE = '2020-02-25 00:00:00.000' AND TRANSTIME = 0
group by TRANSID,POSRECORDID
having count(*) > 1
)
FI
on JT.TRANSID= FI.TRANSID
and JT.POSRECORDID = FI.POSRECORDID; -
You wouldn't want to create a view based on that above piece of code, what it looks like you do want is a stored procedure where you pass in a value for the StoreID and TransDate. Those will be forever changing values, you wouldn't want them to be static would you?
You going to create a view for each store for each potential transdate, that would be object overkill.
If it is a view you need then create the view without order by then select from the view with order by
-
No, I do not want to create a view, this select will be feed by the Robot running the RPA process that this SQL should be used in, I just need the output to be ordered by the TRANSID 🙂
-
The you just order by JT.TRANSID.
As you've use TRANSID in two places you need to alias it correct on the order by.
-
Now all is perfect .. Thank you guys for your help, the final code is :
SELECT JT.TRANSID
,JT.ITEMID
,JT.POSRECORDID
FROM RCMJournalItemTrans JT WITH(INDEX(ITR_RCMJOURNALITEMTRANS_ITEMID2_COVERING))
inner join (
SELECT TRANSID, POSRECORDID
FROM RCMJournalItemTrans WITH(INDEX(ITR_RCMJOURNALITEMTRANS_ITEMID2_COVERING))
WHERE STOREID = 42023 AND TRANSDATE = '2020-02-24 00:00:00.000' AND TRANSTIME = 0
group by TRANSID,POSRECORDID
having count(*) > 1
)
FI
on JT.TRANSID= FI.TRANSID
and JT.POSRECORDID = FI.POSRECORDID
ORDER BY JT.TRANSID;
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply