Solved

CTE SQL Query

Posted on 2014-02-27
15
415 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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
 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

772 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