SQL Split Totals

SQL split totals

You can use a recursive CTE to split the rows (you might need to up the recursion limit if your quantity can be higher than 100).

declare @Test table (qty int, [description] varchar(64), volume decimal(9,2), [weight] decimal(9,2))

insert into @Test (qty, [description], volume, [weight]) values (4, 'Flowers', 3.4, 4);

with cte as (
  select qty, [description], volume, [weight], 1 as rn
  from @Test
  union all
  select qty, [description], volume, [weight], rn + 1
  from cte
  where rn < qty
)
select 1 qty, [description], cast(volume / qty as decimal(9,2)) volume, cast([weight] / qty as decimal(9,2)) [weight]
from cte
for xml path('product'), root('products'), type;
-- option (maxrecursion 200); -- If you need to increase it above the default of 100

Note: If you setup the DDL+DML, as I shown, in your questions you make it much easier for people to reply.

SQL Server : Split Columns to Count Totals By Year in same row

On SQL-Server you could use a PIVOT:

SELECT Person, [2017], [2018]
FROM (SELECT Person, VisitYear FROM #Events) src
PIVOT (COUNT(VisitYear) FOR VisitYear IN ([2017],[2018])) pvt
GO

Person | 2017 | 2018
:----- | ---: | ---:
User1  |    2 |    1
User2  |    2 |    3
User3  |    0 |    1

dbfiddle here

In SQL, split an amount among multiple rows

The following should work for you:

UPDATE  t
SET     Remaining_Days = CASE WHEN CumulativeDayCount < Exceeding_Amt THEN Days_Count
                                WHEN (CumulativeDayCount - Days_Count) > Exceeding_Amt THEN 0
                                ELSE Exceeding_Amt - (CumulativeDayCount - Days_Count)
                            END
FROM    (   SELECT  t.ID,
                    t.Days_Count,
                    t.[State],
                    CumulativeDayCount = SUM(t.Days_Count) OVER(PARTITION BY t.[State] ORDER BY t.ID),
                    t.Remaining_Days,
                    ts.Exceeding_Amt
            FROM    Travel AS t
                    INNER JOIN Travel_Sum AS ts
                        ON ts.[State] = t.[State]
        ) AS t;

The main step here is to get the cumualtive number of days, using the windowed function SUM() OVER():

SELECT  t.ID,
        t.Days_Count,
        CumulativeDayCount = SUM(t.Days_Count) OVER(PARTITION BY t.[State] ORDER BY t.ID)
FROM    Travel AS t;

This gives:

ID  Days_Count  State   CumulativeDayCount
1   20          AL      20
2   2           AL      22
3   14          AL      36

Then you can join to your sum table, to get the Exceeding_Amt column, and work out whether all, some, or none of this should be applied based on the cumulative total. This is where the case expression comes in:

CASE WHEN CumulativeDayCount < Exceeding_Amt THEN Days_Count
    WHEN (CumulativeDayCount - Days_Count) > Exceeding_Amt THEN 0
    ELSE Exceeding_Amt - (CumulativeDayCount - Days_Count)
END

If you just need a select statement, and don't actually need to update your table you can just use:

SELECT  t.ID,
        t.State,
        t.Days_Count,
        Remaining_Days = CASE WHEN CumulativeDayCount < Exceeding_Amt THEN Days_Count
                                WHEN (CumulativeDayCount - Days_Count) > Exceeding_Amt THEN 0
                                ELSE Exceeding_Amt - (CumulativeDayCount - Days_Count)
                            END
FROM    (   SELECT  t.ID,
                    t.Days_Count,
                    t.[State],
                    CumulativeDayCount = SUM(t.Days_Count) OVER(PARTITION BY t.[State] ORDER BY t.ID),
                    ts.Exceeding_Amt
            FROM    Travel AS t
                    INNER JOIN Travel_Sum AS ts
                        ON ts.[State] = t.[State]
        ) AS t;

Example on SQL Fiddle

Split value from a total row to multiple other rows until the sum reaches the value of the total row

I came up with the following:

select i.campaign, i.expected_inbound_date, i.expected_inbound_quantity, i.received_inbound_quantity, (
    select greatest(
        least(
            i.expected_inbound_quantity, 
            (select sum(iii.received_inbound_quantity) from inbound iii where i.campaign = iii.campaign) - 
            (
                select cum_sum
                from (
                    select sum(ii.expected_inbound_quantity) over (partition by ii.campaign order by ii.expected_inbound_date, ii.expected_inbound_quantity, ii.received_inbound_quantity rows between unbounded preceding and 1 preceding) cum_sum, ii.campaign, ii.expected_inbound_date, ii.expected_inbound_quantity, ii.received_inbound_quantity
                    from inbound ii
                ) tmp
                where (tmp.campaign, tmp.expected_inbound_date, tmp.expected_inbound_quantity, tmp.received_inbound_quantity) = (i.campaign, i.expected_inbound_date, i.expected_inbound_quantity, i.received_inbound_quantity)
            )
        ),
        0
    )
) split
from inbound i
order by i.campaign, i.expected_inbound_date, i.expected_inbound_quantity, i.received_inbound_quantity

Here is a db fiddle.

The idea is to calculate the cumulative sum of rows preceding the current row as cum_sum and then to pick whatever is less: the sum of received_inbound_quantity minus the cum_sum or the expected_inbound_date. To avoid values < 0, I've used greatest.

Dividing a list of numbers into roughtly equal totals

How about sorting the tables by size, then for each table, put it into the day that currently has the smallest total number of rows in it? This means the biggest 7 tables will first spread across the days. Then the 8th biggest will go with the smallest of the first 7, etc. You'll continue to fill in the day with the least amount of work scheduled to it.

Where the little reference tables end up finally probably does not make much difference.

You could invent scenarios where this is no good, but I expect it will work in practice without being too complicated.

Split column into multiple columns by criteria

No need for an addition subquery or CTE. You can pivot your dataset using conditional aggregation with slight modifications of your query: just remove shift from the group by clause, and then implement conditional logic in the sum()s:

select
  date,
  sum(case when shift = 1 then value end) shift1,
  sum(case when shift = 2 then value end) shift2,
  sum(case when shift = 3 then value end) shift3
from
  db.table
where
  date >= date '2020-01-01'
  and filter = 'type'
group by date
order by date

Note:

• there is no need to prefix the column names since a single table comes into play. I removed those

• date is the name of datatype in Oracle, hence not a good choice for a column name

SQL - I need to divide a total value into multiple rows in another table

You can do it like this, this query first populates classrooms with greatest capacity:

DECLARE @School TABLE (School_Id INT,Course_Id 
    VARCHAR(50), Total_Students INT)
DECLARE @Class TABLE (School_Id INT,Course_Id 
    VARCHAR(50), Class_ID VARCHAR(50), Capacity INT)
INSERT @School VALUES 
   (1,         'Acct101'        ,150),
   (1,         'Acct102'        ,100),
   (2,         'Acct101'        ,110),
   (2,         'Acct102'        ,130)
INSERT @Class VALUES 
   (1,         'Acct101'     ,'A1'       ,65),
   (1,         'Acct101'     ,'A2'       ,50),
   (1,         'Acct101'     ,'A3'       ,70),
   (1,         'Acct102'     ,'Ab1'      ,100),
   (1,         'Acct102'     ,'Ab2'      ,100),
   (2,         'Acct101'     ,'B1'       ,80),
   (2,         'Acct101'     ,'B2'       ,90)

;WITH y AS (
SELECT  a.*,
        ROW_NUMBER() OVER 
            (PARTITION BY a.School_ID, a.Course_ID ORDER BY a.Capacity DESC) 
            CapacitiyOrderPerSchoolAndCourse,
        SUM(a.Capacity) OVER 
            (PARTITION BY a.School_ID, a.Course_ID) 
            TotalCapacityForSchoolAndCourse,
        b.Total_Students TotalParticipants
FROM    @Class a
JOIN    @School b ON 
        b.School_Id = a.School_Id
        AND b.Course_Id = a.Course_Id
), z AS(
SELECT  x.School_Id, 
        x.Course_Id, 
        y.TotalCapacityForSchoolAndCourse, 
        y.TotalParticipants,
        CASE WHEN y.TotalParticipants < SUM(x.Capacity) THEN 
                y.TotalParticipants
            ELSE 
                SUM(x.Capacity) 
            END NumberOfStudentsInClasses,
        MIN(y.Capacity) ClassCapacity,
        y.Class_ID ClassName,
        MIN(y.Capacity) - 
        CASE WHEN y.TotalParticipants - SUM(x.Capacity) < 0 THEN 
               ABS(y.TotalParticipants - SUM(x.Capacity))
            ELSE
               0
            END StudentsInClass
FROM    y
JOIN    y x ON x.School_Id = y.School_Id 
        AND x.Course_Id = y.Course_Id 
        AND x.CapacitiyOrderPerSchoolAndCourse 
                <= y.CapacitiyOrderPerSchoolAndCourse
GROUP   BY x.School_Id, 
        x.Course_Id, 
        y.CapacitiyOrderPerSchoolAndCourse, 
        y.Class_ID, 
        y.TotalCapacityForSchoolAndCourse, 
        y.TotalParticipants
)

SELECT  
        z.School_Id, 
        z.Course_Id, 
        z.TotalCapacityForSchoolAndCourse, 
        z.TotalParticipants,
        z.ClassName,
        z.ClassCapacity,
        CASE WHEN StudentsInClass < 0 THEN 
                0 
            ELSE 
                StudentsInClass 
            END StudentsInClass
FROM    z

If you want each classroom to have some number of students you can do it like this (it allocates a number of students to each classroom according to it's capacity):

;WITH y AS (
SELECT  a.*,
        SUM(a.Capacity) OVER 
            (PARTITION BY a.School_ID, a.Course_ID) 
            AS TotalCapacityForSchoolAndCourse,
        b.Total_Students TotalParticipants
FROM    @Class a
JOIN    @School b ON 
        b.School_Id = a.School_Id
        AND b.Course_Id = a.Course_Id
), z AS(
SELECT  y.School_Id, 
        y.Course_Id, 
        y.TotalCapacityForSchoolAndCourse, 
        y.TotalParticipants,
        MIN(y.Capacity) ClassCapacity,
        y.Class_ID,
        MIN(y.Capacity) * 1.0 / y.TotalCapacityForSchoolAndCourse 
            AS PercentOfCapacity,
        ROUND(
            MIN(y.Capacity) * 1.0 / y.TotalCapacityForSchoolAndCourse 
                * TotalParticipants
            , 0, 0) 
            AS NumberOfStudents
FROM    y
GROUP   BY y.School_Id, 
        y.Course_Id, 
        y.Class_ID, 
        y.TotalCapacityForSchoolAndCourse, 
        y.TotalParticipants
)
, i AS(
SELECT  
        z.School_Id, 
        z.Course_Id, 
        z.TotalCapacityForSchoolAndCourse, 
        z.TotalParticipants,
        z.Class_ID,
        z.ClassCapacity,
        PercentOfCapacity,
        NumberOfStudents,
        SUM(NumberOfStudents) OVER 
            (PARTITION BY z.School_Id, z.Course_Id) 
            AS SumNumberOfStudents,
        ROW_NUMBER() OVER 
            (PARTITION BY z.School_Id, z.Course_Id 
                ORDER BY NumberOfStudents) 
            AS ClassWithSmallestCapacity
FROM    z
), j AS(
SELECT  i.School_Id, 
        i.Course_Id, 
        i.TotalCapacityForSchoolAndCourse, 
        i.TotalParticipants,
        i.Class_ID,
        i.ClassCapacity,
        i.PercentOfCapacity,
        i.NumberOfStudents,
        i.NumberOfStudents +
        CASE WHEN ClassWithSmallestCapacity = 1 THEN 
                TotalParticipants - SumNumberOfStudents 
            ELSE 0 
            END AS NumberOfStudents2
FROM    i
)

SELECT  *
FROM    j

SQL Split field results into two based in value grouping

try this:

  select item,
         description,
         sum(case code when 200 then 1 else 0 end) two_hundreds,
         sum(case code when 500 then 1 else 0 end) five_hundreds
    from Table1
group by item, 
         description

You can check the demo in SQLFiddler here

---

Related Topics