SELECT PS_TKT_HIST.EVENT_NO, Convert(decimal(12,2), PS_TKT_HIST_LIN.QTY_SOLD) as QTY_SOLD, AR_CUST.NAM, PS_TKT_HIST.CUST_NO, 
	PS_TKT_HIST_LIN.ITEM_NO, PS_TKT_HIST_LIN.TKT_NO, PS_TKT_HIST.TKT_DAT, 	PS_TKT_HIST_LIN.LIN_TYP 
FROM PS_TKT_HIST AS PS_TKT_HIST 
INNER JOIN PS_TKT_HIST_LIN AS PS_TKT_HIST_LIN ON PS_TKT_HIST.EVENT_NO = PS_TKT_HIST_LIN.EVENT_NO AND PS_TKT_HIST.STR_ID = PS_TKT_HIST_LIN.STR_ID 
	AND PS_TKT_HIST.STA_ID = PS_TKT_HIST_LIN.STA_ID AND PS_TKT_HIST.TKT_NO = PS_TKT_HIST_LIN.TKT_NO 
	LEFT OUTER JOIN AR_CUST AS AR_CUST ON PS_TKT_HIST.CUST_NO = AR_CUST.CUST_NO 
	LEFT OUTER JOIN IM_ITEM AS IM_ITEM ON PS_TKT_HIST_LIN.ITEM_NO = IM_ITEM.ITEM_NO 
WHERE 
--(PS_TKT_HIST_LIN.LIN_TYP = 's') and (PS_TKT_HIST_LIN.LIN_TYP != 'r')
	(PS_TKT_HIST_LIN.LIN_TYP <> 'U') 
	and (PS_TKT_HIST.TKT_DAT <= dateadd(day, datediff(day, 0, GETDATE())-7, 0))
ORDER BY PS_TKT_HIST.CUST_NO, PS_TKT_HIST_LIN.ITEM_NO, PS_TKT_HIST_LIN.TKT_NO

 (PS_TKT_HIST_LIN.LIN_TYP = 's') 

quote:

---

Originally posted by dinakar

Just add only this condition to the WHERE clause and check if the results you get are same as what you were expecting:

 (PS_TKT_HIST_LIN.LIN_TYP = 's') 

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

---

SELECT		PS_TKT_HIST.EVENT_NO,
		CONVERT(decimal(12, 2), PS_TKT_HIST_LIN.QTY_SOLD) AS QTY_SOLD,
		AR_CUST.NAM,
		PS_TKT_HIST.CUST_NO,
		PS_TKT_HIST_LIN.ITEM_NO,
		PS_TKT_HIST_LIN.TKT_NO,
		PS_TKT_HIST.TKT_DAT,
		PS_TKT_HIST_LIN.LIN_TYP 
FROM		PS_TKT_HIST
INNER JOIN	PS_TKT_HIST_LIN ON PS_TKT_HIST.EVENT_NO = PS_TKT_HIST_LIN.EVENT_NO AND PS_TKT_HIST.STR_ID = PS_TKT_HIST_LIN.STR_ID AND PS_TKT_HIST.STA_ID = PS_TKT_HIST_LIN.STA_ID AND PS_TKT_HIST.TKT_NO = PS_TKT_HIST_LIN.TKT_NO
INNER JOIN	(
			SELECT		ITEM_NO
			FROM		PS_TKT_HIST_LIN
			GROUP BY	ITEM_NO
			HAVING		MAX(LIN_TYP) = 's'
					AND MIN(LIN_TYP) = 's'
		) AS d ON d.ITEM_NO = PS_TKT_HIST_LIN.ITEM_NO
LEFT JOIN	AR_CUST ON PS_TKT_HIST.CUST_NO = AR_CUST.CUST_NO
LEFT JOIN	IM_ITEM ON PS_TKT_HIST_LIN.ITEM_NO = IM_ITEM.ITEM_NO
WHERE		PS_TKT_HIST_LIN.LIN_TYP <> 'U'
		AND PS_TKT_HIST.TKT_DAT <= DATEADD(day, DATEDIFF(day, 7, CURRENT_TIMESTAMP), 0)
ORDER BY	PS_TKT_HIST.CUST_NO,
		PS_TKT_HIST_LIN.ITEM_NO,
		PS_TKT_HIST_LIN.TKT_NO

quote:

---

Originally posted by Peso

OR...
Is this a history table? Where several records with the same PK can be stored?

Example: Order #1 is

1) Created
2) Backordered
3) Sent
4) Paid

This would create 4 records with same order number.

Is this what you mean?

SELECT		PS_TKT_HIST.EVENT_NO,
		CONVERT(decimal(12, 2), PS_TKT_HIST_LIN.QTY_SOLD) AS QTY_SOLD,
		AR_CUST.NAM,
		PS_TKT_HIST.CUST_NO,
		PS_TKT_HIST_LIN.ITEM_NO,
		PS_TKT_HIST_LIN.TKT_NO,
		PS_TKT_HIST.TKT_DAT,
		PS_TKT_HIST_LIN.LIN_TYP 
FROM		PS_TKT_HIST
INNER JOIN	PS_TKT_HIST_LIN ON PS_TKT_HIST.EVENT_NO = PS_TKT_HIST_LIN.EVENT_NO AND PS_TKT_HIST.STR_ID = PS_TKT_HIST_LIN.STR_ID AND PS_TKT_HIST.STA_ID = PS_TKT_HIST_LIN.STA_ID AND PS_TKT_HIST.TKT_NO = PS_TKT_HIST_LIN.TKT_NO
INNER JOIN	(
			SELECT		ITEM_NO
			FROM		PS_TKT_HIST_LIN
			GROUP BY	ITEM_NO
			HAVING		MAX(LIN_TYP) = 's'
					AND MIN(LIN_TYP) = 's'
		) AS d ON d.ITEM_NO = PS_TKT_HIST_LIN.ITEM_NO
LEFT JOIN	AR_CUST ON PS_TKT_HIST.CUST_NO = AR_CUST.CUST_NO
LEFT JOIN	IM_ITEM ON PS_TKT_HIST_LIN.ITEM_NO = IM_ITEM.ITEM_NO
WHERE		PS_TKT_HIST_LIN.LIN_TYP <> 'U'
		AND PS_TKT_HIST.TKT_DAT <= DATEADD(day, DATEDIFF(day, 7, CURRENT_TIMESTAMP), 0)
ORDER BY	PS_TKT_HIST.CUST_NO,
		PS_TKT_HIST_LIN.ITEM_NO,
		PS_TKT_HIST_LIN.TKT_NO

Peter Larsson
Helsingborg, Sweden

---