I have a table similar to the following (XYZ). I would like to write a select statement that will return the count of the unique items for each user that also happen to be less than 1 year old. The less than one year old part is rather easy dateadd(year, -1, getdate()), but I seem to be having a hard time figuring out how to get my desired result without using subselects. Any help greatly appreciated. Thanks in advance - Dan.
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Detail #etc WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header Work Order Line Work Order Line Details Work Order Line Requisitions
The Header has a unique PK. The Line uses the Header and a Line # as foreign keys that together are unique. The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No, [Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No FROM [Work Order Header] LEFT OUTER JOIN [Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN [Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN [Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
I'm sure this is very basic, I'll try not to be too embarrassed asking it.
Given a table called MainTable, and a second table called Details, with a one-to-many relationship between them, which is the better practice in regards to getting a number of values from each MainTable record plus an aggregate of a value from the related Details records...?
--=====================================
--Method 1 --Group by every selected field in MainTable SELECT MainTable.Rec_Num, MainTable.Name, MainTable.Description, SUM(Details.Amount) AS Amount FROM MainTable INNER JOIN Details ON MainTable.Rec_Num = Details.MainTable_Rec_Num GROUP BY MainTable.Rec_Num, MainTable.Name, MainTable.Description
--Method 2 --Group by PK record in MainTable and aggregate the others SELECT MainTable.Rec_Num, MAX(MainTable.Name) AS [Name], MAX(MainTable.Description) AS [Description], SUM(Details.Amount) AS [Amount] FROM MainTable INNER JOIN Details ON MainTable.Rec_Num = Details.MainTable_Rec_Num GROUP BY MainTable.Rec_Num
--=====================================
Or perhaps there's another option I'm not aware of?
In SRS2000, I have a simple grouping matrix report setup like the following:
Code Snippet =Fields!Date.Value Total =Fields!Section.Value =Fields!Count.Value Total
It's essentially a table of departmental statistics that would report like this:
Code Snippet 5/1/08 5/2/08 5/3/08 5/4/08 Total Dept1 0 99 33 4 XX Dept2 1 0 2 1 XX Total XX XX XX XX XX
The report has 2 groups - 1 for the dates and 1 for the departments.
The report data itself displays correctly. My problem comes in when I try to add the Subtotals.
I simply right-click on each of the two Groups and chose "Subtotal". This action inserts the Total column & row respectively and you would think everything is just fine.
Unfortunately when I actually run the report, I see that the Totals it's coming up with are incorrect. In fact, they are the totals of the FIRST row/column only.
Meaning - (using the previous example) - I'd see this:
It's like it's not taking anything into account except the first instance.
And it's not like you can actually *see* the expression or formula being used for the Total function. The data cells are just grayed.. all you can really change is the "Total" text.
I must be doing something really stupid here, but for the life of me I don't see what it is.
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData ( Id BIGINT, --is actually an identity column Manifest_Id BIGINT, Doc_Num BIGINT, Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X) FROM #InvLogData GROUP BY X HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
Hi, I have this query... cmd = New SqlCommand("SELECT name, webd_category_desc.category_id, (name + cast(webd_category_desc.category_id as nvarchar)) as CNameID, link_id FROM webd_category_desc left outer join webd_link_category on webd_category_desc.category_id = webd_link_category.category_id where display = 'True' order by CNameID, link_id ;", SqlConnection1) It produces the following output (trunctated by me for this post example).
Architecture 5 Architecture5 90 I would like it to display instead (where 8 and 2 are the counts): Accounting/Bookkeeping 8 Architecture 2 Seeing that I had to join a few tables to get the above output, how can I now group on it to get the name, count(name) output I desire.I'm using ADO.NET in a VB.NET/ASP.NET 2.0 webapp. The data is in SQL Server 2000. I was hoping to do it in one SqlCommand statement if possible. I guess I can drop it into a view and then run my group by query on the view if I had to. I am getting a variety of 'field in select list must be used in a function or aggregate' errors in the attempts I have tried so far. Thanks in advance, Stewart
Hi folks. Hopefully this is a simple question. What's the easiest and most efficient way to group by a dateTime field in an SQL query? Here is exactly what I'm trying to do. I have a database table that contains transactions from an email maillog, so there are dateTime entries every second or so. I'm trying to build a query that will group a count of messages per hour for a given day. How can I make an hourly grouping of the total number of messages?SELECT count(*) FROM emailTable WHERE (delivDate >= '2007-10-03 00:00' AND delivDate < '2007-10-03' 01:00) Thanks, Chris
select (select fieldx from tabley where pk = tz.fk) as field1, field2 from tablez tz group by field1
this doesn't work..i get an error that field1 is not valid...so is there a way to do this that does work?
please realize that the above example is exactly that..and had i needed to do something that easy, join would be the easy choice..what i'm trying to do requires a sub query
and now for the $1M question...How ? I've tried the following select, but it is not working the way I want it to..
SELECT c2.ACCNO, c2.Rundate, c2.TrdCap, c2.TRANQTY, c2.DLPRCE, c2.NOTEAMNT FROM CLIENTSHAREDEALS c2 FULL OUTER JOIN (SELECT c1.ACCNO, c1.SHARENAME, SUM(c1.TRANQTY) AS Expr1 FROM CLIENTSHAREDEALS c1 WHERE (c1.ACCNO = '275479') GROUP BY c1.ACCNO, c1.RUNDATE, c1.SHARENAME) c1 ON c1.ACCNO = c2.ACCNO AND c1.RUNDATE = c2.RUNDATE WHERE (c2.ACCNO = '275479') ORDER BY c1.RUNDATE
I have a table with the following structure:main_category| category| sub_category| answer|dateBasically, the data will be along these lines:Neuro | LOC | Status | answer1|dateNeuro | LOC | Status | answer2|dateNeuro | LOC | Status | answer3|dateSenso| Visi | Clarity | answer1|dateSenso| Visi | Clarity | answer2|dateetc...I am trying to query the db and present the user with the data in thefollowing structure:Main CategoryCategorySub Categoryanswer1answer2answer3...Main CategoryCategoryEtc...There are literally 3 dozen main categories, categories, andsub-categories each with distinct answers.I could really use some help on a query to group the data in this way!Thanks in advance!!!Frank
HI all, I got a tsql that needs to be simplified. Select * from Table1 where condition1 and id not in ( Select id from table1 where condition2 and id in ( Select id from Table1 where condition1 ) ) basicly all records thats in condition1 but that doesnt have condition2 but limited to condition1. I'm probably maken this to complicated. but im tired and im losing time just on one stupid query. Thanks for the help.
Hi,I have data stored as in below sample :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-14 00:00:0063.00002005-11-14 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-16 00:00:0045.00002005-11-16 00:00:002005-11-17 00:00:0045.00002005-11-17 00:00:002005-11-18 00:00:0045.00002005-11-18 00:00:002005-11-19 00:00:0045.00002005-11-19 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-21 00:00:0063.00002005-11-21 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------I have to group the select query in this way :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------When I run below grouped statement, I get follewed result:SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,Rate FROM X GROUP BY Rate-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-22 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.0000-------------------------------+---------------------------------+--------------How can I do a query like in 2nd sample from top?best regards,rustam bogubaev
I'm having much difficulty figuring out how to write the followingquery. Please help!I have this table:EventEventId int Primary KeyPatientId intSeverityLevel intWhat I want returned in my query is a list of all (distinct)PatientIds appearing in Event, with the *most severe* EventId returnedfor each Patient. The higher the value of SeverityLevel, the moresevere that Event is considered to be.The problem I am having is that I can't figure out how to (a) group byPatientId, AND (b) return the EventId of the highest-severity Eventfor *each* PatientId (Order By SeverityLevel Desc).So if my table contained:EventId PatientId SeverityLevel------- --------- -------------1 1 02 1 13 1 54 2 55 2 2I would want my result set to be:PatientId EventId--------- -------1 32 4since events 3 and 4 are the most severe events for patients 1 and 2,respectively.Any help would be greatly appreciated. This seems to be something thatcould be handled easily with a FIRST() aggregate operator (as in MSAccess) but this is apparently lacking in SQL Server. Also note theremay be multiple Events with a given PatientId and SeverityLevel, inthat case I'd want only one of the EventIds (the Max() one).Many thanks,Joel ThorntonDeveloper, Total Living Choices<joelt@tlchoices.com>(206) 709-2801 x24
If i go for Group by clause (SQL) then I can group them by week but cant display the individual rows. Is there any way to do this (better if in a single query)
some one plz help me. I had a table with these columns. Table(Id int,Name varchar,Value Varchar). I have to group them by ID and each Name becomes column name of the new table
ex:- Id Name Value -------------------- 1 x a1 2 x a2 3 x a3 1 y b1 2 y b2 3 y b3 1 z c1 2 z c2 3 z c3
I need it in this way
x y z ------------ a1 b1 c1 a2 b2 c2 a3 b3 c3
(no of columns in the new table can't be pre determined)
and which one would be better option to do this in VB.Net code or in a Storedprocedure?
I have a simple question, but I can't seem to get around it. Please help.
I have a table like this: [select * from Enrollment] Course ID PersonID Role BCC110 123 Student BCC110 321 Student BCC110 456 teacher BCC123 457 Student
and I want to have a report like
Course ID Total Students Total Teachers BCC110 2 1 BCC123 1 0
I want to group data in matrix column. Lets say i have a field say Weekday which has weekdays from monday to friday. then suppose i have measure "my expenditure".
I will place Weekdays in column field of matrix and "my expenditure" in data field". lets not worry about rows.
Now i want something like this.
Group my expenditure in three categories like
1. My expenditure on monday
2. My expenditure on tuesday
3. My expenditure on days other than monday and tuesday.(means it should show me data for wednesday,thursday,friday and also if no weekday is entered)
what I am doing now is i m writting IIF expression in the column field but with that i get data for monday and tuesday but data for all the other days is not getting clubbed.
I am trying to develop a report query that will dynamically group the data by day,week, quarter, month, or year. The user will select the interval from the site and pass it to the query. The query will convert the date field using a function I created. Is this the most efficient way to do this?
QUERY ------------------ SELECT dbo.FormatDate(DateCreated,3) As DateCreated, Count(*) As NumOfCalls FROM Agent.LeadTracker WHERE DateCreated BETWEEN '1/1/2008' AND '2/7/2008' GROUP BY dbo.FormatDate(DateCreated,3)
RESULT ------------------ DateCreated NumOfCalls
2008-02-01 5442 2008-01-01 14150
FUNCTION------------------ CREATE FUNCTION [dbo].[FormatDate] ( @Date datetime, @Time int ) RETURNS datetime AS BEGIN DECLARE @Res datetime IF @Time = 1 --Day SELECT @Res = DATEADD(day,DATEDIFF(day,0,@Date),0) ELSE IF @Time = 2 --Week SELECT @Res = DATEADD(week,DATEDIFF(week,0,@Date),0) ELSE IF @Time = 3 --Month SELECT @Res = DATEADD(month,DATEDIFF(month,0,@Date),0) ELSE IF @Time = 4 --Quarter SELECT @Res = DATEADD(quarter,DATEDIFF(quarter,0,@Date),0) ELSE IF @Time = 5 --Year SELECT @Res = DATEADD(year,DATEDIFF(year,0,@Date),0) RETURN @Res END
I have a query where I have customers, date they ordered a swatch, date they ordered an item, and eh date diff between the two. I want to show the MIN date diff for each customer, and also show the swatch date and item date as well. But to use the MIN aggregate, it forces me to group everything, where I just want to group by customer, and have the 2 dates tag along, because i only want one record per customer. What is the easiest way for me to accomplish this?
I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these
SQL QUERY SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9) THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
I have a query that gets a supplier, a month, a year, status and sum of recpits. returning the following. but my problem is I also need a col of totals. i tried to put a sub grouped query in the select statement but keep getting an error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
SELECT ot_ven_num, ot_ven_name, sum(ot_rec_qty) as ot_rec_qty, ot_rec_stat, datepart(Month,ot_rec_dt) as mth, datepart(year,ot_rec_dt) as ryear, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)) as ot_rpt_date, (SELECT ot_ven_num, ot_ven_name, sum(ot_rec_qty) as ot_rec_qty, 'Totals' as ot_rec_stat, datepart(Month,ot_rec_dt) as mth, datepart(year,ot_rec_dt) as ryear, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)) as ot_rpt_date FROM supplierOT where ot_ven_name = 'ARROW ELECTRONICS' and datepart(year,ot_rec_dt) > 2007 group by ot_ven_num, ot_ven_name, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)), datepart(Month,ot_rec_dt), datepart(year,ot_rec_dt)) as total
FROM supplierOT where ot_ven_name = 'ARROW ELECTRONICS' and datepart(year,ot_rec_dt) > 2007 group by ot_ven_num, ot_ven_name, ot_rec_stat, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)), datepart(Month,ot_rec_dt), datepart(year,ot_rec_dt)
I am trying to create a group on this data example
RecordNo, Item, Date,NULL 1, ABBB, NULL, 0 << first group 2, ABBB, 01-06-2011,NULL << first group 3, ABBB,NULL,NULL << 2nd group 4, ABBB, 02-01-2011,NULL << 2nd group 5,CAAA,NULL,NULL << 1st group 6,CAAA,NULL,NULL << 1st group 7,CAAA,01-01-2010,NULL << 1st group 8,CAAA,01-01-2011,NULL << 2nd group 9,CAAA,01-05-2012, NULL << 3rd group
Basically 3 groups on date, by item, so for each date change/ per part the group increments been playing with row_number partitions, but cant seem to get what i need.
I am using SQL Server 2000. I have a table of over 1 million accounting transactions. I need to be able to remove all items that have contra items.
e.g
debit £100 - credit £100 - debit of £125 ( I only want to see the debit of £125)
I can achieve this in MS Access by grouping the key fields, suming the value fields and using the First() or Last() command for columns that I need to display but not group.
