Kombinieren von Zeilen ohne Einzigartigkeit

Ich habe einen Tisch wie folgt:

+------+--------+----------+---------+---------+-----------+---------------+----------+ | Temp | UserID | TaskType | DayType | DayPart | TotalTask | PriorityTasks | LineCost | +------+--------+----------+---------+---------+-----------+---------------+----------+ | 0 | 1001 | 1 | Weekday | AM | 155 | NULL | 125.30 | | 0 | 1001 | 1 | Weekday | AM | 155 | 155 | 125.30 | | 0 | 1001 | 1 | Weekday | PM | 125 | NULL | 101.58 | | 0 | 1001 | 1 | Weekday | PM | 125 | 125 | 101.58 | | 1 | 1001 | 1 | Weekday | PM | 125 | NULL | 51.81 | | 0 | 1001 | 1 | Weekend | AM | 45 | 45 | 97.55 | | 0 | 1001 | 1 | Weekend | PM | 54 | NULL | 54.38 | | 0 | 1001 | 1 | Weekend | PM | 54 | 54 | 54.38 | +------+--------+----------+---------+---------+-----------+---------------+----------+ 

Ich habe anscheinend verunsichert, wo ich versuche, die Priority-Aufgaben zu gruppieren, da es keine separate Zeile erstellen sollte, wenn sie existieren, sondern stattdessen den Wert in der aktuellen Zeile ausfüllen. So sollte der Tisch so aussehen:

 +------+--------+----------+---------+---------+-----------+---------------+-----------+ | Temp | UserID | TaskType | DayType | DayPart | TotalTask | PriorityTasks | TotalCost | +------+--------+----------+---------+---------+-----------+---------------+-----------+ | 0 | 1001 | 1 | Weekday | AM | 155 | 155 | 125.30 | | 0 | 1001 | 1 | Weekday | PM | 125 | 125 | 101.58 | | 1 | 1001 | 1 | Weekday | PM | 125 | NULL | 51.81 | | 0 | 1001 | 1 | Weekend | AM | 45 | 45 | 97.55 | | 0 | 1001 | 1 | Weekend | PM | 54 | 54 | 54.38 | +------+--------+----------+---------+---------+-----------+---------------+-----------+ 

Meine Frage ist wie folgt:

 SELECT DISTINCT Temp, UserID, TaskType, DayType, DayPart, SUM(TaskCount) OVER (PARTITION BY UserID, TaskType, DayType, DayPart) as TotalTasks, CASE WHEN TaskType='PRIORITY' THEN SUM(TaskCount) OVER (PARTITION BY UserID, TaskType, DayType, DayPart) END as PriorityTasks, SUM(Cost) OVER (PARTITION BY Temp, UserID, TaskType, DayType, DayPart) as LineCost FROM ( SELECT * FROM ( SELECT Temp, TaskType, UserID, TaskType, DayType = 'Weekend', DayPart, TaskCount=SUM(WeekendTasks), Cost=CONVERT(DECIMAL(10,2),SUM((CostPerWeek / TasksPerWeek) * WeekendTasks)) FROM #Temp_CostPerAffiliateByTemp3 GROUP BY Temp, TaskType, UserID, TaskType, DayPart, CostPerWeek UNION ALL SELECT Temp, TaskType, UserID, TaskType, DayType = 'Weekday', DayPart, Tasks=SUM(WeekdayTasks), Cost=CONVERT(DECIMAL(10,2),SUM((CostPerWeek / TasksPerWeek) * WeekdayTasks)) FROM #Temp_CostPerAffiliateByTemp3 GROUP BY Temp, TaskType, UserID, TaskType, DayPart, CostPerWeek ) AS Y WHERE (TaskCount > 0) ) AS X GROUP BY X.UserID, X.TaskType, X.DayType, X.DayPart, X.Temp, X.TaskCount, X.TaskType, X.Cost 

Ich vermute, es ist etwas einfaches, aber alles, was ich suchte nach referenceen mit STUFF das ist nicht das, was ich suche, oder mit MAX aber ich kann nicht verwenden, wo ich denke, dass etwas fehlt

 CASE WHEN TaskType='PRIORITY' THEN SUM(TaskCount) OVER (PARTITION BY UserID, TaskType, DayType, DayPart) END as PriorityTasks 

Aber natürlich kann ich das nicht machen, da ich den Fehler bekomme:

* windowsfunktionen können nicht im Kontext einer anderen windowsfunktion oder Aggregat verwendet werden. *

Danke im Voraus!

Solutions Collecting From Web of "Kombinieren von Zeilen ohne Einzigartigkeit"

Warum ist einer von ihnen Teilung von Temp und die anderen nicht?

 SELECT .... ...SUM(TaskCount) OVER (PARTITION BY UserID, TaskType, DayType, DayPart) as TotalTasks, ...SUM(TaskCount) OVER (PARTITION BY UserID, TaskType, DayType, DayPart) END as PriorityTasks, ...SUM(Cost) OVER (PARTITION BY Temp, UserID, TaskType, DayType, DayPart) as LineCost FROM ... 

Es sieht so aus, als könntest du max() , es sei denn, ich vermisse etwas …

 select id = min(id) , Temp , UserID , TaskType , DayType , DayPart , TotalTask , PriorityTasks = max(PriorityTasks) , LineCost from t group by Temp , UserID , TaskType , DayType , DayPart , TotalTask , LineCost order by min(id) 

rextester demo: http://rextester.com/UERVTJ42678

kehrt zurück:

 +----+------+--------+----------+---------+---------+-----------+---------------+----------+ | id | Temp | UserID | TaskType | DayType | DayPart | TotalTask | PriorityTasks | LineCost | +----+------+--------+----------+---------+---------+-----------+---------------+----------+ | 1 | 0 | 1001 | 1 | Weekday | AM | 155 | 155 | 125,30 | | 3 | 0 | 1001 | 1 | Weekday | PM | 125 | 125 | 101,58 | | 5 | 1 | 1001 | 1 | Weekday | PM | 125 | NULL | 51,81 | | 6 | 0 | 1001 | 1 | Weekend | AM | 45 | 45 | 97,55 | | 7 | 0 | 1001 | 1 | Weekend | PM | 54 | 54 | 54,38 | +----+------+--------+----------+---------+---------+-----------+---------------+----------+ 
 declare @foo table([Temp] int, UserID int, TaskType int, DayType varchar(20), DayPart varchar(10), TotalTask int, PriorityTasks int, LineCost decimal(10,2)); insert into @foo values (0, 1001, 1, 'Weekday', 'AM', 155, NULL, 125.30), (0, 1001, 1, 'Weekday', 'AM', 155, 155, 125.30), (0, 1001, 1, 'Weekday', 'PM', 125, NULL, 101.58), (0, 1001, 1, 'Weekday', 'PM', 125, 125, 101.58), (1, 1001, 1, 'Weekday', 'PM', 125, NULL, 51.81), (0, 1001, 1, 'Weekend', 'AM', 45, 45, 97.55), (0, 1001, 1, 'Weekend', 'PM', 54, NULL, 54.38), (0, 1001, 1, 'Weekend', 'PM', 54, 54, 54.38); SELECT [Temp], UserID, TaskType, DayType, DayPart, TotalTask, SUM(PriorityTasks) PriorityTasks, LineCost FROM @foo GROUP BY [Temp], UserID, TaskType, DayType, DayPart, TotalTask, LineCost GO 
 Temp  UserID |  TaskType |  DayType |  DayPart |  TotalTask ​​|  PriorityTasks |  LineCost
 ---: |  -----: |  -------: |  : ------ |  : ------ |  --------: |  ------------  : -------
    0 |  1001 |  1 |  Wochentag |  AM |  155 |  155 |  125.30  
    0 |  1001 |  1 |  Wochentag |  PM  125 |  125 |  101,58  
    0 |  1001 |  1 |  Wochenende |  AM |  45 |  45 |  97,55   
    0 |  1001 |  1 |  Wochenende |  PM  54 |  54 |  54,38   
    1 |  1001 |  1 |  Wochentag |  PM  125 |  null |  51,81   

 Warnung: Nullwert wird durch eine Aggregat- oder andere SET-Operation eliminiert.

dbfiddle hier