Please Help Me ... - SQL Server Forums

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

Transact-SQL (2000)

Please Help Me ...

Author

Topic

skiski
Starting Member

15 Posts

Posted - 2010-05-08 : 22:57:28

tblA => ID,Date,Number | tblB => ID,Date,Number
+-----------------------------|---------------------------+
| 1,2010/02/08,1000 | 1,2010/02/08,3000 |
| 2,2010/02/08,1000 | 2,2010/02/08,1000 |
| 3,2010/02/09,1000 | 3,2010/02/08,5000 |
| 4,2010/02/09,3000 | 4,2010/02/09,1000 |
| 5,2010/02/10,8000 | 5,2010/02/11,2000 |
| 6,2010/02/11,1000 | 6,2010/02/11,1000 |
+-----------------------------+---------------------------+
Result = >
1000 = 1
3000 = 0
8000 = 1

please help me for this query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-08 : 23:57:50

what's is the logic ?

KH
[spoiler]Time is always against us[/spoiler]

skiski
Starting Member

15 Posts

Posted - 2010-05-09 : 01:18:02

Hello

I have two tables in the table a number repeated three times in 1000 and in Table II, number 1000 repeated once. I plan to table a table two numbers together and control the output numbers show twice in 1000.
Please help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-09 : 03:12:10

from your sample data posted, I can see 1000 appear 4 times in Table I and 3 times in Table II and not 3 and once as you said. Can you explain why it is so ?

also what does the 1 and 0 signify ? Where does it comes from ?

KH
[spoiler]Time is always against us[/spoiler]

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-09 : 03:18:40

What i feel is that the OP is doing count of number in table one and deleting the count from number two..
Ex:

4 times 1000 in table I and 3 times in table II = 4-3 = 1
1 time 3000 in table I and 1 time in table II = 1-1 = 0
1 time 8000 in table I and 0 time in table II = 1-0 = 1

If my understanding is correct then you can try the below solution. In case my understanding is wrong then you need to be more clear on your requirement.

Declare @Table1 table
( Id int identity,
Number int)

Declare @Table2 table
( Id int identity,
Number int)

Insert into @Table1
Select 1000 union all
Select 1000 union all
Select 1000 union all
Select 3000 union all
Select 8000 union all
Select 1000

Insert into @Table2
Select 3000 union all
Select 1000 union all
Select 5000 union all
Select 1000 union all
Select 2000 union all
Select 1000

Select a.number, T1Number - isnull(T2number,0) As Balance From
(Select a.number,count(a.number) as T1number from @Table1 a Group by a.number) As A
Left outer join
(Select b.number,count(b.number) as T2number from @Table2 b Group by b.number) As b
on A.number = B.number

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

skiski
Starting Member

15 Posts

Posted - 2010-05-10 : 00:05:06

Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-10 : 00:11:38

Welcome

I am here to learn from Masters and help new bees in learning.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-10 : 01:25:58

[code]SELECT Number,
SUM(Items)
FROM (
SELECT Number,
1 AS Items
FROM @Table1

UNION ALL

SELECT Number,
-1
FROM @Table2
) AS d
GROUP BY Number
HAVING SUM(Items) >= 0
ORDER BY Number[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources