SQL Server 2008 Using Sum() Over(Order By...)

SQL Server 2008 using SUM() OVER(ORDER BY...)

The documentation you found for ROWS/RANGE is not for SQL Server 2008 - it's for a future version of SQL Server.

To accomplish your query in SQL 2008, one approach would be similar to:

SELECT a.TIC, a.datadate, a.effdate, x.s
FROM quarterResults a
    CROSS APPLY (   SELECT ISNULL(SUM(v), 0)
                    FROM (  SELECT TOP(4) b.valuei
                            FROM quarterResults b
                            WHERE b.datadate < a.datadate
                            ORDER BY b.datadate DESC ) x(v)
                ) x(s)
ORDER BY a.TIC, a.datadate

Note that this is potentially an expensive query. The use of the OVER expression with ROWS would probably be more efficient but, again, it is not available in SQL Server 2008.

cumulative sum with calculation sql server 2008

This is a set-based solution for getting the result as you want, you did not answer if you want also to group by CustomerId, so I did not include it as your result set does not contain it:

    declare @t table (dt date, descr varchar(100), drcr char(2), am decimal(10,2));
    insert into @t values
    ('20170511', 'xxx', 'Dr', 25000), 
    ('20170511', 'aaa', 'Cr', 20000), 
    ('20170512', 'xyz', 'Dr', 5000), 
    ('20170601', 'abc', 'Cr', 10000), 
    ('20170601', 'abc', 'Cr', 10000);

    with cte as
    (
    select dt, descr, 
           Debit, Credit,
           am_signed,
           row_number() over(order by dt, Credit) as rn
    from @t cross apply
         (
           select
           case drcr 
                when 'Dr' then am
           end as Debit,

           case drcr 
                when 'Cr' then am
           end as Credit,

           case drcr 
                when 'Dr' then -am
                when 'Cr' then am
           end as am_signed

         )a
    )       

    ,cte1 as
    (
    select t1.dt, t1.descr, t1.Debit, t1.Credit, t1.am_signed,
           sum(t2.am_signed) as balance, t1.rn
    from cte as t1 
         join cte as t2 
             on t2.rn <= t1.rn
    group by t1.dt, t1.descr, t1.Debit, t1.Credit, t1.am_signed, t1.rn
    )

    select dt, descr, Debit, Credit, balance
    from cte1
    order by rn;

..................................................................

To @Jayvee: check How to Use Microsoft SQL Server 2012's Window Functions:

SQL Server 2012 (formerly code-named SQL Server Denali) introduces
several important T-SQL programmability features; this article focuses
on one of those features—window functions. SQL Server 2005 was the
first milestone in supporting window functions; it introduced window
ranking functions (ROW_NUMBER, RANK, DENSE_RANK, and NTILE), as well
as limited support for window aggregate functions—only with a window
partition clause. SQL Server 2012 enhances support for window
aggregate functions by introducing window order and frame ***clause***s,
support for offset functions (LAG, LEAD, FIRST_VALUE, and LAST_VALUE),
and support for window distribution functions (PERCENT_RANK,
CUME_DIST, PERCENTILE_DISC, and PERCENTILE_CONT).

OVER PARTITION BY giving error in SQL Server 2008

if you want a solution which can work on both then try this :

;WITH cte
     AS (SELECT Date,
                Item_Code,
                Sum(In_Quantity)                           AS In_Quantity,
                Sum(Issue_Quantity)                        AS Issue_Quantity,
                ( Sum(In_Quantity) - Sum(issue_Quantity) ) AS BalanceQty
         FROM   (SELECT tbl_add_product.Date AS Date,
                        tbl_add_product.Item_Code,
                        tbl_add_product.In_Quantity,
                        0                    AS Issue_Quantity
                 FROM   tbl_add_product
                 WHERE  Item_Code = 'pen'
                 UNION ALL
                 SELECT tbl_issue_product.Date AS Date,
                        tbl_issue_product.Item_Code,
                        0                      AS In_Quantity,
                        Issue_Quantity
                 FROM   tbl_issue_product
                 WHERE  Item_Code = 'pen') X
         GROUP  BY Item_Code,Date)
SELECT *,(select SUM(BalanceQty) from cte c2 where c2.Date <=c1.Date)
FROM   cte c1

SQL Server 2008 version of OVER(... Rows Unbounded Preceding)

One straight-forward way to do it is to use a correlated sub-query in CROSS APPLY.

If your table is more or less large, then your next question would be how to make it fast. Index on PlaceB, Product, PickTime INCLUDE (Qty) should help. But, if your table is really large, cursor would be better.

WITH
ADVPICK
AS
(
    SELECT 'A' as PlaceA,PlaceB, case when PickTime = '00:00' then '07:00' else isnull(picktime,'12:00') end as picktime, cast(Product as int) as product, Prd_Description, -Qty AS Qty FROM t_pick_orders
    UNION ALL
    SELECT 'A' as PlaceA,PlaceB, '0', cast(Code as int) as product, NULL, Stock FROM t_pick_stock
)
,stock_post_order
AS
(
    SELECT
        *
    FROM
        ADVPICK AS Main
        CROSS APPLY
        (
            SELECT SUM(Sub.Qty) AS new_qty
            FROM ADVPICK AS Sub
            WHERE
                Sub.PlaceB = Main.PlaceB
                AND Sub.Product = Main.Product
                AND T.PickTime <= Main.PickTime
        ) AS A
)
SELECT
    *,
    CASE WHEN new_qty > qty THEN new_qty ELSE qty END AS order_shortfall
FROM
    stock_post_order
WHERE
    new_qty < 0
ORDER BY PlaceB, picktime, product;

Oh, and if (PlaceB, Product, PickTime) is not unique, you'll get somewhat different results to original query with SUM() OVER. If you need exactly same results, you need to use some extra column (like ID) to resolve the ties.

SQL Server 2008 : group by , get max and sum up the values

You need to add name column in partition clause

select name,sum(t.value), sum(t.total)
from (
  select *, row_number() over (partition by category,name order by id desc) as rn
  from [dbo].[Table_1]   
  where category in ('A', 'B')
) t
where t.rn = 1 group by name

---

Related Topics