asked on

SQL QTD BETWEN TWO @START AND @END DATE RANGES

Hiya

I im trying to determine the opening balance for calendar qtrs in a year when an end user inputs a start and end date.

What I am looking for when the user selects '01-JUL-2014' as the start date and '31-OCT-2014' the following should display

QTD_SD        LDGR_ID        QTR_#      YR QTR_OPN_BAL
2014-07-01 00:00:00.000      LDGR_ID1       3        2014 456.000
2014-07-01 00:00:00.000      LDGR_ID3       3        2014 498.000
2014-07-01 00:00:00.000      LDGR_ID4       3        2014 9856.000
2014-10-01 00:00:00.000      LDGR_ID1       4        2014 700.000
2014-10-01 00:00:00.000      LDGR_ID3       4        2014 40.000
2014-10-01 00:00:00.000      LDGR_ID4       4        2014 89000.000

Can anyone help me with what I need to do to achieve this? The syntax below is what I am using...

SELECT            DISTINCT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0) AS QTD_SD,A.LDGRID,DATEPART(QUARTER, ORDER_DATE) QTR_#,(DATEPART(YYYY,@START_DATE)) AS YR,
SUM((CASE WHEN L.TDM = '0' THEN -ISNULL(L.T_AMT,0) ELSE 0 END)) +
SUM((CASE WHEN L.TDM = '1' THEN ISNULL(L.T_AMT,0) ELSE 0 END)) QTR_OPN_BAL

FROM            LDGRACCTS A LEFT JOIN LDGRTRN L ON A.LDGRID = L.LDGRID
WHERE            A.LDGRID IN ('LDGR_ID1','LDGR_ID2','LDGR_ID3','LDGR_ID4')
AND                  DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0) BETWEEN @START_DATE AND @END_DATE

PortletPaul

What is the exact data type of [ORDER_DATE]? (date, datetime, smalldatetime etc)

If it is NOT "date" does the data include times other than 00:00:00 ?

--------
observation.

if you are listing or summing transactions between 2 dates you should never use DISTINCT

What happens if the raw data looked like this?
QTD_SD LDGR_ID QTR_# YR QTR_OPN_BAL
2014-07-01 00:00:00.000 LDGR_ID1 3 2014 456.000
2014-07-01 00:00:00.000 LDGR_ID1 3 2014 456.000
2014-07-01 00:00:00.000 LDGR_ID1 3 2014 456.000
2014-07-01 00:00:00.000 LDGR_ID1 3 2014 456.000

(you would only get one of those rows, and that would be wrong)

Scott Pletcher

The GROUP BY is missing, obviously.

Not sure what might be wrong the output, but I've adjusted the statement for performance and accuracy.

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0) AS QTD_SD,
A.LDGRID,
DATEPART(QUARTER, ORDER_DATE) QTR_#,
(DATEPART(YYYY, ORDER_DATE)) AS YR,
SUM(ISNULL(L.T_AMT, 0) * CASE WHEN L.TDM = '0' THEN -1 ELSE 1 END) AS QTR_OPN_BAL
FROM LDGRACCTS A LEFT JOIN LDGRTRN L ON A.LDGRID = L.LDGRID
WHERE A.LDGRID IN ('LDGR_ID1','LDGR_ID2','LDGR_ID3','LDGR_ID4')
AND ORDER_DATE >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @START_DATE), 0)
AND ORDER_DATE < DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @END_DATE) + 1, 0)
GROUP BY DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0),
A.LDGRID,
(DATEPART(YYYY, ORDER_DATE)),
DATEPART(QUARTER, ORDER_DATE)
ORDER BY QTD_SD,
LDGRID

PortletPaul

Will @START_DATE and @END_DATE always be the same year?

e.g.
could @START_DATE = '01-JUL-2010'
and @END_DATE = '31-OCT-2014'

barkome

ASKER

Hi Paul

In most cases yes, but having the flexibility of different years as suggested will be good. Data type is datetime

barkome

ASKER

Hi ScottPlecther

The qtr_opn_bal syntax didn't display the accurate figure.

The opening qtr balance should be the sum of all dr's and cr's prior to a particular date. I should see the qtr_opn_bal for 2014-07-01 00:00:00.000 and 2014-10-01 00:00:00.000 for each of the Ldgr_id's within both the @start_date and @end_date.

ORDER_DATE <= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @START_DATE), 0) OR ORDER_DATE <= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @END_DATE), 0)

PortletPaul

As you are seeking "opening" balance you have to bring forward the balance of the prior quarter. This would be easier in SQL 2012 or later where LEAD() is supported. But, as you have indicated SQL 2005/8 I have used a CTE approach in lieu so that we can join to the prior quarter using a number generated by ROW_NUMBER().

WITH BASIS1
AS (
      SELECT
            DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0) AS QTD_SD
          , A.LDGRID
          , DATEPART(QUARTER, ORDER_DATE) QTR_#
          , DATEPART(YYYY, ORDER_DATE) AS YR
          , SUM(ISNULL(L.T_AMT, 0) *
                                    CASE
                                          WHEN L.TDM = '0' THEN -1
                                          ELSE 1
                                    END) AS QTR_BAL
      FROM LDGRACCTS A
            LEFT JOIN LDGRTRN L ON A.LDGRID = L.LDGRID
      WHERE A.LDGRID IN ('LDGR_ID1', 'LDGR_ID2', 'LDGR_ID3', 'LDGR_ID4')
            AND ORDER_DATE >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @START_DATE) - 1, 0)
            AND ORDER_DATE < DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @END_DATE) + 1, 0)
      GROUP BY
            DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0)
          , A.LDGRID
          , DATEPART(YYYY, ORDER_DATE)
          , DATEPART(QUARTER, ORDER_DATE)
      ),
BASIS
AS (
      SELECT
            *
          , ROW_NUMBER() OVER (PARTITION BY LDGRID ORDER BY YR ASC, QTR_# ASC) AS RN
      FROM BASIS
)
SELECT
            A.QTD_SD
          , A.LDGRID
          , A.QTR_#
          , A.YR
          , B.QTR_BAL AS QTR_OPN_BAL
FROM BASIS AS A
INNER JOIN BASIS AS B ON A.RN = B.RN+1 AND A.LDGRID = B.LDGRID
ORDER BY
        A.YR
      , A.QTD_SD
      , A.LDGRID

Open in new window

e.g. for a generated row number of 2 in "A" that will join to the generated row_number of 1 in "B" which is the prior quarter.

In addition to joining to a prior qurter, the date range is also expanded to include an additional quarter.
AND ORDER_DATE >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @START_DATE) - 1, 0)
AND ORDER_DATE < DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @END_DATE) + 1, 0)

Please note how this approach leaves ORDER_DATE unaffected by any functions (making it a "sargable" approach)

also see: "Beware of Between"

barkome

ASKER

Thanks PortletPaul

Im actually using 2014 (or is it 2012? SSMS says 2014), but will try what you just suggested...and get back to you in about 10min

barkome

ASKER

Hi PortletPaul

Something doesn't seem right. Below is the DDL:

USE [test]
GO
DROP TABLE [dbo].[named_query_table]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[named_query_table](
	[A_LDGR_ID] [nvarchar](255) NULL,
	[L_ORDER_DATE] [datetime] NULL,
	[L_TDM] [float] NULL,
	[L_T_AMT] [float] NULL
) ON [PRIMARY]

GO

Open in new window

I have attached the desired output and sample data from 1st May to 31st October 2014. Please see the attached spreadsheet. Maybe this will provide more insight for experts to help me out here
SSC.xlsx

PortletPaul

how is [dbo].[named_query_table] relevant to a query using these tables?

FROM LDGRACCTS A
LEFT JOIN LDGRTRN L ON A.LDGRID = L.LDGRID

barkome

ASKER

Hi PortletPaul

It's not relevant at all. Instead of pulling sample data from both ldgr acct and ldgr trans tables, I used the left join syntax to create a named qery and import the data set into the [dbo].[named_query_table] to produce the DDL statement.
The only field from ldgr acct table is the LDGRID.

Scott Pletcher

>> The opening qtr balance should be the sum of all dr's and cr's prior to a particular date. <<

Starting from what date? Just the previous qtr or previous year(s)?

If you need these balances at all often, I suggest creating a separate table to store the qtrly starting balances.

Q: In the original query, from which table does "ORDER_DATE" come? A good tip for writing SQL: always indicate every column's source table when more than one table is used in a query :-).

barkome

ASKER

Hi ScottPlether

The starting date will be the first day of a particular qtr.

How do I create a separate table qtry starting balances if reference the ldgr transaction table with more than one date record for each ldgr ID?

Order Date comes from the ldgr transaction table - LDGRTRN L

The only column that come from the ldgr accts header table is A.LDGRID
table alias of L = ldgr transaction table
table alias of A = ldgr accts header table

Scott Pletcher

>> The starting date will be the first day of a particular qtr. <<

Not to get the starting balance for that qtr, right? That doesn't make any sense.

For this row:
2014-07-01 00:00:00.000 LDGR_ID1 3 2014

i.e. for QTR3 of 2014, how do I get the starting total? Add up the credits and debits for (just) the previous qtr? Or add up credits and debits for that entire yr, i.e., back to the start of qtr1? That seems the most logical. And the "starting balance" for q1 is 0 or is it the sum of all credits and debits for the prior year or qtr??!

However, if there's "carry-over" from year to year, I might even have to back to an additional year(s)??

barkome

ASKER

That's right ScottPletcher

Add up credits and debits from the earliest transaction.

And the "starting balance" for q1 will be the sum of all credits and debits from the previous year/first transaction recorded.

Its basically the opening balance according to finance general ledger principles.

SELECT SUM(ISNULL(L.T_AMT, 0) * CASE WHEN L.TDM = '0' THEN -1 ELSE 1 END) AS QTR_OPN_BAL
FROM LDGRACCTS A LEFT JOIN LDGRTRN L ON A.LDGRID = L.LDGRID
WHERE A.LDGRID IN ('LDGR_ID1','LDGR_ID2','LDGR_ID3','LDGR_ID4')
AND ORDER_DATE <= THE_FIRST_DAY_OF_A_QTR

Scott Pletcher

SELECT SUM(ISNULL(L.T_AMT, 0) * CASE WHEN L.TDM = '0' THEN -1 ELSE 1 END) AS QTR_OPN_BAL
FROM LDGRACCTS A LEFT JOIN LDGRTRN L ON A.LDGRID = L.LDGRID
WHERE A.LDGRID IN ('LDGR_ID1','LDGR_ID2','LDGR_ID3','LDGR_ID4')
AND L.ORDER_DATE <= THE_FIRST_DAY_OF_A_QTR

One last technical point: I believe that should be "<" rather than "<=", right ;-) ?

I'd create a separate table because I think the starting totals would be static after a certain point.

However, for now, without more details about your specific situation, we'll probably just have to loop to generate a starting_totals table from scratch.

Scott Pletcher

Try this and see if we can get the correct opening balances ... then we can worry about tuning it if necessary.

;WITH cte_qtr_bals AS (
SELECT
DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0) AS QTD_SD,
SUM(ISNULL(T_AMT, 0) * CASE WHEN TDM = '0' THEN -1 ELSE 1 END) AS QTR_BAL
FROM LDGRTRN
WHERE LDGRID IN ('LDGR_ID1','LDGR_ID2','LDGR_ID3','LDGR_ID4')
)
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, L.ORDER_DATE), 0) AS QTD_SD,
A.LDGRID,
DATEPART(QUARTER, ORDER_DATE) QTR_#,
(DATEPART(YYYY, ORDER_DATE)) AS YR,
(SELECT SUM(QTR_BAL)
FROM cte_qtr_bals cqb
WHERE cqb.QTD_SD < DATEADD(QUARTER, DATEDIFF(QUARTER, 0, L.ORDER_DATE), 0)) AS QTR_OPN_BAL
FROM LDGRACCTS A LEFT JOIN LDGRTRN L ON A.LDGRID = L.LDGRID
WHERE A.LDGRID IN ('LDGR_ID1','LDGR_ID2','LDGR_ID3','LDGR_ID4')
AND L.ORDER_DATE >= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @START_DATE), 0)
AND L.ORDER_DATE < DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @END_DATE) + 1, 0)
GROUP BY DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0),
A.LDGRID,
(DATEPART(YYYY, ORDER_DATE)),
DATEPART(QUARTER, ORDER_DATE)
ORDER BY QTD_SD,
LDGRID

barkome

ASKER

Hi Scott

We're getting there.

When I executed the syntax as it is, I get the following error message:

Msg 8120, Level 16, State 1, Line 8
Column 'L.ORDER_DATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

After modifying the following portion of the syntax to:

GROUP BY DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0)

Open in new window

The expected format appears, but the qtr_opn_bal repeats its self for each of ldgr id's, like a Cartesian product

However, when I limit the ldgr id's on line 6 and 17 to one ldgr id, the report is100% accurate with the ldgr id qtr_opn_bal for each qtr_start_date

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

barkome

ASKER

A big thanks to ScottPletcher...

This solution gave me the foundation to build a robust report for my end users, giving them more than they thought they could have...