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 ?
I'm trying to use the Slowly Changing Dimension transformation and it seems that in order to get the transformation to work as expected the custom property "sql command" has to be manually changed so that a "RTrim" function is applied to all string columns referenced. As an example consider this:
SELECT Initials, Department_Name from dimEmployee
has to be changed to
Select RTrim(Initials) as Initials, RTrim(Department_Name) as Department_Name from dimEmployee
where Initials is the "bussines key" and "department_name" a historic attribute.
I know that connecttimeout and commandtimeout are separate entities. Is it possible to change the default command timeout value by changing the connection string? I need to increase the command timeout and want to know if I can do it without changing my code and rebuilding my ASP.NET 1.1 web app. Thanks in advance. -- ZLA
acutally my network dept. has change the backup file server IP address.. i m now hving problem for taking backup. i have around 85 backup that runs everyday.. what i m doing now running every single command to drop backup device and then adding again.. but it's taking agess to do..
is there any simple script that just update the device path folder..
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
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....
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'.
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
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 ?
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
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?
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.
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.
I am encountering an error: Server: Msg 8120, Level 16, State 1, Line 25 Column 'CALLSTARTTIME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Here's the query: SELECT
CASE WHEN (GROUPING(DATEPART(yy, CALLSTARTTIME))=1) THEN 'Total' ELSE DATEPART(yy, CALLSTARTTIME) END AS 'Year', Count(*) as 'Total Calls' FROM CALL_LOG_MASTER GROUP BY DATEPART(yy, CALLSTARTTIME) with ROLLUP ORDER BY DATEPART(yy, CALLSTARTTIME)
Hi,I am attempting to achieve some form of report that needs to make use of sql rollup and display it as follows:Category Subject Matter1 Subject Matter2 Subject Matter3 No of CasesClubs Facilities Sport Facilities Swimming Pool 3 SubTotal 3Events SBR/AHM NULL NULL 1 SubTotal 1 GrandTotal 4However, with my sql query, using roll up, it will look like the following which is not correct.Category Subject Matter1 Subject Matter2 Subject Matter3 No of CasesClubs Facilities Sport Facilities Swimming Pool 3Clubs Facilities Sport Facilities NULL 3Clubs Facilities NULL NULL 3Clubs Sub Total NULL NULL 3Events SBR/AHM NULL NULL 1Events SBR/AHM NULL NULL 1Events SBR/AHM NULL NULL 1Events Sub Total NULL NULL 1This is the query I am using:<code>select casewhen (grouping(Cat.Description)=1) then 'Grand Total'else Cat.Descriptionend as Category,casewhen (grouping(sub.description)=1) then 'Sub Total'else Sub.descriptionend as SM1,SubSub.Description as SM2, SM.Description as SM3, count(sub.description)from tb_feedbackcase FB left join tb_category Cat on FB.Category_ID = Cat.Category_ID left join tb_subcategory Sub on FB.SubCategory_ID = Sub.SubCategory_IDleft join tb_subsubcategory SubSub on FB.SubSubCategory_ID = SubSub.SubSubCategory_IDleft join tb_SubjectMatterLV3 SM on FB.SM3_ID = SM.SM3_IDwhere fb.commenttype_id in (select commenttypes_id from tb_comment_types where description = @feedback_type)and convert(char(10),feedback_datetime,102) >= convert(char(10),@date_from, 102)and convert(char(10), feedback_datetime, 102) <= convert(char(10),@date_to, 102)group by Cat.Description, Sub.Description, SubSub.Description, SM.Description with rollup</code>How can I change it to reflect more accurately? Please help. Thanks.
l would like to get totals at the bottom of the following columns
SELECT sum(capital_Amount) AS Capital_Amount, sum(interest_Amount) AS Interest_Amount, sum(total_Amount) AS Total_Amount, sum(admin_Fee) AS Admin_Fee
to sum up all totals by column. i tried using the rollup,cube like in oracle no luck. Whats the best way of achieving this? if l have to write it as a function how would l do that?
Alter View Test2 As SELECT loan_No AS Loan_No, date_Issued AS Date_Issued, store AS Store, product AS Product, capital_Amount AS Capital_Amount, interest_Amount AS Interest_Amount, total_Amount AS Total_Amount, admin_Fee AS Admin_Fee, user_Issued AS User_Issued, LoanBook AS Company, status FROM Loan
I have two tables - the first contains machine info, and the second contains monthly readings.
Table1 Serial, Model, Location, etc...
Table2 Serial, Year, Month, Reading
I would like to write a query that gives me one row for each machine that has the serial, model, and the reading for the previous 12 months. The date will be passed into the query
insert into tmpTable values ('table', 'blue', 12) insert into tmpTable values ('table', 'red', 100) insert into tmpTable values ('table', 'white', 50) insert into tmpTable values ('chair', 'blue', 12) insert into tmpTable values ('chair', 'red', 1) insert into tmpTable values ('chair', 'white', 123) insert into tmpTable values ('chair', 'yellow', 50)
SELECT CASE WHEN (GROUPING(prod_name) = 1) THEN 'Grand Total' ELSE ISNULL(prod_name, 'UNKNOWN') END AS Item, CASE WHEN (GROUPING(prod_color) = 1) and grouping(prod_name) != 1 THEN 'Sub Total' when grouping(prod_color) = 1 and grouping(prod_name) = 1 then '' ELSE ISNULL(prod_color, 'UNKNOWN') END AS Color, SUM(quantity) AS QtySum FROM tmpTable GROUP BY prod_name, prod_color WITH ROLLUP
--drop table tmpTable
I want to be able to do an order by for each section of the rollup so that the quantity can be asc for both the chair part of the query and the table part.
I found this great rollup query example that uses grouping and subtotals. Would it be possible to expand on this and include a groupwithin a group and subtotal each group? For example, parent product thenchild product? Help appreciated. Thanks.FrankSQL:SELECTCASEWHEN (Grouping(CategoryName)=1) THEN 'MainTotal'ELSE CategoryNameEND AS CategoryName,CASEWHEN (Grouping(ProductName)=1) THEN 'SubTotal'ELSE ProductnameEND AS ProductName,Sum(UnitPrice) as UnitPrice,Sum(UnitsinStock) as UnitsInStockFROM ProductsINNER JOIN Categories OnProducts.CategoryID = Categories.CategoryIDGROUP BY CategoryName, ProductName WITH ROLLUP*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi, Can anyone explain me that WHAT IS CUBE AND ROLLUP Statments for?? Why to use, Where to use and When to use??? Are they usefull in any point of view from ADO.NET???
I would like to only have 1 column rollup but I still need the other columns in the output. Here is my T-SQL code:
This is the current output:
0 2007-02-14 00:00:00 test test 03-Barnes Healthcare Services 246.00 0 2007-02-14 00:00:00 test test 03-Barnes Healthcare Services 246.00 0 2007-02-14 00:00:00 test test NULL 246.00 0 2007-02-14 00:00:00 test NULL NULL 246.00 0 2007-02-14 00:00:00 NULL NULL NULL 246.00 ... ... 0 NULL NULL NULL NULL 3992230.50
I would like it to look like this:
0 2007-02-14 00:00:00 test test 03-Barnes Healthcare Services 246.00 .. ... 0 NULL NULL NULL NULL 3992230.50
I only want the total on the 1st column, but it rollups all of the columns.
Code Snippet
SELECT ServiceTypeCode, ServiceDate, IFirst, ILast, VendorName, Sum(ISNULL(AfterDiscountAmt,0)) AS Amount FROM Holding_Billable WHERE DocumentDate BETWEEN '1/1/2007' AND '12/31/2007' AND SiteCode = 7001 GROUP BY ServiceTypeCode, ServiceDate, IFirst, ILast, VendorName, AfterDiscountAmt WITH ROLLUP
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
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'
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).
So I'm making some kind of vain attempt to follow this tutorial to get summary rows, and I've followed it as best I can to the letter using my own data. But when I try to run the query, it tells me there's a syntax error near "WITHROLLUP". Am I missing something?
SELECT CASE GROUPING(Employee) WHEN 0 THEN Employee ELSE 'Total' END AS TheEmployee, CASE GROUPING(ID) WHEN 0 THEN ID ELSE - 1 END AS TheID, SUM(WorkDays) AS TotDays FROM EmpScheduleExceptions WHERE (YEAR(Start) = YEAR(GETDATE())) GROUP BY Employee, ID WITH ROLLUP ORDER BY Employee, TotDays
I'm struggling to fin a way to use DISTINCT keyword with ROLLUP (or Cube).For example,SELECT employee_city, employee_country, COUNT(DISTINCT employee_name)FROM employeeGROUP BY employee_city, employee_country WITH ROLLUPthat query does not work.Is there a workaround?Thx.