How do I get available stock with same servicetags

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Table_1

SQL

Table_1
Stockno|StartDate|ServiceTag|Modelno
123	2015-08-01	ABC	Inspiron
123	2015-06-01	DEF	Inspiron
123	2015-08-01	GHI	Inspiron
123	2015-08-01	JKL	Inspiron
456	2015-08-01	MNO	Galaxy
456	2015-07-01	PQR	Galaxy
456	2015-08-01	STU	Galaxy
456	2015-08-01	VWX	Galaxy
456	2015-08-01	ABC	Galaxy

Table_2
Stockno|TransDate|TransType|ServiceTag|Modelno
                                            
123	2015-08-04	2100	  ABC	  Inspiron
123	2015-08-19	2100	  GHI	  Inspiron
456	2015-08-25	2100	  STU	  Galaxy
123	2015-07-25	2100	  DEF	  Inspiron

I have two tables.Table_1 is available stock. Table_2 is sold stock. I want remaining stock after sales. I wrote the following query,it works fine but the problem is it doesnt return Galaxy with service tag= ABC(not sold) since Inspiron(servicetag=ABC) is sold. So in remaining stock I should get totally 5 records instead I get only 4. How to correct this query?

SQL

 Select P.Stockno,P.Modelno,P.ServiceTag, Count(P.Stockno) as ClosingBal
From Table_1 as P
Where  P.ServiceTag  Not in (Select ServiceTag from Table_2 )
Group by P.Stockno,P.Modelno,P.ServiceTag

Posted 8-Sep-15 19:47pm

Member 11377180

Add a Solution

Comments

Tomas Takac 9-Sep-15 3:06am

If servicetag is not enough to identify the sold stock then why don't you add more columns to the comparison? I guess you should compare on all three columns: Stockno, Modelno, ServiceTag.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2015-09-08T22:53:00

Change the NOT IN to an outer join and add the other fields to the missing conditions

SQL

SELECT  P.Stockno
       ,P.Modelno
       ,P.ServiceTag
       ,Count(P.Stockno) as ClosingBal
FROM    Table1 P
left OUTER
JOIN    Table2 t2
    ON  p.servicetag = t2.servicetag
    AND p.stockno = t2.stockno
    AND p.Modelno = t2.Modelno
WHERE   t2.servicetag IS NULL
    AND t2.stockno IS NULL
    AND t2.Modelno IS NULL
GROUP BY P.Stockno,P.Modelno,P.ServiceTag