How do I use Minus operator to Minus between 2 fields

Question

4.00/5 (1 vote)

See more:

, +

I stuck on this I write SQL to show a count of product
Example
I need them to show like this

I have 12 Pieces of Product
I show them on "Textbox1" as all product
then the second "Textbox" I need to show "Registered Product" (Registered Product is count from binary 0 - 1 that is 0 = Approved 1 = not approved)
like this

All Product = 12
Registered = 6 (All Product(12) - Registered Product(6)

Then it will expanded the product to a category like this

FOOD : 2
USES : 2
TAILOR : 2

now I just can show the
All product then expanded 12 products to a category
i'm stuck on

1.how can I count a registered product by Stored procedures (T-SQL)
2.how can I expanded them to a category

I had write a code to show them like this

SQL

select count(T.[ProductName]) as ProductCount,
       T.CategoryID,
       T.estbProducerID,
       T.NewProducer,
       T.[ProductCountAll]
from (
     select tn.[ProductName],
            tn.[CategoryID],
            tp.estbProducerID,
            tp.NewProducer,
            count(*) over() as [ProductCountAll]
     from [tpdcn] tp,[tpdtn] tn
       left join [tpdcn]
         on tn.parentid = tpdcn.libDocumentID
     where tp.libdocumentID = @id
     ) as T
group by T.[CategoryID],
         T.estbProducerID,T.NewProducer,  T.[ProductCountAll]
		
	

END

it's show

ProductCount	CategoryID	estbProducerID	NewProducer	ProductCountAll
      4	             1	               2	     1	               12
      3	             2	               2	     1	               12
      2	             3	               2	     1	               12
      2        	     4	               2	     1	               12
      1	             5	               2	     1	               12

FOOD = 4
TAILOR = 3
USES = 2
EX1 = 2
Ex2 = 2
ex3 = 1

Posted 16-Mar-14 23:17pm

Touchy Vivace

Updated 16-Mar-14 23:22pm

v4

Add a Solution

2 solutions

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)

sohail awr · Answer 1 · 2014-03-17T07:08:00

SQL

USE [MyDatabase] 
GO
/****** Object:  StoredProcedure [dbo].[sp_1]    Script Date: 03/17/2014 22:07:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_1]

as
Begin
Declare @AllProducts int
Declare @RegProducts int
Declare @UnregProducts int

Select @AllProducts = (Select COUNT(NewProducer) as Allproducts from TN)
Select @RegProducts = (select COUNT(NewProducer) as Regproducts from TN where newproducer=0)
Select @UnregProducts = (select COUNT(NewProducer) as UnRegproducts from TN where newproducer=1)

Select  @AllProducts as AllProducts, @RegProducts as RegProducts,@UnRegProducts as UnRegProducts
end

SQL

select a.cat_name,COUNT(b.categoryid) as tot from t_cat a, tn b where a.categoryid=b.categoryid group by a.cat_name order by tot desc

sohail awr · Answer 2 · 2014-03-18T04:30:00

Solution 2

If you want to calculate on Textbox. Please Follow this method

VB

Me.textbox3.text = val(me.textbox1.text)- val(me.textbox2.text)

Posted 18-Mar-14 4:30am

sohail awr