Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: SQL Query assistance needed.



Permlink Replies: 2 - Last Post: Jul 31, 2017 1:53 PM Last Post By: Ian Branch Threads: [ Previous | Next ]
Ian Branch

Posts: 373
Registered: 9/23/99
SQL Query assistance needed.
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 18, 2017 3:14 PM
Hi Team,
Not sure if this is the right place for this question but I couldn't find a SQL group...

I have a query:

select Invoice.Job_type as [Job Type], Invoice.job_code as [Job Code], count(Invoice.MSN) as [Total Units],
count(distinct Invoice.MSN) as [Unique Units],
count(Invoice.msn)-count(distinct invoice.msn) as [Repeats],
(1000/count(Invoice.MSN))*(count(Invoice.msn)-count(distinct invoice.msn)) as [kppm]
from Invoice Invoice
where (trim(Invoice.msn) <> '') and (Invoice.Date_in >= :StartDate) and (Invoice.date_in <= :EndDate)
group by Job_Type, Job_Code;

That produces:

Job Type Job Code Total Units Unique Units Repeats kppm
AC WTY 2 2 0 0
CE ACC 265 231 34 102
CE T&M 1309 1064 245 0
CE WTY 4021 2388 1633 0
IT T&M 1 1 0 0
VC T&M 17 17 0 0
VC WTY 2 2 0 0

I also need the query to give me a Sum of Total units, and a Sum of Repeats where the Job Code = T&M.

I'm pretty sure it needs a sub query in there somewhere but my SQL skills aren't what they used ot be. ;-(

Any assistance greatly appreciated,

Regards & TIA,
Ian
Elmothana Khogali

Posts: 24
Registered: 9/14/12
Re: SQL Query assistance needed.
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 31, 2017 3:56 AM   in response to: Ian Branch in response to: Ian Branch
Have u tried to use
With Cube// With Rollup ?
Ian Branch

Posts: 373
Registered: 9/23/99
Re: SQL Query assistance needed.
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 31, 2017 1:53 PM   in response to: Elmothana Khogali in response to: Elmothana Khogali
Elmothana Khogali wrote:

Have u tried to use
With Cube// With Rollup ?

Hi Elmothana,
Cube?

Regards,
Ian
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02