Row Count?? - 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)

Row Count??

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2004-12-01 : 02:52:47

I dont even know if what I am trying to do is possible, but I have sure spent the entire night trying to find out.

I have tables like this:
TransHeader
ID
TicketNumber
TransDate
CoverTotal

TransDetail
ID
TransHeaderID
Amount

My current statement looks like this:

SELECT th.CoverTotal,
th.TransDate,
th.TicketNumber,
td.Amount
FROM TransHeader th
INNER JOIN TransDetail td
ON th.ID = td.TransHeaderID

However, the cover total is the same. So for each detail record I have, I get the cover total that many times. (Which I know is how I would normally want it.) But in this particular case, I only want the cover total for the first record.

I dont know if there is a way to do a case statement or a running row count or something that will allow me to say
Case TransRowRecordNumber When 1 then th.CoverTotal Else 0 End

Obviously TransRowRecordNumber is completely made up.. I dont know if there is a way to do this or get this number. Or maybe if there is a way to compare the ID's somehow so that once we get to a new ID, we use the CoverTotal, else 0.

Again, like I said, this may not even be possible, but if anyone could tell me one way or another, it is you guys. You have saved me more then once!

Thanks so much,
JAdauto

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 03:55:54

Is there some reason why you do NOT want:


SELECT th.CoverTotal,
       th.TransDate, 
       th.TicketNumber,
       SUM(td.Amount) AS Amount
FROM TransHeader th
     INNER JOIN TransDetail td
          ON th.ID = td.TransHeaderID
GROUP BY th.CoverTotal, th.TransDate, th.TicketNumber

Kristen

Andraax
Aged Yak Warrior

790 Posts

Posted - 2004-12-01 : 04:33:51

Either go with Kristens suggestion, or if you want all the rows but only CoverTotal for the first ID in TransDetail, then do this (everything is possible with subqueries):

SELECT case when td.id=(select min(id) from TransDetail where TransHeaderID=th.ID) then th.CoverTotal else 0 end as CoverTotal,
th.TransDate,
th.TicketNumber,
td.Amount
FROM TransHeader th
INNER JOIN TransDetail td
ON th.ID = td.TransHeaderID

JAdauto
Posting Yak Master

160 Posts

Posted - 2004-12-01 : 09:07:41

I cant use Kristen's suggestion because I need the detail. It normally would not matter that I am getting all the header info multiple times, but in the case of this cover total, it makes the cover total for one ticket appear to be larger then it is if you have more then on detail.

Andreax solution worked perfectly. Again, you guys have come through for me! I really appreciate it.

JAdauto

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources