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
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
Hey all,I am having some serious trouble getting the correct syntax for a select statement to work the way I need it, any help I could get on this would be greatly appreciated.I have a table called Units which stores computers and a table called Software which stores software. I have a bit field in Units called OEM, when this is set to true I don't want the select statement to pull this unit down when I am assigning software to other units.Here is my select statement: SELECT Software.SID, Software.SN, Software.Name, Users.First + ' ' + Users.Last AS 'Assigned User', Units.Make + '-' + Units.Model AS 'Assigned Unit' FROM dbo.Software LEFT JOIN dbo.Units ON Software.SN = Units.SN LEFT JOIN dbo.Users ON Units.UID = Users.UID WHERE (Units.OEM = 'FALSE') AND (Software.SN LIKE '%' + @SearchString + '%')Everything works as expected, unless of course the unit has no software assigned to it yet, it won't return it because its not tied to a Units.OEM field. Is there anyway to have it return ALL records that even arn't joined OR are joined but have OEM set to false?Thanks, let me know if I need to clear anything up.-Matthew
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
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
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'm at a bit of a loss here. My T-SQL skills are not up to the task at hand here :(
I've got company records in one table and SIC codes that correlate to the companies linked by the company ID. So, I can run the query, but the output I get is multiple records for each company, because some companies have multiple SIC codes associated with them.
I understand how to get only one record, but what I want to do is create a result set that has all the SIC codes associated with one company. Possibly in a comma seperated list, that would count as one field.
Anyone have any idea how to bring back all the SIC codes for one company as one variable (or multiple variables, but in one record)?
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.
Is it possible to roll up all child records into one comma delimited field? For example, if the parent table is league info, and the child table is team info, I would be looking to return results like this:
Field 1 Field 2 NFL Bears, Browns, Saints, etc NBA Bulls, Celtics, Lakers, etc
MS has been nice enough to add the Cube and Rollup operators so I canhave totals with my results.But out of shear meanness they won't let me use them unless I use aGroup By clause in my select. I have a proc with 25 fields, and haveno desire to Group By anything- when a certain field changes, I justwant a row with the total of that field.Is this possible?Thanks,Burt
I am trying to join seperate records into one return set. IE. select accountNum, flag1, flag2, flag3 from MyDB where flag1='A' or flag2='B' or Flag3='C'
The problem is that there could be 2 records with the same account number but flag1 ='A' Flag2='B' ect.
This would return to lines. Is there a way that I can have all return sets show up on one line only. Eliminating duplicate return sets???
I am new at sql programming and I need all the help i can possibly get..Im using SQL Server 2000 and I have been given an assignment which follows:
- i have to add 100 new records at once to an existing table. I have to do it through SQL Server only( query analizer,i suppose) and I am not allowed to use any other languages to achieve this. Now, my question is how can i do it through sql only? What is the code i have to write to make this thing work? I simply dont know where and how to start...
Dear ALL, I have two tables with named Reports and Backup_Reports with same fields. Id(numeric), MobileNumber(varchar), SmsBody(varchar),date(datetime),Status(char). Here what I want is, if I am searching some records by entering the mobile number then the sql query first will search on the first table Reports then it will show the related records and again the query will search on the second table Backup_Reports then will show related records from this table also. What I mean is, if I am searching some records by mobilenumber first will search on the Reports and then will go to second table Backup_Reports and will show all the related records together. How can I handle this?
I have a query that returns multiple identical records, however it shouldonly return one. Indeed there is only one record for the OrderActionTypecodeof 'P' yet there are two orderactions so it returnd both.Oddly the second query returns only the desired single record but wihtoutall the additional fields I need. Does the Inner join somehow mess with thequery?ThanksQuery 1SELECT Orders.OrderID, Orders.TicketID, A.AttemptID, E.EventID,E.AssetCode AS Asset, Orders.FK_Prod_Alias AS Alias, Orders.ContractCode,L.OrderLegCode AS OrderLeg, M.MarketActionName ASAction, Orders.OrderVolume AS Volume, Orders.OrderPrice AS Price,T.OrderTypeName AS OrderType, S.StatusName AS Status,Orders.FilledVolume AS Filled, Orders.OriginalOrderDateTime,Orders.PlaceOrderDateTime,Orders.MonitorFlagFROM Orders INNER JOINOrderAction OA ON Orders.OrderID = OA.OrderID INNERJOINAttempt A ON Orders.AttemptID = A.AttemptID INNER JOINEvent E ON A.EventID = E.EventID INNER JOINMarketAction M ON Orders.MarketActionCode =M.MarketActionCode INNER JOINOrderLegs L ON Orders.OrderLegCode = L.OrderLegCodeINNER JOINOrderType T ON Orders.OrderTypeCode = T.OrderTypeCodeINNER JOINOrderState S ON Orders.Status = S.Status ANDOrders.OrderID =(SELECT OrderIDFROM ORDERACTIONWHERE ORDERACTIONID =(SELECTMAX(ORDERACTIONID)FROMORDERACTIONWHEREOrderActionTypeCode = 'P'))Query 2SELECT OrderID, TicketIDFROM OrdersWHERE (OrderID =(SELECT OrderIDFROM ORDERACTIONWHERE ORDERACTIONID =(SELECTMAX(ORDERACTIONID)FROMORDERACTIONWHEREOrderActionTypeCode = 'P')))
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
after I've created a new column in a table with the "Create a new column" with custom members. How do I actually remove this newly created column without recreating the dimension ?
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 ?
How to writing this query using rollup or grouping sets in two way?
data shop time date sku discount% sales qty amount A 13:00pm, 2014-feb-11 apple 30% 5 #20 A 13:00pm, 2014-feb-11 apple 30% 5 #20 A Shop Qty :10 Amt:40 B 23:00pm, 2014-feb-11 apple 30% 5 #20 B 23:00pm, 2014-feb-11 apple 30% 5 #20 B shop Qty :10 Amt $40 Grand total qty:20 , Amt $80
Hi, I am a rookie at sql and asp.net. I have another post is the asp.net section http://forums.asp.net/thread/1589094.aspx. Maybe I posted it in the wrong section. I am collecting multiple rows from a gridview. I validated/confirmed I am capturing the correct values. How ever, I am having major problems passing these to an sql database. The problems that I know of is declaring my parameters correctly and then adding the values through a for each statement. I added the post over 30 hours ago with only me as the replier trying to refining or clarifying the problem. Part 1 is the code that works, part 2 is my problem, I have tried may different ways to resolve this but no luck. Regards! Protected Sub Botton1_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click**************** PART1 **************************Dim gvIDs As String = ""Dim ddIDs As String = ""Dim chkBox As Boolean = FalseDim chkBox1 As Boolean = False'Navigate through each row in the GridView for checkbox items ddIDs = DropDownList1.SelectedValue.ToStringFor Each gv As GridViewRow In GridView1.RowsDim addChkBxItem As CheckBox = CType(gv.FindControl("delCheckBox"), CheckBox)Dim addChkBx1Item As CheckBox = CType(gv.FindControl("CheckBox1"), CheckBox) If addChkBxItem.Checked Then chkBox = True gvIDs = CType(gv.FindControl("TestID"), Label).Text.ToString Response.Write(ddIDs + " " + gvIDs + " ") If addChkBx1Item.Checked Then chkBox1 = True Response.Write("Defaut Item") End If Response.Write("<br />")End IfNext******************** Part 2 *****************************************************Dim cn As SqlConnection = New SqlConnection(SqlDataSource1.ConnectionString)If chkBox ThenTry Dim insertSQL As String = "INSERT INTO testwrite (TestLast, test1ID, TestDefault) VALUES (@TestLast, @test1ID, @TestDefault)" Dim cmd As SqlCommand = New SqlCommand(insertSQL, cn) cmd.Parameters.AddWithValue("@TestLast", DropDownList1.SelectedValue.ToString) cmd.Parameters.AddWithValue("@test1ID", CType(gv.FindControl("TestID"), Label).Text.ToString) cmd.Parameters.AddWithValue("@TestDefault, CType(gv.FindControl("CheckBox1"), CheckBox)) cn.Open() For Each ..... Problems here also Next cmd.ExecuteNonQuery()Catch err As SqlException Response.Write(err.Message.ToString)Finally cn.Close()End TryEnd If
I have the Temporary table:ItemDetailID (int)FieldID (int)FieldTypeID (int)ReferenceName (Varchar(250))[Value] (varChar(MAX))in one instance Value might equal: "1, 2, 3, 4"This only happens when FieldTypeID = 5.So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the Table FieldListValues(ItemDetailID, [value])I have created a function to split the [Value] into a table of INTs Any Advice?
