Hia all
I am having a bit of a Friday moment with some SQL and am hoping someone can show me the light…??
I am sure its very basic – but the finer details are some what eluding me today…
I have this script equivalent…
Select
B.Description
Count(B.Description)
Sum(A.Amount)
From
Table1 A
Join Table2 B on (A.id = B.id)
Where table A has one to many records of table B.
This runs fine, but what I am seeing is an incorrect Sum amount due to this one to many association between the two tables.
i.e. if an ID from Table A has £5 in the Amount column, and the same ID exists on 4 records on Table B – my sum result is coming back at £20, I only want a distinct £5.
My initial thought was to do something like putting Distinct in front of the A.Amount (Sum(Distinct A.Amount)) within the aggregate, but this groups by the money values, and not the ID…
I hope that makes sense to someone out there?!
Many thanks in advance!!
Christopher Hill
Swyddog Dadansoddi a Chymorth SQL
SQL Support Analyst
Canolfan Mileniwm Cymru
Wales Millennium Centre
Ebost\Email Christopher.Hill@wmc.org.uk
Ffon\Tel 029 2063 6372
www.wmc.org.uk
Dy Le Di
Make it Yours this year
Cysylltwch yn Gymraeg neu'n Saesneg fel dymunwch
Contact us in English or Welsh as you prefer
Plas Bute\Bute Place
Bae Caerdydd\Cardiff Bay
CF10 5AL
P Meddyliwch am yr amgylchedd cyn printio / Please consider the environment before printing this email
Hi Christopher
You've probably solved it by now, since Friday's long gone, but i would suggest a subquery to limit what you get back in the join.
That is, something like this (assuming the description in table2 is always the same value for a given id value) :
Select q.Description, Count(*) , Sum(A.Amount)From Table1 A Join (select distinct id, description from Table2) q on A.id = q.idgroup by q.description
Ken
Many thanks for your response Ken…
I thought I had tried something like this – but upon looking into your suggestion a bit more it got me right down the correct path! So many thanks for that!
Regards
Chris
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain Sent: 19 July 2009 04:52 To: Christopher Hill Subject: [Tessitura Technical Forum] Re:
Select q.Description, Count(*) , Sum(A.Amount) From Table1 A Join (select distinct id, description from Table2) q on A.id = q.id group by q.description
From: Christopher Hill <bounce-christopherhill1040@tessituranetwork.com> Sent: 7/17/2009 9:58:39 AM
Mae’r ohebiaeth hon at ddefnydd y derbynnydd/derbynyddion bwriadedig yn unig. Os nad chi yw’r derbynnydd/derbynyddion bwriadedig, nodwch fod dosbarthu, copïo neu ddefnyddio’r ohebiaeth hon neu’r wybodaeth ynddi mewn unrhyw ffordd wedi ei wahardd yn gyfangwbl a gall fod yn anghyfreithlon. Os ydych wedi derbyn yr ohebiaeth hon trwy gamgymeriad a fyddech cystal â’i ddychwelyd i’r anfonwr. Yn yr achos hwn byddem yn ddiolchgar pe gallech hefyd anfon yr ohebiaeth at administrator@wmc.org.uk ac yna dileu’r e-bost a dinistrio unrhyw gopïau ohono. Cwmni cyfyngedig dan warrant, cofrestrwyd yng Nghymru a Lloegr. Rhif Cwmni 3221924. Rhif Elusen 1060458. Swyddfa gofrestredig: Plas Bute, Bae Caerdydd, Caerdydd CF10 3AL This communication is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful.If you have received this communication in error please return it to the sender. In this event would be grateful if you would also copy the communication to administrator@wmc.org.uk then delete the email and destroy any copies of it. A company limited by guarantee, registered in England and Wales. Company number 3221924. Charity number 1060458. Registered office: Bute Place, Cardiff Bay, Cardiff CF10 5AL
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
I am trying to write a custom procedure and I know my question can easily be answered by some of you SQL gurus out there…
Here is the code I have so far:
select cc.customer_no, cc.constituency, ct.description, ct.short_desc,
c.fname, c.lname, st.department, st.title, st.hire_date, st.end_date, st.status
from tx_const_cust cc
join t_customer c
on c.customer_no = cc.customer_no
join tr_constituency ct
on ct.id = cc.constituency
join lt_MCFTA_STAFF st
on st.customer_no = c.customer_no
where cc.constituency in (48,49,50)
and st.status = 'Active'
order by c.lname
I want to add in a CASE statement, so that the code pulls fname and lname when the n1n2_ind = 1, but pulls fname2 and lname2 instead, when the n1n2_ind = 2. I am guessing I need something like the below, but am not sure how to format and where to insert the new code into the above statement:
CASE c.fname, c.lname
Where cc.n1n2_ind = 1
ELSE c.fname2. c.lname2
Where cc.n1n2_ind = 2
Thanks in advance for help!
Penny -
Try this on for size:
c.fname, c.lname, st.department, st.title, st.hire_date, st.end_date, st.status,
CASE WHEN cc.n1n2_ind = 2 THEN
c.fname2 +', ' + c.lname2
ELSE c.fname +', ' + c.lname
END as full_name
Case when cc.n1n2_ind = 1 then c.fname Else c.fname2 end as FName,
Case when cc.n1n2_ind = 2 then c.lname Else c.lname2 end as LName
You also have an n1n2_ind of 3, what do you want to do in those cases?
Marty Jones
Database Administrator
Omaha Performing Arts 1200 Douglas Street
Omaha, Nebraska 68102
P 402.661.8469 | F 402.345.0222
Marty.Jones@omahaperformingarts.org
www.omahaperformingarts.org
For tickets, call Ticket Omaha at 402.345.0606
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Penny Tabor Sent: Tuesday, September 07, 2010 3:23 PM To: Martin A. Jones Subject: [Tessitura Technical Forum] <No Subject>
Sorry, Error in the second one,
Should be
Case when cc.n1n2_ind = 1 then c.lname Else c.lname2 end as LName
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Marty Jones Sent: Tuesday, September 07, 2010 3:43 PM To: Martin A. Jones Subject: RE: [Tessitura Technical Forum] <No Subject>
Thanks to both Marty and Heather for your solutions…using a combo of both worked perfect!!! It is so nice to have such knowledgeable people out there that can help so quickly! Hope I can return the favor sometime!
Penny Tabor
IT Manager
Midland Center for the Arts
Midland, MI 48640
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Marty Jones Sent: Tuesday, September 07, 2010 5:58 PM To: Tabor, Penny Subject: RE: [Tessitura Technical Forum] <No Subject>
Does anyone know if you can create a multi-column report in Infomaker? For example, I have created a custom staff directory report that shows one person at a time, horizontally down the page. I was wondering if it is possible to have 3 columns going across the page so we can see 3 staff people at a time?
I have never tried it, but there is an "N - Up" datawindow type you can select when creating the report datawindow.
David
Thanks David. I gave that a shot, and it works great as long as all you need are the fields from the stored procedure. However, I wanted to create a staff directory with photos, so I needed to add the computed field for the bitmap image. The N-up report turns your field names into customer_no_1, customer_no_2, customer_no_3 when you select 3 columns, and you would think that using customer_no_1 in the bitmap expression would work fine, but it does not – it generates an invalid expression error, and I could find no way around it. So, I ended up creating labels instead, which worked great, but I don’t think it will let me add a header to the first page…it’s fine for now, and I suspect that Report Services might do a better job of this report once we get going in SSRS.
Thanks again,
Penny
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of David Woodall Sent: Friday, October 15, 2010 3:00 PM To: Tabor, Penny Subject: Re: [Tessitura Technical Forum] <No Subject>
From: Penny Tabor <bounce-pennytabor2237@tessituranetwork.com> Sent: 10/15/2010 1:22:43 PM
Error! Filename not specified.
In case anyone needs it, I DID just figure out how to get computed fields to work in an N-Up report. For my bitmap picture of our staff, I had to enter:
bitmap("\\mcftaapps\apps\Tessitura\Staff_Photos\"+ (customer_no[0])+".BMP"), bitmap("\\mcftaapps\apps\Tessitura\Staff_Photos\"+ (customer_no[1])+".BMP") and bitmap("\\mcftaapps\apps\Tessitura\Staff_Photos\"+ (customer_no[2])+".BMP") for my three computed field expressions. The numbers in parenthesis designates the data from which of my three columns the field should use.
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Penny Tabor Sent: Monday, October 18, 2010 12:46 PM To: Tabor, Penny Subject: RE: [Tessitura Technical Forum] <No Subject>
Does anyone know what table contains the “Inactive” field that displays on the Constituencies screen? It is not located in the tx_const_cust table as I expected. We have constituencies for our staff levels, and when staff changes from one level to another, they end up with multiple constituency entries, so I need to be able to report on the constituencies that are not inactive.
Penny,
I don't think this is kept in a table, It is calculated based on the end_dt which is stored in tx_const_cust. For easier use you can also see only active constituencies in the view vxs_const_cust.Dale
I don't think it is stored any where - I think its one of those that is set depending on the end date for the Constituency.
Caryl