I have a table with two groups, unit and domicile. Structure appears as follows:
GH1 - Domicile Group: Bentwood GH2- Unit Group: 200 Detail: LegID Unit ID Miles TripMiles 1 200 25 150 2 200 75 150 3 200 50 150
Subtotal for GH2 Count(Fields!legID.value) Sum(Fields!Miles.value) First(Fields!TripMiles.value) 3 150 150
This works, what breaks is the subtotal for GH1. on the TripMiles Field. It doesnt like Sum(First(Fields!Tripmiles.value,"Unit_Group")), nor can I do Sum(ReportItems!Trip_group.value) I get the error that an aggregate cannot occur on a report item unless it is in the page header or footer.
Of course if I try Sum(Fields!TripMiles.value,"Domicile_Group") I get 450 which is incorrect.
I've tried making First(fields!tripmiles.value) a calculated field which I just then drop into the header of the Unit group<GH2>. This works perfectly until I then try to Sum that field in the Domicile group. I can create the expression but once I click ok, VS2005 reports that it must shut down and crashes to desktop, when I do a recovery all my changes are lost.
<by the way this crash always occurs when I create a calculated field that uses First/Max or Min, then try to use that field in another calculation. Fields based on IIF work without issue>
To clarify, I want to take the TripMiles Column and for The Unit group show the first value that occurs for that unit as the value.
In the Domicile Group, I want to sum all of those Unit Values. if unit 200 had 3 occurances of 150, my Unit group would have a value of 150 <not 450> if Unit 300 had 4 occurances of 100, my Unit group would have a value of 100 <not 400> My total for the Domicile would be 250 <not 850>
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 have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.
“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.
My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.
I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.
hi...i need a stored procedure query to get the maximum occurred value ie(more than one time occurred value).... this is my table structure.... table Name: PHOTO_GALLERY_VISITSpgv_id pgv_main_category_id pgv_sub_category_id pgv_path pgv_visitor_id pgv_visited_date 6 54 72 photo/Writer/Content/Mobiles/LG/camer4.jpg 26 4/3/2008 6:41:02 PM 7 54 73 photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 26 4/3/2008 6:41:43 PM 8 64 85 photo/Writer/Content/Movie/Bachan/bhachan1.jpg 25 4/4/2008 3:37:17 PM 9 63 82 photo/Writer/Content/sports/Cricket/cricket1.jpg 34 4/4/2008 3:37:29 PM 10 54 73 photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 34 4/4/2008 3:37:29 PM 11 54 73 photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 26 4/4/2008 3:37:29 PM 12 54 73 photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 54 4/4/2008 3:37:29 PM 13 54 72 photo/Writer/Content/Mobiles/LG/camer4.jpg 59 4/4/2008 3:37:17 PM The above is my sample table...... In that filter the most occurrence based on the column "pgv_sub_category_id"in the above table the 73 is the maximum occurred id after that the second maximum ie..72 is the 2nd maximum occurred....hence i want the output as only the id & path which occurred more than one time..... pgv_sub_category_id pgv_path 73 photo/Writer/Content/Mobiles/Samsung/mobile1.jpg 72 photo/Writer/Content/Mobiles/LG/camer4.jpg plz anybody send me the query....its urgent.....thanks in advance.....regardsjanu
Wondering if there is a physical or realistic limitation to the numberof UNION statements I can create in a query? I have a client withapprox 250 tables - the data needs to be kept in seperate tables, but Ineed to be filtering them to create single results sets. Each tableholds between 35,000 - 150,000 rows. Should I shoot myself now?lq
I am executing a SELECT statement that has about 500 characters of literal characters concatenated with the contents of a field from a table. I am then storing the result to be run as dynamic SQL. I am finding that when run this as select statement in query analyzer, the last part of the literal gets truncated. When I run it as a cursor and store it in a varchar(1000) variable and print the variable everything works fine. In addition when I put the select statement in a stored procedure and return this to a ADO recordset, the resultset is fine as well. But running the stored procedure in query analyzer truncates the results as well. The issue seems to be getting the results of the SELECT in query analyzer. Even running the stored procedure in the SQL area of Enterprise Manager returns a proper result. Has anyone heard of a maximum return from a select in query analyzer?
In MS Sql Server 2000, if you run a stored procedure that query's morethan 8 databases, you get this error gets raised:Maximum number of databases used for each query has been exceeded. Themaximum allowed is 8.In 2005, does this maximum number still exist? if so, is it still 8, orhas the number been increased?Thanks,Mike
1. PhoneID is nothing but the participant in the call. PhoneID = 1 is twice from above. Which means 2 particpants (Same call )with 2 numbers with their callstarttime and callendtime. Similarly for PhoneID =2, there are 4 participants. And the list goes on for a day and then for a month.
2. For example a phone call P1 with 2 participants is going on for a particular day. We should not consider the same phone call having 2 participants involved. So that the concurrency would be 2. We have to ignore that here.
3. Only to be considered is other Phone calls for that day. Lets say P1 having call with 2 participants, P2 having some 4 participants which fall in the time period of P1. Then we should consider P1 and P2 the common period
4. In order to find number of concurrent calls happened for a day basing on callstarttime and callendtime. What would be the query?
5. Should consider the Timeperiod or the bucket with 1 hour as the period.
6. A Phone Call P1, Phone Call P2, should have matching (common) time ( keeping all the scenarios) is required for this query.
Result for Concurrent calls for a day should be like below. Should get all the concurrent connections happened for a particular day.
Date|TimePeriod/Bucket(hr part)|Concurrentconnections| Jan-01-2015|01 to 02|3 Jan-01-2015|11 to 12|2 Jan-02-2015|04 to 05|5 Jan-02-2015|12 to 13|13 ........
ii) So once the above is achieved.
Have to find the Maximum concurrent connections for day from the above.
For below Maximum Concurrent connections are '3' Date|TimePeriod/Bucket(hr part)|Concurrentconnections| Jan-01-2015|01 to 02|3 Jan-01-2015|11 to 12|2
Hence the Result for Maximum Concurrent Connections would be
Date|TimePeriod/Bucket(hr part)|MaxConcurrentconnections| Jan-01-2015|01 to 02|3 Jan-02-2015|12 to 13|13 .............
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
SQL Server 2000 8.00.760 (SP3)I've been working on a test system and the following UDF worked fine.It runs in the "current" database, and references another database onthe same server called 127-SuperQuote.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = [127-SuperQuote].dbo.tblCompany.Address1FROM[Work] INNER JOIN[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =[127-SuperQuote].dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDSo now the system has gone live and it turns out that the live"SuperQuote" database is on a different server.I've linked the server and changed the function as below, but I get anerror both in QA and when checking Syntax in the UDF builder:The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than themaximum number of prefixes. The maximum is 3.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = Zen.SuperQuote.dbo.tblCompany.Address1FROM[Work] INNER JOINZen.SuperQuote.dbo.tblCompany ON [Work].ClientID =Zen.SuperQuote.dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDHow can I get round this? By the way, I've rather simplified thefunction to ease readability. Also, I haven't posted any DDL because Idon't think that's the problem!ThanksEdward
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 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)
hi, im trying to get the last row of a column by using this code cmd.CommandText = "SELECT max(orderID) as orderID from orders where memberNo = '" & Session.Item("memberNum") & "'"but it return me 1 instead which is the smallest value in my orderID column n located in the first rowthen i try to use this code but they say got syntax errorcmd.CommandText = "SELECT max(orderID) as orderID from orders where memberNo = '" & Session.Item("memberNum") & "'" ' order by orderID asc " ' and orderID = '" & Session("ordersID") & "'" can anyone tell me the right way to write the syntax?? thx in advance
