OT - SQL help!

[Sam_2.0]

I have tried google for two days but apparently having a hard time searching. If anyone knows how to do this, can you please point me in the right direction?

Query 1 - gives me a table with 12 rows (one for each month), and 6 columns.
Query 2 - I want this to look up a value from Query 1 in order to complete a calculation.

Basically like a VLOOKUP in Excel. But with the results of one query into another. Is this possible? I only have query access to the database, not write access. So I can't save the first table into the database if that makes sense. So this will almost be like a nested query?

[Sharon]

select (look up column name)
from table 2
and exists
(select *
from table 1
where table 2 key field = table 1 keyfield)


This is a sorta quick and dirty example.  Hopefully it helps.  It is hard (at least for me) to come up with an example with out actual column/table names.

[saveinla]

Yes. You can use the query 1 like a table and get the results like you would for looking up a table - something like this

select *
from
(select x.col_name1, y.col_name2
from x, y
where x.col_name1 = y.col_name1) a
where a.col_name = 'xxx'

[wvugurl26]

I searched Google for nested SQL query. The first hit had some good rules. I'm sure it can be done. My SQL is a little rusty right now.

[Sam_2.0]

Thanks!! I just needed to get the right terms to give me what I wanted in google Back to work!! (really hard to try & teach myself this stuff - hopefully I get the hang of it soon!)

[saveinla]

This site has some basic examples - here is one for subquery - www.techonthenet.com/oracle/subqueries.php

[wvugurl26]

Jun 3, 2015 11:38:07 GMT -5 Sam_2.0 said:

Thanks!! I just needed to get the right terms to give me what I wanted in google Back to work!! (really hard to try & teach myself this stuff - hopefully I get the hang of it soon!)

It does get easier the more you do it.

[Tiny]

Is query 1 a 'summary' from other tables?  And you are trying to build a table with 6 totals by month? 

Month, tot1, tot2, etc
01, 100, 200, etc
02, 200, 300, etc
03, 500, 20, etc

For query 1, you may need to do something like:

Select month(date you are selecting on), sum(field you want to sum), sum(fieldx+fieldy you want to sum), etc...
FROM the tables you are working with
Where  (date you are selecting on) between '01/01/2014' and '12/31/2014'
group by month(date you are selecting on)
order by month(date you are selecting on)

That gives you the month "number" - you can do alittle more magic if you need it to be the month Name OR an actual date (like the first of the month).

[Sam_2.0]

Ok, this is beyond me. Ughgh.

Table one is like this (columns):

Revenue Month

SP ID

Total Usage

Total Days

Avg Usage (total usage/total days)

adjusted average (% factor of Avg Usage

I want to pull in the results of the last column into table two:

Revenue Month

SA ID

Segment Days

Segment End Date

Quantity - here I want the adjusted average from the first query to be multiplied by the number of segment days in this query - key here is the revenue month.

With VLOOKUPS this would be easy - just look up the revenue month. But I need this all contained in one query so I can have it set up as a web tool.

[Sam_2.0]

I am pulling from 4 different tables within our database to get the info summarized in my first query. Then pulling from another table for the second.

[Sam_2.0]

Jun 3, 2015 11:41:15 GMT -5 Tiny said:

Is query 1 a 'summary' from other tables?  And you are trying to build a table with 6 totals by month? 

Month, tot1, tot2, etc
01, 100, 200, etc
02, 200, 300, etc
03, 500, 20, etc

For query 1, you may need to do something like:

Select month(date you are selecting on), sum(field you want to sum), sum(fieldx+fieldy you want to sum), etc...
FROM the tables you are working with
Where  (date you are selecting on) between '01/01/2014' and '12/31/2014'
group by month(date you are selecting on)
order by month(date you are selecting on)

That gives you the month "number" - you can do alittle more magic if you need it to be the month Name OR an actual date (like the first of the month).

This is basically how my first query is set up

[Tiny]

Jun 3, 2015 11:49:21 GMT -5Sroo said:
I often thought if my first career doesn't pan out, I'd start a consulting company to do psychological evaluations based on scripting and coding.


I find it fascinating to read different people's scripts to reach the same result.   

It's not that unusual.  There are lots of ways to accomplish a particular goal.   Also depends on what rules they are use to... I write terrible queries... but mostly it's because I've got to keep 3 sets of "rules" in my head.   A query I write for application A, might need to also be 'plugged' into application B - but the syntax is different for application B, and then there's application C that's got some sort of homegrown wack a doodle "compiler" with no documentation that I also need to use the query in. 

I know for a fact my query writing skills make the other developers here cringe... but then they are working in only one environment. 

[Sam_2.0]

Yahoo I think I got it!!!!!! Oh crap this is messy, LOL!!

[Tiny]

Jun 3, 2015 12:11:20 GMT -5 @sroo4 said:

Jun 3, 2015 11:55:14 GMT -5 Tiny said:

It's not that unusual.  There are lots of ways to accomplish a particular goal.   Also depends on what rules they are use to... I write terrible queries... but mostly it's because I've got to keep 3 sets of "rules" in my head.   A query I write for application A, might need to also be 'plugged' into application B - but the syntax is different for application B, and then there's application C that's got some sort of homegrown wack a doodle "compiler" with no documentation that I also need to use the query in. 

I know for a fact my query writing skills make the other developers here cringe... but then they are working in only one environment. 

Whenever I would ask for a second set of eyes on something the conversation would usually go like this: 

Me: Hey Bob can you take a look at this,   I need to A and it's not quite working out how I thought it should. 
Bob: Sure,  wait,  that won't work,  look at B,  you can't do that
Me: No don't worry about B.  It's fine it works perfectly.  It's A that I'm having problems with.
Bob: What-wait..B's working?!?  That can't work.  You need to rewrite B.
Me: No really it works fine.. let me show you.  
After proving B works
Me: So can you help me with A
Bob: Umm... I still don't know how you got B to work? 
Me: Nevermind... I'll figure out how to do A

LOL! I've had conversations like that... and sometimes I'm "Bob".                I try to recognize when I'm being 'Bob' and focus on what the person asking me for help is really after.   That's probably one of the hardest parts of my job (for me).

[Sam_2.0]

Not bad for someone trying to teach themselves:

Select d.bf_rev_month,
d.sa_id,
d.nbr_of_seg_days,
round(((d.nbr_of_seg_days)*(select round((sum(a.usage_qty)/sum(a.nbr_of_seg_days)*.9),2) as "Adjusted Avg Daily Usage"
from bi_bill_segment_t a, cis_sp_t b, cis_sa_sp_t c
where a.coy = 1
and b.coy = c.coy
and a.coy = c.coy
and b.sp_id = c.sp_id
and a.account_id = c.account_id
and substr(a.bf_rev_month,5,2) = substr(d.bf_rev_month,5,2)
and b.sp_id = 5259022429 --unique SP ID - parameter for web query
and a.seg_status = 'F'
and b.util_type = 'E'
and a.sa_type like ('E%')
and a.seg_end_dt9c between (99999999 - 20110602) and (999999999 - 20150602))),0) as "Quantity",
d.seg_end_dt as "Read Date"
--"Meter Read"
from bi_bill_segment_t d
where d.coy = 1
and d.seg_end_dt9c between (99999999 - 20150501) and (99999999 - 20140101)
and d.sa_id = 296390289
order by d.bf_rev_month asc


Thank you all!!

[fatbear]

If you want to make the queries easier to read in the future, you could look at creating a view that is the join of the 3 tables.

[Sam_2.0]

I will have to go read about that Basically teaching myself by reading others' queries and tweaking what's on my network drive to fit what I need. But this I had to do from scratch!

[fatbear]

The syntax and capabilities will vary across database servers.  Keep that in mind when learning SQL.

[Miss Tequila]

Reading this thread reminds me of why I hire people that know how to do this...because I haven't a clue what you all are talking about!

[Sam_2.0]

Ok, another problem. I've been googling and trying different things, and asked my mentors here, but no one seems to know how to do this. I need to have a running total column. The problem is, the data in the column comes from an if/then statement, so it's pulling from 2 different tables depending on the type of transaction. So it's not as easy as just doing the normal running total calculations. I tried lag, but it doesn't like the if/then statement in there. I could get that to work part of the time, but then it was duplicating records and throwing out null values and I didn't know why.

What terms do I need to be searching for to get help with this one? Running total, add to previous row are what I've been doing so far.

[Sam_2.0]

I don't know if I can, but I will try that. Thanks!!

[Sam_2.0]

They don't like it that I have query access anyway. If they knew what I was trying to do they would probably flip Our IT dept isn't very friendly here.

[Sam_2.0]

Gah! I wish I could just this into Excel and make it do the calcs there. So much easier. I still can't figure this out.

[Sam_2.0]

Here's what I have. The bolded is where I need help:

select distinct
a.tran_dt "Date",
a.tran_sa_type "SA Type",
a.tran_type "Transaction",
d.desc_on_bill "Additional Detail",
a.tran_tot_amt "Actual Amount",
a.tran_curr_amt "Current Amount",
case when a.tran_type = 'BILL' then (c.util_camtd + c.nut_camtd) else (a.tran_curr_amt -- + previous row value) end as "Amount Now Due"


from ft_finan_tran_t a, bi_bill_header_t c, ad_adjust_t d
where a.coy = 1
and a.coy = d.coy (+)
and (a.coy = c.coy(+))
and a.tran_account_id = d.account_id (+)
and a.tran_type = d.adj_code (+)
and (a.tran_account_id = c.account_id(+))
and (a.tran_dt = c.bill_end_dt(+))
and a.tran_account_id = xxx
and a.Tran_Dt9c between (99999999 - 20150601) and (99999999 - 20140101)
order by a.tran_dt asc

Our system doesn't store account balances, but does dynamic calculations to get them. The only stored values are the Bills, which is why I pulled in that value specifically. So I want the "Amount Now Due" to show me either the bill, or take the previous row and add in the current transaction amount (payment, late fees, other account adjustments).

I think I am having so much trouble because it's not just pulling from one table for that field. Well, that and I really have no clue what the heck I am doing

[ArchietheDragon]

Jun 17, 2015 9:27:48 GMT -5 Sam_2.0 said:

Ok, another problem. I've been googling and trying different things, and asked my mentors here, but no one seems to know how to do this. I need to have a running total column. The problem is, the data in the column comes from an if/then statement, so it's pulling from 2 different tables depending on the type of transaction. So it's not as easy as just doing the normal running total calculations. I tried lag, but it doesn't like the if/then statement in there. I could get that to work part of the time, but then it was duplicating records and throwing out null values and I didn't know why.

What terms do I need to be searching for to get help with this one? Running total, add to previous row are what I've been doing so far.

www.databasejournal.com/features/mssql/article.php/3112381/SQL-Server-Calculating-Running-Totals-Subtotals-and-Grand-Total-Without-a-Cursor.htm


don't know if this will help