February 12, 2010 at 8:52 pm
Hi i have two tables Periods and IncomeStatement and I need to found out for each month whether all accounts in department have transactions are not if not found i need to
insert credit and debit values as '0'
I tried with cursor i am able to do row by row processing w.r.t to periods. But also i need to check for unique combination of dept,acct
for example say dept 1 and acct 1
it should have transactions for all periods(jan to dec).
Please help me in this. Here i am giving schema of tables.
Create Table #Periods
( Id int,
Period datetime
)
INSERT INTO #Periods(Id,Period)
SELECT '1','1/31/09' UNION ALL
SELECT '2','2/28/09' UNION ALL
SELECT '3','3/30/09' UNION ALL
SELECT '4','4/31/09' UNION ALL
SELECT '5','5/30/09' UNION ALL
SELECT '6','6/30/09' UNION ALL
SELECT '7','7/31/09' UNION ALL
SELECT '8','8/31/09' UNION ALL
SELECT '9','9/30/09' UNION ALL
SELECT '10','10/31/09' UNION ALL
SELECT '11','11/30/09' UNION ALL
SELECT '12','12/31/09' UNION ALL
CREATE TABLE #IncomeStatement
(
Dept INTEGER,
Acct INTEGER,
Date SMALLDATETIME,
Credit DECIMAL(9,2),
Debit DECIMAL(9,2)
)
INSERT INTO #IncomeStatement (Dept,Acct,Date,Credit,Debit,OpeningBalance,EndingBalance)
SELECT '1','1','2/2/09','100','50' UNION ALL
SELECT '1','1','3/4/09','150','120' UNION ALL
SELECT '1','1','4/6/09','280','120' UNION ALL
SELECT '2','1','2/2/09','300','130' UNION ALL
SELECT '2','1','3/3/09','200','110' UNION ALL
SELECT '2','2','2/5/09','200','80' UNION ALL
SELECT '2','2','2/7/09','200','80'
Thanks in advance
Surya
February 13, 2010 at 2:29 am
Please edit and correct your sample data script. There's a UNION ALL on the last SELECT for #periods, when this is corrected there are further errors:
Msg 207, Level 16, State 1, Line 33
Invalid column name 'OpeningBalance'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'EndingBalance'.
Msg 120, Level 15, State 1, Line 33
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 13, 2010 at 3:44 am
It's charity Saturday so I took the time to clean up the sample data you provided...
Additionally to the errors Chris already mentioned: there is no '4/31/09'. At least not in my calendar...
For the next time please test your sample data before posting. It would help both of us: we don't have to start with bad data just to figure out they won't work and you would have a faster answer.
And here's what I came up with (@ Chris: yes, a bunch of CTE's again :-D)
Create Table #Periods ( Id int, Period datetime)
INSERT INTO #Periods(Id,Period)
SELECT '1','20090131' UNION ALL
SELECT '2','20090228' UNION ALL
SELECT '3','20090331' UNION ALL
SELECT '4','20090430' UNION ALL
SELECT '5','20090531' UNION ALL
SELECT '6','20090630' UNION ALL
SELECT '7','20090731' UNION ALL
SELECT '8','20090831' UNION ALL
SELECT '9','20090930' UNION ALL
SELECT '10','20091031' UNION ALL
SELECT '11','20091130' UNION ALL
SELECT '12','20091231'
CREATE TABLE #IncomeStatement
(
Dept INTEGER,
Acct INTEGER,
Date SMALLDATETIME,
Credit DECIMAL(9,2),
Debit DECIMAL(9,2)
)
INSERT INTO #IncomeStatement (Dept,Acct,Date,Credit,Debit)
SELECT '1','1','20090202','100','50' UNION ALL
SELECT '1','1','20090304','150','120' UNION ALL
SELECT '1','1','20090406','280','120' UNION ALL
SELECT '2','1','20090202','300','130' UNION ALL
SELECT '2','1','20090303','200','110' UNION ALL
SELECT '2','2','20090205','200','80' UNION ALL
SELECT '2','2','20090207','200','80'
;WITH cte AS -- get dept,acct including last day of the month per entry (grouped) from #IncomeStatement
(
SELECT dept,acct,
DATEADD(mm, DATEDIFF(mm, 0, DATE) + 1, 0)-1 AS mo -- Last day of month
FROM #IncomeStatement
GROUP BY dept,acct,DATEADD(mm, DATEDIFF(mm, 0, DATE) + 1, 0)-1
),
cte3 AS -- get distinct dept,acct values and assign each dept,acct value to the values in #Periods
(
SELECT DISTINCT dept,acct,period
FROM #IncomeStatement
CROSS APPLY
(SELECT * FROM #Periods)p
)
-- finally, insert all missing values into #IncomeStatement
INSERT INTO #IncomeStatement (Dept,Acct,DATE,Credit,Debit)
SELECT cte3.dept,cte3.acct,cte3.Period,0,0
FROM cte3
LEFT OUTER JOIN cte
ON cte3.dept = cte.dept
AND cte3.acct = cte.acct
AND cte3.Period= cte.mo
WHERE cte.mo IS NULL
SELECT *
FROM #IncomeStatement
ORDER BY dept,acct,DATE
Edit: code revised.
February 13, 2010 at 5:14 am
lmu92 (2/13/2010)
And here's what I came up with (@ Chris: yes, a bunch of CTE's again :-D)
Who needs CTE's? 😛
SELECT p.PeriodStart, p.PeriodEnd, da.Dept, da.Acct,
ISNULL(s.[Date], p.PeriodEnd), ISNULL(s.Credit, 0) AS Credit, ISNULL(s.Debit, 0) AS Debit
FROM (
SELECT p1.Id, p1.Period AS PeriodStart, p2.Period AS PeriodEnd
FROM #Periods p1
LEFT JOIN #Periods p2 ON p2.Id = p1.Id+1) p
CROSS JOIN (SELECT Dept, Acct FROM #IncomeStatement GROUP BY Dept, Acct) da
LEFT JOIN #IncomeStatement s
ON s.Dept = da.Dept and s.Acct = da.Acct AND s.[Date] >= p.PeriodStart AND s.[Date] < p.PeriodEnd
ORDER BY p.PeriodStart, da.Dept, da.Acct
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 13, 2010 at 5:25 am
surya-434952 (2/12/2010)
Hi i have two tables Periods
A single date doesn't constitute a period. I've assumed that a "period" is represented (in the periods table) as follows:
startdate is the date from any row and is inclusive for processing
enddate is taken as the lowest date from another row which is greater than the startdate - as it is the startdate for the next period, then enddate processed appropriately i.e. exclusive.
Since the ID's in the periods table appear to be aligned exactly with dates, they were used to fetch date pairs equating to periods.
There are quite a few assumptions here :sick:
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 13, 2010 at 10:24 am
My Sincere apologies for my faulty sample data . I am thankful to you all for your valuable time to provide me solution. I will give try for the solutions for provided and give my feedback
Thanks,
Surya
February 13, 2010 at 11:41 am
ChrisM@home (2/13/2010)
lmu92 (2/13/2010)
And here's what I came up with (@ Chris: yes, a bunch of CTE's again :-D)Who needs CTE's? 😛
Maybe someone who looks at execution plans and wonder why the "non-CTE" solution is significantly slower... (which should be tested against much larger set of data with proper indexing for verification, of course...) 😉
February 13, 2010 at 11:54 am
lmu92 (2/13/2010)
ChrisM@home (2/13/2010)
lmu92 (2/13/2010)
And here's what I came up with (@ Chris: yes, a bunch of CTE's again :-D)Who needs CTE's? 😛
Maybe someone who looks at execution plans and wonder why the "non-CTE" solution is significantly slower... (which should be tested against much larger set of data with proper indexing for verification, of course...) 😉
Heh nice work Lutz!
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 13, 2010 at 4:13 pm
Lutz,
I'm possibly missing the bubble here... why wouldn't the following two entries be added together for a single "period"?
SELECT '2','2','20090205','200','80' UNION ALL
SELECT '2','2','20090207','200','80'
--Jeff Moden
February 13, 2010 at 5:04 pm
Jeff Moden (2/13/2010)
Lutz,I'm possibly missing the bubble here... why wouldn't the following two entries be added together for a single "period"?
SELECT '2','2','20090205','200','80' UNION ALL
SELECT '2','2','20090207','200','80'
I'm not the OP so I can only guess (like we all do...).
My assumption is based on the OPs statement
I need to found out for each month whether all accounts in department have transactions are not if not found i need to insert credit and debit values as '0'
I thought it's not a question about "how many / how much" but more "does it exist or not"... (assuming that "have transactions are not" would have to be read as "have transactions or not"). To me it looks like filling gaps in a table rather than building a report.
We need the OP for clarification.
February 13, 2010 at 5:23 pm
surya-434952 (2/12/2010)
... i need to insert credit and debit values as '0'
That's a bit of an ambiguous statement as it is... WHERE do you want to insert the zero values? In just the result set or actually in the statement table? Please clarify.
--Jeff Moden
February 13, 2010 at 8:44 pm
Yeah the assumption is correct. I need to calculate if a transaction either credit or debit exists for each account in the department
February 13, 2010 at 8:49 pm
I need to insert into #IncomeStatement if a account in a department does not have any entries for a month both credit and debit as '0'
February 13, 2010 at 10:26 pm
Heh... looks like Lutz was right.
In that case, we can keep this really short...
;
WITH cteAllDates AS
(
SELECT s.Dept, s.Acct, DATEADD(mm,DATEDIFF(mm,0,p.Period),0) AS PeriodStart, p.Period, 0 AS Credit, 0 AS Debit
FROM (SELECT DISTINCT Dept, Acct FROM #IncomeStatement) s
CROSS JOIN #Periods p
)
INSERT INTO #IncomeStatement
(Dept, Acct, Date, Credit, Debit)
SELECT Dept, Acct, Period AS Date, Credit, Debit
FROM cteAllDates ad
WHERE NOT EXISTS (SELECT 1 FROM #IncomeStatement s
WHERE s.Dept = ad.Dept
AND s.Acct = ad.Acct
AND s.Date >= ad.PeriodStart AND s.Date <= ad.Period)
--Jeff Moden
February 14, 2010 at 2:39 am
Looks like I'm getting too addicted to CTEs... 😛
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply