I have a report with several columns which include Status(0,1,2) and Time in hours. I want to be able to total up the time for each status. I Sum up the all of the fields fine. I'm new to reporting services so any help would be appreciated. Thanks
View 3 RepliesHi,I need some help in summing each column in a gridview.id name sun mon tue wed total1 Tim 5 6 5 10 263 Sam 6 6 6 5 23The above is how the gridview looks like. In the database, I have all the fields except for total. So, I know I have to use the SUM function in SQl to get the Total. So, I am wondering how do I sum each column to get the total. I have something like this but it doesn't work:"SELECT ID, name, Sun, Mon, Tue, Wed, SUM(Sun + Mon + Tue + Wed) AS Total FROM testTable"Please helpahTan
View 7 Replies View RelatedHello, This is my first post so please be kind. I have been attempting to convert a query I built in MS Access for use in MSSQL 2000, the syntax for these is different so I was frustrated to find out I could not use the access query.
I have 4 columns one containing a user Id and the others costs, I wish to total the costs per user ID at the end of each row.
So far I have managed to convert about half of my access query, this gives mev the clientID's and costs in columns but I cannot for the life of me get the costs in a total. It's annoying because my access query works perfectly.
This is my Access query:
SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.CostPerNight,
Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings!Booking_Cost+Room_Facilities!FacilityCost AS TotalCost,
[TotalCost]*17.5/100+[TotalCost] AS [Total+VAT]
FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;
And this is what I have been able to salvage into MSSQL format:
SELECT
Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Rooms.CostPerNight,
Room_Facilities.FacilityCost
FROM
Rooms
INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)
How can I total the three columns and add the tax?
hello, i'm using sql200 and i am attempting to create a table that has an hourly-incrementing 'Date_Time' column, with a corresponding 'Total' column (which keeps a running total of values off of another table) . The code I am using right now is...
declare @date as smalldatetime
set @date = dateadd(yy, -1, cast(convert(char(11), current_timestamp, 101) + '00:00:00' as smalldatetime))
select dateadd(hh, i, @date) as Date_Time, sum(Subtotal) as Total
into #POGtable
from Pivot, OrderGroup
where
i between 0 and 24 and
CreationDate between @date and dateadd(hh, i, @date)
group by i
select dateadd(hh, i, @date) as Date_Time, 0 as Total
into #Ptable
from Pivot
where i between 0 and 24
group by i
select *
from #POGtable
union
select * from #Ptable p
where not exists(
select * from #POGtable pog
where p.Date_Time >= pog.Date_Time)
the solution is ugly, but the problem i'm having is that values for 'SubTotal' don't usually appear before 8 or 9 am. what you see above is me getting all the times (hours) that a subtotal present, creating another table with every possible hour in it (and with a 'Total' column as just zero), and then combining the two tables to create one flowing table over a 24-hour period.
there has GOT to be a better way to do this; the main point being that i want the sum( ) function to start adding up values immediately so i don't have to union two tables
In SQL server Reporting service we need to export excel formula for summing column values. scenario : After generating report we are exporting report to excel file using report viewer.when user will modify a column value we need to calculate(update) automatically sum of the column values.Basically we are setting excel formula.
View 3 Replies View RelatedMS SQL Server 2008 R2
I have both positive and negative values in a single column, where I want sum total of positive values & negative values. Is there any Expression for this to sort out.
Hi!
I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:
Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName
Create a derived column in the SSIS flow which does the same thing
Create the ProductShortName column as a computed column which uses substring on ProductName
Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated
Create a named calculation in the table in the Analysis Services project's data source view
Create a named query in the Analysis Services project's data source view
I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried?
Which approach(es) do or would you use? Pros and cons?
Thanks!
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
Using MDS 2012: I have an entity "XYZ_Entity". In "XYZ_Entity" entity I have 2 domain based Columns "DealerGroup" and "Dealer".
While inserting information into "XYZ_Entity" entity user can select the required dealer group from domain base Dealer Group values. Now for selecting Dealer he wants the dealers to be filter based on selected dealer group and he can select from the filtered list. reason to do that is he don't want to go through thousands of dealers and select an incorrect one.
Is it possible, if yes then how?
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
View 5 Replies View RelatedI have Table Like this
t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail
[Code] ....
Table structure as follows
Employee
Empno empname salary
commission
I want to have an other employee table named employee_modified
Empno empname salary
commission derived_column1(salary+commission)
derived_column2(derived_column1 + xxxx) and so on derive other
columns based on the earlier derived columns)
Is that possible to do it.. or am I doing something wrong.
something like
Select empno , empname , salary , commission,
(salary + commission) as derived_colum1 ,
(derived_colum1 + xxxxx) as derived_colum2 ,
(derived_colum2 + xxxxx) as derived_colum3
into employee_modified from employee
#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies View RelatedI am trying to add a column to query based on the value of another column in the query.
I first tried creating a calculated field in SSRS 2008 with this statement:
=IIF(Fields!ChargeableFlag.Value=1,Fields!Negamt.Value,0)
The report runs but I get a "#ERROR" when I place the field on the report.
I next tried creating a new column with the SQL statement:
SELECT Project.ProjectCode AS PC, Project.StatusCode AS SC, Time.StandardHours AS Hours,
Time.StandardChargeAmt AS StdAmt, Time.TaskUID as UID,
Time.StandardChargeRate as Rate, ChargeableFlag, 'Bill' =
Case
When TaskRule.ChargeableFlag = 0 Then 'Non-Bill'
When TaskRule.ChargeableFlag = 1 Then 'Billable'
[Code] .....
This query, less the case statement for BLAmt creates the dataset for the SSRS. Adding the Case statement for the BLAmt produces the error: "Invalid column name 'Negamt'."
How to Update Column Value in the whole data base (based on Column Value)?
View 2 Replies View RelatedLeaveEntitlementID PeriodID LeaveType EmployeeID NumberOfDays
1 1 Annual 1 10
2 1 Annual 1 10
3 1 Sick 2 10
4 2 Sick 2 10
5 2 Sick 2 10
I have the above table (LeaveEntitlement) which has the above columns.
What I want to sum the column NumberOfDays based on EmployeeID, LeaveType and PeriodID columns as of LeaveTypeNumberOfDays.
For example sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Annual
The result should be shown in new column name AnnualLeave (20)
sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Sick
The result should be shown in new column name SickLeave (10)
Same all leave Types
The table should be shown as the below after executing the query
LeaveEntitlementID PeriodID EmployeeID AnnualLeave SickLeave
1 1 1 20 0
2 1 2 0 10
3 2 2 0 20
is it possible in sql server
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green
  pen       2    0    1
  marker    0    1    0
  pencil      1    3    0
  highlighter  1    1    1Â
We tried Sum but not able to display in single row.
Hi.
I'm writing a web application with VS2005 andSQL Server 2005 express edition.
I have an SQL table:
Table name:
statistics
Columns:
stat_id
firm_name
stat_month
stat_year
view_count
follow_count
percentage
When a user clicks a button, an sql query is fired which increments the view_count value by one and calculates a new percentage value from this. The query to update the percentage value doesn't work, here's the query:
UPDATE [statistics]
SET percentage = follow_count / view_count * 100
WHERE (stat_id = 15)
This code worked fine with MySQL, but since migrating to MSSql it doesn't seem to work. The data type of the percentage column is: decimal(5, 2)
Any help would be appreciated.
I have two tables with the following structure:
Table 1
---------
id | Name
---------
1 | John
2 | Debbie
3 | Kim
4 | Mary
Table 2
---------
id | Name
---------
654 | John
415 | Debbie
68 | Kim
289 | Mary
Table 2 id's values are all messed up and I need to replace them so that they look exactly like in Table one, my guess is that query will have to replace value of "Id" based on the "Name" column, I hope I made myself clear.
I have this table where there is tow primary keys ,one for Year and the other for DocNumber.
In this table the DocNumber takes a number based on each year,for example in year 2008 the DocNumber should start counting from 1 and increment by one for every record,when the year changes to 2009 the DocNumber should start again from 1 until and seed by one again.
Those columns are PKs because there must be one doc Number for each year and they can not be null .
Is there any simple way to approach this in Sql Server 2005 ,or do I have to work on trigger to do so...???
Any ideas will be really appreciated.
Eng.Osman Hamad
http://arabic.aspose.com
I have a column with serial numbers 1, 2,3,4,....etc...I want to have P1 for first 4 serial numbers to be displayed in a separate column beside Serial number column and for the next four serial numbers (5,6,7,8) it should be P2 and like that I want till P13 and for next again it should start with P1(I mean after P13 it should give again P1 instead of P14) and continue the same process.
Column name of Serial number is [S.No]
table name for example is #temp1
I am trying to do the following.
While inserting a record into a table I need one field to be created based on a stored procedure output.The stored procedure will create something like this, "XX-mm-yyyy-incremental number which resets to zero at the end of every month". All other field except the Identity field and the above mentioned storeproc generated field will be inputted manually. How can I do this?
Thanks
Hope my question is clear enough.
Greetings,
I need to avg column data based on grid textboxes. I cannot use AVG because the column values come from a comma delimited string. I have tried using !Parameters to store a count and total for each column but they are always read only. What is the best approach.
The columns come from a field which is in the format of (99,75,60,100,-1,20,-1,80,75) for each record
-1 means the values are not counted in the average. I have a function for each textbox that parses the value from the list based on the column index. There can be 1 to many columns.
Everytime I to set the value of a field or parameter at runtime I get a read-only error.
Maybee the only way is to use calculated fields.
Any ideas.
I am trying to check a list (MyList) against another List(SupplierList).I want sum the Qty's of UniqueID on MyList and extract the sum of thesame UniqueId's on SupplierList.BTW There are more than one instances of Unique Id on each list.The Script below is providing me with the correct answer for someproducts (UniqueId), but incorrect amounts for others.The incorrect answer is always a multiple of the correct answer.What am i doing wrong???Regards,CiaránSELECT MyList.[Unique ID], SupplierList.[Unique ID], Sum(MyList.[SHP_QTY]), Sum (SupplierList.[Qty new])FROM MyList LEFT OUTER JOIN SupplierList ON MyList.[Unique ID]= SupplierList.[Unique ID]GROUP BY MyList.[Unique ID], SupplierList.[Unique ID]
View 1 Replies View RelatedI'll try to simplify the problem as much as I can.
There's survey. It has different types of question answer pairs, but the problem is in Multiple Choices, Multiple Answers questions, where user can choose, for example 3 checkboxes from 5 at the web form, for the question.
I am emphasizing three tables:
tbl_questions
IDquestion (primary)
question (textual question)
tbl_answers
IDanswer (primary)
IDquestion (foreign,to table tbl_questions)
answer (textual answer)
tbl_results
IDresult (primary)
IDquestion (foreign, to tbl_question)
result (string based on CSV, may contain few IDanswers, because visitor may choose few checkboxes, for example it can be "4,6,7")
Inside those tables are data (I'll put here just two questions):
Example:
tbl_questions
IDquestion_question
1_________Which OS do you use?
2_________Which databases do you use?
tbl_answers
IDanswer_IDquestion_answer
1________1_________Windows
2________1_________Linux
3________1_________Mac OS
4________2_________SQL Server
5________2_________MS Access
6________2_________MySQL
7________2_________Oracle
8________2_________Other
9________2_________No, I do not use databases
tbl_results
IDresult_IDquestion_results
........
23_______1________1,2
24_______2________4,6,7
........
Is there a possibility to make query, using columns, but also the data inside the CSV format in results (varchar or char) column?
Result of the query would be:
IDquestion_IDanswer_answer
1_________1________Windows
1_________1________Linux
2_________4________SQL Server
2_________6________MySQL
2_________7________Oracle
Further queries based on this query should be able to make statistics (GROUP BY).
i have Table A - Column A,Column B...Column B can not be a null value if Coulmn A = "xxx", how can i do this.
View 2 Replies View RelatedSELECT EmployeeId, PiecemealType,SUM(PiecemealQty) /100 AS TotalTrays, ActivityId
FROM SR.dbo.PayTrays
WHERE WorkDate > '2012-01-01'
AND WorkDate < '2012-12-31'
GROUP BY EmployeeId, PiecemealType, ActivityId
ORDER BY EmployeeId, PiecemealType
PiecemealType is the type of tray
1= 15 Items per tray
2= 12 Items per tray
3= 8 Items per tray
4= 6 Items per tray
I'm trying to work out a bonus for 1 cent per item worked. The divide by 100 moves the total from cent to dollar and the SUM is adding all weekly values together.I just need to work out how to multiply the the SUM value for trays by the 4 different PiecemealType values. If it was static i could just add *15 to my SUM.
Trying to figure out a SQL query.I have 2 identical tables: Table A and Table B..This query doesnt seem to be working:
SELECT * FROM (
SELECT ClientID, sum(ABC), count(*)
FROM Table A
Where <condition>
[code]...
The result I am looking for is that for each ClientID, I need the total sum of ABC and total count.
I have a view where the results would be like this.(userid,name,rolekey are my col names with data)
userid name rolekey
test1 tname rolekey1
test1 tname rolekey2
test1 tname rolekey3
is this possible to retireve data from view where i need only userid with rolekey1.? tried with a function but its taking more time? any options in doing it in the view itself?
I have a table that is something like this:
Animal | Count
Dog |10
Dog |5
Dog |16
Dog |2
Cat |1
Cat |12
Cat |14
Cat |7
I need to write a query that will return something like this:
Dog_Count| Cat_ Count
10 |10
5 |5
16 |16
2 |2
hi,
i have the following scenario,
100 tables in the database,i have to display the table names if the column name given by the user is repeated in the different tables...
e.g.: Let us say,the column name ID IS REPEATED in the 70 tables out of 1000 tables.Then i have to display all the 70 table names..
how to write sql query for it?
i am greateful if any one help me in solving this scenario..
I have a large website with over 100,000 images and the location of the images are stored in a column (img_url) as below:
/images/imagename.jpg
Because all these images are stored in the same folder it is hard to manage so we want to store each image under a directory based on the 1st letter of the image name, ie:
/images/a/aimage.jpg
/images/b/bimage.jpg
I can automate the physical move of the images into the correct directory but I need SQL update query that will update each column based on the 1st letter of the image.
So:
/images/aimage.jpg
/images/bimage.jpg
Updated to:
/images/a/aimage.jpg
/images/b/bimage.jpg
I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....
Sales Table:
BillMasterId BillDate CustomerId NetAmount CreditAmount
26 03/03/2014 101 1000 1000
My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101
My Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 0
03/03/2014 1000 500 0
03/03/2014 1000 300 0
03/03/2014 1000 200 0
Exact Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 1000
03/03/2014 1000 500 500
03/03/2014 1000 300 200
03/03/2014 1000 200 0