How to Create a New Columns Using Count(*)

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

SQL Server 2000 Forums

SQL Server Development (2000)

How to Create a New Columns Using Count(*)

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-05-31 : 19:15:12

HI

if I

Select Invoice, Date , Count(*) as Qty from table group by invoice, date
I get this

Invoice Date Qty
97 01/01/2006 3
102 01/07/2007 2
44 01/07/2007 7

But I have a Work @Field1 = 4

I would like to get this

Invoice Date Qty Diff
97 01/01/2006 3 3
102 01/07/2007 2 1
44 01/07/2007 7 0

***Remarks

Diff will be

if @field1 - qty > 0
diff = 3
@field = @field - 3
else
diff = @field
@field = 0
End-if

Any help will be Apreciated

Tks Again

CLages
RIo de Janeiro

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-31 : 19:37:53

Not sure if I understand what you mean by this

quote:

if @field1 - qty > 0
diff = 3
@field = @field - 3
else
diff = @field
@field = 0
End-if

Can you rephrase your question?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-31 : 19:40:15

ahh..I think I see it. You want the diff to be cumulatively subtracted from each row...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-31 : 19:46:30

Is there any PK on the table? Are these quantities for a specific product?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-06-01 : 07:48:14

exactly
I want the diff to be cumulatively subtracted from each row

In this sample this tables represents one Products
with 3 invoices with differents Dates.

in this this case i sell 12, the number 4 is the qty thas has returned.
then i want to subtract for each row from the Oldest date
I eliminate the First (3) and the next date one more.

I expect to be returned 8 more some day in the future.

Tks

C.Lages

ps. The If clause is the rule to be subtracted.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-01 : 12:26:38

Now between the recrods with Invoices 102 and 44, both have the same date, so what is the criteria to assign the @Field to Qty?
Here's something to get you started. If you understand the logic you can take it from there:


Declare @t Table(ProductId int,Invoice int, Date datetime, Qty int, Diff int)
Insert into @t 
Select 1,97 ,'01/01/2006' ,3 ,0 union all
Select 1,102 ,'01/07/2007' ,2 ,0 union all
Select 1,44 ,'01/07/2007' ,7 ,0
select * from @t
Declare @field int, @Qty int, @invoiceId int
Set @field = 4

While @field >0 
	Begin
		Select Top 1 @Qty = Qty, @invoiceId = Invoice from @t Where ProductId = 1 And Diff = 0Order by Date Asc
		IF @field >= @Qty 
			Begin
				update @t Set Diff = @Qty Where productId = 1 And Invoice = @invoiceId 
				Set @field = @field - @Qty
			End
		else
			Begin
				update @t Set Diff = @field  Where productId = 1 And Invoice = @invoiceId 
				Set @field = 0
			End
	End
Select * from @t

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-06-04 : 15:40:32

Dinakar Nethi

It will help a lot,

Tks

Clages
Rio de Janeiro

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources