Compute Sums In Select Query

Dec 24, 2003

i have this query and would like to have a sum for each column. how can i phrase the compute line please ?

select office as Office
, Sum(Case (role) when 'ebp' then 1 else 0 end) as 'EBP'
, Sum(Case (role) when 'support' then 1 else 0 end) as 'Support'
, Sum(Case (role) when 'Awaiting Disposal' then 1 else 0 end) as 'Awaiting Disposal'
, Sum(Case (role) when 'Interview Room' then 1 else 0 end) as 'Interview Room'
, Sum(Case (role) when 'Sch Drop In' then 1 else 0 end) as 'Sch Drop In'
, Sum(Case (role) when 'Sch CX Staff' then 1 else 0 end) as 'Sch CX Staff'
, Sum(Case (role) when 'Not in Use' then 1 else 0 end) as 'Not in Use'
, Sum(Case (role) when 'Public' then 1 else 0 end) as 'Public'
, Sum(Case (role) when 'IAG' then 1 else 0 end) as 'IAG'
, Sum(Case (role) when 'Delivery' then 1 else 0 end) as 'Delivery'
, Sum(Case (role) when 'NVQ Use' then 1 else 0 end) as 'NVQ Use'
, Sum(Case (role) when 'Hot Swap Spare' then 1 else 0 end) as 'Hot Swap Spare'
, Sum(Case (role) when 'Archived' then 1 else 0 end) as 'Archived'
, Sum(Case (role) when 'Network Infrastructure' then 1 else 0 end) as 'Network Infrastructure'
, Sum(Case (role) when 'Unknown' then 1 else 0 end) as 'Unknown'
, Sum(Case (role) when 'Drop in Centres' then 1 else 0 end) as 'Drop in Centres'
, Sum(Case (role) when 'Training' then 1 else 0 end) as 'Training'
from tempassets2
group by office, role
order by office

any help appreciated.

View 2 Replies


ADVERTISEMENT

Select With A COMPUTE

Nov 9, 2007

Is there a way to change the column headings when using a compute? I have the following code:


Select IP_address, Caption1, VendorIcon, InterfaceID, Caption2, InterfaceIcon, In_Maxbps, Out_Maxbps, DateTime1

from #test

WHERE InterfaceID IN('144', '154')

ORDER BY InterfaceID

COMPUTE max(In_Maxbps), max(OUT_maxbps), min(OUT_maxbps), avg(OUT_maxbps)

BY InterfaceID


The compute gives me 2 columns with the heading max. I would like to change those headings.

Thanks.

View 4 Replies View Related

Stored Procedure, Select/Compute/Update

Oct 20, 2005

I am trying to update fields in my table based on certain critera.

UPDATE tblALMLoans

SET tblALMLoans.NextRepDate = dateadd(YY,5,tblALMLoans.OriginalDate)

do until tblALMLoans.NextRepDate > getdate()
tblALMLoans.NextRepDate = dateadd(YY,1,tblALMLoans.OriginalDate)
loop

FROM tblALMLoans
WHERE (tblALMLoans.RateFlag = 'A');

I know this is no where near what its supposed to look like, but this is in code what I am looking to do.

Any help would be greatly appriciated.

View 8 Replies View Related

Running Sums Query

Oct 18, 2007

I am trying to write a query that gets daily sales for each of our stores plus WTD and MTD totals

columns would be

Store date daily_total wtd_total mtd_total

the query need to produce a row per store per day and have the wtd sum reset for each week so that it
sums up sales day by day for that week

Is there a good way to do this?

View 3 Replies View Related

Making Two Sums In One Query

Jun 10, 2006

Hi

Im new in this forum so don't if this is the right place to post these kind of questions..

Im using vb2005 and have made a program that deals with customers and payments.

I have a table called acount. Roughly it contains these columns

Number, amount, type..

type can be a payment or a charge

I would like to have a query that gives me this result:

Number, sum(payments), sum(charges) (grouped by number)

I think it is some type of merging these to querys

select number, sum(amount) where type = 0 group by number

and

select number, sum(amount) where type <> 0 group by number

Im using it to calculate a customers acount to see if they have paid to much or to little.

Any ideas?

View 6 Replies View Related

Compute The Age

Mar 5, 2007

Hi all. How could i get the age of the employees given the birthdate.

table - personalinfo

name birthdate
john 2/15/2004 12:00:00 AM
peter 2/15/2003 12:00:00 AM
jon 2/15/2001 12:00:00 AM
mike 2/15/2000 12:00:00 AM
sam 2/15/2002 12:00:00 AM

Thanks
-Ron-

View 8 Replies View Related

Sum Of Sums

Oct 19, 2005

Hello, any help is appreciated.Here is what I’m trying. I want a month to date total of bookingsbased on a fiscal month.I can isolate my records for the month and sum up the days so I windup with a list of days of the month with their bookings total forthat day. Like this10/1/05 - $100010/2/05 - $500010/3/05 - $2000And so on thru the month.But I want the sum of month 10.I have a line of code like “select sum(EXT_AMT) where month = “10””What I need is sum(sum(EXT_AMT).And that does not work.Any suggestions? I thought to throw the results into a table then sumthe table.Not sure how to do that either! I'm frustrated! Any help isappreciated.Thanks, Duane

View 2 Replies View Related

COMPUTE Clause

Feb 2, 2008

I run SELECT statement with a COMPUTE clause,columns in the select list overrides on aggregate functions in COMPUTE clause.
why was overrided?
how show  output COMPUTE clause?
SELECT     NUMBERFROM         Table_1COMPUTE AVG(NUMBER)
Result:
NUMBER
1
56
78
89
56
 
Thanks,mohsen

View 3 Replies View Related

Compute Column In View...

Mar 11, 2006

I have a SQL table that consists of columns A, B and C.  I am trying to construct a view consisting of all columns (i.e. A, B, C) and a computed column.  This computed column has the following logic:
If B is blank or null then  NewColumn = A + ' - ' C
else
 NewColumn = A + ' - ' + B
I am just wondering how the SQL statement should look like....
 

View 2 Replies View Related

Compute And Update In One Statement

Jun 13, 2008

Hi Guys, got a problem.
I am trying to create a table of summarized fees. I was unable to do a Insert command so I settled for an Update command. But I ran into this error msg which I’m not sure how to fix. Can anyone see the problem or is it not even possible to Update and Compute in the same statement? Do I have to do a sub query?

Update FEE_SUMs_20080402
SET LOAN_Num = F.DDLOAN, Fee_Amt = F.DDMFEE
FROM FEE_Recs AS F
INNER JOIN dbo.Addr_20080402 ON
Account_Num = F.DDLOAN
ORDER BY F.DDLOAN
COMPUTE SUM(F.DDMFEE) BY F.DDLOAN

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'ORDER'.

View 3 Replies View Related

Two Sums From Different Tables

Jul 29, 2004

I have this query:

select Customers.CustomersID, Customers.name, sum(table1.amount), sum(table2.amount)
from Customers, table1, table2
where Customers.CustomerID = table1.CustomerID
and Customers.CustomersID = table2.CustomerID
group by Customers.CustomersID, Customers.name

I am trying to make a query to report the total amount of two different things from two different tables by customer. the problem is that the total amount from table1 doubles if there are two rows of that customer in table2. My guess is that I have to group the things in a different way, but I don't know how.

Any suggestions?

View 1 Replies View Related

Gets Sums On Inner Joins

Feb 21, 2008

Ok I have two tables

clients and expenses

enpense table columns: clientid, expensetype, expensevalue

clients.clientid and expenses.clientid relate the tow tables on an inner join

what i have is one client that will have multiple expense records in the expense table

cleint1, expensetype1, expensevalue1
cleint1, expensetype2, expensevalue2
cleint1, expensetype3, expensevalue3

what i need to output is client.*, sum(expensevalues), and also expensevalue1, expensevalue2, expensevalue3 as one output line

how do I get this?

View 3 Replies View Related

Getting 2 SUMs From The Same Table

Jul 23, 2005

Hi AllI'm really stuck on this one so would appreciate any help you can give.In essence, I have 1 SQL 2000 table with rows of data logging stockmovement. To differenciate between a stock sale and a stock receipt thetable has a TRANSACTIONTYPE field so that 8,7 equal invoices and 3 equals areceipt.I've been asked to report on this data by suming the total qty used oninvoices and the total qty recvd for each stock item, but I can't figure outhow I sum the same rows twice in the one query.For example, my query is as follows:select st.stockid as 'STYLE',s.picture as 'COLOUR','' as 'IN FIRST IN LAST WEEK','' as 'THIS WEEK IN','' as 'TOTAL IN','' as 'OUT FIRST OUT LAST WEEK',SUM(st.quantity) as 'THIS WEEK OUT','' as 'TOTAL OUT','' as 'REMAINING','' as 'TOTAL DIGESTION %'from stocktransactions st, stock swhere st.stockid = s.stockid andst.transactiontype in (8,7) andst.transactiondate >= '2005-07-12 00:00:00' andst.transactiondate <= '2005-07-12 23:59:59'group by st.stockid,s.pictureorder by st.stockidApart from the 'THIS WEEK OUT' column SUMing all of the stock sales bytransactiontype 7,8, I also want the 'THIS WEEK IN' column to SUM all of thetransactions by transactiontype 3, so that I get the following results:STYLE COLOUR .... THIS WEEK IN .... THIS WEEK OUT .......IVP Red 12 23STP Blue 4 15etc etcMy problem is that I don't want to exclude a stock item if it hasn't got arow/value for the THIS WEEK IN and/or the THIS WEEK OUT. Am I asking toomuch of SQL?My table schemas are as follows:create table STOCKTRANSACTIONS(STOCKTRANSACTIONID T_STOCKTRANSACTIONSDOMAIN not nullidentity(1,1),TRANSACTIONTYPE smallint not null,TRANSACTIONDATE datetime null ,REFERENCE varchar(40) null ,Comment varchar(255) null ,STOCKID T_STOCKDOMAIN null ,DESCRIPTION varchar(255) null ,UNITOFSALE varchar(20) null ,WAREHOUSEID T_WAREHOUSESDOMAIN null ,PEOPLEID T_PEOPLEDOMAIN null ,AccountID T_AccountsDomain null ,AgentID T_AgentsDomain null ,PLRate float null ,CONTACTID T_CONTACTDETAILSDOMAIN null ,JOBID T_JOBSDOMAIN null ,QUANTITY float null ,CURRENCYID T_CURRENCIESDOMAIN null ,SELLINGPRICE float null ,DISCOUNTPERCENT float null ,COSTPRICE float null ,MINIMUMPRICE float null ,TILLID T_TILLSDOMAIN null ,UserID T_UsersDomain null ,ClockDate DateTime null ,TimeStamp TimeStamp ,constraint pk_stocktransactions primary key (STOCKTRANSACTIONID))gocreate table STOCK(STOCKID T_STOCKDOMAIN not null,NAME varchar(40) not null,PICTURE varchar(40) null ,WEIGHT float null ,VOLUME float null ,BARCODE smallint null ,NumberOfPriceBreaks SmallInt not null default 1,STOCKCATEGORYID T_STOCKCATEGORIESDOMAIN null ,SALESNOMINALID T_NOMINALACCOUNTSDOMAIN null ,PURCHASENOMINALID T_NOMINALACCOUNTSDOMAIN null ,SELLINGCOMMENT varchar(255) null ,INCLUDESELLINGCOMMENT TinyInt null ,DISPLAYSELLINGCOMMENT TinyInt null ,COSTCOMMENT varchar(255) null ,DISPLAYCOSTCOMMENT TinyInt null ,PRODUCTTRACKING smallint null ,ITEMTYPE smallint null ,VALUATIONPRICE float not null default0.00 ,INCLUDEINCUSTOMERSTURNOVER TinyInt null ,INCLUDEINAGENTSTURNOVER TinyInt null ,SUPERCEDED TinyInt null ,SUPERCEDEDBY T_STOCKDOMAIN null ,SUPPLIERID T_PEOPLEDOMAIN null ,SUPPLIERSTOCKID varchar(40) null ,SUPPLIERCOMMENT varchar(255) null ,NEXTSERIALNUMBER int null ,SERIALNUMBERLENGTH smallint null ,SERIALNUMBERPREFIX varchar(10) null ,SERIALNUMBERSUFFIX varchar(10) null ,SERIALNUMBERPREFIXLENGTH smallint null ,SERIALNUMBERSUFFIXLENGTH smallint null ,TIMESTAMP timestamp not null,constraint pk_stock primary key (STOCKID))goThanksRobbie

View 3 Replies View Related

How To Do Multiple Sums?

May 13, 2008

Hello All,

I'm trying to do a query to produce multiple sums based on how many rows are in a table.
Here's a sample of the table and data, what I want is to have a query to sum each company's total and display it.




create table #invoices


(
InvoiceNumber varchar(5),
--other info
CompanyCode int,
InvoiceAmount real
)


Insert Into #invoices values('A1000', 1, 1000)
Insert Into #invoices values('A1000', 2, 100)
Insert Into #invoices values('A1000', 3, 300)
Insert Into #invoices values('A1000', 1, 600)
Insert Into #invoices values('A1001', 2, 2000)
Insert Into #invoices values('A1001', 3, 1000)
Insert Into #invoices values('A1001', 1, 300)
Insert Into #invoices values('A1002', 2, 2500)
Insert Into #invoices values('A1002', 3, 2000

I was thinking of doing it something like this:

Select

Sum(case when CompanyCode=1 Then CompanyCode End) as TOTAL1,
Sum(case when CompanyCode=2 Then CompanyCode End) as TOTAL2,

Sum(case when CompanyCode=3 Then CompanyCode End) as TOTAL3
from
#invoices


But I would rather not have to hard code the company numbers in the query as they can be added or removed from the list. Ideally it would take the CompanyCode from the COMPANY table and SUM each companies totals and display it.
Any help on this would be greatly appreciated!
Thanks,

View 3 Replies View Related

Different Sums From Table

May 12, 2006

Could someone explain to me, how I can get sum from row which I have values in 2 colums and I want the realtime sum to third column. Fourth colum is for item.

Also can someone tell me how to sum these third colums where the item is same so I have real time values for the item sum.

Thanks!

AD

View 1 Replies View Related

Combine SUMs

May 5, 2006

How can I combine the 2 Sum amounts below. Basically teh 2 queries are exactly the same, just hitting 2 different tables (pdc and pdcdeleted) with the same structure:

SELECT SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT     c.name,
          c.customer,
          (SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
                    where c.feeSchedule = fd.code)
          AS FeeSchedule,
          m.branch,
         pd.desk,
        'PDC' AS Type,
       pd.Active,
        m.number,
        pd.Amount,
        CONVERT(money, 0) AS OverPaidAmt,
          pd.OnHold

FROM Master m (NOLOCK)
LEFT JOIN pdc pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer

WHERE     pd.Active = 1
          AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
          AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
          AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate))
          AND pd.Deposit IS NOT NULL    
          AND pd.OnHold IS NULL
          AND c.customer <> '9999999'
) as PQuery

 

SELECT SUM(PQuery2.Amount) as PDCs_IL_deleted
FROM
(SELECT     c.name,
          c.customer,
          (SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
                    where c.feeSchedule = fd.code)
          AS FeeSchedule,
          m.branch,
         pd.desk,
        'PDC' AS Type,
       pd.Active,
        m.number,
        pd.Amount,
        CONVERT(money, 0) AS OverPaidAmt,
          pd.OnHold

FROM Master m (NOLOCK)
LEFT JOIN pdcdeleted pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer

WHERE     pd.Active = 1
          AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
          AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
          AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @ProcessDate) + 1, @ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @ProcessDate), DATEADD(MONTH, 1, @ProcessDate))
          AND pd.Deposit IS NOT NULL    
          AND pd.OnHold IS NULL
          AND c.customer <> '9999999'
) as PQuery2

View 1 Replies View Related

Compute By Clause Problem In SQL 2000

Feb 9, 2004

I am having a problem with this compute by clause

The statement is a simple select

Select col1, col2 from table1
where <where clause>
order by col1
Compute Sum(col2) by col1

I want the display to show details and group totals.

The error message I keep getting is
Server: Msg 410, Level 16, State 2, Line 1
COMPUTE clause #1 'BY' expression #1 is not in the order by list.


I have previously used the compute clause so I know it should work !!

Ay help will be appreciated - Thank you.

View 4 Replies View Related

Compute Sum Result Cannot Show In Report?

Aug 23, 2014

How to display sql compute sum command their result in amount and qty in report?

I am try this query is successful show the total amount and qty in studio management server and show in screen. But run this query on report is without to show last column of qty and amount, why ?

select
xsodetail.shopcode,xsodetail.memono,xsodetail.txdate,
xsodetail.sku,xsoheader.depositamt,xsopayment.paymentcode,xsopayment.paymentamt,
xsodetail.itemamt,xsodetail.salesqty

from xsoheader
inner join xsodetail on xsoheader.shopcode + xsoheader.memono = xsodetail.shopcode + xsodetail.memono
inner join xsopayment on xsoheader.shopcode + xsoheader.memono = xsopayment.shopcode + xsopayment.memono
where (xsodetail.sku = 'L000254' or xsodetail.sku='L000256') and xsoheader.voidflag='N' and xsodetail.txdate = CONVERT(varchar(100), GETDATE(), 112)
compute sum(xsopayment.paymentamt) , sum(xsodetail.salesqty)

View 2 Replies View Related

Compute Command Change To Rollup?

Aug 25, 2014

i want this keep query output result but don't want the "compute " this command , because this query cannot run in this dos command directc.exe , Thus, it is have any writing skill , it is possible using of rollup ?

select d.shopcode
,d.memono
,d.txdate
,d.sku
,h.depositamt

[Code] ....

View 2 Replies View Related

How To Compute For A Backup File Size

Jan 9, 2008

hello guys,

Please bare with my english.

Is there a way to compute for the backup file size of the database.
Example.

DatabaseName
TEST_DB

*in MB
DataSize DataSize_Used DataSize_UnUsed
150.00 81.00 70.00

LogSize LogSize_Used LogSize_UnUsed
30.00 1.00 29.00


When I run a backup for TEST_DB ,
*base on this figure - can we compute the the estimated backup file size for TEST_DB?

View 2 Replies View Related

SQL Group By With Multiple Sums?

Feb 27, 2008

I am creating a statistics page for our site. Using a very basic select statement, my query currently returns:
Select DateAdded, MTCreds, HICreds from tblStudentsAndCredits 
DateAdded-MTCreds-HICreds-----------------------------------------1/1/2008 - 2 - 01/1/2008 - 0 - 41/2/2008 - 3 - 01/2/2008 - 2 - 41/3/2008 - 2 - 01/3/2008 - 0 - 3
Instead, I would like it sum up MTCreds and HICreds for each day and group them into something more usable like this:
 DateAdded-MTCreds-HICreds-----------------------------------------1/1/2008 - 2 - 41/2/2008 - 5 - 41/3/2008 - 2 - 3
Thanks for any help - SQL is not my strong point.

View 3 Replies View Related

Reformulated Question On Sums

May 1, 2001

here is the script im using.

SELECT dbo_ITEM.part_no, dbo_SALES_ORDER.entered_date_time, Sum(dbo_SALES_ORDER_ITEM.sales_price) AS SumOfsales_price
FROM dbo_ITEM INNER JOIN (dbo_SALES_ORDER INNER JOIN dbo_SALES_ORDER_ITEM ON dbo_SALES_ORDER.sales_order_id = dbo_SALES_ORDER_ITEM.sales_order_id) ON dbo_ITEM.item_id = dbo_SALES_ORDER_ITEM.item_id
GROUP BY dbo_ITEM.part_no, dbo_SALES_ORDER.entered_date_time
HAVING (((dbo_ITEM.part_no)="5030" Or (dbo_ITEM.part_no)="5040" Or (dbo_ITEM.part_no)="5050" Or (dbo_ITEM.part_no)="5060" Or (dbo_ITEM.part_no)="6014" Or (dbo_ITEM.part_no)="6016" Or (dbo_ITEM.part_no)="6017" Or (dbo_ITEM.part_no)="5071" Or (dbo_ITEM.part_no)="5081" Or (dbo_ITEM.part_no)="5091") AND ((dbo_SALES_ORDER.entered_date_time) Between [Enter Start Date] And [Enter End Date]))
ORDER BY dbo_ITEM.part_no;


If i delete the

((dbo_SALES_ORDER.entered_date_time) Between [Enter Start Date] And [Enter End Date]))

statement than the SUM function works, for some reason (no pun intended) when a date set is entered the SUM statement is inopperative. Any help would be much appriciated.

Thanks
Matthew

View 2 Replies View Related

Running Sums Of Averages

Aug 3, 2007

Hi,

I have a table with a userid and duration field. I need to contrast and individual against a group of users from the table. I can get the users cumulative hours by date in the select clause.

I am having a problem producing the group's sum of averages in the main query.I have tried it many ways and just cant get it to work. I need to be able to display something like this below:


Date GV IV
1/1/07 .45 .37
1/2/07 .56 .45
1/3/07 .68 .59
--
So Far I have this:

SELECT


D1.Date,

GV=ROUND(CAST(SUM(D2.Duration) AS FLOAT) / 3600,2),
IV=(SELECT ROUND(CAST(SUM(DurationAll)AS FLOAT) / 3600,2)

FROM IR_UserDaily WHERE UserID=@UserID AND Date<=D1.Date)


FROM

( SELECT Date,Duration=AVG(DurationAll)

FROM IR_UserDaily

WHERE

UserID IN(SELECT FilterID FROM IR_Filter WHERE ReportID=@RID)AND(Date BETWEEN @LowDate and @HighDate)

GROUP BY Date
) AS D1
INNER JOIN


( SELECT Date,Duration=AVG(DurationAll)

FROM IR_UserDaily

WHERE

UserID IN(SELECT FilterID FROM IR_Filter WHERE ReportID=@RID)AND(Date BETWEEN @LowDate and @HighDate)

GROUP BY Date

) AS D2
ON D2.Date < = D1.Date
WHERE (D1.Date BETWEEN @LowDate and @HighDate) AND (D2.Date BETWEEN @LowDate and @HighDate)
GROUP BY D1.Date,D1.Duration
ORDER BY D1.Date



The problem is that the avg function is returning the avg for all values up to the inner join condition, I think.
Can someone help me here I know it should be simple.

View 1 Replies View Related

Need Two SUMS In Pivot Table

Feb 6, 2008



I currently have a pivot table query that is working great but I need to add to it. The below code is giving me the total ServiceTime per date. I need to split this service time out depending on the stage of this note. Basically I need to pivot table queries and join them together.

1) If the note has been signed
2) If the note has been signed and countersigned

SUM(CASE WHEN countersigned_id IS NULL AND signed_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
0 END) as PendingServiceTime,

SUM(CASE WHEN countersigned_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
0 END) as ApprovedServiceTime

How do I add this to my pivot table query and the dates are dynamic.

SELECT lastname + ', ' + firstname as FullName, [12/3/2007], [12/4/2007], [12/5/2007]
FROM (SELECT p.LastName, p.FirstName, t.ServiceDate,

ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) AS ServiceTime

FROM dbo.allNotes(8) AS t
LEFT JOIN dbo.note_Collateral_provider AS d ON d.note_Collateral_id = t.ID
LEFT JOIN dbo.Personnel as p ON d.personnel_id = p.ID
LEFT JOIN dbo.Clients as c on t.ClientID = c.ID
LEFT JOIN fPayor(8) fp on fp.noteId = t.id and fp.dbTable = 'collateral'
LEFT JOIN dbo.payor py ON py.ID = substring(fp.fPayorName, 41, 19)
LEFT JOIN dbo.payorinfo pyInfo ON pyInfo.ID = py.payorinfoid
WHERE t.AgencyID = 8 AND t.tableName = 'collateral'
AND t.not_billable_reason_id IS NULL AND VOID_ID IS NULL
AND ((t.signed_id IS NOT NULL AND t.countersigned_id IS NULL) OR (t.countersigned_id IS NOT NULL))
AND t.ServiceDate BETWEEN CONVERT(DATETIME, '12/03/2007') AND CONVERT(DATETIME, '12/05/2007')
) rs Pivot (SUM(rs.ServiceTime) FOR rs.ServiceDate IN ([12/3/2007], [12/4/2007], [12/5/2007]

View 1 Replies View Related

Return The Results Of A Select Query In A Column Of Another Select Query.

Feb 8, 2008

Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?

View 9 Replies View Related

Joe Celko Nested Set Model: How To Compute The Lft And Rgt Cols

Jul 3, 2006

Hi,

With reference to http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427
I want the "sql stmt" which wud give the lft and rgt col values..

i am reading his book but cant understand :eek: where he explains
wat lft and rgt cols are..

"The root is always (lft,rgt) (1, 2*(Select count(*)from table) and leaft nodes are (lft+1=rgt)" :S

View 3 Replies View Related

Compute Parent Status From Child Statuses?

May 22, 2008

Take the example of a master-detail/parent-child set of tables, say, for example, an order table and order details. The order details table has bit field called "IsShipped". I want the overall order status to be shipped=true when all of the order details have their IsShipped column true. Let's say this is in a stored procedure that returns a result set of orders and whether or not they are completely shipped. How could I achieve this?

View 10 Replies View Related

SQL Statement To Compute Employee Pay For A Year At Different Pay Rates

Nov 23, 2005

I would like a single SQL to return all employee's total billablecompensation for a year. Their billable rates change throughout the year sounder the employee table (one), there is a compensation table (to many)which has the employee id, effective date, billable hourly rate. So in agiven year calendar year they could have many different (though usually 2 atmost) rates. These rates then have to correspond to and e multiplied bytheir corresponding billable hours from the time sheet table.I know I could create a series of UNIONs and hard code the effective dates,i.e.select from time sheets where employee=john and timesheet.task_date betweenjan 1 and jun 1, compensation.billable rate * timesheet.billable hoursUNIONselect from time sheets where employee=john timesheet.task_date between jun1 and dec 31 compensation.billable_rate * timesheet.billable_hoursI'd have to do that for every employee in a very large SQL.Is there an easier way using straight SQL? If not could it be done with astored procedure?Thanks for any insight.

View 2 Replies View Related

TSQL Or Procedure To Compute According To Previous Data

May 19, 2008

I have three coloumn in Salary Table
Emp ID, Emp Salary , Sequence
a1 1000 1
a1 2000 2
a1 2000 3
a2 4000 1
a2 5000 2
a2 5000 3
a2 6000 4



Now I have to calculate the count on salary if the previous salary is different then count +1 else is previous salary same then add +0.
so output be
EmpID and Updation in Salary
a1 = 2 and for a2 =3

Can anyone help me with the query or storeprocedure i can achieve this output counting according to previous data.

View 9 Replies View Related

How To Compute Biweekly Periods When Date Is Passed In As Parameter

Oct 7, 2014

How can I compute the biweekly periods when a date is passed in as a parameter, for example if the user enters 9/12/2014 I should get:

Friday /2nd half9/12/2014
Saturday9/13/2014
Sunday9/14/2014
Monday9/15/2014
Tuesday9/16/2014
Wednesday9/17/2014
Thursday9/18/2014
Friday/1st half 9/19/2014
Friday/2nd half9/19/2014
Saturday9/20/2014
Sunday9/21/2014
Monday9/22/2014
Tuesday9/23/2014
Wednesday 9/24/2014
Thursday9/25/2014
Friday/1st half 9/26/2014

View 2 Replies View Related

SQL 2005 On Windows Compute Cluster 2003 Will It Work ?

Mar 22, 2007

Hi all



I am busy looking at the WIndows 2003 Comptu Cluster setup (Well I am loading it)

And I am interested to know if any body has run SQL 2005 Enterprise on this platform before ?

I know that Windows Compute Cluster uses a JOB scheduler to run tasks and this can be customised for each task , to allow it to use more resourses or less .

I also know that SQL 2005 is much more capable of running cluster mode with database replication and log file shipping

I want to know is of I load SQL 2005 on a Clustered Compute setup will it handle the SQL jobs, database requests with out the scheduler.



The question might be a bit tricky but any help is appricated



Thanks



Sylvester

View 2 Replies View Related

Data Access :: How To Compute Student Grade In Server

Nov 21, 2015

i am having problem putting this query to calculate students grade using the condition and legend bellow.

WHEN EXAMS BETWEEN 75 AND 100 THEN 'A'
WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB'
WHEN EXAMS BETWEEN 65 AND 69 THEN 'B'
WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'

[code]...

View 7 Replies View Related

Power Pivot :: Compute Billing In Previous Period

Jun 14, 2015

Imagine a 5 column dataset with the following fields - Organiser, Date of Invoice, Total Invoice Value, Reimbursements and Service tax charged.  Using the PowerPivot, I want to determine the "Growth in Professional fee billed over the previous period" - please note that previous period need not be previous year because a client may be billed after a gap of 1-2 years as well.To compute growth, I first need to determine the absolute value of Professional fee billed over the previous period.  This is where I am getting stuck.  Since the billing periods for different clients need not be consecutive, I cannot use the SAMEPERIODLASTYEAR function.

In trying to solve the problem, I tried to frame a calculated field formula but could not do so.  Therefore, I tried solving it via a calculated column formula in the PowerPivot window.  My idea here was to determine the client wise previous financial year for each row and then use this column in a calculated field formula to get my desired result.  I am getting an error when I write this formula (see Billing data tab of PowerPivot window).

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved