logo
down
shadow

SQL - dynamic sum based on dynamic date range


SQL - dynamic sum based on dynamic date range

By : bahar22
Date : October 20 2020, 08:10 PM
To fix this issue I'm new to SQL and I'm not even sure if what I am trying to achieve is possible. , You should be able to use join and group by:
code :
select d.account#, d.Date_from, d.Date_to, sum(mv.volume)
from dates d left join
     monthly_volume mv
     on mv.account# = d.account# and
        mv.date between d.Date_from and d.Date_to
group by d.account#, d.Date_from, d.Date_to;


Share : facebook icon twitter icon
Dynamic date range based a range of cells

Dynamic date range based a range of cells


By : nava sujay
Date : March 29 2020, 07:55 AM
will be helpful for those in need The following will work if you are only dealing with checking dates in a giving year, if you are looking at calculating date spanning multiple years with the WORKDAY formula, you will need to either repeat the list adding +1 to the year in the function, or you will need to develop a different function for counting workdays and that would probably entail some VBA.
What you need to do is work out the logic on how the dates are figured out and store those calculated values in your sheet. Now assuming you have a date somewhere in your sheet or you refer to the year someone in your sheet that you will be dealing with, you can try the following. I will develop the logic for 3rd Monday in January. you can then use this though process to develop your own method for coming up with similar holidays. Take a look at the various built in date manipulation formulas built into excel such as DATE, YEAR, MONTH, DAY, WEEKDAY, TODAY, NOW, EOMONTH, etc. they may be of great help to you.
code :
(A)                         |     (B)
Year                        |     2014
Weekday of 1st day of month |        4
Weekday number for Monday   |        2
Difference                  |       -2
3rd Monday                  |       21
Days from start of month    |       20
Target date                 | 14/01/20
=YEAR(Q10)
=WEEKDAY(DATE(B1,1,1))
=2
=B3-B2
=if(B4<=0,21,14)
=B5+B4+1
=date(B1,1,B6)
=DATE(2014,1,IF(2-WEEKDAY(DATE(2014,1,1))<=0,21,14)+2-WEEKDAY(DATE(2014,1,1))+1)
=IF(WEEKDAY(DATE(YEAR(Q10),12,25))=7,DATE(YEAR(Q10),12,24),IF(WEEKDAY(DATE(YEAR(Q10),12,25))=1,DATE(YEAR(Q10),12,26),DATE(YEAR(Q10),12,25)))
WEEKDAY(DATE(YEAR(Q10),12,25))
WEEKDAY(DATE(YEAR(Q10),12,25))=7
DATE(YEAR(Q10),12,24)
DATE(YEAR(Q10),12,25)-1
or
DATE(YEAR(Q10),12,25-1)
IF(WEEKDAY(DATE(YEAR(Q10),12,25))=1,DATE(YEAR(Q10),12,26),DATE(YEAR(Q10),12,25))
WEEKDAY(DATE(YEAR(Q10),12,25))=1
DATE(YEAR(Q10),12,26)
or
DATE(YEAR(Q10),12,25)+1
or
DATE(YEAR(Q10),12,25+1)
DATE(YEAR(Q10),12,25)
=DATE(YEAR(Q4)+1,1,1)
changing a static date range into a dynamic date range in excel

changing a static date range into a dynamic date range in excel


By : maebgchd
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I have the year broken up into 6 columns & 6 rows on another sheet. One row is for the month title & 2 rows are for the dates. I.e. October's title is in cell K5, October 1 is in Cell K6, & October 31st is in cell K7. The other months are the same except for the cell location & that they are based on cells K6 &K7. Here are the 2 formulas: =COUNTIFS(Referrals!$E$3:E$325,"IT/TWE",Referrals!$A$3:$A$325,"<"&'Month Source'!K6) & =COUNTIFS(Referrals!$E$3:$E$325, "IT/TWE", Referrals!$A$3:$A$325, ">="&'Month Source'!L6,$A$3:$A$325,"<="&'Month Source'!L7).
Dynamic SQL Procedure with Pivot displaying counts based on Date Range

Dynamic SQL Procedure with Pivot displaying counts based on Date Range


By : Ankit Yadav
Date : March 29 2020, 07:55 AM
this one helps. Now that you have provided examples, I have updated my answer which provides you with a solution based on the data you have provided.
Note that you are able to change the date range and the query will update accordingly.
code :
create table #tbl1 (
    [UserId] int
    ,[UserName] nvarchar(max)
    ,[EntryDateTime] datetime
);

insert into #tbl1 ([UserId],[UserName],[EntryDateTime])
values
    (1,'John Doe','20171109')
    ,(1,'John Doe','20171109')
    ,(1,'John Doe','20171110')
    ,(1,'John Doe','20171111')
    ,(2,'Mike Smith','20171109')
    ,(2,'Mike Smith','20171110')
    ,(2,'Mike Smith','20171110')
    ,(2,'Mike Smith','20171110')
;

-- declare variables
declare
    @p1 date
    ,@p2 date
    ,@diff int
    ,@counter1 int
    ,@counter2 int
    ,@dynamicSQL nvarchar(max)
;

-- set variables
set @p1 = '20171109';   -- ENTER THE START DATE IN THE FORMAT YYYYMMDD
set @p2 = '20171111';   -- ENTER THE END DATE IN THE FORMAT YYYYMMDD
set @diff = datediff(dd,@p1,@p2); -- used to calculate the difference in days
set @counter1 = 0;  -- first counter to be used in while loop
set @counter2 = 0;  -- second counter to be used in while loop
set @dynamicSQL = 'select pivotTable.[UserId] ,pivotTable.[UserName] as [Name] '; -- start of the dynamic SQL statement

-- to get the dates into the query in a dynamic way, you need to do a while loop (or use a cursor)
while (@counter1 < @diff)
begin
    set @dynamicSQL += ',pivotTable.[' + convert(nvarchar(10),dateadd(dd,@counter1,@p1),120) + '] '

    set @counter1 = (@counter1 +1)
end

-- continuation of the dynamic SQL statement
set @dynamicSQL += ' from (
    select
        t.[UserId]
        ,t.[UserName]
        ,cast(t.[EntryDateTime] as date) as [EntryDate]
        ,count(t.[UserId]) as [UserCount]
    from #tbl1 as t
    where
        t.[EntryDateTime] >= ''' + convert(nvarchar(10),@p1,120) + ''' ' +
        ' and t.[EntryDateTime] <= ''' + convert(nvarchar(10),@p2,120) + ''' ' +
    'group by
        t.[UserId]
        ,t.[UserName]
        ,t.[EntryDateTime]
    ) as mainQuery

    pivot (
        sum(mainQuery.[UserCount]) for mainQuery.[EntryDate]
            in ('
;

-- the second while loop which is used to create the columns in the pivot table
while (@counter2 < @diff)
begin
    set @dynamicSQL += ',[' + convert(nvarchar(10),dateadd(dd,@counter2,@p1),120) + ']'

    set @counter2 = (@counter2 +1)
end

-- continuation of the SQL statement
set @dynamicSQL += ')
    ) as pivotTable'
;

-- this is the easiet way I could think of to get rid of the leading comma in the query
set @dynamicSQL = replace(@dynamicSQL,'in (,','in (');

print @dynamicSQL   -- included this so that you can see the SQL statement that is generated

exec sp_executesql @dynamicSQL; -- this will run the generate dynamic SQL statement

drop table #tbl1;
Dynamic X-Axis Date Range Based on Disconnected Slicer

Dynamic X-Axis Date Range Based on Disconnected Slicer


By : silentmojo
Date : March 29 2020, 07:55 AM
Hope that helps I have an issue that I need a hand with. I am using a disconnected slicer based off of this table , You might want to try this one:
code :
3 Month Period :=
CALCULATE (
    [Sum Of Paid],
    KEEPFILTERS (
        DATESINPERIOD (
            Data[Date],
            CALCULATE (
                MAX ( Data[Date] ),
                ALLSELECTED ()
            ),
            -3,
            MONTH
        )
    )
)
vba dynamic sum based on dynamic range values

vba dynamic sum based on dynamic range values


By : Momin Imran
Date : March 29 2020, 07:55 AM
will help you If you know where your data starts you can use a method such as that given by Shai Rado.
You can't have any entirely empty rows or columns in the range.
code :
Option Explicit

Public Sub AddSubTotal()

    Dim lastRow As Long
    Dim lastCol As Long
    Dim firstRow As Long
    Dim rowHeight As Long
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet2")             'change as appropriate

    With ws.Range("D3").CurrentRegion 'change as appropriate
        firstRow = .Rows(1).Row
        lastRow = .Rows(.Rows.Count).Row
        lastCol = .Columns(.Columns.Count).Column
        rowHeight = lastRow - firstRow + 1     
    End With

    With ws
        .Range(.Cells(lastRow + 1, "D"), .Cells(lastRow + 1, lastCol)).FormulaR1C1 = "=SUBTOTAL(9,R[-" & rowHeight & "]C:R[-1]C)"
    End With

End Sub
Related Posts Related Posts :
  • How to create table via ODBC in ansi sql compliant way?
  • Postgres. How to get all the parents that meet the childs criteria?
  • 'Exploratory' SQL queries that uses one criteria to find more criteria
  • Adding Date columns
  • Display ContactName from Guid in Gridview using SQLDataSource / Asp.Net
  • making groups based on match between column 1 and column 2 from 1 table
  • Teradata 15.10 - Remove set of characters from a string
  • PostgreSQL array_agg(INTEGER[])
  • Does a query goes through all data when you only select the last N?
  • Displaying results based on customer first order after specific date SQL Server
  • Sum records and add note what was summed up in sql
  • Need help in generating values
  • RODBC - import a table
  • SQL: How to Group By task and choose only those that are completed?
  • PostgreSQL/PostGIS Count number of visits
  • How to I get a total count?
  • Many-to-many SQL relationship
  • Procedure call inside procedure
  • How to have decreasing running total in Oracle sql
  • SQL Group function query?
  • Which is the best way to calculate Year/Month in SQL Server?
  • Best way of sanitize unparametrizable sql
  • Querying for JSON data in Oracle creates syntax error
  • Show top 1 by max column
  • SQL: Insert newly created column in the same table
  • How to extract numbers after string using regexp?
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org