How to do minus for every row for certain amount

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

General SQL Server Forums

New to SQL Server Programming

How to do minus for every row for certain amount

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-09 : 02:21:21

hello all,

i have query where i wll get the details of points and one Profile ID will have for example 2000 points in this regard application prespective if he look on another profile 20 points will be reduced and count will 1980 and if he like on another profile another 20 points
My output like be this
[CODE]
ProfileID Profilename AllotedPoints ReducedPoints
1011 Mohan 2000 20
1011 Mohan 1980 20
1011 Mohan 1960 20
[/code]

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 02:23:36

do you've a unique valued field in your table? id column or a datetime column to deterine the order?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-09 : 02:41:09

Yes visakh i have date field and need to come by order

P.V.P.MOhan

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-09 : 03:05:13

Suggest me

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 04:15:35

[code]
SELECT ProfileID,Profilename,t.ReducedPoints,
MAX(CASE WHEN Seq=1 THEN AllotedPoints END) - SUM(t1.ReducedPoints) AS AllotedPoints
FROM YourTable t
CROSS APPLY (SELECT ROW_NUMBER() OVER (ORDER BY DateField) AS Seq,AllotedPoints, ReducedPoints
FROM Table
WHERE ProfileID = t.ProfileID
AND DateField <= t.DateField
)t1
GROUP BY ProfileID,Profilename,t.ReducedPoints,t.DateField
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-09 : 04:41:28

Hello visakh here to do minus i dont have a column of reduced points, IN your above query i have modified and used to my present requirement my query is look like this in that one table
Cust_ExpressIntrestLintStatus will look for profileID and Cust_CheckoutLimits gives you point and i need to reduce 20 points how i can reduce the 20 points dynamically

my query looks like this :


CREATE TABLE #REDUCEDPOINTS(ID INT,ReducedPoints INT)
      select 
		    (L.ProfileID ) As  ProfileID,	
		    (ccl.Cust_ID ) AS Cust_ID,
		   B.FirstName + B.LastName  As Name ,
		   'CustomService' As TypeOfService ,
			CONVERT(DATE,CCL.OfflineMembershipExpiryDate,101) As OffLineExpiryDate,
			CONVERT(DATE,CCL.OnlineMembershipExpiryDate,101) AS OnLineExpiryDate,
			MAX(CASE WHEN SEQ = 1 THEN CCL.Max_Offline_Allowed END )- SUM(ReducedPoints)AS TotalOfflinePoints,
			20 As ReducedPoints,
			CCS.ToCustID
	
				FROM Cust_BasicInfo B
			INNER JOIN Cust_Login L
				ON B.Cust_ID = L.Cust_ID
			INNER JOIN Cust_Details D
				ON D.Cust_ID = B.Cust_ID
			INNER JOIN Cust_CheckoutLimits CCL
				ON B.Cust_ID = CCL.Cust_ID
			INNER JOIN Cust_ExpressIntrestLintStatus CCS
			    ON CCL.Cust_ID = CCS.Fromcust_ID
			INNER JOIN #REDUCEDPOINTS RP
				ON CCL.Cust_ID = RP.ID
			    CROSS APPLY  (SELECT ROW_NUMBER() OVER (ORDER BY CreatedDate) AS Seq,CCL.Max_Offline_Allowed
             FROM Cust_ExpressIntrestLintStatus
             WHERE ProfileID  = L.ProfileID  
             AND CreatedDate <= CCS.CreatedDate
            )t1
            GROUP BY L.ProfileID,ccl.Cust_ID,B.FirstName,B.LastName ,CCL.OfflineMembershipExpiryDate,
            CCL.OnlineMembershipExpiryDate,CCS.ToCustID

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 04:51:29

so is the reduced points a user input? how do you determine its value otherwise?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-09 : 04:54:45

--try this

MAX(CASE WHEN SEQ = 1 THEN CCL.Max_Offline_Allowed END )- SUM(20)AS TotalOfflinePoints,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 05:03:18

quote:
Originally posted by bandi

--try this

MAX(CASE WHEN SEQ = 1 THEN CCL.Max_Offline_Allowed END )- SUM(20)AS TotalOfflinePoints,

SUM(20) has no meaning

then it should atleast be

MAX(CASE WHEN SEQ = 1 THEN CCL.Max_Offline_Allowed END )-(20 * MAX(Seq) )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-09 : 05:20:25

while executing the query i am getting result like this one


DECLARE @Point INT 
Set @Point =( select PointsToBeCounted from Admin_Points where Admin_Points_ID = 1)
      select 
		    (L.ProfileID ) As  ProfileID,	
		    (ccl.Cust_ID ) AS Cust_ID,
		   B.FirstName + B.LastName  As Name ,
		   'CustomService' As TypeOfService ,
			CONVERT(DATE,CCL.OfflineMembershipExpiryDate,101) As OffLineExpiryDate,
			CONVERT(DATE,CCL.OnlineMembershipExpiryDate,101) AS OnLineExpiryDate,
			MAX(CASE WHEN SEQ = 1 THEN CCL.Max_Offline_Allowed END )- (@Point)AS TotalOfflinePoints,
			@Point As ReducedPoint,
			CCS.ToCustID
	
				FROM Cust_BasicInfo B
			INNER JOIN Cust_Login L
				ON B.Cust_ID = L.Cust_ID
			INNER JOIN Cust_Details D
				ON D.Cust_ID = B.Cust_ID
			INNER JOIN Cust_CheckoutLimits CCL
				ON B.Cust_ID = CCL.Cust_ID
			INNER JOIN Cust_ExpressIntrestLintStatus CCS
			    ON CCL.Cust_ID = CCS.Fromcust_ID
		

			    CROSS APPLY  (SELECT ROW_NUMBER() OVER (ORDER BY CreatedDate) AS Seq,CCL.Max_Offline_Allowed
             FROM Cust_ExpressIntrestLintStatus CCLS
              
            )t1
            GROUP BY L.ProfileID,ccl.Cust_ID,B.FirstName,B.LastName ,CCL.OfflineMembershipExpiryDate,
            CCL.OnlineMembershipExpiryDate,CCS.ToCustID

[CODE]
ProfileID Cust_ID Name TypeOfService OffLineExpiryDate OnLineExpiryDate TotalOfflinePoints ReducedPoint ToCustID
010000026 2 allaKishore CustomService 2013-05-29 NULL 6980 20 3
010000026 2 allaKishore CustomService 2013-05-29 NULL 6980 20 6
010000026 2 allaKishore CustomService 2013-05-29 NULL 6980 20 72
010000026 2 allaKishore CustomService 2013-05-29 NULL 6980 20 73
010000026 2 allaKishore CustomService 2013-05-29 NULL 6980 20 74
010000026 2 allaKishore CustomService 2013-05-29 NULL 6980 20 79
[/CODE]

but i need to get results like this one :

[CODE]
ProfileID Cust_ID Name TypeOfService OffLineExpiryDate OnLineExpiryDate TotalOfflinePoints ReducedPoint ToCustID
010000026 2 allaKishore CustomService 2013-05-29 NULL 7000 20 3
010000026 2 allaKishore CustomService 2013-05-29 NULL 6980 20 6
010000026 2 allaKishore CustomService 2013-05-29 NULL 6960 20 72
010000026 2 allaKishore CustomService 2013-05-29 NULL 6940 20 73
010000026 2 allaKishore CustomService 2013-05-29 NULL 6920 20 74
010000026 2 allaKishore CustomService 2013-05-29 NULL 6900 20 79
[/CODE]

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 05:22:55

where did the conditions inside subquery go? your last posted suggestion had them



DECLARE @Point INT 
Set @Point =( select PointsToBeCounted from Admin_Points where Admin_Points_ID = 1)

      select 
		    (L.ProfileID ) As  ProfileID,	
		    (ccl.Cust_ID ) AS Cust_ID,
		   B.FirstName + B.LastName  As Name ,
		   'CustomService' As TypeOfService ,
			CONVERT(DATE,CCL.OfflineMembershipExpiryDate,101) As OffLineExpiryDate,
			CONVERT(DATE,CCL.OnlineMembershipExpiryDate,101) AS OnLineExpiryDate,
			MAX(CASE WHEN SEQ = 1 THEN CCL.Max_Offline_Allowed END )- (@Point)AS TotalOfflinePoints,
			@Point As ReducedPoint,
			CCS.ToCustID
	
				FROM Cust_BasicInfo B
			INNER JOIN Cust_Login L
				ON B.Cust_ID = L.Cust_ID
			INNER JOIN Cust_Details D
				ON D.Cust_ID = B.Cust_ID
			INNER JOIN Cust_CheckoutLimits CCL
				ON B.Cust_ID = CCL.Cust_ID
			INNER JOIN Cust_ExpressIntrestLintStatus CCS
			    ON CCL.Cust_ID = CCS.Fromcust_ID
		

			    CROSS APPLY  (SELECT ROW_NUMBER() OVER (ORDER BY CreatedDate) AS Seq,CCL.Max_Offline_Allowed
             FROM Cust_ExpressIntrestLintStatus CCLS
              WHERE ProfileID  = L.ProfileID  
             AND CreatedDate <= CCS.CreatedDate
            )t1
            GROUP BY L.ProfileID,ccl.Cust_ID,B.FirstName,B.LastName ,CCL.OfflineMembershipExpiryDate,
            CCL.OnlineMembershipExpiryDate,CCS.ToCustID

Also part in blue will work fine only if select returns a single row

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-09 : 05:37:09

I am getting the same output as i posted above and not getting any expected out put like this

i need to get like this


ProfileID	Cust_ID	Name	TypeOfService	OffLineExpiryDate	OnLineExpiryDate	TotalOfflinePoints	ReducedPoint	ToCustID
010000026	2	allaKishore	CustomService	2013-05-29	NULL	7000	20	3
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6980	20	6
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6960	20	72
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6940	20	73
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6920	20	74
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6900	20	79

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 05:41:46

why does your group by have lot of fields? its not same as what i suggested. see my group by clause

GROUP BY ProfileID,Profilename,t.ReducedPoints,t.DateField

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-09 : 05:51:05

offcourse thats an example but in query scenario i need to give all the columns in group by as i gave in my query then only query is executing and giving out put like this one :


ProfileID	Cust_ID	Name	TypeOfService	OffLineExpiryDate	OnLineExpiryDate	TotalOfflinePoints	ReducedPoint	ToCustID
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6980	20	3
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6980	20	6
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6980	20	72
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6980	20	73
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6980	20	74
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6980	20	79

but i need to get output like the above posted

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 05:54:47

then it wont work as your date field (OfflineMembershipExpiryDate) will have different values for them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-09 : 05:57:47

there is no importance for OfflineMembershipExpiryDate just now i commented that field reexecuted the query it is giving the same result set

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 06:04:48

what about the other fields? do any of them have different values with same profile group?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-09 : 07:10:32

yeah i already applied with 'ToCustId' but it is also showing same result set or else is there any way to give dynamic ID and do the required output suggest me

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 07:31:21

is this any better?


DECLARE @Point INT 
Set @Point =( select PointsToBeCounted from Admin_Points where Admin_Points_ID = 1)

      select 
		    (L.ProfileID ) As  ProfileID,	
		    (ccl.Cust_ID ) AS Cust_ID,
		   B.FirstName + B.LastName  As Name ,
		   'CustomService' As TypeOfService ,
			CONVERT(DATE,CCL.OfflineMembershipExpiryDate,101) As OffLineExpiryDate,
			CONVERT(DATE,CCL.OnlineMembershipExpiryDate,101) AS OnLineExpiryDate,
			MAX(CASE WHEN SEQ = 1 THEN CCL.Max_Offline_Allowed END )- (@Point* MAX(Seq))AS TotalOfflinePoints,
			@Point As ReducedPoint,
			CCS.ToCustID
	
				FROM Cust_BasicInfo B
			INNER JOIN Cust_Login L
				ON B.Cust_ID = L.Cust_ID
			INNER JOIN Cust_Details D
				ON D.Cust_ID = B.Cust_ID
			INNER JOIN Cust_CheckoutLimits CCL
				ON B.Cust_ID = CCL.Cust_ID
			INNER JOIN Cust_ExpressIntrestLintStatus CCS
			    ON CCL.Cust_ID = CCS.Fromcust_ID
		

			    CROSS APPLY  (SELECT ROW_NUMBER() OVER (ORDER BY CreatedDate) AS Seq,CCL.Max_Offline_Allowed
             FROM Cust_ExpressIntrestLintStatus CCLS
              WHERE ProfileID  = L.ProfileID  
             AND CreatedDate <= CCS.CreatedDate
            )t1
            GROUP BY L.ProfileID,ccl.Cust_ID,B.FirstName,B.LastName ,CONVERT(DATE,CCL.OfflineMembershipExpiryDate,101),
            CONVERT(DATE,CCL.OnlineMembershipExpiryDate,101),CCS.ToCustID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-09 : 08:09:39

hi visakh above query giving out put like this one


ProfileID	Cust_ID	Name	TypeOfService	OffLineExpiryDate	OnLineExpiryDate	TotalOfflinePoints	ReducedPoint	ToCustID
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6840	20	3
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6800	20	6
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6940	20	72
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6800	20	73
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6920	20	74
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6940	20	79

but i need to get like this :


ProfileID	Cust_ID	Name	TypeOfService	OffLineExpiryDate	OnLineExpiryDate	TotalOfflinePoints	ReducedPoint	ToCustID
010000026	2	allaKishore	CustomService	2013-05-29	NULL	7000	20	3
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6980	20	6
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6960	20	72
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6940	20	73
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6920	20	74
010000026	2	allaKishore	CustomService	2013-05-29	NULL	6900	20	79

P.V.P.MOhan

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-09 : 08:56:55

Suggest me i am not able to move a bit

P.V.P.MOhan

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources