SUM and MAX date in the same table

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)

SUM and MAX date in the same table

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2003-06-09 : 18:35:31

Hi!!
I have a table ICTRANS that have the item transaction.
I have to sum the QUANTITY field of eache item , and put the TRANS_DATE where QUANTITY is the MAX.

For example
CREATE TABLE #ICTRASN(ITEM SMALLINT,QUANTITY SMALLINT, TRANS_DATE DATETIME)
INSERT INTO #ICTRASN VALUES(2006,23,'01/01/2003')
INSERT INTO #ICTRASN VALUES(2006,43,'01/02/2003')
INSERT INTO #ICTRASN VALUES(2006,24,'01/02/2003')
INSERT INTO #ICTRASN VALUES(2006,42,'01/04/2003')

SELECT * FROM #ICTRASN
DROP TABLE #ICTRASN

I have to return
Item Sum(QUANTITY) DATE
========================
2006 132 01/02/2003

How can I do that?????

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-09 : 18:50:02

select ITEM ,
sum(QUANTITY),
(select top 1 t2.TRANS_DATE from ICTRANS t2 where t2.ITEM = t.ITEM order by t2.QUANTITY desc)
from ICTRANS t
group by ITEM

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shifis
Posting Yak Master

157 Posts

Posted - 2003-06-09 : 19:21:55

Thanks it work just grade!!
Now I have another doubt, I have this where clause

WHERE (t.COMPANY = 2000) AND (t.TRANS_DATE BETWEEN CONVERT(DATETIME, '2002-01-02 00:00:00', 102) AND CONVERT(DATETIME,
'2002-06-05 00:00:00', 102)) AND (t.LOCATION = 'TJU01') AND (t.DOC_TYPE = 'IS') AND (t.REASON_CODE = 'VTCL') AND (SUBSTRING(t.ITEM, 2, 2) = '57')

I have to put this where in the : (select top 1 t2.TRANS_DATE from ICTRANS t2 where t2.ITEM = t.ITEM order by t2.QUANTITY desc) ????

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-09 : 19:31:49

Yep if you want everything restricted to this.
I also have concerns about

AND t.TRANS_DATE BETWEEN CONVERT(DATETIME, '2002-01-02 00:00:00', 102) AND CONVERT(DATETIME, '2002-06-05 00:00:00', 102)

If TRANS_DATE is a datetime it should be
AND t.TRANS_DATE BETWEEN '20020102' AND '20020605'

format 102 is ambiguous and can cause trouble.
You can put in the convert(datetime if you wish but there is no need for a style as this will always work.
Due to the operator precedence this will be implicitely converted to datetime.

Try formatting like this
WHERE t.COMPANY = 2000
AND t.TRANS_DATE BETWEEN '20020102' AND '20020605'
AND t.LOCATION = 'TJU01'
AND t.DOC_TYPE = 'IS'
AND t.REASON_CODE = 'VTCL'
AND SUBSTRING(t.ITEM, 2, 2) = '57'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources