MotivateMan1394 (9/5/2015)
No It's not true.If I Group by Cartid,SHCode , I get all records in my result.
I Need sort of Group BY cartid And get max(editDate) >>> at result I should get the shcode according to max(editdate)
Quick solution with all the requirements so far. Suggest that next time you should start with the full and complete requirements, piecemeal'ing them like this is a waste of everyone's time.
😎
/* Safe place */
USE tempdb;
GO
SET NOCOUNT ON;
--ActionType=1 UserPay
--ActionType=2 UserBack
DECLARE @PAYMENT Table
(
Id INT NOT NULL PRIMARY KEY CLUSTERED
,Pay INT NOT NULL
,Userid INT NOT NULL
,ActionType SMALLINT NOT NULL
);
Insert Into @PAYMENT (Id, Pay,Userid,ActionType)
Values ( 1,1000, 300, 1)
,( 2,2500, 300, 1)
,( 3, 500, 300, 2)
,( 4, 750, 301, 1)
,( 5, 200, 301, 1)
,( 6,3000, 302, 1)
,( 7,3000, 302, 2)
;
DECLARE @PAYMENT_ORDER Table
(
PId INT NOT NULL
,Cartid INT NOT NULL
);
INSERT INTO @PAYMENT_ORDER(PId,Cartid)
VALUES ( 1, 44)
,( 2, 44)
,( 3, 44)
,( 4, 55)
,( 5, 55)
,( 6, 66)
,( 7, 66)
;
DECLARE @SALES_CODE TABLE
(
cartid INT NOT NULL
,shcode INT NOT NULL
,regdate DATE NOT NULL
);
INSERT INTO @SALES_CODE(cartid,shcode, regdate)
VALUES ( 44, 1,'2015-01-01')
,( 44, 2,'2015-01-02')
,( 44, 3,'2015-01-03')
,( 55, 4,'2015-01-01')
;
/* CTE to mark the code by the last regdate */
;WITH SALES_CODE_LIST AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY SCC.cartid
ORDER BY SCC.regdate DESC
) AS SCC_RID
,SCC.cartid AS cartid
,SCC.shcode AS shcode
,SCC.regdate AS regdate
FROM @SALES_CODE SCC
)
/* Pick only the lates code from SALES_CODE_LIST */
,LAST_SALES_CODE AS
(
SELECT
SCL.cartid
,SCL.shcode
FROM SALES_CODE_LIST SCL
WHERE SCL.SCC_RID = 1
)
/* Signing and aggregating the values, grouping
by the desired identifiers
*/
SELECT
P.Userid
,PO.Cartid
,SUM(CASE
WHEN P.ActionType = 1 THEN P.Pay
ELSE 0
END) AS Pay
,SUM(CASE
WHEN P.ActionType = 2 THEN P.Pay
ELSE 0
END) AS Back
,SUM(CASE
WHEN P.ActionType = 1 THEN P.Pay
WHEN P.ActionType = 2 THEN (-1) * P.Pay
ELSE 0
END) AS Pure
,MAX(LSC.shcode) AS shcode
FROM @PAYMENT P
INNER JOIN @PAYMENT_ORDER PO
ON P.Id = PO.PId
LEFT OUTER JOIN LAST_SALES_CODE LSC
ON PO.Cartid = LSC.cartid
GROUP BY P.Userid
,PO.Cartid
;
Results
Userid Cartid Pay Back Pure shcode
----------- ----------- ----------- ----------- ----------- -----------
300 44 3500 500 3000 3
301 55 950 0 950 4
302 66 3000 3000 0 NULL