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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply