none
Help with Query about score calculation ... RRS feed

  • Question

  • Hi everybody.

    I have a simple database with 4 Tables.
    This is a simplified version for test purpose and I cannot modify DB structure.

    Rules are simple : Users bring objects in the system and obtain points. The rules about points are stored in the Object Type Table. Every Object Type defines how many pieces a User must bring in the year ( calendar year ) to obtain points for each Object brought in each Type.

    These are Tables and Test Data :

    - Users Table

    /* Users */
    CREATE TABLE Users
    (
    	UserCode NVARCHAR(50) PRIMARY KEY NOT NULL,
    	Name NVARCHAR(50) NOT NULL
    )
    /* Some Test Users */
    INSERT INTO Users (UserCode, Name) VALUES ('USR001', 'Billy')
    INSERT INTO Users (UserCode, Name) VALUES ('USR002', 'Frank')

    ObjectTypes Table

    /* ObjectTypes */
    CREATE TABLE ObjectTypes
    (
    	ID INT PRIMARY KEY NOT NULL,
    	Description NVARCHAR(50) NOT NULL,
    	PointsPerPiece INT NOT NULL,
    	MinPiecesPerYear INT NOT NULL,
    	MaxPiecesPerYear INT NOT NULL
    )
    /* Some Test Objects Types */
    INSERT INTO ObjectTypes (ID, Description, PointsPerPiece, MinPiecesPerYear, MaxPiecesPerYear) VALUES (1, 'Apples', 3, 1, 10)
    INSERT INTO ObjectTypes (ID, Description, PointsPerPiece, MinPiecesPerYear, MaxPiecesPerYear) VALUES (2, 'Pears', 2, 2, 15)
    INSERT INTO ObjectTypes (ID, Description, PointsPerPiece, MinPiecesPerYear, MaxPiecesPerYear) VALUES (3, 'Peaches', 1, 3, 20)

    Objects Table / Operations Table

    /* Objects */
    CREATE TABLE Objects
    (
    	Code NVARCHAR(50) PRIMARY KEY NOT NULL,
    	IdObjectType INT NOT NULL
    )
    
    /* Operations */
    CREATE TABLE Operations
    (
    	ObjectCode NVARCHAR(50) NOT NULL,
    	OperationTime NVARCHAR(14) NOT NULL,
    	UserCode NVARCHAR(50) NOT NULL,
    	OperationType TINYINT NOT NULL,
    	PRIMARY KEY (ObjectCode, OperationTime)
    )
    
    /* Some Test Objects / Operations */
    /* USR001 */
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ001', 1)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ001', '20191021000000', 'USR001', 1)
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ002', 2)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ002', '20191021000001', 'USR001', 1)
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ003', 2)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ003', '20191021000002', 'USR001', 1)
    
    /* USR002 */
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ004', 2)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ004', '20191021000003', 'USR002', 1)
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ005', 3)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ005', '20191021000004', 'USR002', 1)
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ006', 3)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ006', '20191021000005', 'USR002', 1)
    
    

    1.
    User USR001 ( Billy ) in year 2019 has brought 1x Apple + 2x Pears.
    Apples has PointsPerPiece = 3 and MinPiecesPerYear = 1, so --> 3 Points collected.
    Pears has PointsPerPiece = 2 and MinPiecesPerYear = 2, so --> 4 Points collected.
    Total Points = 7.

    2. 
    User USR002 ( Frank ) in year 2019 has brought 1x Pear + 2x Peaches.
    Pears has PointsPerPiece = 2 and MinPiecesPerYear = 2, so --> 0 Points collected, because User hasn't met the minimum amount of pieces yet in order to collect points.
    Peaches has PointsPerPiece = 1 and MinPiecesPerYear = 3, so --> 0 Points collected, because User hasn't met the minimum amount of pieces yet in order to collect points.
    Total Points = 0.

    In the same way, a User that brings N objects with N > MaxPiecesPerYear ( in that calendar year ) desn't collect points for the amount of pieces that exceeds the MaxPiecesPerYear limit.

    The Query must calculate the Total Score for each User, so I'm here to ask for possible ways.

    Thanks to anyone able to help.

    Monday, October 21, 2019 2:35 PM

Answers

  • Hi

    Hope the below query fulfills your requirement.

    WITH Fact AS (
    SELECT LEFT(F.OperationTime,4) Year,U.Name,T.Description,COUNT(1) Count FROM 
    Operations F
    INNER JOIN Users U
    ON F.UserCode = U.UserCode
    INNER JOIN Objects O
    ON F.ObjectCode = O.Code
    INNER JOIN ObjectTypes T
    ON O.IdObjectType = T.ID
    GROUP BY LEFT(F.OperationTime,4),U.Name,T.Description)
    
    SELECT 
    	F.Name
    	,SUM(CASE WHEN F.Count >= T.MinPiecesPerYear THEN F.Count*T.PointsPerPiece ELSE 0 END) TotalPoints
    FROM Fact F
    INNER JOIN ObjectTypes T
    ON F.Description = T.Description
    GROUP BY F.Name

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by MarcoGG Tuesday, October 22, 2019 8:39 AM
    Monday, October 21, 2019 2:57 PM
  • Would you like this one ?

    ;with cte as(
    SELECT F.UserCode, left(F.OperationTime,4) [Year] ,
    case when T.MinPiecesPerYear>count(*)over(partition by F.UserCode,T.id) or T.MaxPiecesPerYear<count(*)over(partition by F.UserCode,T.id)
    then 0 else O.Points end  [Points]
    FROM  Operations F
    INNER JOIN Users U
    ON F.UserCode = U.UserCode
    INNER JOIN Objects O
    ON F.ObjectCode = O.Code
    INNER JOIN ObjectTypes T
    ON O.IdObjectType = T.ID )
    select UserCode,Year,sum(Points) [Total Points]
    from cte 
    group by UserCode,Year
    /*
    UserCode                                           Year Total Points
    -------------------------------------------------- ---- ------------
    USR001                                             2019 6
    USR002                                             2019 0
    */
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by MarcoGG Tuesday, October 22, 2019 8:39 AM
    Tuesday, October 22, 2019 7:58 AM

All replies

  • As it seems to be homework:

    1) You need to query (aggregate) the objects per type and user and year.

    2) Join in the types table to determine whether you need to output a value and do the actual calculus.

    3) Aggregate the result over user and year.

    You can use sub-queries or a CTE to structure your query.

    p.s. I hope Joe will not see your schema...

    Monday, October 21, 2019 2:53 PM
  • Hi

    Hope the below query fulfills your requirement.

    WITH Fact AS (
    SELECT LEFT(F.OperationTime,4) Year,U.Name,T.Description,COUNT(1) Count FROM 
    Operations F
    INNER JOIN Users U
    ON F.UserCode = U.UserCode
    INNER JOIN Objects O
    ON F.ObjectCode = O.Code
    INNER JOIN ObjectTypes T
    ON O.IdObjectType = T.ID
    GROUP BY LEFT(F.OperationTime,4),U.Name,T.Description)
    
    SELECT 
    	F.Name
    	,SUM(CASE WHEN F.Count >= T.MinPiecesPerYear THEN F.Count*T.PointsPerPiece ELSE 0 END) TotalPoints
    FROM Fact F
    INNER JOIN ObjectTypes T
    ON F.Description = T.Description
    GROUP BY F.Name

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    • Marked as answer by MarcoGG Tuesday, October 22, 2019 8:39 AM
    Monday, October 21, 2019 2:57 PM
  • We normally don't post code for homework assignments..

    p.s. the ObjectTypes JOIN in the first CTE is not necessary and you missed the max limit.

    Monday, October 21, 2019 2:58 PM
  • /* Users */
    CREATE TABLE Users
    (
    	UserCode NVARCHAR(50) PRIMARY KEY NOT NULL,
    	Name NVARCHAR(50) NOT NULL
    )
    /* Some Test Users */
    INSERT INTO Users (UserCode, Name) VALUES ('USR001', 'Billy')
    INSERT INTO Users (UserCode, Name) VALUES ('USR002', 'Frank');
    /* ObjectTypes */
    CREATE TABLE ObjectTypes
    (
    	ID INT PRIMARY KEY NOT NULL,
    	Description NVARCHAR(50) NOT NULL,
    	PointsPerPiece INT NOT NULL,
    	MinPiecesPerYear INT NOT NULL,
    	MaxPiecesPerYear INT NOT NULL
    )
    /* Some Test Objects Types */
    INSERT INTO ObjectTypes (ID, Description, PointsPerPiece, MinPiecesPerYear, MaxPiecesPerYear) VALUES (1, 'Apples', 3, 1, 10)
    INSERT INTO ObjectTypes (ID, Description, PointsPerPiece, MinPiecesPerYear, MaxPiecesPerYear) VALUES (2, 'Pears', 2, 2, 15)
    INSERT INTO ObjectTypes (ID, Description, PointsPerPiece, MinPiecesPerYear, MaxPiecesPerYear) VALUES (3, 'Peaches', 1, 3, 20);
    
    
    
    /* Objects */
    CREATE TABLE Objects
    (
    	Code NVARCHAR(50) PRIMARY KEY NOT NULL,
    	IdObjectType INT NOT NULL
    )
    
    /* Operations */
    CREATE TABLE Operations
    (
    	ObjectCode NVARCHAR(50) NOT NULL,
    	OperationTime NVARCHAR(14) NOT NULL,
    	UserCode NVARCHAR(50) NOT NULL,
    	OperationType TINYINT NOT NULL,
    	PRIMARY KEY (ObjectCode, OperationTime)
    )
    
    /* Some Test Objects / Operations */
    /* USR001 */
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ001', 1)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ001', '20191021000000', 'USR001', 1)
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ002', 2)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ002', '20191021000001', 'USR001', 1)
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ003', 2)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ003', '20191021000002', 'USR001', 1)
    
    /* USR002 */
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ004', 2)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ004', '20191021000003', 'USR002', 1)
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ005', 3)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ005', '20191021000004', 'USR002', 1)
    INSERT INTO Objects (Code, IdObjectType) VALUES ('OBJ006', 3)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ006', '20191021000005', 'USR002', 1)
    ;with rawAggregate as (
    select Description,
    Case when Count(*)<MinPiecesPerYear then 0 
    else 
    Case when Count(*) <=MaxPiecesPerYear then Count(*)  else MaxPiecesPerYear end 
    End * PointsPerPiece as Points,
    PointsPerPiece, MinPiecesPerYear, MaxPiecesPerYear
    ,Name,
    Year(try_cast(Left(OperationTime,8) as date)) [Year]
      from Objects ob 
    join Operations op on ob.Code=op.objectCode
    join ObjectTypes obj on obj.ID=ob.IdObjectType
    join Users u on u.UserCode =op.UserCode 
    Group by   Name,Description 
    ,PointsPerPiece, MinPiecesPerYear, MaxPiecesPerYear
    ,Year(try_cast(Left(OperationTime,8) as date))
    )
    
    
    Select Name	
    ,[Year]
    ,Sum(Points) TotalPoints 
    From rawAggregate
     Group by Name,[Year]
    
    drop table Objects,Operations, Users,ObjectTypes;

    Monday, October 21, 2019 3:12 PM
    Moderator
  • Hi

    Hope the below query fulfills your requirement.

    WITH Fact AS (
    SELECT LEFT(F.OperationTime,4) Year,U.Name,T.Description,COUNT(1) Count FROM 
    Operations F
    INNER JOIN Users U
    ON F.UserCode = U.UserCode
    INNER JOIN Objects O
    ON F.ObjectCode = O.Code
    INNER JOIN ObjectTypes T
    ON O.IdObjectType = T.ID
    GROUP BY LEFT(F.OperationTime,4),U.Name,T.Description)
    
    SELECT 
    	F.Name
    	,SUM(CASE WHEN F.Count >= T.MinPiecesPerYear THEN F.Count*T.PointsPerPiece ELSE 0 END) TotalPoints
    FROM Fact F
    INNER JOIN ObjectTypes T
    ON F.Description = T.Description
    GROUP BY F.Name

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Thank You.
    This seems to be helpful.
    By now I vote your answer, but please wait a bit before marking it as final answer.
    I'll do some tests tomorrow, then return here for further questions and I'll mark as answer soon.

    Thanks again.

    • Marked as answer by MarcoGG Tuesday, October 22, 2019 8:26 AM
    • Unmarked as answer by MarcoGG Tuesday, October 22, 2019 8:38 AM
    Monday, October 21, 2019 3:45 PM
  • Hi,

    Also , please try following script .

    ;with cte as(
    SELECT F.UserCode, left(F.OperationTime,4) [Year] ,
    case when T.MinPiecesPerYear>count(*)over(partition by F.UserCode,T.id) 
    then 0 else PointsPerPiece end  [Points]
    FROM  Operations F
    INNER JOIN Users U
    ON F.UserCode = U.UserCode
    INNER JOIN Objects O
    ON F.ObjectCode = O.Code
    INNER JOIN ObjectTypes T
    ON O.IdObjectType = T.ID )
    select UserCode,Year,sum(Points) [Total Points]
    from cte 
    group by UserCode,Year
    /*
    UserCode                                           Year Total Points
    -------------------------------------------------- ---- ------------
    USR001                                             2019 7
    USR002                                             2019 0
    */
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 22, 2019 7:17 AM
  • Hi

    Hope the below query fulfills your requirement.

    WITH Fact AS (
    SELECT LEFT(F.OperationTime,4) Year,U.Name,T.Description,COUNT(1) Count FROM 
    Operations F
    INNER JOIN Users U
    ON F.UserCode = U.UserCode
    INNER JOIN Objects O
    ON F.ObjectCode = O.Code
    INNER JOIN ObjectTypes T
    ON O.IdObjectType = T.ID
    GROUP BY LEFT(F.OperationTime,4),U.Name,T.Description)
    
    SELECT 
    	F.Name
    	,SUM(CASE WHEN F.Count >= T.MinPiecesPerYear THEN F.Count*T.PointsPerPiece ELSE 0 END) TotalPoints
    FROM Fact F
    INNER JOIN ObjectTypes T
    ON F.Description = T.Description
    GROUP BY F.Name

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Hi,

    I've made a little change, including control on Max Pieces Per Year, so this is the Query now :

    WITH T AS 
    (
    	SELECT LEFT(OP.OperationTime,4) YYYY,
    		   U.Name,
    		   OT.Description,
    		   COUNT(1) CNT 
    	FROM Operations OP INNER JOIN Users U ON OP.UserCode = U.UserCode
    	INNER JOIN Objects O ON OP.ObjectCode = O.Code
    	INNER JOIN ObjectTypes OT ON O.IdObjectType = OT.ID
    	GROUP BY LEFT(OP.OperationTime, 4), U.Name, OT.Description
    )
    SELECT 
    	T.Name,
    	SUM(CASE WHEN ( T.CNT >= OT.MinPiecesPerYear AND T.CNT <= OT.MaxPiecesPerYear ) THEN T.CNT * OT.PointsPerPiece ELSE 0 END ) TotalPoints
    	FROM T INNER JOIN ObjectTypes OT ON T.Description = OT.Description
    	GROUP BY T.Name

    Now, I've got the last Question.

    Let's say the Objects Table now contains itself the Points of each Object, so the Points per piece doesn't depend on the Object-Type, but it is written in each Object collected.

    The new Objects / Operations script is :

    /* Objects */
    DROP TABLE Objects
    CREATE TABLE Objects
    (
    	Code NVARCHAR(50) PRIMARY KEY NOT NULL,
    	IdObjectType INT NOT NULL,
    	Points INT NULL
    )
    
    /* Operations */
    DROP TABLE Operations
    CREATE TABLE Operations
    (
    	ObjectCode NVARCHAR(50) NOT NULL,
    	OperationTime NVARCHAR(14) NOT NULL,
    	UserCode NVARCHAR(50) NOT NULL,
    	OperationType TINYINT NOT NULL,
    	PRIMARY KEY (ObjectCode, OperationTime)
    )
    
    /* Some Test Objects / Operations */
    /* USR001 */
    INSERT INTO Objects (Code, IdObjectType, Points) VALUES ('OBJ001', 1, 1)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ001', '20191021000000', 'USR001', 1)
    INSERT INTO Objects (Code, IdObjectType, Points) VALUES ('OBJ002', 2, 2)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ002', '20191021000001', 'USR001', 1)
    INSERT INTO Objects (Code, IdObjectType, Points) VALUES ('OBJ003', 2, 3)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ003', '20191021000002', 'USR001', 1)
    
    /* USR002 */
    INSERT INTO Objects (Code, IdObjectType, Points) VALUES ('OBJ004', 2, 4)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ004', '20191021000003', 'USR002', 1)
    INSERT INTO Objects (Code, IdObjectType, Points) VALUES ('OBJ005', 3, 5)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ005', '20191021000004', 'USR002', 1)
    INSERT INTO Objects (Code, IdObjectType, Points) VALUES ('OBJ006', 3, 6)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ006', '20191021000005', 'USR002', 1)

    I should now do the same thing, but the sum of the points should be done on the Objects Points.

    Thanks again.

    Tuesday, October 22, 2019 7:47 AM
  • Great !!

    Here it goes

    SELECT LEFT(F.OperationTime,4) Year,F.UserCode,SUM(O.Points)
    FROM Operations F
    INNER JOIN Objects O
    ON F.ObjectCode = O.Code
    GROUP BY LEFT(F.OperationTime,4),F.UserCode

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    • Edited by nkumar230 Tuesday, October 22, 2019 7:59 AM
    Tuesday, October 22, 2019 7:50 AM
  • Great !!

    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Could you answer also my second question ? Thanks.

    Tuesday, October 22, 2019 7:57 AM
  • Would you like this one ?

    ;with cte as(
    SELECT F.UserCode, left(F.OperationTime,4) [Year] ,
    case when T.MinPiecesPerYear>count(*)over(partition by F.UserCode,T.id) or T.MaxPiecesPerYear<count(*)over(partition by F.UserCode,T.id)
    then 0 else O.Points end  [Points]
    FROM  Operations F
    INNER JOIN Users U
    ON F.UserCode = U.UserCode
    INNER JOIN Objects O
    ON F.ObjectCode = O.Code
    INNER JOIN ObjectTypes T
    ON O.IdObjectType = T.ID )
    select UserCode,Year,sum(Points) [Total Points]
    from cte 
    group by UserCode,Year
    /*
    UserCode                                           Year Total Points
    -------------------------------------------------- ---- ------------
    USR001                                             2019 6
    USR002                                             2019 0
    */
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by MarcoGG Tuesday, October 22, 2019 8:39 AM
    Tuesday, October 22, 2019 7:58 AM
  • Yes i did, By mistake i haven't added code, Just edited my previous reply.

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, October 22, 2019 8:02 AM
  • Great !!

    Here it goes

    SELECT LEFT(F.OperationTime,4) Year,F.UserCode,SUM(O.Points)
    FROM Operations F
    INNER JOIN Objects O
    ON F.ObjectCode = O.Code
    GROUP BY LEFT(F.OperationTime,4),F.UserCode

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.



    The Min and Max Year limits are still active so the Objects Type Table should be present in query...
    Or I didn't understand where to use it.

    The new query should be like the previous except the score calculation is now made on single objects points and not on the points-per-piece in the Object-Type.

    Tuesday, October 22, 2019 8:15 AM
  • Would you like this one ?

    ;with cte as(
    SELECT F.UserCode, left(F.OperationTime,4) [Year] ,
    case when T.MinPiecesPerYear>count(*)over(partition by F.UserCode,T.id) or T.MaxPiecesPerYear<count(*)over(partition by F.UserCode,T.id)
    then 0 else O.Points end  [Points]
    FROM  Operations F
    INNER JOIN Users U
    ON F.UserCode = U.UserCode
    INNER JOIN Objects O
    ON F.ObjectCode = O.Code
    INNER JOIN ObjectTypes T
    ON O.IdObjectType = T.ID )
    select UserCode,Year,sum(Points) [Total Points]
    from cte 
    group by UserCode,Year
    /*
    UserCode                                           Year Total Points
    -------------------------------------------------- ---- ------------
    USR001                                             2019 6
    USR002                                             2019 0
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thank You Rachel. This works.
    I voted +1 on this and also on your previous code. 

    • Marked as answer by MarcoGG Tuesday, October 22, 2019 8:27 AM
    • Unmarked as answer by MarcoGG Tuesday, October 22, 2019 8:38 AM
    Tuesday, October 22, 2019 8:17 AM
  • Hi MarcoGG,

     

    We are glad to hear that they are helpful to you. If you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 22, 2019 8:21 AM
  • Hi MarcoGG,

     

    We are glad to hear that they are helpful to you. If you have solved your issue ,please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    I've marked as answer the first one given by nkumar230, and also your last answer.

    Thank You.

    Tuesday, October 22, 2019 8:29 AM
  • Oh ....

    Maybe you have an incorrect operation. You marked yourself replies.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 22, 2019 8:31 AM
  • Oh ....

    Maybe you have an incorrect operation. You marked yourself replies.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Yes. My mistake. Sorry.
    Now I've marked the correct posts as answers. nkumar230 and you. Thanks.

    Tuesday, October 22, 2019 8:41 AM
  • Thank you for your affirmation of our reply.

    If you have any other issue about Transact-SQL, please let me know.

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 22, 2019 8:45 AM
  • Thank you for your affirmation of our reply.

    If you have any other issue about Transact-SQL, please let me know.

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    I've done some test on your last code, and it seems like it doesn't take care of the calendar year limits.

    You can test it quickly with this script :

    DELETE FROM Objects 
    DELETE FROM Operations 
    /* USR001 */
    INSERT INTO Objects (Code, IdObjectType, Points) VALUES ('OBJ001', 2, 5)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ001', '20191021000000', 'USR001', 1)
    INSERT INTO Objects (Code, IdObjectType, Points) VALUES ('OBJ002', 2, 5)
    INSERT INTO Operations (ObjectCode, OperationTime, UserCode, OperationType) VALUES ('OBJ002', '20201021000001', 'USR001', 1)
    
    
    

    Now, User USR001 brings an object Type 2 ( Minimum 2 pieces per year ) in year 2019, and then another object Type 2 in year 2020.

    The query output is :

    UserCode	YYYY	Total Points
    USR001	        2019	5
    USR001	        2020	5
    Which is not correct.
    In 2019 the minimum of 2 was not reached, so Total Points in 2019 should be 0.
    In 2020 same way, Total shoul be 0.

    2 Objects in two different years shouldn' meet the minimum per-year.

    Thanks again for attention.

    Tuesday, October 22, 2019 10:05 AM
  • Ok, found by myself.

    Added LEFT(OP.OperationTime, 4) to PARTITION BY.

    So the final Query is :

    WITH CTE AS
    (
    	SELECT 
    	OP.UserCode, 
    	LEFT(OP.OperationTime, 4) [YYYY],
    	CASE WHEN T.MinPiecesPerYear > COUNT(*) OVER ( PARTITION BY OP.UserCode, T.ID, LEFT(OP.OperationTime, 4)) OR 
    	          T.MaxPiecesPerYear < COUNT(*) OVER ( PARTITION BY OP.UserCode, T.ID, LEFT(OP.OperationTime, 4))
    	THEN 0 ELSE O.Points END [Points]
    	FROM  Operations OP
    	INNER JOIN Users U ON OP.UserCode = U.UserCode
    	INNER JOIN Objects O ON OP.ObjectCode = O.Code
    	INNER JOIN ObjectTypes T ON O.IdObjectType = T.ID 
    )
    SELECT 
    UserCode, 
    YYYY, 
    SUM(Points) [Total Points]
    FROM CTE 
    GROUP BY UserCode, YYYY

    Tuesday, October 22, 2019 12:36 PM