MAX without MAX - 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)

MAX without MAX

Author

Topic

chexwebson
Starting Member

17 Posts

Posted - 2004-09-08 : 15:06:49

8.0 compatibility is great with tons of advantages, however I have 1 db that the vendor claims they won't support at 8.0, only at 6.5. Thus I am trying to find the customerkeys where the latest transaction date is < getdate()-365 and > getdate()-30. Basically I need to find only the custkey's where the MAX trandate (which there can be multiples during the time frame) fall within that 11 month range. With MAX this is easy, however is there a way to do this without using MAX and no cursors. This results are needed in Reporting Services...

Thanks for reading and for the help.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-08 : 15:10:26

how about some sample data and exactly wha tyou are trying to return. i personally cannot make any sense out of your question, but that's probably just me.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 15:15:44

Why can't you use MAX in 65 compatibility mode? MAX is a valid aggregate function in SQL Server 6.5.

Tara

chexwebson
Starting Member

17 Posts

Posted - 2004-09-08 : 15:34:30

I haven't had enough caffeine. You are right Tara. Thanks for that, seems I had a problem with my group by. However, this still leaves an issue. It will select the MAX trandate from within the date range, however if there is a trandate within the GETDATE()-30 range, it still includes it in the selection. So should I leave off the bottom end of the WHERE range and filter for this within reporting services after I have the majority of the dataset correct?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 15:37:17

We need to see sample data and your tables.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-08 : 16:40:34

stil not sure what you are aksing, but if you need to filter your rows based on an aggregate calculation, you need to use a HAVING clause.

select Customer, SUM(Amount) as TotalSales
from Sales
group by Customer
HAVING SUM(Amount) > 1000

not sure if this helps you, but thought I'd throw it out there.

- Jeff

chexwebson
Starting Member

17 Posts

Posted - 2004-09-08 : 17:33:27

So I am preparing my data to send up to you guys, and got the table structures, but was having a problem creating the insert scripts...and then I read Jeff's reply...Here is the final result

SELECT (tsoSalesOrder.CustKey), MAX(tsoSalesOrder.TranDate)
FROM tsoSalesOrder INNER JOIN
tarCustomer ON tsoSalesOrder.CustKey = tarCustomer.CustKey INNER JOIN
tciAddress ON tarCustomer.PrimaryAddrKey = tciAddress.AddrKey INNER JOIN
tciContact ON tarCustomer.PrimaryCntctKey = tciContact.CntctKey
WHERE (tsoSalesOrder.TranDate > GETDATE() - 365) AND (tsoSalesOrder.CompanyID = 'XXX')
GROUP BY tsoSalesOrder.CustKey
HAVING MAX(tsoSalesOrder.TranDate) < GETDATE()-30

Tested, verified the returned data, and it works wonderfully. Thanks Tara and Jeff for your help.

Thanks T for the other links, however either one of you have any good tips on the Creating insert scripts from existing data?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 17:36:23

We only would need about 5-10 rows per table, so it would be fastest to just manually write those out. But you can use Vyas' script:

http://vyaskn.tripod.com/code/generate_inserts.txt

Tara

chexwebson
Starting Member

17 Posts

Posted - 2004-09-08 : 17:40:18

I would have, but considering that there were 4 tables, with between 20-45 columns per table, with 5 rows of handwritten, would have taken quite a while... Just trying to get the data to you all faster, however you were able to solve the problem without it, and I will have this for next time, thanks for the wonderful help once again. Next time I will be able to provide better data to assist in finding a solution.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 17:42:55

We would only need to see the columns that are relevant to your problem. So you could delete the non-relevant columns from the CREATE TABLE statements and only write INSERT INTO statements for the columns provided.

Tara

Kristen
Test

22859 Posts

Posted - 2004-09-09 : 23:20:56

and you know what? I've done that on a couple of occassions, and by the time I had built the whole message ready to post I had discovered what the problem was!

Perhaps SQL Team could sell cardboard cutouts of Tara that harrassed SQL developers could discuss their problems with. Soltuions would be readily forthcoming, I wager ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources