How to Put a COUNT in this? - 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

SQL Server Development (2000)

How to Put a COUNT in this?

Author

Topic

soonerX
Starting Member

16 Posts

Posted - 2007-06-06 : 16:59:19

Where and how would I use this following and just count the records that this shows.
SELECT h.EVENT_NO,
CONVERT(decimal(12, 2), l.QTY_SOLD) AS QTY_SOLD,
AR_CUST.NAM,
h.CUST_NO,
l.ITEM_NO,
l.TKT_NO,
h.TKT_DAT,
l.LIN_TYP
FROM PS_TKT_HIST AS h
INNER JOIN PS_TKT_HIST_LIN AS l ON l.EVENT_NO = h.EVENT_NO AND l.STR_ID = h.STR_ID AND l.STA_ID = h.STA_ID AND l.TKT_NO = h.TKT_NO
INNER JOIN (
SELECT ITEM_NO,
MAX(TKT_NO) AS TKT_NO
FROM PS_TKT_HIST_LIN
GROUP BY ITEM_NO
) AS d ON d.ITEM_NO = l.ITEM_NO AND d.TKT_NO = l.TKT_NO
LEFT JOIN AR_CUST AS c ON c.CUST_NO = h.CUST_NO
LEFT JOIN IM_ITEM AS i ON i.ITEM_NO = l.ITEM_NO
WHERE l.LIN_TYP <> 'U'
AND h.TKT_DAT > DATEADD(day, DATEDIFF(day, 3, CURRENT_TIMESTAMP), 0), CURRENT_TIMESTAMP), 0)
ORDER BY h.CUST_NO,
l.ITEM_NO,
l.TKT_NO

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 17:21:11

[code]
select count(*) from ( SELECT h.EVENT_NO,
CONVERT(decimal(12, 2), l.QTY_SOLD) AS QTY_SOLD,
AR_CUST.NAM,
h.CUST_NO,
l.ITEM_NO,
l.TKT_NO,
h.TKT_DAT,
l.LIN_TYP
FROM PS_TKT_HIST AS h
INNER JOIN PS_TKT_HIST_LIN AS l ON l.EVENT_NO = h.EVENT_NO AND l.STR_ID = h.STR_ID AND l.STA_ID = h.STA_ID AND l.TKT_NO = h.TKT_NO
INNER JOIN (
SELECT ITEM_NO,
MAX(TKT_NO) AS TKT_NO
FROM PS_TKT_HIST_LIN
GROUP BY ITEM_NO
) AS d ON d.ITEM_NO = l.ITEM_NO AND d.TKT_NO = l.TKT_NO
LEFT JOIN AR_CUST AS c ON c.CUST_NO = h.CUST_NO
LEFT JOIN IM_ITEM AS i ON i.ITEM_NO = l.ITEM_NO
WHERE l.LIN_TYP <> 'U'
AND h.TKT_DAT > DATEADD(day, DATEDIFF(day, 3, CURRENT_TIMESTAMP), 0), CURRENT_TIMESTAMP), 0)
ORDER BY h.CUST_NO,
l.ITEM_NO,
l.TKT_NO ) Z
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources