Solved

CTE SQL Query

Posted on 2014-02-27
15
409 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
  • 7
  • 5
  • 3
15 Comments
 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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
 

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:Kdo
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
t-sql look for records in two tables 2 21
Write a function 5 26
SQL Select Query help 3 32
Delete Trigger in SQL Server2008R2 5 13
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now