January 8, 2015 at 1:06 am
The opening qtr balance is the be the sum of all dr's and cr's prior to a particular date. I want to 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.
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? I have a funny feeling the order date syntax is an issue...
ORDER_DATE <= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @START_DATE), 0) OR ORDER_DATE <= DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @END_DATE), 0)
January 8, 2015 at 1:13 am
I think that this query is complex enough to warrant a request for sample DDL, data and desired results. I've reread it a couple of times and still don't fully understand what you are after.
You've been here long enough to know the drill.
January 8, 2015 at 2:20 am
Hi Phill
Thanks for getting back, DDL is:
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
while the syntax i'm using to generate the report is:
DECLARE @START_DATE DATE
SET @START_DATE = '01-JUL-14'
DECLARE @END_DATE DATE
SET @END_DATE = '31-OCT-14'
SELECT
DATEADD (QUARTER, DATEDIFF (QUARTER, 0, L.ORDER_DATE), 0) AS QTD_SD,
A.LDGR_ID, DATEPART (QUARTER, L.ORDER_DATE) QTR_#, (DATEPART (YYYY, L.ORDER_DATE)) AS YR,
SUM (ISNULL (L.T_AMT, 0) * CASE WHEN L.TDM= '0' THEN -1 ELSE 1 END) AS QTR_OPN_BAL
FROMLDGRACCTS A LEFT JOIN LDGRTRN L ON A.LDGRID = L.LDGRID
WHERE A.LDGRID IN ('LDGR_ID1','LDGR_ID2','LDGR_ID3','LDGR_ID4')
ANDL.ORDER_DATE >= DATEADD (QUARTER, DATEDIFF (QUARTER, 0, @START_DATE), 0) AND L.ORDER_DATE < DATEADD (QUARTER, DATEDIFF (QUARTER, 0, @END_DATE) + 1, 0)
GROUP BYDATEADD (QUARTER, DATEDIFF (QUARTER, 0, L.ORDER_DATE), 0), A.LDGR_ID, (DATEPART (YYYY, L.ORDER_DATE)), DATEPART (QUARTER, L.ORDER_DATE)
ORDER BYQTD_SD, A.LDGR_ID
I have attached the desired output and sample data from 1st May to 31st October 2014. Please see the attached spreadsheet.
January 8, 2015 at 5:47 am
Can you explain how one of the opening balance results is calculated please, from the sample data?
Eg, you have -449999.60 for (LDGR_ID1, 2014-10-01, 4).
January 8, 2015 at 6:26 am
Hi Phill
Sure, its calculated with the following syntax:
SUM (ISNULL (L.T_AMT, 0) * CASE WHEN L.TDM= '0' THEN -1 ELSE 1 END) AS QTR_OPN_BAL
And its basically the sum of all debit and credit amounts at the beginning of an/current accounting period being queried (technically the closing balance for the previous accounting period).
QTR_OPN_BAL = SUM(-DEBITS + CREDIT)
WHERE ORDER_DATE <= THE_FIRST_DAY_OF_A_QTR
Debit transactions are indicated with '0' while Credit transactions are indicated with '1' from the L.TDM column.
The sample data will not provide the exact figure of -449999.6000, as it does not include all debit and credit transactions from inception/the entire ledger transactions table.
January 9, 2015 at 2:39 pm
Just in case anyone has a similar problem to this, I'm sharing the solution that was provided by ScottPlecther with the help of a CTE. Once again many thanks to Scott, your a star!!!
;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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy