Solved

SQL QTD BETWEN TWO @START AND @END DATE RANGES

Posted on 2015-01-07
19
227 Views
Last Modified: 2015-01-08
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
0
Comment
Question by:barkome
  • 9
  • 6
  • 4
19 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40536471
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)
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40536618
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40536778
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'
0
 

Author Comment

by:barkome
ID: 40536823
Hi Paul

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

Author Comment

by:barkome
ID: 40537201
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)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40537446
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"
0
 

Author Comment

by:barkome
ID: 40537448
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
0
 

Author Comment

by:barkome
ID: 40537542
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40537645
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
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:barkome
ID: 40537650
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40538116
>> 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 :-).
0
 

Author Comment

by:barkome
ID: 40538141
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
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40538312
>> 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)??
0
 

Author Comment

by:barkome
ID: 40538342
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
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40538647
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40538694
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
0
 

Author Comment

by:barkome
ID: 40538929
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
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40539113
;WITH cte_qtr_bals AS (
     SELECT
         DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0) AS QTD_SD,
         LDGRID,
         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')
     GROUP BY DATEADD(QUARTER, DATEDIFF(QUARTER, 0, ORDER_DATE), 0), LDGRID
 )
 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)
                       AND cqb.LDGRID = L.LDGRID
                    ) 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
0
 

Author Closing Comment

by:barkome
ID: 40539584
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...
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now