Solved

CTE SQL Query

Posted on 2014-02-27
15
423 Views
Last Modified: 2014-03-03
Hi Experts,
I have attached an xl file. I require the output described in the sheet using a CTE
Book1.xlsx
0
Comment
Question by:vikas_nm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
15 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39892584
Hi Vikas,

Since the spreadsheet contains only headers, it looks like you just need the syntax.  Easy enough.

WITH MyCTE (Code, NextEffectiveType, NextEffectiveCycleDate)
AS
(
  SELECT statement
)
SELECT * FROM MyCTE


Good Luck,
Kent
0
 

Author Comment

by:vikas_nm
ID: 39892602
Sorry Kent i had attached the wrong file. here is the correct one
cte.xlsx
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39892655
Hi Vikas,

This looks very much like classwork/homework.  As such, I'm limited by the amount of help that I can give you.  I can help, but the work has to be yours.

Where do we begin?  What don't you understand?
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:vikas_nm
ID: 39892659
Can you provide me with a CTE Query for the xl i have provided to you. I have explained the scenario in the xl sheet itself
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39892688
Hi Vikas,

I refer you to Chapter 6, item 16 (6-16) in the site's Terms of Use.

  http://www.experts-exchange.com/terms.jsp

I can help and I can teach.  But since I believe this to be an academic assignment I cannot do it for you.
0
 

Author Comment

by:vikas_nm
ID: 39892842
This is not an academic question. Seriously i am in a hurry and is a tonned down version of the bigger query.

Request you to please help me in this.

Thanks
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39893768
Well, given your spread sheet, the following will work.

However, it needs to be pointed out there are a lot of assumptions...

;with cte_balance as 
( select row_number() over (order by a.month) as rn, a.month,a.expense,b.po,a.expense+b.po as total
  from TableA a
  left outer join TableB b on a.month = b.month
)
select c.rn, c.month, c.expense
     , case when c.po is null then c.expense - p.total else c.po end as po
     , case when c.po is null then p.total else c.total end as total
from cte_balance c
left outer join cte_balance p on c.rn - 1 = p.rn 

Open in new window


Do you want to discuss your requirement in more detail, or some of the potential traps  ?

As a matter of interest, I am confused with the spreadsheet results, total column might be a running total, but taking two months into account, 25,000 + 7,000 it is very hard to get to the totals indicated. But up to you.
0
 

Author Comment

by:vikas_nm
ID: 39893816
The query works fine with one exception.

The basic calculation is as follows:

PO:
if there is a PO amount in Table B for that month it should take that amount else
it should take the previous row total amount - the expense of that row

Total:
it is the expense + po of that row.

this should happen for each row in table A.

In the query you have provided the last month is blank.

Thanks
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39893913
Here are my results from the above :

1	2013-10-01 00:00:00.000	10000.00   7000.00   17000.00
2	2013-11-01 00:00:00.000	15000.00   -2000.00   17000.00
3	2013-12-01 00:00:00.000	0.00      3000.00   3000.00

Open in new window


Not sure what you mean by "last month is blank"

Blank PO, I calculated this as "expense - last total", and can just swap that arithmetic.

Blank PO for total will always be the same as prior total. e.g
15000 + (17000 - 15000) = 17000 so that 2nd case statement is still correct.

I think all we need is the first "case" statement becomes p.total - c.expense instead of c.expense - p.total (which was a guess from your spreadsheet being a different colour).

So, that leaves "the last month is blank" comment being a mystery to me.

Maybe some more test data including current expense, but no PO, and greater than prior total ? And a few more rows ?
0
 

Author Comment

by:vikas_nm
ID: 39893929
iF you remove the record of 3000 from the table b you shall get the last row as blank means the Po and Total is coming as null.

Which should have been 17000 in both PO and Total columns because it should take the previous month total.

One more thing is that Expense column can be zero also and records in table A are not static. There will be one record for each month in Table A
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 500 total points
ID: 39894048
Riiiight... So the total is propagated until such time there is a PO to start the calcs again...

Sounds tempting to become a recursive CTE to me...

But first, let's try the outer apply (to test if you get the results and gives me thinking time on recursive CTE)

;with cte_balance as 
( select row_number() over (order by a.month) as rn, a.month,a.expense,b.po,a.expense+b.po as total
  from TableA a
  left outer join TableB b on a.month = b.month

)
select c.rn, convert(varchar,c.month, 106) as month, c.expense
     , case when c.po is null then p.total - c.expense else c.po end as po
	 , case when c.po is null then p.total else c.total end as total
from cte_balance c
outer apply (select top 1 * from cte_balance p where p.rn < c.rn and p.po is not null order by p.rn desc) p 

Open in new window



Oh, and converted date to your example data format...
0
 

Author Comment

by:vikas_nm
ID: 39894144
Everything is working. But when I delete records from table b the result is not coming. Forgot to mention records in table b are optional.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 39894220
Yep, understood they were optional, and mine works fine here.

Well, fine in so much as delete all of TableB then it still runs, just that PO and Total are NULL.

Also, tried adding a few different rows in TableA and seems to be generating the right results as far as I can tell.

So, why not prep a couple of spreadsheets with dummy data and expected results ?

And/or show me your real query :)
0
 

Author Comment

by:vikas_nm
ID: 39894252
If there are no records in table b then it should calculate like the condition when the po is null means

If no po found for that row than it will be previous row total - expense and total for that row would be expense + po
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 39897980
Well, I am beginning to think that given the increasing disclosures of difficulty, then maybe a cursor would work more flexibly - then go from row to row to do the sums / calcs.

But there is one last stab at making a CTE work for you.

Essentially, we need to create a starting point so that every other calc is equally applicable across the entire dataset.

Have a look at :

;with cte_balance as 
( select row_number() over (order by a.month) as rn, a.month,a.expense,b.po,a.expense+isnull(b.po,0) as total
  from TableA a
  left outer join TableB b on a.month = b.month
  union all
  select rn * 0,month,expense,isnull(po,0),total  -- artificial row for calculations with RN = 0
  from cte_balance
  where RN = 1
) 
select c.rn, convert(varchar,c.month, 106) as month, c.expense   --, c.c_po, c.c_total
     , case when c.po is null then p.total - c.expense else c.po end as po
	 , case when c.po is null then p.total else c.total end as total
from cte_balance c
outer apply (select top 1 total from cte_balance p where p.rn < c.rn and p.po is not null order by p.rn desc) p 
where c.rn > 0
-- maybe order by RN

Open in new window

0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 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