logo
down
shadow

how to calculate all previous datas sum of a sql column on a date range report


how to calculate all previous datas sum of a sql column on a date range report

By : Chris Swaine
Date : November 22 2020, 03:03 PM
wish of those help I have a crystal report with debit credit columns using a sql command. This report contains a date to date filtering parameters. So the problem is if i filter the report to date range i need all the previous data sum using a sql command. , Here is your sample table
code :
CREATE TABLE #TEMP(DEBIT NUMERIC(20,2),CREDIT NUMERIC(20,2),DT VARCHAR(20))

INSERT INTO #TEMP
SELECT 1000 DEBIT,500 CREDIT,'2014-11-27' DT
UNION ALL
SELECT 2000 DEBIT,700 CREDIT,'2014-11-28' DT
UNION ALL
SELECT 3000 DEBIT,900 CREDIT,'2014-11-29' DT
SELECT T2.DEBIT,T2.CREDIT,T2.sum_date,
  (SELECT SUM(DEBIT)-SUM(CREDIT) FROM sum_balance WHERE sum_date <= CAST(T2.sum_date AS DATE))
   AMOUNT
FROM sum_balance T2
;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY CAST(T2.sum_date AS DATE))RNO,
     T2.DEBIT,T2.CREDIT,T2.sum_date,
    ISNULL((SELECT SUM(DEBIT)-SUM(CREDIT) FROM sum_balance WHERE sum_date <= CAST(T2.sum_date AS DATE)),0)
     AMOUNT
    FROM sum_balance T2
)
SELECT C1.*,ISNULL(C2.AMOUNT,0) CARRYFORWARD
FROM CTE C1
LEFT JOIN CTE C2 ON C1.RNO=C2.RNO+1


Share : facebook icon twitter icon
Crystal Report Date Range Report

Crystal Report Date Range Report


By : rahul kumar
Date : March 29 2020, 07:55 AM
seems to work fine You'd want to check the time whether it's before or after 12:00 noon. If it's 11:59:59 or less, add it to the previous day. Anything after, current day. Create a formula using something like this
code :
    IF TIME({yourdatetime}) <= TIME(11,59,59) THEN
       DATE({yourdatetime}) - 1
    ELSE 
       DATE({yourdatetime})
MYSQL how to calculate the sum of a column within a date range

MYSQL how to calculate the sum of a column within a date range


By : user7567289
Date : March 29 2020, 07:55 AM
Hope that helps Update: Query as suggested by @MatBailie
code :
mysql> SELECT
    ->    name, 
    ->    SUM(count) as total_count
    -> FROM table_name
    -> WHERE `date` >= DATE_SUB(NOW(), INTERVAL 30 day) AND `date` <= NOW()
    -> GROUP BY name    
    -> ;
+------+-------------+
| name | total_count |
+------+-------------+
| Jack |          10 |
| Mary |           4 |
| Tom  |           6 |
+------+-------------+
3 rows in set (0.02 sec)
SELECT
   name, 
   SUM(count) as total_count
FROM table_name
WHERE ABS(DATEDIFF(`date` , now())) < 30
GROUP BY name
mysql> create table table_name (`date` datetime, name char(20), count int);
Query OK, 0 rows affected (0.21 sec)

mysql> insert into table_name values
    -> ('2013-1-1',   'Jack',     20),
    -> ('2014-3-8',   'Jack',     3),
    -> ('2014-3-1',   'Tom',      1),
    -> ('2014-3-1',   'Jack',     7),
    -> ('2014-2-28',  'Mary',     4),
    -> ('2014-2-28',  'Tom',      5);
Query OK, 6 rows affected (0.12 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->    name, 
    ->    SUM(count) as total_count
    -> FROM table_name
    -> WHERE ABS(DATEDIFF(`date` , now())) < 30
    -> GROUP BY name;
+------+-------------+
| name | total_count |
+------+-------------+
| Jack |          10 |
| Mary |           4 |
| Tom  |           6 |
+------+-------------+
3 rows in set (0.00 sec)
mysql> SELECT
    ->    name, 
    ->    SUM(count) as total_count
    -> FROM table_name
    -> WHERE `date` > DATE_SUB(NOW(), INTERVAL 30 day) and `date` < NOW()
    -> GROUP BY name
    -> ORDER BY total_count DESC
    -> LIMIT 2;
+------+-------------+
| name | total_count |
+------+-------------+
| Jack |          10 |
| Tom  |           6 |
+------+-------------+
2 rows in set (0.03 sec)
VBA - Compare Column on Previous Report With New Report to Find New Entries

VBA - Compare Column on Previous Report With New Report to Find New Entries


By : rohan
Date : March 29 2020, 07:55 AM
Any of those help Let's assume that you have 3 worksheets:
worksheet1 - to compare with worksheet2 worksheet2 - to compare with worksheet1 worksheet3 - to write the values, which are different in worksheet1
code :
Public Sub FindDifferences()

    Dim firstRange As Range
    Dim secondRange As Range

    Dim wks1 As Worksheet: Set wks1 = Worksheets(1)
    Dim wks2 As Worksheet: Set wks2 = Worksheets(2)
    Dim wks3 As Worksheet: Set wks3 = Worksheets(3)

    Set firstRange = wks1.UsedRange
    Set secondRange = wks2.UsedRange

    Dim myCell  As Range

    For Each myCell In firstRange
        If myCell <> secondRange.Range(myCell.Address) Then
            wks3.Range(myCell.Address) = myCell
        End If
    Next myCell

End Sub
Set firstRange = wks1.UsedRange.Columns(2).Cells
Set secondRange = wks1.UsedRange.Columns(4).Cells

For Each myCell In firstRange
    If myCell.Value2 <> secondRange.Cells(myCell.Row, secondRange.Column).Value2 Then
        wks3.Range(myCell.Address) = myCell.Value2
    End If
Next myCell
Run access report including another non-associated attached report with same date range

Run access report including another non-associated attached report with same date range


By : user3350932
Date : March 29 2020, 07:55 AM
With these it helps Well, you can always setup the PT queryes BEFORE you launch the report.
So you could use some code like this:
code :
Sub Mytestzz()


  Dim strDtStart     As String
  Dim strDtEnd       As String

  strDtStart = qudateT("some date source for start date")
  strDtEnd = qudateT("some date source for end date")


  With CurrentDb.QueryDefs("qryJobsPressPass")
     .SQL = "exec dbo.uspJobsCompletedThruPress " & strDtStart & "," & strDtEnd
  End With

  With CurrentDb.QueryDefs("qryJobsPressPassSub")
     .SQL = "exec dbo.uspJobsCompletedThruPress_SubReport " & strDtStart & "," & strDtEnd
  End With

  ' now launch your report.



End Sub
Public Function qudateSQLt(myDate As Variant) As String

  ' returns a formatted string of date, surrounded with # signs
  ' format is mm/dd/yyyy as required for sql, regardless of date setting

  If IsNull(myDate) = True Then
     qudateSQLt = ""
  Else
     ' use ISO date format
     qudateSQLt = "'" & Format(myDate, "yyyy-mm-dd HH:NN:SS") & "'"
  End If

End Function
Teradata - Calculate the previous quarter date start date and end date from current date

Teradata - Calculate the previous quarter date start date and end date from current date


By : Tien Lanh Manh
Date : September 22 2020, 05:00 AM
Does that help You should be able to do this using the TRUNC function, something like:
code :
SELECT 
  TRUNC(ADD_MONTHS(CURRENT_DATE, -3), 'Q') AS Start_Quarter, -- Previous quarter start
  TRUNC(CURRENT_DATE, 'Q') - 1 AS End_Quarter -- Current quarter start date - 1 day
SELECT 
  TD_QUARTER_BEGIN(CURRENT_DATE) AS Start_Quarter,
  TD_QUARTER_END(CURRENT_DATE) AS End_Quarter
Related Posts Related Posts :
  • How to change datetime format of varchar datatype column
  • Trigger on extended properties? SQL SERVER
  • Database Schema guidance for an app store
  • sql trigger to update based on specific column is updated and if value is equal to 'x'
  • How to descope a local variable in t-sql
  • SQL Server Compact stored procedure with multiple values in 1 input
  • How to validate the ICCID number of a SIM card in TSQL?
  • How to create view listing all friendship for "friend1,friend2" table in T-SQL and prevent inserting duplicate
  • Sum of multiple column in SQL Server
  • Check month and day lies between two dates
  • How would I configure Effort Testing Tool to mock Entity Framework's DbContext withOut the actual SQL Server Database up
  • While looping through a function, Error: Maximum stored procedure, function, trigger, or view nesting level exceeded (li
  • Collapsing records with adjacent start and end dates
  • SQL Server Management Studio 2008 - Timeout Expired error
  • Insert date from VB to SQL Server
  • How to find what table foreign key is from?
  • Sql Server Nested Inserts possible
  • Deterministic way of getting WeekEnding (Sun) for given date column (for use in computed column)
  • SQL Server, why does one order by query work with a string, but not with an integer?
  • How do I install a SQL Server on the hosted build agent in Visual Studio Online?
  • EXECUTE AS/REVERT within a transaction in SQL Server
  • Distinct 2 field AS one column SQL SERVER
  • how to create a new database after a trigger fired from a table
  • How to sort string alphabetically
  • Checking data in one column in a temp table to a column in another table
  • What's wrong with my IF/ELSE? "ELSE: Incorrect syntax near 'ELSE'."
  • CASE CLAUSE IN WHERE CONDITION
  • TSQL - Creating a running total of a column
  • Why am I getting not null here?
  • Why does sp_refreshview raise an exception on views that are not schema bound?
  • EXEC parameters from a SELECT
  • SQL Server Compact connection
  • Star Schema Structure - To many Dimensions
  • sort utf string in sql server with join clause
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org