ROLLUP - Retrieve A List Showing Total Repair Cost Per Year
Mar 1, 2014
I have a question regarding ROLLUP and how to use the operator.
I've been asked to retrieve a list showing the total repair cost per year displaying Transport Type, Manufacturer and Transport. The query I used is:
SELECT ty.Description AS TransportDescription,
t.SerialNumber AS Transport,
ty.Manufacturer,
SUM (r.RepairCost) AS RepairCost,
YEAR(r.EndWorkDate) AS [Year]
[Code] .....
How could I use the rollup function to display subtotals per Transport Type, Manufacturer and Transport?
View 1 Replies
ADVERTISEMENT
May 3, 2004
I am using the SQL GROUP BY WITH ROLLUP PER MS's
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndive/html/data01102002.asp
To create subtotals and totals.
The problem is, I want items on every line, like transaction dates, that are neither grouped by or summed. They are just for information only.
ANSI-92 SQL does not allow any column to be selected that is either not grouped on or has an aggregate function performed.
The MS example in the link above works fine, but does not present much data.
If I wanted to sum total orders and each order's subtotal for a given customer, I would also likely want the order date, who the order was placed by, etc. in each line of the report without grouping or summing that extraneous info.
Here is an example of the SQL syntax:
SELECT
CASE GROUPING (companyId) WHEN 0
THEN (SELECT CFP..Company.companyName FROM Company WHERE Company.companyId = Activity.companyId) ELSE 'TOTAL' END AS [Company name],
CASE GROUPING (dateOrder) WHEN 0
THEN (SELECT CAST(dateOrderAS VARCHAR)) ELSE 'SUB TOTAL' END AS [dateOrder],
SUM(transactionAmount) AS [Request amount]
FROM Activity
WHERE DATEPART(year, dateOrder) = DATEPART(year, '2004')
GROUP BY companyId, dateOrderWITH ROLLUP
...
But I want more details.
View 1 Replies
View Related
Jul 20, 2005
I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob
View 4 Replies
View Related
Apr 24, 2000
Running SQL 7, Standard version. I had a database marked 'suspect'. I copied the MDF and LDF files to another directory, then deleted the suspect database. I tried to create a new database and attach the MDF file to it. It did not work. Any other ideas? I have no other backups of the data. I am willing to pay for consulting over the phone if necessary.
View 1 Replies
View Related
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
Sep 26, 2007
Hi there.
I have a corrupt .sdf database sat on a SD Card but i am unable to find a way to repair it.
So far I have tried a few different methods, most of which just tell me what i already know i.e. "the database is corrupt and needs repairing!!"
I am unable to open the file in SSMS object browser (see above message) which means i cant run repair / shrink utilites.
I am also unable to add the database to a vs2005 project (again see above message) meaning again i can't run any repair utilities.
I have also tried using the compact / Repair utility in Query analyser 3.0 on my Windows Mobile 5.0 PDA but once again i am greeted with an "error reading file - Run the Repair Utility" Message.
Lastly i've tried using the SQLCEengine.repair method and, depending on whether i do it on the PDA or on a desktop pc it throws the exceptions "Error occurred reading database file - run the repair utility"(PDA) or "invalid database type" (PC)
Can anyone shed any light as to whether there is another (easier???) way of doing this or if i just have to face it that this file is lost?
Thanks
ps. the code i used for the .repair is as below:
Dim conn As SqlCeEngine
Dim strdb As String
strdb = "SD CardPathnameDatabase.sdf"
conn = New SqlCeEngine(String.Format("Data Source={0}", strdb))
If conn.Verify = False Then
conn.Repair(Nothing, RepairOption.RecoverCorruptedRows)
MsgBox("Successfully Repaired")
End If
View 8 Replies
View Related
May 19, 2008
My FactEmployeeTable showing 13 rows of data related to employees, but i used a bridge table for connecting project and employee dimensions. In the ProjEmpBridge table i mapped only 6 employees data to the proj's data. When dragging projet name and employee name it is showing data related to 6 employees but grand total showing 13 as the result. I dont know why it is showing 13 instead of 6. Can anyone please solve this issue.
View 10 Replies
View Related
May 28, 2015
I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.
This is my script:
With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl
[code]....
the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.
Example:
invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075
That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.
View 4 Replies
View Related
May 6, 2015
I am currently working on a T-Sql query(Sql server 2008) to calculate total no of days between date ranges by year
Table:
Start Date End Date
01/01/2013 04/30/2014
11/01/2014 05/31/2015
06/01/2015 12/31/2015
My expected result.
2013 - 365
2014 - 181
2015 - 365
Note:
Date range can span b/w multiple years
Date ranges will not overlap
I just want the total number of days covered by the range for each year.
Is there any simple way to do this calculation.
View 9 Replies
View Related
Jul 3, 2014
I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?
View 9 Replies
View Related
Feb 20, 2015
In the Reference_Master table I have Total count based on date for each RefCode. Can I get the total count of each RefCode by monthly wise for each year
Reference_Master
RefCode Date Count
100012/18/20074
100012/19/20078
100012/20/20074
100022/18/20073
100022/19/20072
100022/20/20076
[Code] ...
I need the result as below
Result
RefCodeMonth/YearTotalCount
10001FEB-200716
10002FEB-200711
10003Mar-20077
10004Mar-20073
10005Mar-20072
10001FEB-200816
10002FEB-200811
10003Mar-20087
10004Mar-20083
10005Mar-20082
View 8 Replies
View Related
May 28, 2015
I want to get a running total. But its a bit tricky. I have data depending on ID, YEAR, Month, Value like the following.
ID, Year, Month, Value
36002084 2014 Jan 8391
36002084 2014 Jan 0
36002084 2014 Jan 3440
36002084 2014 Jan 0
36002084 2014 Jan 548
36002084 2014 Jan 109
36002084 2014 Jan 125
I wrote the Query below
select
[UEP ID],[Year],[Month],sum([Total VtM EUR]) 'Running Total'
from
RegData
where
[UEP ID]=36002084 and [Year]=2014
group by [UEP ID],[Year],[Month]
I got this output
36002084 2014 Apr 887620
36002084 2014 Aug 789440
36002084 2014 Dec 528453
36002084 2014 Feb 606627
36002084 2014 Jan 331613
36002084 2014 Jul 681314
36002084 2014 Jun 330384
36002084 2014 Mar 1044301
36002084 2014 May 671818
36002084 2014 Nov 465576
36002084 2014 Oct 590147
36002084 2014 Sep 527861
Now I need to get a output like this,
36002084 2014 Jan 331613
36002084 2014 Feb 938240
A running total depending on the ID, Year and Month. Although I showed this for two steps I need to get it from one query.
Question is - To get a running total based on ID, Year, Month. If I do the normal group by and sum query I could get it for all the months for each id. But need to then add January value to February and updated February value.
View 10 Replies
View Related
Feb 3, 2005
Having two tables.
1. ID Year
----------
1 1990
2 1991
3 1992
2. Name Year
--------------
ABC 1
XYZ 2
123 3
By passing year as an argument, Want to retrieve all the Names that does not have records for that particular year.
For example, if pass 1990, then the result set should be 'XYZ' and '123'
Thanks in advance
View 2 Replies
View Related
Feb 2, 2006
Hi friends
how
can i access report server database ?I've C# application and i want to
develop a screen with a grid that loads all reports created on report
server.
am using sql server 2005 standard edition and VS 2005 standard edi.
can someone point me to some sample examples?
Thanks for your help
View 10 Replies
View Related
Feb 6, 2008
I am trying to get JPG to show up in the Export list I have added another instance of the image renderer with a default to jpg. I also have added the following code to rsreportserver.config under Configuration/Extensions/Render:
<Extension Name="JPG" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport,Microsoft.ReportingServices.ImageRendering">
<OverrideNames>
<Name Language="en-us">JPG</Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<OutputFormat>JPEG</OutputFormat>
</DeviceInfo>
</Configuration>
</Extension>
I have restarted the service and tried everything else I can thing of with no luck, it's just not showing up. Does anyone have suggestions?
Here is the version of my server. 9.00.3161.00 SP2 Standard Edition (64-bit)
Thanks
-JW
View 1 Replies
View Related
May 15, 2007
hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev] [Selected Month] [Next>>]
the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
View 2 Replies
View Related
Nov 13, 2007
I have a year-month hierarchy that I am using as a parameter in a report. I'm using the From query choice for available values and using the ParameterValue.
My parameter list displasy as:
2006
1
10
11
12
2
3
...
2007
1
10
11
12
2
3
4
Is there a way to get this to display months as integers rather than strings?
Thanks.
View 5 Replies
View Related
Jul 23, 2007
I've installed both the AW db and dw off the MS download. I see the db in the directory I use for other databases, the dw doesnt seem to want to go where I tell it. Anyway, when I connect to my instance of sql (developer's) I dont see the AW db.
View 1 Replies
View Related
Dec 3, 2007
When using Sql Server Enterprise Manager and viewing a Database / Tables section, most of the tables if not all have a create date of 11/5/2004.Except for one, DNN_Users, has a creation date of 7/10/2007What factor could have caused that create date to have changed?What factors go into the date being set on that column in the database design? Does the date get updated say if I were go go in and change a datatype in a table?
View 5 Replies
View Related
Jul 17, 2006
Time Price Volume
090048 510 6749
090122 510 2101
090135 510 1000
090204 505 2840
090213 505 220
090222 505 1260
090232 505 850
090242 505 200
090253 510 1200
090313 510 570
090343 510 250
090353 510 160
Criteria
Retrieve records with independent price and its total volume per minute
SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4
WHERE sd='20060717' AND serial='0455'
GROUP BY SUBSTRING(st,1,4),d_price,l_cum
Result of the above query: -
Time Price Volume
0900 510 6749
0901 510 1000
0901 510 2101
0902 505 200
0902 505 220
0902 505 850
0902 505 1260
0902 505 2840
0902 510 1200
0903 510 160
0903 510 250
0903 510 570
THE FOLLOWING RESULT STILL NOT A TOTAL FOR A MINUTE]
E.G
0901 510 1000
+
0901 510 2101
=
0901 510 3101 <- I NEED THIS
Can any one advice or give me tips over this. Please
View 3 Replies
View Related
Apr 30, 2008
I was trying to write an expression someting like this.
(CASE WHEN (GroupVar2 IN('CBank','DTC', 'EDirect')) THEN GroupVar2 ELSE 'InstLend' END) AS COALESCE(GroupVar2,'Total') AS GroupVar2
In GroupVar2 column, following values are available;
CBank
DTC
EDirect
InstLend
Inst-Load
I use this for a parameter in my report. I want to consider inst-Load as the same as InstLend. In drop down menu , I should see only InstLend. When I select it, I should get summation of InstLend and Inst-Load.
Also I should see 'Total' as one of the available value. So when I select total it should give me summation of all of above.
Can anyone help me to write this corretly?
Thanks
View 1 Replies
View Related
Jan 8, 2004
When I try to open the subscribers tab in the publisher and distributor properties in SQL Enterprise Manager I get the following error:
SQL Enterprise Manager could not retrieve the list of registered subscribers.
Error 8152: String or binary data will be truncated
Anyone who has experienced similar problems or has a solution?
Best Regards
MF
View 1 Replies
View Related
Apr 9, 2006
http://www.csharphelp.com/archives2/archive342.htmlI am using the sample code from this link but I amunable to figure out how to retrieve the list ofthe User-Defined Functions. I am able to get thecount of the user defined functions correctly using:db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj _UserDefinedFunction,SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name).Cou ntbut I am unable to get to enumerate the function names.Then I tried to see if I can achieve what I want usingSQLObjectList but I was unsuccessful.Does someone know how I can do this using C#?Thank youThis is the full code I have:private void linkLabel5_LinkClicked(object sender,LinkLabelLinkClickedEventArgs e){this.Cursor = Cursors.WaitCursor;SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();srv.Connect(this.cboServers.SelectedItem.ToString( ), this.txtUser.Text,this.txtPassword.Text);for (int i = 0; i < srv.Databases.Count; i++){if (srv.Databases.Item(i + 1, "dbo").Name ==this.cboDatabase.SelectedItem.ToString()){SQLDMO._Database db = srv.Databases.Item(i + 1, "dbo");this.lstObjects.Items.Clear();SQLDMO.SQLObjectList sqludf;sqludf =db.ListObjects(SQLDMO.SQLDMO_OBJECT_TYPE.SQLDMOObj _UserDefinedFunction,SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name);for (int j = 0; j < sqludf.Count; j++){//this.lstObjects.Items.Add(db.ListObjects(SQLDMO.SQ LDMO_OBJECT_TYPE.SQLDMOObj_UserDefinedFunction,SQLDMO.SQLDMO_OBJSORT_TYPE.SQLDMOObjSort_Name).Ite m(j + 1, "dbo").Name);}this.Cursor = Cursors.Default;return;}}this.Cursor = Cursors.Default;}
View 4 Replies
View Related
Dec 9, 2014
I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
View 1 Replies
View Related
Apr 8, 2015
i was trying to use the XML read functionality using t-SQL for XML attached.The column is coming with the token names and token-values in XML format and we are using the XML nodes() functionality to read the token names and token value.I am able to read only the parent token names and its values(using the sql attached) and could not be able to get the child token names and its values.how can i acheive the tokenNames with its values with the SQL query.i am attaching both SQL script which i am using and the XML entity.
View 9 Replies
View Related
Jul 18, 2011
I have a requirement like, we are having two tables in our database.
Table names: student, marklist
Student table values:
id studname
------------------
1x
2y
3z
4a
5b
Marklist table values:
id maths physics English
---------------------------------
1506070
2706040
3508070
45010070
5906070
But my requirement is, I need to display the data "subject wise" highest marks for each student.
for example:
id name highestmark
---------------------------------
1 x English
View 9 Replies
View Related
Apr 24, 2015
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
View 6 Replies
View Related
Jul 17, 2007
Can someone please tell me how to retrieve/query the list of fields from an entity of a report data model that has been published on the reporting server programmatically ?
I am trying to upload a report data model to the reporting server and planning to use that model as the data source and consume it through our existing web application?
Thank you ,
Rashid A. Khan
View 1 Replies
View Related
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
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
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
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
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
