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 billable
compensation for a year. Their billable rates change throughout the year so
under the employee table (one), there is a compensation table (to many)
which has the employee id, effective date, billable hourly rate. So in a
given year calendar year they could have many different (though usually 2 at
most) rates. These rates then have to correspond to and e multiplied by
their 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 between
jan 1 and jun 1, compensation.billable rate * timesheet.billable hours

UNION

select from time sheets where employee=john timesheet.task_date between jun
1 and dec 31 compensation.billable_rate * timesheet.billable_hours



I'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 a
stored procedure?



Thanks for any insight.

View 2 Replies


ADVERTISEMENT

SQL Server 2014 :: How To Update The Year Employee Has Been In Specific Title

Apr 1, 2014

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable
(
EMP_ID INT,
Title varchar(50),
DateValue DATETIME,
TITLE_YEAR INT,

[code]....

I am new to this level of coding in SQL SERVER 2012, but I am looking to update the TITLE_YEAR field in the temp table with the Year the employee is in that title. For example for employee 11127 the data should look like this:

EMP_IDTitle DateValue TITLE_YEAR
3 Senior Consultant 2009-01-01 00:00:00.0001
3 Director 2010-01-01 00:00:00.0001
3 Director 2011-01-01 00:00:00.0002
3 Director 2012-01-01 00:00:00.0003
3 Director 2013-01-01 00:00:00.0004
3 Senior Director 2014-01-01 00:00:00.0001

View 6 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

Create WHERE Statement That Will Calculate Values From Current Fiscal Year To Last Complete Month

Feb 3, 2015

I'm trying to create a WHERE statement that will calculate values from our current fiscal year to the last complete month.I'm using code that was created for us that does the calculations for our entire fiscal years. I thought I had fixed the WHERE statement to work like we wanted last year, but it appears to be broken now after trying it again in January and February. I'm guessing my WHERE statement only works for March and up, but how to get it to work for every month. Most attempts I'm trying it's just returning very large and inaccurate values.

I included my WHERE statement below of what I originally had that worked last year. The @BeginYear/Month/etc are retrieved from a different table and @Month is just set to MONTH(GETDATE())-1.

WHERE
(YEAR(SA3.DocumentDate)=@BeginYear AND MONTH(SA3.DocumentDate)>=@BeginMonth AND MONTH(SA3.DocumentDate)<=@Month)
OR
(YEAR(SA3.DocumentDate)=@EndYear AND MONTH(SA3.DocumentDate)<=@EndMonth AND MONTH(SA3.DocumentDate)>=@Month)

View 6 Replies View Related

Rates Of Change...

Jul 20, 2005

Hi,Is there an easy way to determine the rate of change for table? I realizethat I could do periodic select count(*) from XXX, but that would onlydetermine the size of the table. It would ignore updates.What I'd really like to do would be able to determine the number of non-readoperations over a table so that I can determine the rate of change for thattable.Thanks for any help..

View 1 Replies View Related

Printing Out Transfer Rates

Dec 19, 2007

Hi All,
I would like to print out a dataflow's tarnsfer rate (row/sec) to the output window and refresh it in each 5. second. Could anybody help me?
Thanks in advance.
m

View 9 Replies View Related

Fiscal Year Totals - Calculating Sales By Month And Current Year

Sep 18, 2013

I have the following script that calculates Sales by month and current year.

We run a Fiscal year from April 1st thru March 31st.

So April 2012 sales are considered Fiscal Year 2013.

Is there a way I can alter this script to get Fiscal Year Totals?

select ClassificationId, YEAR(inv_dt) as Year, cus_no,
isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr',
isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May',
isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun',
isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',

[Code] ....

Data returned looks like the following.

ClassificationID Year Cus_no Apr May June ....
100 2012 100 $23 $30 $400
100 2013 100 $40 $45 $600

What I would need is anything greater than or equal to April to show in the next years row.

View 2 Replies View Related

Reporting Services :: Calculate Sales Percentage Difference Between Selected Year And Previous Year In A Matrix

Mar 27, 2015

I'm trying to generate a report using matrix like this

                                                      Month
Product     PreviousYearSalesAmount    SelectedYearSalesAmount      %SalesDifference

I can populate year sales amount, but i cant calculate the percentage.

Note: Month and Year are passed as parameters.

View 5 Replies View Related

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year

Aug 30, 2004

I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,
Cesar

View 4 Replies View Related

SQL Server 2012 :: How To Get This Year And Last Year Totals In Two Separate Columns

Jun 19, 2014

I have two queries that give me the total sales amount for the current year, and the last year.

SELECT SUM([Sales (LCY)])
FROM [$Cust_ Ledger Entry] cle
LEFT OUTER JOIN dw.dim.FiscalDate fd
ON fd.CalendarDate = cle.[Posting Date]
WHERE [Customer No_] = '10135'
AND fd.CalendarYear = '2013'

[Code] ....

I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.

This is my query without the columns I need:

SELECT
c.CustomerNumber
,c.Name
,c.ChainName
,c.PaymentTermsCode
,cle.CreditLimit AS 'CreditLimit'
,SUM(cle.Amount) AS 'Amount'

[Code] ....

View 1 Replies View Related

Average Download Rates For SQL Server

May 4, 2007

I have a SQL Server hosted on a Win2003 server. I have a monitor on the server letting me know the average download speeds. When I do a large select from a remote connection, it looks like the download rate maxes out at around 10 kb/sec.

Is this an average download rate for a server hosting sql server?

View 5 Replies View Related

Latest Known Exchange Rates MDX Query

May 29, 2008

Hi,

I have this specific problem, that has been causing me alot of headaches for the last couple of days, and I can't seem to fiure it out on my own.

Basically i am working on a many to one currency conversion setup, where I have a fact table with exchange rates for periods and for all input currencies. The reporting currency is USD. Here is a demonstration of the data in it:

Valid Date Source Currency Rate
2008-01-01 GBP 2.0563
2008-01-01 EUR 1.4634
2008-04-01 GBP 1.9968
2008-04-01 EUR 1.5197

Normal period by period conversion is easy enough, but I want to convert the measures at the latest known rate, based on the period selected. Lets say I run an analysis without slicing on time, then it want it to convert at the last entered rate of all time, and if the user wants to run the analysis as of March 08, then it should convert all data to the rate as of January, since this is the latest one known.

I have tried to set the measure to a semi-additive behaviour, with "lastnonempty", but the problem with that is the conversion over time. Here is an example:

I want to show a measure summed "all to date" as of June 08. What I want is it to convert everything at the rates entered in April 08, but what I get is that it convert everything up until Jan 08 and the rates entered in January, and thereafter it starts to use the rated entered in April.

Period Amount LCY Currency Code Exchange Rate
Dec 07 100 GBP 2.0563 (Wrong, should be 1.9968)
Dec 07 100 EUR 1.4634 (Wrong, should be 1.5197)
Jan 08 100 GBP 2.0563 (Wrong, should be 1.9968)
Jan 08 100 EUR 1.4634 (Wrong, should be 1.5197)
Feb 08 100 GBP 1.9968
Feb 08 100 EUR 1.5197
Mar 08 100 GBP 1.9968
Mar 08 100 EUR 1.5197
Apr 08 100 GBP 1.9968
Apr 08 100 EUR 1.5197
May 08 100 GBP 1.9968
May 08 100 EUR 1.5197
Jun 08 100 GBP 1.9968
Jun 08 100 EUR 1.5197

How do I create a measure containing the latest known rates based on time selection?

Thanks in advance

View 2 Replies View Related

Select Month/year When Range Spans Year

Feb 25, 2004

I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.

The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.

I've tried evaluating the parts of the episode date seperately like:


where
(epimonth>=10 and epiyear=2003)
or
(epimonth<=2 and epiyear=2004)


and that works, but what happens when someone wants to see from 10/2002 to 2/2004?

Any suggestions on the best way to do this?

View 5 Replies View Related

How To Find Year And Past Year Compare In Business

May 5, 2014

We have customers who are new to this year (2014) and there same customers in last year (2013). also there are customers we have not received business this year but only last year. so there are 4 conditions.

1) New customer (2014) - Customer(B)
2) Old Regular customer (2013 and 2014) - Customer(A)
3) Last Year (Lost) customer (2013) - Customer(C), no business received in year(2014)

For example we have a transaction table:

TransactionId, ReceivedDate, Customer
1, 2-Dec-2013, A
2, 3-Jan-2014, A
3, 2-Mar-2014, B
4, 25-Nov-2013, C

I want results like

Customer, Business (this year activity/last year activity)
A, 1/1
B, 1/0
C, 0/1

How can i show this for each year? I used to separate it month wise as below but it does not return applying year with each customer anyhow...

select t.customerId, YEAR(Receiveddate),
sum(case month(ReceivedDate) when 1 then 1 else 0 end )as Jan,
sum(case month(ReceivedDate) when 2 then 1 else 0 end )as Feb,
sum(case month(ReceivedDate) when 3 then 1 else 0 end )as Mar,
sum(case month(ReceivedDate) when 4 then 1 else 0 end )as Apr,

[Code] ....

View 2 Replies View Related

Compare Given Period In Current Year / Previous Year

Mar 15, 2006

HiI want to write a function that can return a sum for a given daterange. The same function should be able to return the sum for the sameperiod year before.Let me give an example:The Table LedgerTrans consist among other of the follwing fieldsAccountNum (Varchar)TransdateAmountMST (Real)The sample data could be1111, 01-01-2005, 100 USD1111, 18-01-2005, 125 USD1111, 15-03-2005, 50 USD1111,27-06-2005, 500 USD1111,02-01-2006, 250 USD1111,23-02-2006,12 USDIf the current day is 16. march 2006 I would like to have a functionwhich called twice could retrive the values.Previus period (for TransDate >= 01-01-2005 AND TransDate <=16-03-2005) = 275 USDCurrent period (for TransDate >= 01-01-2006 AND TransDate <=16-03-2006) = 262 USDThe function should be called with the AccountNum and current date(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.How can I create a function that dynamically can do this ?I have tried f.ex. calling the function with@ThisYear as GetDate()SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +'-'+datepart(y,@ThisYear)But the value for @dateStart is something like 12-07-1905 so thisdon't work.I Would appreciate any help on this.BR / Jan

View 3 Replies View Related

How To Do A Year-to-date SQL Query Where Year Commences In August?

Jul 20, 2005

Does anyone have an example of an SQL query which returns rows for theyear-to-date, but where the "year" commences on August 1st?e.g. select * from mytable where datefield > last august 1stTIA for any helpIsabel

View 2 Replies View Related

Power Pivot :: DAX Ranking Events Year Over Year?

Jul 14, 2015

I have a table of data that has a format similar to the following:

EventID | Event Date
--------------------
1 | 1/1/2014
2 | 2/8/2014
3 | 10/1/2014
4 | 2/5/2014
5 | 4/1/2014
6 | 9/1/2014

What I am trying to do is create a DAX formula to rank each event in the order that it happened for the year. So I want to end up with something like this. This way I can compare the events year over year as the events don't happen on any regular time schedule.

Event Date | Year | Rank
------------------------
1/1/2014 | 2014 | 1
2/8/2014 | 2014 | 2
10/1/2014 | 2014 | 3
2/5/2015 | 2015 | 1
4/1/2015 | 2015 | 2
9/1/2015 | 2015 | 3

I have tried to do this by creating a formula that will give me the day number of the year:

Day of Year =(YEARFRAC(CONCATENATE("Jan 1 ", YEAR([Event Date])),[Event Date])*360)+1

Then using rankX on this table, but I cant seem to get the proper result. Perhaps I am not understanding the use of rankX or going about this the right way.

=RANKX(FILTER(Event,EARLIER(Event[Event Year])=Event[Event Year]),Event[Day of Year])

or

=RANKX(All(Event[Event Year]),[Day of Year],,1,Dense)

View 5 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

SQL Server 2012 :: Get List Of All Workers Having Same SET Of Rates?

Feb 3, 2014

I have attached some test data for you that has two temp tables "#worker" and "#worker_rate".

The issue is to find all workers who are sharing SAME SET of rate_codes.

I'm able to get the output as "workers sharing same rate_codes", but unfortunately I could not get the list of workers sharing same SET of rate_codes. Also definition of SAME SET is not defined.

I don't know what I'm missing.

View 9 Replies View Related

T-SQL (SS2K8) :: Comparing Various Currencies Hedged Rates

Jun 13, 2014

I have a task to provide users with a list of currency rates in the following format:

Base Currency
Hedged Currency
Base to Hedge Rate
Hedged to Base Rate

E.g.

"Base Currency"..."Hedged Currency"..."Base to Hedge Rate"..."Hedged to Base Rate"
GBP....................USD.......................1.70.........................0.59
GBP....................EUR.......................1.25..........................0.80

(the dots are for the visual only)

The table I have been provided in SQL has the following structure:

Currency Code
Currency Rate (Rate against the Base Currency)
Base Currency (BIT to recognise which currency is the base one as it can vary from site to site. Only once currency can be Base per a given site)

I have started to implement, but ended up with cursor and temporary table, where I insert the data.

View 3 Replies View Related

SQL 2012 :: SELECT Query On Currency Rates

Jun 20, 2015

I have this query to select the highest currency rate from a currency table:

SELECT
Target_currency AS [Currency]
,rate_exchange AS [Rate]
,date_L AS [Date]

FROM
rates

WHERE
target_currency IN ('USD','GBP','JPY')

ORDER BY
target_currency desc
,date_L desc

My output is something like this:

CurrencyRateDate
USD 1,13572015-06-20 00:00:00.000
USD 1,37952014-03-31 00:00:00.000
USD 1,28352014-03-24 00:00:00.000
USD 1,28522013-04-05 00:00:00.000
JPY142,37552014-03-31 00:00:00.000
JPY1202014-03-24 00:00:00.000
JPY119,32013-04-05 00:00:00.000
GBP 0,82872014-03-31 00:00:00.000
GBP 0,08482014-03-24 00:00:00.000
GBP 0,084392013-04-05 00:00:00.000

How can I get the top 3 of the most recent rates from every rate in the WHERE filtering?

So:

USD 1,13572015-06-20 00:00:00.000
JPY142,37552014-03-31 00:00:00.000
GBP 0,82872014-03-31 00:00:00.000

I have tried many solutions, but I can't get it to work.

View 4 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

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

SSIS Package Fails In A Job - Currency Rates Updater

Oct 12, 2006

Ok so I created a SSIS package to get currency exchange rates from web service - http://www.webservicex.net/CurrencyConvertor.asmx . Everything works fine I can run it successfully - i uploaded to SSIS package store - runs successfully./

BUT when I put it in a job - it FREAKING fails every *dem time.

In addition to run it the JOB as SSIS package from store - also straight from the File System = still same freagging error - and SQL 2005 has gotten more stupid than SQL 2000 & DTS - they provide no clue as to y you get an error

Help please?

View 4 Replies View Related

How To Add The Total Balances Of Previous Year Into The Next Year?

Apr 13, 2006

I'm working with a table called SALFLDGAMS where it has two columns that I need to work with. One of the columns is Period which has years from 2000 to 2005 and the other column is called amount which has the balance for that year. Let me explain in a little more detail. There are account codes associated with the years as well so there will be many places where for example the year 2000 will show up with a given value. What I'm trying to do is to create a view which has a BeginBalance column which adds lets say all the values for 2000 and sticks them to 2001. So what I'm saying is all the values from the previous year I want them in the current year. All in one column. Thanks for the help guys.

View 8 Replies View Related

Parameter Year And Previous Year (Beginner)

Dec 14, 2006

I have one matrix that shows the CrashCount (measure) by month. Looks like this:



Jan Feb Mar Apr May Jun Jul Aug Sep Oct Dec

25 90 100 55 52 55 22 55 22 35 65



The user selects a Year as a parameter. I want to put another matrix in that displays the previous year, just as the first year is displayed. How do i edit the second matrix? Do i put the parameter as

=(Parameters!CrashStatisticalYear.Value)-1 or is there some other way this can be done. Without having the user put 2 years. I just want them to pick one. And the previous year shows up in the matrix below this one. Can anyone help me with this...what should i do?

View 6 Replies View Related

Sales For Current Year To Previous Year

Oct 20, 2007

Guys,

I wanted to find the ratio: (sales made for current year 2007 - sales made for previous year 2006)/sales made for previous year 2006.

so, the result should be something like this:

Year: Sales: %change in sales:
2005 100 10%
2006 200 20%
2007 300 30%

How do I write a query for this...??? so that i can plot this in a chart in SSRS.

somebody help me.

View 4 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

Data Collection On Production Server To Capture Growth Rates

Mar 18, 2015

I setup data collection on a production server to capture growth rates.

When I run the dis usage report, it shows a daily growth rate of over 500 megs. This seems excessive to me.

As a troubleshooting step I then ran sp_space used and got these results:

database_namedatabase_sizeunallocated space
rgc_prod 273442.63 MB3648.48 MB

reserved data index_size unused
265345488 KB164385384 KB99826072 KB1134032 KB

What should my next steps be to try and determine why there is so much growth? And isn't the index size rather large?

View 1 Replies View Related

T-SQL (SS2K8) :: Import Data From XML Feed To Get Daily Exchange Rates

Aug 26, 2015

I'm trying to find a way to import data from this data xml feed to get daily exchange rate. I' tried:

select *
from
openrowset(bulk 'http://www.bankofcanada.ca/stats/assets/xml/fx-noon.xml',single_blob) as x

Which is a feeble attempt at a start; however, am getting this error message:

Cannot bulk load because the file "http://www.bankofcanada.ca/stats/assets/xml/fx-noon.xml" could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).

How to parse this file using SQL.

View 1 Replies View Related

Power Pivot :: Calculating Exchange Rates Depending On MONTH

Apr 23, 2015

I have created a spreadsheet using powerquery and powerpivot. Loading the data on powerquery from different spreadsheets was a charm, the problem is calculating the exchange rates per month.The structure is as following:I have 3 tables, they are:

Dates– Just a dates (loaded directly on PowerPivot).
Invoice – Details of invoices for all customers, gross totals are in different rates (loaded through power query)
Customer – Detailed information of customers (loaded directly on PowerPivot).
Currency – exchange rates (loaded directly on PowerPivot).

Note: I mentioned where they were loaded because I do not know if that really matters, I guess it does not... but extra information will not hurt. Now, let's go to how these tables are connected between each other and their key columns.The Invoice table has information on invoices dates, invoice gross total and customer code. This table is related to Customer table, which has the actual name of customers and other details. This link is done through the Customer Code column. The Currency table have a relationship with the Customer Table through the Country Column.

What I want to achieve is a formula to do the exchange rate based on the Invoice Date column present on the Invoice Table. So I have created a column on the Currency Table named at each month of the year and populated it with the exchange rates for all countries (countries listed using ISO3 format).My problem is while I am able to select a column from the Currency table to do the exchange rate calculation using the below formula, I fail to realize how to do it per month:=CALCULATE(sum(invoice[Gross Value])*SUM(Currency[Reverse]))*Reverse is the column with the exchange rate for the country money to USD.What I am failing to do is how to make that calculation to also consider the month of the invoice and pull the exchange rate for the same month from the Currency table

Get the invoice date then find the column on Currency[Month] and multiply, providing me the USD amount.I was unable to figure out a solution using the filters parameters from Calculate. If some of you ask why I am using SUM inside Calculate, is because if I only use SUM and try to do the math between the two tables I get an error saying that it was not possible to determine the value on Currency[Reverse]. Same happens with SUMX. It worked out with Calculate, but then I cannot create other formulas using CALCULATE on that table because it will give that error on redundancy, unless I add a filter on it, which worked last time (but I do not remember which thing /parameter used to filter, so do not ask it right now.I do not really need that level of accuracy, but my Virgo perfectionist side demands me to find a solution to that, that will not use a single exchange rate, but consider which is the correct exchange rate for the month of the invoice and calculate it correctly for all invoices. Just so you know, the Invoices tables has more than 1million lines.

View 3 Replies View Related

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 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







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