2

I want to get all records according to per day Group by user id and count L point and R point and M point, according to user id and get record according to per day

     CREATE TABLE [dbo].[payoutPoints](
            [id] [int] IDENTITY(1,1) NOT NULL,
            [userid] [int] NOT NULL,
            [payoutBatchId] [int] NOT NULL,
            [points] [int] NOT NULL,
            [pointPosition] [char](1) NOT NULL,
            [forUserId] [int] NOT NULL,
            [insertDate] [datetime] NOT NULL,
         CONSTRAINT [PK_payoutPoints] PRIMARY KEY CLUSTERED 
        (
            [id] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
        ) ON [PRIMARY]
        GO
        SET ANSI_PADDING OFF
        GO
        SET IDENTITY_INSERT [dbo].[payoutPoints] ON
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (256, 4, 5, 1, N'R', 149, CAST(0x0000A5140128A180 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (299, 4, 5, 1, N'L', 182, CAST(0x0000A51700B91CCA AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (300, 149, 5, 1, N'L', 183, CAST(0x0000A51700BC2CA9 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (301, 4, 5, 1, N'R', 183, CAST(0x0000A51700BC2CA9 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (302, 149, 5, 1, N'R', 184, CAST(0x0000A51700C0FD98 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (303, 4, 5, 1, N'R', 184, CAST(0x0000A51700C0FD98 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (304, 149, 5, 0, N'M', 185, CAST(0x0000A51700C1DFB4 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (305, 4, 5, 0, N'R', 185, CAST(0x0000A51700C1DFB4 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (306, 185, 5, 1, N'L', 186, CAST(0x0000A51700C29FAC AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (307, 149, 5, 1, N'M', 186, CAST(0x0000A51700C29FAC AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (308, 4, 5, 1, N'R', 186, CAST(0x0000A51700C29FAC AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (309, 185, 5, 1, N'R', 187, CAST(0x0000A51700C33073 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (310, 149, 5, 1, N'M', 187, CAST(0x0000A51700C33073 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (311, 4, 5, 1, N'R', 187, CAST(0x0000A51700C33073 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (312, 182, 5, 1, N'L', 188, CAST(0x0000A51700C3BB78 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (313, 4, 5, 1, N'L', 188, CAST(0x0000A51700C3BB78 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (314, 188, 5, 0, N'L', 189, CAST(0x0000A51700CA44B9 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (315, 182, 5, 0, N'L', 189, CAST(0x0000A51700CA44B9 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (316, 4, 5, 0, N'L', 189, CAST(0x0000A51700CA44B9 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (317, 189, 5, 0, N'L', 190, CAST(0x0000A51700CADF58 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (318, 188, 5, 0, N'L', 190, CAST(0x0000A51700CADF58 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (319, 182, 5, 0, N'L', 190, CAST(0x0000A51700CADF58 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (320, 4, 5, 0, N'L', 190, CAST(0x0000A51700CADF58 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (321, 187, 5, 1, N'L', 192, CAST(0x0000A517010F1535 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (322, 185, 5, 1, N'R', 192, CAST(0x0000A517010F1547 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (323, 149, 5, 1, N'M', 192, CAST(0x0000A517010F1547 AS DateTime))
        INSERT [dbo].[payoutPoints] ([id], [userid], [payoutBatchId], [points], [pointPosition], [forUserId], [insertDate]) VALUES (324, 4, 5, 1, N'R', 192, CAST(0x0000A517010F1547 AS DateTime))
        SET IDENTITY_INSERT [dbo].[payoutPoints] OFF


    GO
    SET QUOTED_IDENTIFIER ON
    GO
    /*
    exec [test]
    @userId =4
    */

    alter PROC [dbo].[test]

      @userId integer

       AS
    BEGIN

      declare @totalLPoint as int;
      declare @totalRPoint as int;
      declare @totalMPoint as int;
      declare @MainIdPlacement as char;
      declare @ResultLpoint as int;
      declare @ResultRpoint as int;

        select @MainIdPlacement=mainidplacement from binaryUser where pId=@userId and MainIdPlacement!='0'     



        IF OBJECT_ID('tempdb..#temp') IS NOT NULL
        DROP TABLE #temp

         IF OBJECT_ID('tempdb..#result') IS NOT NULL
         DROP TABLE #result

         create table #result
          (
           date varchar(max),
           leftpoint int,
           rightpoint int
          ) 


        create table #temp
        (
        alldate date
         )
        insert into #temp (alldate) (select distinct(CONVERT(varchar(10),insertDate, 111)) as allDate  from payoutPoints )


        declare @date nvarchar(50)
        declare @RowNum int
        declare CustList cursor for
        select * from #temp
        OPEN CustList
        FETCH NEXT FROM CustList 
        INTO @date
        set @RowNum = 0 
        WHILE @@FETCH_STATUS = 0
        BEGIN     

         DECLARE @start_date as datetime
         DECLARE @end_date as datetime     
         set @start_date=  cast(convert(varchar(30), @date, 110) as datetime)     
         set @end_date=  CAST(CONVERT(VARCHAR(10), @date, 110) + ' 23:59:59' AS DATETIME)  


         --set condition

        SELECT @totalMPoint=SUM(points)  FROM payoutPoints   
        WHERE payoutPoints.userid = @userId AND payoutPoints.pointPosition ='M' and payoutPoints.insertDate between @start_date
        and @end_date
        GROUP BY pointPosition    

        SELECT @totalLPoint= SUM(points)  FROM payoutPoints   
        WHERE payoutPoints.userid = @userId AND payoutPoints.pointPosition ='L' 

         and
        payoutPoints.insertDate between @start_date
        and @end_date
        GROUP BY pointPosition 

        SELECT @totalRPoint= SUM(points)  FROM payoutPoints   
        WHERE payoutPoints.userid = @userId AND payoutPoints.pointPosition ='R'     
         and
        payoutPoints.insertDate between @start_date
        and @end_date

        GROUP BY pointPosition 

        --end

        --set condition to sum c leg 

            if(@MainIdPlacement='R')
           begin
        set @ResultRpoint=(@totalMPoint +@totalRPoint)

           end
        else
            begin
         set @ResultRpoint=@totalRPoint        

       end         

            if(@MainIdPlacement='L')
           begin
        set @ResultLpoint=(@totalMPoint +@totalLPoint)

           end
        else
            begin
         set @ResultLpoint=@totalLPoint        

       end   
          --end     



              insert into #result values(@date,@ResultLpoint,@ResultRpoint)

          FETCH NEXT FROM CustList 
            INTO @date

        END
        CLOSE CustList
        DEALLOCATE CustList

       select * from #result



     --get left and right point


    end
4

This definitely doesn't need a CURSOR.

You can do this for one person with just one statement:

SELECT
  CAST(pp.insertDate AS DATE) date,
  SUM(CASE WHEN pp.pointPosition = 'L' THEN pp.points END) AS leftpoint,
  SUM(CASE WHEN pp.pointPosition = 'R' THEN pp.points END) AS rightpoint
FROM dbo.payoutPoints AS pp
WHERE pp.userid = 4
GROUP BY CAST(pp.insertDate AS DATE);

Or build it into a procedure that takes the UserID:

CREATE PROCEDURE #GetPointsForUser
  @UserID INT
AS
  SELECT
    CAST(pp.insertDate AS DATE) date,
    SUM(CASE WHEN pp.pointPosition = 'L' THEN pp.points END) AS leftpoint,
    SUM(CASE WHEN pp.pointPosition = 'R' THEN pp.points END) AS rightpoint,
    SUM(CASE WHEN pp.pointPosition = 'M' THEN pp.points END) AS mpoint
  FROM dbo.payoutPoints AS pp
  WHERE pp.userid = @userId
  GROUP BY CAST(pp.insertDate AS DATE);
GO


EXEC #GetPointsForUser @UserID = 4;

To take into account MainIdPlacement in the binaryUser table, you could use the results returned by the above SELECT statement as a derived table and add mpoint to leftpoint or rightpoint conditionally based on the value of MainIdPlacement, like this:

CREATE PROCEDURE #GetPointsForUser
  @UserID INT
AS
  SELECT
    p.date,
    p.leftpoint  + ISNULL(CASE u.MainIdPlacement WHEN 'L' THEN p.mpoint, 0) AS leftpoint,
    p.rightpoint + ISNULL(CASE u.MainIdPlacement WHEN 'R' THEN p.mpoint, 0) AS rightpoint
  FROM
    (
      SELECT
        CAST(pp.insertDate AS DATE) date,
        SUM(CASE WHEN pp.pointPosition = 'L' THEN pp.points END) AS leftpoint,
        SUM(CASE WHEN pp.pointPosition = 'R' THEN pp.points END) AS rightpoint,
        SUM(CASE WHEN pp.pointPosition = 'M' THEN pp.points END) AS mpoint
      FROM dbo.payoutPoints AS pp
      WHERE pp.userid = @userId
      GROUP BY CAST(pp.insertDate AS DATE)
    ) AS p
    LEFT JOIN binaryUser AS u ON u.pId = @userId
      AND u.MainIdPlacement <> '0'  -- this may be unnecessary
  ;
GO

If pId is the primary key of binaryUser, the u.MainIdPlacement <> '0' predicate is probably unnecessary, as stated in the comment.

  • its not working Msg 102, Level 15, State 1, Procedure #GetPointsForUser, Line 6 Incorrect syntax near ','. Msg 156, Level 15, State 1, Procedure #GetPointsForUser, Line 18 Incorrect syntax near the keyword 'AS'. – Amit Kumar Singh Sep 21 '15 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.