Double condition in a Table - 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)

Double condition in a Table

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-11 : 15:12:26

I have a table that keeps all the inverotory movements, In this table we have fields like, company, location, item, quantity, Transaccion date, and reason code.
I need to made a report that bring me in the same line per item the sale and the waste.

For example:
Cia=2000
Location TJU01
For 1/01/2002 to 01/31/2002
Item Sale Waste Unid
--------------------------
0010087 30 -5 KG
0007834 69 -14 UNID

I can made a query to have the sale or waste in a line but not togethers. For example Reason Code is VTCL if is sale and MERM if is waste.
This is the code for waste, if a change the contidion to VTCL, I have the sale. ( I hope you understand my English)

SELECT COMPANY, LOCATION, ITEM, COUNT(QUANTITY) AS EXPR1, REASON_CODE, TRAN_UOM
FROM dbo.ICTRANS
WHERE (COMPANY = @CIA) AND(LOCATION = @LOC) AND (TRANS_DATE BETWEEN @F1 AND @F2) AND (REASON_CODE = 'MERM')
GROUP BY COMPANY, LOCATION, ITEM, REASON_CODE, TRAN_UOM
ORDER BY ITEM DESC

Edited by - shifis on 04/11/2003 15:19:48

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-12 : 12:03:12

I see no reference to "SALE" or to "WASTE" anywhere in your query; more info and sample data would definitely be helpful.

However, I *think* I know what you are asking, this may help you out:

declare @t table(ID int, Type varchar(5), Amount money)

insert into @t
select 1, 'SALE', 100 union
select 1, 'WASTE', 50 union
select 2, 'SALE', 150 union
select 2, 'WASTE', 23 union
select 3, 'SALE', 45 union
select 3, 'WASTE', 72

select ID,
sum(Case when type='SALE' then amount else 0 end) as SaleAmount,
sum(Case when type='WASTE' then amount else 0 end) as WasteAmount
from
@t
GROUP BY ID

you are essentially doing a very small, fixed column cross-tab. Let me know if this helps at all. It may not apply to you directly and certainly needs some tweaking to fit your needs, but try to learn from it and apply it to your problem.

Good luck

- Jeff

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-14 : 14:53:14

It works just fine, thanks a lot for your help, this help me a lot.

SELECT ITEM,
sum(Case when REASON_CODE='VTCL' then QUANTITY else 0 end) as SaleAmount,
sum(Case when REASON_CODE='MERM' then QUANTITY else 0 end) as WasteAmount
FROM dbo.ICTRANS
WHERE (COMPANY = 2000) AND (LOCATION = 'TJU01') AND (TRANS_DATE BETWEEN CONVERT(DATETIME, '2002-01-01 00:00:00', 102) AND
CONVERT(DATETIME, '2002-01-31 00:00:00', 102)) AND (REASON_CODE = 'MERM' OR
REASON_CODE = 'VTCL')
GROUP BY ITEM
ORDER BY ITEM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources