Watch, Follow, &
Connect with Us

Welcome, Guest
Guest Settings

Thread: SQL Query assistance needed.

Permlink Replies: 0 Threads: [ Previous | Next ]
Ian Branch

Posts: 291
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,
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02