August 31, 2015 at 8:40 am
Hi Dears
I Have This Structure :
--ActionType=1 UserPay
--ActionType=2 UserBack
Create Table P
(Id int , Pay int,Userid int,ActionType smallInt)
Insert Into P 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),(6,3000,302,2)
Select * from P
Create Table PO
(PId int, Cartid int)
Insert Into PO Values(1,44),(2,44),(3,44),(4,55),(5,55),(6,66),(7,66)
Select * from PO
Select * from P Inner Join Po On P.id=PO.PID
-------------------------------------------------------------------------
And I Need This Result.
-- I Nead This Result :
Row 1 : Userid=300, Cartid=44, pay=3500, Back = 500, Pure = 3000
Row 2 : Userid=301, Cartid=55, pay=950, Back = 0 , Pure = 950
Row 3 : Userid=302, Cartid=66, pay=3000, Back = 3000, Pure = 0
It means I need one record for every : (Userid , Cartid) . MoreOver for every Record sum of pay and sum of back is needed .
Please Guide Me .
Thank You
August 31, 2015 at 8:59 am
Perhaps something like this?
SELECT
Userid,
Cartid,
pay=SUM(CASE WHEN ActionType=1 THEN pay ELSE 0 END),
Back=SUM(CASE WHEN ActionType=2 THEN pay ELSE 0 END),
Pure=SUM(CASE WHEN ActionType=2 THEN -pay ELSE pay END)
FROM P INNER JOIN Po on P.id=PO.PId
GROUP BY Userid, Cartid
Cheers!
August 31, 2015 at 9:09 am
I'd use what Jacob suggested. This an easy way to move rows to columns when they are aggregated based on some value in the rows.
August 31, 2015 at 6:05 pm
Tnx so much
August 31, 2015 at 7:11 pm
Hi again
I have another table
Create table sh
(cartid int , shcode int,regdate date)
Insert Into sh values(44,1,'2015-01-01'),(44,2,'2015-01-02'),(44,3,'2015-01-03'),(55,4,'2015-01-01')
Select * from P Inner Join Po On P.id=PO.PID
Inner Join sh On sh.cartid=PO.Cartid
But I need Shcode with max Date . For example I Need this result
-- I Nead This Result :
Row 1 : Userid=300, Cartid=44, pay=3500, Back = 500, Pure = 3000 , ShCode = 3
Row 2 : Userid=301, Cartid=55, pay=950, Back = 0 , Pure = 950 , SHCode=4
Row 3 : Userid=302, Cartid=66, pay=3000, Back = 3000, Pure = 0 ,SHCode=Null
I Use CTE For sh table. But it get me 90 percent Cost :
,Sh
As
(
SELECT SHCode,Cartid,Row_number() Over(Partition By Cartid order by regdate desc) Rownumsh
FROM sh
)
... And in query
...Inner Join sh on sh.cartid=po.cartid and rownumsh=1
1- How to join this with old query
2- Is there any solution with better performance?
September 1, 2015 at 11:16 pm
Is there any Idea ?
:rolleyes:
September 2, 2015 at 9:07 am
You can use a CTE to get the max date from the table with the SH code along with the cartid and then join that to your other data.
with mycte (cartid, shcode, maxdate)
as
(select cartid, shcode, maxdate = max(maxdate)
from sh
group by cartid, shcode
)
select *
...
September 5, 2015 at 1:07 am
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)
September 5, 2015 at 2:28 am
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
September 5, 2015 at 3:25 am
Yes you are right.
Thank you for your kindness.
It is complete and right;-):-P
September 5, 2015 at 3:35 am
MotivateMan1394 (9/5/2015)
Yes you are right.Thank you for your kindness.
It is complete and right;-):-P
You are very welcome and thank you for the feedback.
😎
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply