July 14, 2013 at 10:06 am
Hi,
Is that possible to write in single query to get the below results -
I need to get the month wise records from the transaction table. If records for that month is not present, then previous month set of common columns values to be created for current month (Company, Customer ID, Account No, Type) with previous month amount as opening balance and current amount as 0 and closing balance as opening balance.
thanks
July 14, 2013 at 10:10 am
s_v_narayanan (7/14/2013)
Hi,Is that possible to write in single query to get the below results -
I need to get the month wise records from the transaction table. If records for that month is not present, then previous month set of common columns values to be created for current month (Company, Customer ID, Account No, Type) with previous month amount as opening balance and current amount as 0 and closing balance as opening balance.
thanks
suggest you post some sample data (create table / insert script)...plenty of examples on the forum
and expected results based on the data you provide
read up on calendar tables...may be useful to you.
regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 17, 2013 at 11:31 am
Thanks for the reply.
Please find below the sample and expected output -
CompanyCustomer IDAccount IDAmountTransaction Date
100 C001 A001 100 10/01/2013
100 C001 A001 200 10/04/2013
100 C001 A001 250 10/05/2013
100 C001 A001 100 10/07/2013
Output should be -
CompanyCustomer IDAccount IDMonth Opening Balance Amount Closing Balance
100 C001 A001 01 0 100 100
100 C001 A001 02 100 0 100
100 C001 A001 03 100 0 100
100 C001 A001 04 100 200 300
100 C001 A001 05 300 250 550
100 C001 A001 06 550 0 550
100 C001 A001 07 550 100 650
100 C001 A001 08 650 0 650
100 C001 A001 09 650 0 650
100 C001 A001 10 650 0 650
100 C001 A001 11 650 0 650
100 C001 A001 12 650 0 650
Thanks
July 17, 2013 at 1:06 pm
s_v_narayanan (7/17/2013)
Thanks for the reply.Please find below the sample and expected output -
CompanyCustomer IDAccount IDAmountTransaction Date
100 C001 A001 100 10/01/2013
100 C001 A001 200 10/04/2013
100 C001 A001 250 10/05/2013
100 C001 A001 100 10/07/2013
Output should be -
CompanyCustomer IDAccount IDMonth Opening Balance Amount Closing Balance
100 C001 A001 01 0 100 100
100 C001 A001 02 100 0 100
100 C001 A001 03 100 0 100
100 C001 A001 04 100 200 300
100 C001 A001 05 300 250 550
100 C001 A001 06 550 0 550
100 C001 A001 07 550 100 650
100 C001 A001 08 650 0 650
100 C001 A001 09 650 0 650
100 C001 A001 10 650 0 650
100 C001 A001 11 650 0 650
100 C001 A001 12 650 0 650
Thanks
It is very unclear what you are trying to do here. All of your dates are in the same month...oh wait you probably are used to dmy format. I honestly didn't realize the format issue until I was typing. This is why we want to see more details. What would really help is to turn this into ddl and insert statements. This sort of looks like a running total issue? You will also need a tally table to make this work.
Please take a few minutes to read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 17, 2013 at 2:12 pm
This might not be the best way to achieve what you're looking for and might need some changes to adjust to your real data. However, this can be a start. I wouldn't advise to use it on long periods because recursive CTEs won't scale well on many recursions, but a year seems safe.
CREATE TABLE #TEST(
Company int,
Customer_ID char(4),
Account_ID char(4),
Amount int,
Transaction_Date date)
INSERT #TEST VALUES
(100, 'C001', 'A001', 100, '20130110'),
(100, 'C001', 'A001', 200, '20130410'),
(100, 'C001', 'A001', 250, '20130510'),
(100, 'C001', 'A001', 100, '20130710');
WITH Months AS(
SELECT month
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))x(month)
),
CTE AS(
SELECT *
FROM Months m
LEFT
JOIN #TEST t ON m.month = MONTH(transaction_Date)
),
rCTE AS(
SELECT a.Company,
a.Customer_ID,
a.Account_ID,
a.month,
0 AS Opening_Balance,
a.Amount,
ISNULL(a.Amount, 0) AS Closing_Balance
FROM CTE a
WHERE month = 1
UNION ALL
SELECT b.Company,
b.Customer_ID,
b.Account_ID,
a.month,
b.Closing_Balance AS Opening_Balance,
ISNULL(a.Amount,0),
b.Closing_Balance + ISNULL(a.Amount,0) AS Closing_Balance
FROM CTE a
JOIN rCTE b ON a.month = b.month + 1
)
SELECT *
FROM rCTE
DROP TABLE #Test
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply