1

I have 3 queries...

Query 1 Gives the Opening Balance at the Start of the Month

SELECT AccountBalance As 'OpenBalance', MONTHNAME(DateTime) as 'Month', MIN(DateTime) as 'Open'
FROM Table1
GROUP BY MONTHNAME(DateTime)

Query 2 gives the Closing Balance at the end of the Month

SELECT
   ClosingBalance,
   Month,
   DateTime
 FROM (
   SELECT 
       ClosingBal As 'ClosingBalance', 
       MONTHNAME(DateTime) as 'Month', 
       DateTime,
       ROW_NUMBER() OVER (
           PARTITION BY DATE_FORMAT('month', DateTime)
           ORDER BY DateTime DESC) as priority_row
   FROM 
       Table1) t1
   GROUP BY MONTHNAME(DateTime)

Query 3 gives the Max and Min Balances from each month

SELECT MIN(ClosingBal) As 'MinBalance', MAX(ClosingBal) As 'MaxBalance', 

MONTHNAME(DateTime) as 'Month' FROM Table 1 GROUP BY MONTHNAME(DateTime)

I am looking for a way to combine into 1 query in ordr to use this to form a chart.

I have tried, with no joy, the following

SELECT * FROM
(
SELECT AccountBalance As 'OpenBalance', MONTHNAME(DateTime) as 'Month', MIN(DateTime) as 'Open'
FROM Table1 t1
GROUP BY MONTHNAME(DateTime)
)
,
(
SELECT
   ClosingBalance,
   Month,
   DateTime
 FROM (
   SELECT 
       ClosingBal As 'ClosingBalance', 
       MONTHNAME(DateTime) as 'Month', 
       DateTime,
       ROW_NUMBER() OVER (
           PARTITION BY DATE_FORMAT('month', DateTime)
           ORDER BY DateTime DESC) as priority_row
   FROM 
       Table1) t1
   GROUP BY MONTHNAME(DateTime)
)
,
(
SELECT MIN(ClosingBal) As 'MinBalance', MAX(ClosingBal) As 'MaxBalance', MONTHNAME(DateTime) as 'Month'
FROM Table1 t1
GROUP BY MONTHNAME(DateTime)
)

Example Table

id DateTime AccountBalance ClosingBal
1 30/01/2023 13:00 1000 1200
2 30/01/2023 14:00 1200 1100
3 31/01/2023 15:00 1100 1500
4 31/01/2023 16:00 1500 1300
5 01/02/2023 13:00 1300 1900
6 02/02/2023 13:00 1900 1700
7 02/02/2023 14:00 1700 1600
8 03/02/2023 16:00 1600 1800

As you will see from the above table... the closing balance is then used as the starting balance on the next row.

The intended table I am looking for is:

Month OpeningBalance ClosingBalance Max Balance MinBalance
January 1000 (id - 1) 1300 (id - 4) 1500 (id - 3) 1100 (id - 2)
February 1300 (id - 5) 1800 (id = 8) 1900 (id - 5) 1600 (id - 7)

I have put the id from the above table next to the intended result.

Opening balance filtered in the query using the AAccountBalance the rest (max,min,closing) I am using the ClosingBal

4
  • Your query creates a cartesian join between the three statements, meaning all rows from one statement are combined with all rows from the second table and those are then combined with all rows from the third. Could you please add some example data and the table you would be expecting as outcome. Thanks!
    – Grimaldi
    Feb 4, 2023 at 20:44
  • Will this be a "candlestick" of stock prices -- open, close, hi, lo? If so, I suggest you build another table what just that info. It will be messy to get open and close repeatedly. See the tag I added.
    – Rick James
    Feb 4, 2023 at 20:49
  • @RickJames Kind of... I have a journal which tracks my trades I am looking to create a candle stick chart which extracts certain data from my jornal. The data being what is mentioned above. Starting/Closing/Max/Min balance of each month I had planned on plotting as a candlestick chart using Fusion Charts (I thought Candlestick could be good because of the intended use.) Feb 4, 2023 at 21:02
  • @Grimaldi I have added a bit to the question, does that explain a bit better? Feb 4, 2023 at 21:21

2 Answers 2

0

Use:

Select monthname(datetime) as month
     , min(datetime) as OpeningDate
     , max(datetime) as ClosingDate 
  from table1
 Group by monthname(datetime)

As driving table to select the respective values for OpeningBalance and ClosingBalance by joining two times to your table then join on month to

select monthname(datetime) as month
     , max(…) as …
  from table1
 group by monthname(datetime)

Provided there‘s only one row per datetime value.

0
SELECT  MinBalance, MaxBalance, 
        ( SELECT  MIN(OpenBal)
            FROM  Table1
            WHERE  DateTime = t2.OpenTime 
        ) AS OpenBal, 
        ( SELECT  MAX(ClosingBal)
            FROM  Table1
            WHERE  DateTime = t2.CloseTime 
        ) AS CloseBal
    FROM  
        ( SELECT  MIN(ClosingBal) AS 'MinBalance',
                  MAX(ClosingBal) As 'MaxBalance',
                  MIN(DateTime) AS OpenTime, 
                  MAX(DateTime) AS CloseTime
            FROM  Table1
            GROUP BY  LEFT(DateTime, 7) 
        ) AS t2 

Note that GROUP BY MONTH(...) messes up if the data has more than one year. The LEFT(...) is a quick-and-dirty way to get both the year and month.

3
  • Thanks Rick, the one thing that this doesnt display is the month, it groups by month but doesnt display the month on the table. Is there a way to achieve this? I have tried adding MONTHNAME(DateTime) as 'Month', to the query but it doesnt seem to like it, regardless of where I place it Feb 5, 2023 at 16:13
  • 1
    Sorted it... I added - SELECT Month, MinBalance, MaxBalance, (Month) to the top and also added MONTHNAME(DateTime) AS Month to ( SELECT MIN(ClosingBal) AS 'MinBalance', MAX(ClosingBal) As 'MaxBalance', MIN(DateTime) AS OpenTime, MAX(DateTime) AS CloseTime, MONTHNAME(DateTime) AS Month... Thanks again for your help Feb 5, 2023 at 16:26
  • (Still has the issue of what to do if two different Januarys are in the dataset.)
    – Rick James
    Feb 5, 2023 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.