July 18, 2008 at 8:26 am
I'm trynig to write this query and a couple of "column values" that I want to display separately are recorded in the database in the same column.
I want to sum and separate these summation values into their own column in the extract?
The PK is "account" column.
Can anybody help me out? 🙂
select
g.arrival as 'Arrival'
g.departure as 'Departure',
g.name as 'Name',
g.status 'Res Status',
g.account as 'Confirmation No.',
g.room as 'Room No.',
g.room_type as 'Room Type',
g.company as 'Guest Company',
g.out_time as 'Departure Time',
r.revenue as 'Revenue Amount'
g.rate as 'Rate'
sum(select t.tax1_amt as 'IVA' where tran_code='7000'
sum(select t.tax1_amt as '2% Tax' where tran_code='7010'
r.revenue as 'Room Revenue'
r.other as 'Other Revenue'
sum(select t.amount as 'CC Payment' where tran_code in ('9202','9203','9204','9205','9206','9207')
sum(select t.amount as 'Cash Paid' where tran_code in ('9000','9001','9400','9401','9402','9403','9404','9405','9406','9407','9408')
g.out_time
from guest g, rpt_revenue r, transactions t
where g.account=r.account
and r.account=t.account
and g.departure < '2008-01-01'
and g.departure > '2006-12-31'
order by g.departure desc
July 18, 2008 at 9:31 am
I just noticed I didn't enclose line 11 and 12...but I have more problems than that...:hehe:
July 18, 2008 at 1:17 pm
I'm not clear on exactly what you're trying to do here, but this part definitely won't work:
sum(select t.tax1_amt as 'IVA' where tran_code='7000'
Nor the other parts like it. Not sure exactly what to tell you to do to fix it, because I'm not sure what the end result it supposed to be.
Does the transactions table have multiple rows for each account?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 18, 2008 at 1:51 pm
GSquared (7/18/2008)
I'm not clear on exactly what you're trying to do here, but this part definitely won't work:sum(select t.tax1_amt as 'IVA' where tran_code='7000'
Nor the other parts like it. Not sure exactly what to tell you to do to fix it, because I'm not sure what the end result it supposed to be.
Does the transactions table have multiple rows for each account?
Hi - thanks for the response. I am not sure what it is called in SQL SVR...but i'm trying to do a sub-query inside of the select statement for these particular column values.
To simplify:
select
sum(select t.tax1_amt as 'IVA' where tran_code='7000')
sum(select t.tax1_amt as '2% Tax' where tran_code='7010')
from guest g, rpt_revenue r, transactions t
where g.account=r.account
and r.account=t.account
and g.departure < '2008-01-01'
and g.departure > '2006-12-31'
order by g.departure desc
You'll notice that I want to select the same column twice, but I want to have a condition on the column so that one of them shows only a value where tran_code='7000' and the other column (same column name) shows only a value where tran_code='7010'
In Oracle this is like a aggregate sub-query....Can ya help me?
July 18, 2008 at 2:18 pm
Okay, I think this is what you are looking for - but I cannot be sure. I am not sure whether or not you need to tie transactions by account (in fact, I am fairly sure you want to change that to the primary key for the transactions).
select g.arrival as 'Arrival'
,g.departure as 'Departure'
,g.name as 'Name'
,g.status 'Res Status'
,g.account as 'Confirmation No.'
,g.room as 'Room No.'
,g.room_type as 'Room Type'
,g.company as 'Guest Company'
,g.out_time as 'Departure Time'
,r.revenue as 'Revenue Amount'
,g.rate as 'Rate'
,(select sum(t1.tax1_amt)
from transactions t1
where t1.account = t.account
and tran_code='7000') As 'IV1'
,(select sum(t1.tax1_amt)
from transactions t1
where t1.account = t.account
and tran_code='7010') As '2% Tax'
,r.revenue as 'Room Revenue'
,r.other as 'Other Revenue'
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('9202','9203','9204','9205','9206','9207') as 'CC Payment'
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('9000','9001','9400','9401','9402','9403','9404','9405','9406','9407','9408') as 'Cash Paid'
,g.out_time
from transactions t
Inner Join rpt_revenue r on r.account = t.account
Inner Join guest g on g.account = r.account
where g.departure < '2008-01-01'
and g.departure > '2006-12-31'
order by g.departure desc
Also, if the departure is a datetime and includes the time (i.e. is not always set to 00:00:00.000) then you want to modify your date checking to:
where g.departure < '2008-01-01'
and g.departure >= '2007-01-01'
If there are times in departure, your original where clause would include all rows after '2006-12-31 00:00:00.000' - or more precisely, would include rows on or after '2006-12-31 00:00:00.003'.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 18, 2008 at 6:59 pm
Jeffrey Williams (7/18/2008)
Okay, I think this is what you are looking for - but I cannot be sure. I am not sure whether or not you need to tie transactions by account (in fact, I am fairly sure you want to change that to the primary key for the transactions).
Hi Jeffrey - thanks so much for your post. Your query is close to working...but in the second set of sub-queries where I use the "is in" filter...it throws an "syntax error near the character 'As'"
I take it out...and it looks like the code below...and it does run...but shows like 200 rows per "account"...I just want to select a unique row based on a unique account.
In Sql is that using a distinct or a unique command? Let me know if I made sense here...
select g.arrival as 'Arrival'
,g.departure as 'Departure'
,g.name as 'Name'
,g.status 'Res Status'
,g.account as 'Confirmation No.'
,g.room as 'Room No.'
,g.room_type as 'Room Type'
,g.company as 'Guest Company'
,g.out_time as 'Departure Time'
,r.revenue as 'Revenue Amount'
,g.rate as 'Rate'
,(select sum(t1.tax1_amt)
from transactions t1
where t1.account = t.account
and tran_code='7000') As 'IV1'
,(select sum(t1.tax1_amt)
from transactions t1
where t1.account = t.account
and tran_code='7010') As '2% Tax'
,r.revenue as 'Room Revenue'
,r.other as 'Other Revenue'
,g.out_time
from transactions t
Inner Join rpt_revenue r on r.account = t.account
Inner Join guest g on g.account = r.account
where g.property ='DELSOL'
and g.account = '36000103096'
and g.departure < '2008-01-01'
and g.departure > '2007-12-26'
and g.room_type NOT IN ('HOUSE','PERM')
order by g.departure desc
July 18, 2008 at 7:15 pm
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('9202','9203','9204','9205','9206','9207') as 'CC Payment'
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('9000','9001','9400','9401','9402','9403','9404','9405','9406','9407','9408') as 'Cash Paid'
I missed a ')' in the above part of the query, it should be:
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('9202','9203','9204','9205','9206','9207')) as 'CC Payment'
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('9000','9001','9400','9401','9402','9403','9404','9405','9406','9407','9408')) as 'Cash Paid'
As for your problem with multiple rows - that is going to be caused by the joins. In the following:
from transactions t
Inner Join rpt_revenue r on r.account = t.account
Inner Join guest g on g.account = r.account
you are joining transactions to rpt_revenue and rpt_revenue to guest. I am guessing, because I don't know the structure here that you can have many transactions to one rpt_revenue.
To correct the above, you need to decide how you want this to work.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 18, 2008 at 10:04 pm
As for your problem with multiple rows - that is going to be caused by the joins. In the following:
from transactions t
Inner Join rpt_revenue r on r.account = t.account
Inner Join guest g on g.account = r.account
you are joining transactions to rpt_revenue and rpt_revenue to guest. I am guessing, because I don't know the structure here that you can have many transactions to one rpt_revenue.
To correct the above, you need to decide how you want this to work.
Hi Jeff, I will change the missing parens and try that...I think I tried adding those already and it threw a similar error.
What i'm looking for is to select a row of data per unique account...but not sure how to do that...would I insert select unique(g.account), instead of just select g.account? I'm out of the office but will check ASAP!
thanks again.
JR
July 18, 2008 at 11:39 pm
jonathanmreynolds (7/18/2008)
As for your problem with multiple rows - that is going to be caused by the joins. In the following:
from transactions t
Inner Join rpt_revenue r on r.account = t.account
Inner Join guest g on g.account = r.account
you are joining transactions to rpt_revenue and rpt_revenue to guest. I am guessing, because I don't know the structure here that you can have many transactions to one rpt_revenue.
To correct the above, you need to decide how you want this to work.
Hi Jeff, I will change the missing parens and try that...I think I tried adding those already and it threw a similar error.
What i'm looking for is to select a row of data per unique account...but not sure how to do that...would I insert select unique(g.account), instead of just select g.account? I'm out of the office but will check ASAP!
thanks again.
JR
I am really guessing here - but, I think you want to perform a group by on the whole query. For a better answer, please review this article: Best Practices: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 19, 2008 at 8:58 am
I am really guessing here - but, I think you want to perform a group by on the whole query.
select
g.arrival as 'Arrival'
,g.departure as 'Departure'
,g.name as 'Name'
,g.nights
,g.status 'Res Status'
,g.account as 'Confirmation No.'
,g.room as 'Room No.'
,g.room_type as 'Room Type'
,g.company as 'Guest Company'
,g.out_time as 'Departure Time'
,g.rate as 'Rate'
,(select sum(t1.tax1_amt)
from transactions t1
where t1.account = t.account
and tran_code in ('7000', '7050')) As 'IVA'
,(select sum(t1.tax1_amt)
from transactions t1
where t1.account = t.account
and tran_code in ('7010', '7060')) As '2% Tax'
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('1000', '1300')) as 'Net Room Revenue'
,(select sum(r.other)from rpt_revenue r
where r.account = t.account) as 'Other Revenue'
,g.out_time
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('9202','9203','9204','9205','9206','9207')) as 'CC Payment'
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('9000','9001','9400','9401','9402','9403','9404','9405','9406','9407','9408')) as 'Cash Paid'
from transactions t
Inner Join rpt_revenue r on r.account = t.account
Inner Join guest g on g.account = r.account
where g.property ='DELSOL'
and g.departure < '2008-01-01'
and g.departure >= '2007-12-20'
and g.room_type NOT IN ('HOUSE','PERM')
and g.property = r.property
and r.property = t.property
and r.roomtype = g.room_type
and g.room = r.room
and t.account = '36000103336'
group by g.account, g.arrival, g.departure, g.name, g.status, g.room,
g.room_type, g.company, g.out_time, r.revenue, g.rate, t.account,
r.other, r.account, g.nights
order by g.departure desc
Here is an example output (for one account). This is a hotel database for hotel guests.
2007-12-30 00:00:00.0002007-12-31 00:00:00.000ROULAND, RYAN1O360001033360004SUPNULL13:075550.001065.6000111.00005550.006915.4313:07-10127.00NULL
2007-12-30 00:00:00.0002007-12-31 00:00:00.000ROULAND, RYAN1O360001033360004SUPNULL13:075550.001065.6000111.00005550.006915.4313:07-10127.00NULL
I noticed something interesting when I was testing this...I noticed unique records sprinkled in randomly...I then compared the data and found that if an "account" did not have values in the tax1_amt columns...it would be unique (i.e - One Row Per PK)...I can't really reference the tax1_amt column to any of the other tables because they don't have that info...I think this is why I get duplicate records for sure...if my other tables don't contain this column...is there still someway i can display just one row per account number?
The number or rows is directly related to the number of days the guest is "checked-in"...and only shows one unique row when the tax1_amt aggregate column values are empty.
July 19, 2008 at 9:51 am
jonathanmreynolds (7/19/2008)
select
g.arrival as 'Arrival'
,g.departure as 'Departure'
,g.name as 'Name'
,g.nights
,g.status 'Res Status'
,g.account as 'Confirmation No.'
,g.room as 'Room No.'
,g.room_type as 'Room Type'
,g.company as 'Guest Company'
,g.out_time as 'Departure Time'
,g.rate as 'Rate'
,(select sum(t1.tax1_amt)
from transactions t1
where t1.account = t.account
and tran_code in ('7000', '7050')) As 'IVA'
,(select sum(t1.tax1_amt)
from transactions t1
where t1.account = t.account
and tran_code in ('7010', '7060')) As '2% Tax'
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('1000', '1300')) as 'Net Room Revenue'
,(select sum(r.other)from rpt_revenue r
where r.account = t.account) as 'Other Revenue'
,g.out_time
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('9202','9203','9204','9205','9206','9207')) as 'CC Payment'
,(select sum(t1.amount)
from transactions t1
where t1.account = t.account
and tran_code in ('9000','9001','9400','9401','9402','9403','9404','9405','9406','9407','9408')) as 'Cash Paid'
from transactions t
Inner Join rpt_revenue r on r.account = t.account
Inner Join guest g on g.account = r.account
where g.property ='DELSOL'
and g.departure < '2008-01-01'
and g.departure >= '2007-12-20'
and g.room_type NOT IN ('HOUSE','PERM')
and g.property = r.property
and r.property = t.property
and r.roomtype = g.room_type
and g.room = r.room
and t.account = '36000103336'
group by g.account, g.arrival, g.departure, g.name, g.status, g.room,
g.room_type, g.company, g.out_time, r.revenue, g.rate, t.account,
r.other, r.account, g.nights
order by g.departure desc
You really should review the article I referenced. The query you have now tells me that there definitely is a problem with how you are relating the tables.
and g.property = r.property
and r.property = t.property
and r.roomtype = g.room_type
and g.room = r.room
By adding the above, we now have additional criteria for the relationship between guest and rpt_revenue and between rpt_revenue and transactions.
At this point, the best I can determine is that the relationship is:
transactions is related to rpt_revenue on (account, property)
rpt_revenue is related to guest on (account, property, roomtype, room)
That does not seem right to me - which is probably why you are getting duplicates. Once I have an understanding of the relationships, it really shouldn't be that hard to figure this out.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 21, 2008 at 8:56 am
Once I have an understanding of the relationships, it really shouldn't be that hard to figure this out.
Hi Jeffrey - thanks again for your time assisting me with this. I do understand your are driving blind a bit. All three tables are linked on the "Account" and "Property" columns.
My Join looks like this now after I added the Property Column...But I still get a duplicate row per Account. The duplicate records display only when the Tax1_Amt columns are "not null". The Tax1_Amt column in transactions table cannot be linked with any other column in the two other tables. If no link exists b/w this column and my other tables involved, does it mean I have to live with the dupes?
from transactions t
Inner Join rpt_revenue r
on r.account = t.account
and r.property = t.property
Inner Join guest g
on g.account = r.account
and g.property = r.property
where g.property ='DELSOL'
and g.departure < '2008-01-01'
and g.departure >= '2007-12-20'
and g.room_type NOT IN ('HOUSE','PERM')
July 21, 2008 at 2:46 pm
jonathanmreynolds (7/21/2008)
Once I have an understanding of the relationships, it really shouldn't be that hard to figure this out.
Hi Jeffrey - thanks again for your time assisting me with this. I do understand your are driving blind a bit. All three tables are linked on the "Account" and "Property" columns.
My Join looks like this now after I added the Property Column...But I still get a duplicate row per Account. The duplicate records display only when the Tax1_Amt columns are "not null". The Tax1_Amt column in transactions table cannot be linked with any other column in the two other tables. If no link exists b/w this column and my other tables involved, does it mean I have to live with the dupes?
from transactions t
Inner Join rpt_revenue r
on r.account = t.account
and r.property = t.property
Inner Join guest g
on g.account = r.account
and g.property = r.property
where g.property ='DELSOL'
and g.departure < '2008-01-01'
and g.departure >= '2007-12-20'
and g.room_type NOT IN ('HOUSE','PERM')
Okay, with the relationships defined - we can now get rid of the sub-queries and just use sum (with a case expression). I re-organized the query and fixed the group by statement - you were grouping on more columns than needed which is probably why you were getting duplicates.
Try the following and see if we are any closer to what you are looking for.
select g.account as 'Confirmation No.'
,g.arrival as 'Arrival'
,g.departure as 'Departure'
,g.name as 'Name'
,g.nights
,g.status 'Res Status'
,g.room as 'Room No.'
,g.room_type as 'Room Type'
,g.company as 'Guest Company'
,g.out_time as 'Departure Time'
,g.rate as 'Rate'
,sum(case when tran_code In ('7000', '7050') then t1.tax1_amt else 0 end) As 'IVA'
,sum(case when tran_code In ('7010', '7060') then t1.tax1_amt else 0 end) As '2% Tax'
,sum(case when tran_code In ('1000', '1300') then t1.tax1_amt else 0 end) As 'Net Room Revenue'
,sum(r.other) As 'Other Revenue'
,sum(case when tran_code In ('9202','9203','9204','9205','9206','9207')
then t1.amount else 0 end) As 'CC Payment'
,sum(case when tran_code ('9000','9001','9400','9401','9402','9403','9404','9405','9406','9407','9408'))
then t1.amount else 0 end) As 'Cash Paid'
from transactions t
Inner Join rpt_revenue r on r.account = t.account
and r.property = t.property
Inner Join guest g on g.account = r.account
and g.property = r.property
where g.property ='DELSOL'
and g.departure < '2008-01-01'
and g.departure >= '2007-12-20'
and g.room_type NOT IN ('HOUSE','PERM')
-- and r.roomtype = g.room_type
-- and g.room = r.room
and t.account = '36000103336'
group by g.account, g.arrival, g.departure, g.name, g.nights, g.status, g.room,
g.room_type, g.company, g.out_time, g.rate
order by g.departure desc;
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 21, 2008 at 3:33 pm
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 't1' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 't1' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 't1' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 't1' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 't1' does not match with a table name or alias name used in the query.
Hi Jeffrey...I added in a missing "In" and a missing ")"....but I still get this error. I feel like this is so close! Thanks again. It appears something wrong with the Sum statements...
July 21, 2008 at 3:53 pm
Shoot - that is one of the reasons I recommended reading the article about how to post. If I had something to test against, I would have been able to provide a tested solution.
The alias t1 was used in the sub-query and I didn't remove it. Modify the sum statements and change the reference from t1 to t and that should do it.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply