SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to do minus for every row for certain amount
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  02:21:21  Show Profile  Reply with Quote
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 


P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/09/2013 :  02:23:36  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  02:41:09  Show Profile  Reply with Quote
Yes visakh i have date field and need to come by order

P.V.P.MOhan
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  03:05:13  Show Profile  Reply with Quote
Suggest me

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/09/2013 :  04:15:35  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  04:41:28  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/09/2013 :  04:51:29  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/09/2013 :  04:54:45  Show Profile  Reply with Quote
--try this

MAX(CASE WHEN SEQ = 1 THEN CCL.Max_Offline_Allowed END )- SUM(20)AS TotalOfflinePoints,
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/09/2013 :  05:03:18  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  05:20:25  Show Profile  Reply with Quote
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



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 results like this one :


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

Edited by - mohan123 on 04/09/2013 05:35:35
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/09/2013 :  05:22:55  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  05:37:09  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/09/2013 :  05:41:46  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  05:51:05  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/09/2013 :  05:54:47  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  05:57:47  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/09/2013 :  06:04:48  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  07:10:32  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/09/2013 :  07:31:21  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  08:09:39  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/09/2013 :  08:56:55  Show Profile  Reply with Quote
Suggest me i am not able to move a bit

P.V.P.MOhan
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000