I'd like to write a query that lists items from a single table but groups the listed items by a date (data of item entered into the table)
So all items matching a criteria and were entered during March should be listed underneath one-another
Then all items matching the same criteria but entered during April should be grouped again.
Not sure what would be the right approach here.
I'm thinkning, creating a temp table putting data in there but altering the data enterd field into just year and month, and then group the result by that field?
I have a bar chart that I need to show the top 10 items by Month. What I get currently is a chart by month with the total Top 10 Items (rather than the top 10 per month). I have tried adding a scope onto my Value expression but that didn't help.
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up ArtNo March <=March 1210 20,500 50,900 1220 21,200 64,000 1230 15,400 40,300 ... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
ID varchar (contains alphanumeric values,not unique) Territory (combined with ID unique) Total_Used int can be null Date_ date (date of the import of the data) ID Territory Total_Used Date_ ACASC CAL071287 2014-06-01 ACASC CAL071287 2014-08-01 ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT RG.Id AS Id, RG.Name AS Name, ISNULL(SUM(AC.Amount), 0) AS Amount, RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
I want to grab the Max footage in this query, there are two values for each group, so i should be able to get the bigger number. The only problem is, its in varchar format, so for some reason its only grabbing the ones with the highest number to the left. So instead of grabbing 12ft, it says 7ft is the max. What am i doing wrong. is there another way to go around this. This is supposed to be a subquery for a a much bigger query.
Code Block SELECT MAX(Qry_Questions.Response) AS Max_Footage, CONVERT(varchar, dbo.Qry_Questions.Date, 110) AS shortDate, Qry_Sales_Group.salesperson_purchaser_code, Qry_Questions.StoreName, MIN(Qry_Questions.Response) as Min_Footage
FROM Qry_Questions
INNER JOIN Qry_Sales_Group
ON Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer AS Customer
ON Customer.Customer_Code = dbo.Qry_Questions.Customer COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Category AS CusCat
ON CusCat.Customer_Category_Id = Customer.Customer_Category_Id
WHERE (dbo.Qry_Questions.[Question Code] IN ('SN017', 'SN015')) AND (CusCat.Customer_Category_Id = 6201) GROUP BY CONVERT(varchar, dbo.Qry_Questions.Date, 110), Qry_Sales_Group.salesperson_purchaser_code, dbo.Qry_Questions.StoreName
This displays this: Max Footage Date store name Min Footage 8ft 09-07-2007 12140 PETCO #1437 8ft 8ft 09-10-2007 12069 PETCO #698 8ft 8ft 09-11-2007 12106 PETCO #1916 8ft 8ft 09-11-2007 12108 PETCO #683 4ft 8ft 09-13-2007 12140 PETCO #918 12ft
I have the following sql table and would like to group the results by "StoryTitle" to display in a datalist. The Storytitle field in the datalist is a LabelID StoryTitle StoryAuthor Rating StoryID Comments 1 About Me goodyone 6 20 Great Story 2 About Me goodyone 5 20 Love your work 3 Hello World magicme 6 26 What a Story 4 Hello World magicme 7 26 This Reminds me of... I know i have to do something in the SQL Datasource statement. Not sure how to do it. here is my statement below <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:BrillConnectionString1 %>" SelectCommand="SELECT * FROM [iaw.comments]"> </asp:SqlDataSource>
SQL = "SELECT email FROM Contacts WHERE email = ANY ( SELECT email FROM Contacts GROUP BY email HAVING COUNT(email) > 1)"
It is supposed to return all of the records that have duplicate email addresses (i.e. the subquery finds the duplicate addys and the main query finds all of the records with those emails.)
I am trying to get a count of a job received date and a job closed date from the same table. I need these counts to be grouped by which team they are for. This is what I have and it isn't working:
SELECT HEAT.dbo.Profile.PrimaryTeamName, COUNT(CallLog1.RecvdDate) AS OpenCalls, COUNT(CallLog2.ClosedDate) AS ClosedCalls FROM HEAT.dbo.Profile, HEAT.dbo.CallLog CallLog1, HEAT.dbo.CallLog CallLog2 WHERE HEAT.dbo.Profile.CustID = CallLog1.CustID AND HEAT.dbo.Profile.CustID = CallLog2.CustID AND CallLog1.CallID = CallLog2.CallID AND ((HEAT.dbo.Profile.PrimarySupportGroupID = 'ATS') OR (HEAT.dbo.Profile.PrimarySupportGroupID = 'ats')) GROUP BY HEAT.dbo.Profile.PrimaryTeamName, CallLog1.RecvdDate, CallLog2.ClosedDate HAVING (CallLog1.RecvdDate = CONVERT([VARCHAR](10), GETDATE(), 120)) OR (CallLog2.ClosedDate = CONVERT([VARCHAR](10), GETDATE(), 120))
I can get both counts to work individually, but as soon as I try to get them to go together I get some very interesting returns. I am drawing a complete blank as to what to do. Any info would be very helpful.
I am new here, and I am sure this is a simple query, but im being forcefed database chores from my job, so i have to teach this stuff to myself/get help from places like this, I need help with a query, lets say that there are columns a,b,c,d,e,f,g if columns c,d,e are the same, than I want the info in column g changed to the info in column b in the first record of that group the reason I am doing this is, I have like items (sku's) grouped in my database, and i want to create a blanket part number for skus that have matching descriptions which is the information in colums c,d,e, I want to link them to the part number of the first product with that description, and add that part number in a new column at the end of the grouped SKU's record
this is what i start out with
a b c d e f g 2 4 5 6 9 8 2 5 5 6 9 9 2 7 5 6 0 5 1 2 3 4 5 6 1 3 3 4 5 7 1 4 3 4 5 8 1 5 3 4 5 9
i want to end up with a b c d e f g 2 4 5 6 9 8 4 2 5 5 6 9 9 4 2 7 5 6 0 5 7 1 2 3 4 5 6 2 1 3 3 4 5 7 2 1 4 3 4 5 8 2 1 5 3 4 5 9 2
Hi,I want to get the count of rows per each 20 minutes, is that possible?Is there a date function or any other function that I can use in Groupby clause, which will group the data of every 20 minutes and give methe count?Thank you.Vidya
Hi,I want to create query where I could group records by quarters, and getthe last record in each group.e.gCreate Table MyTable(Value [float],date[datetime])Insert into MyTable (value, [date]) values (100, '1-1-2000')Insert into MyTable (value, [date]) values (110, '1-2-2000')Insert into MyTable (value, [date]) values (120, '1-3-2000')Insert into MyTable (value, [date]) values (130, '1-4-2000')Insert into MyTable (value, [date]) values (140, '1-5-2000')Insert into MyTable (value, [date]) values (150, '1-6-2000')Insert into MyTable (value, [date]) values (160, '1-7-2000')Now I would like to get this data grouped by quarter, and get the lastvalue from each quarter. So here I would like to get result like this(120, q1 -2000)(150, q2 -2000)(160, q3 -2000)I know how to create aggregate functions but I have problem with gettingthat last record from each group.*** Sent via Developersdex http://www.developersdex.com ***
Sorry for my englishI have a table that contains data of career about the person (staff)like this ...EMATREANIDEMEIDEGIIDecc. ..ecc. ..ecc. ..ecc. ..1199912312002123111200311000321999123120021231122003110003419991231200073115199912312001131161999123120021231162003110003719991231200212311whereEMATR is a not unique key (person ID)EANID, EMEID, EGIID (assembled) are the date of the last advance ofcareerI want extract from the table below only the actual position,therefore a view that return this rowsEMATREANIDEMEIDEGIIDecc. ..ecc. ..ecc. ..ecc. ..1200311000322003110003419991231200073115199912312001131162003110003719991231200212311I hope of to have been explainThank you from Maximiliano (italy)RE-Sorry for my english--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
same colored items are grouped by GroupID. Each group contains ItemID, Qty and rate. How can i compare IteamID, Qty and Rate of each group with other group's ItemID, Qty and rate? OR How can i get number of groups with same ItemID, Qty and rate?
This is probably one of the easier questions you get, but I have brain freeze and just can't do it and very rusty.
How can i assign the the values from sql below into variables e.g I want to get all new, pending and cancelled Leads from the rows returned which will come from the status id.
SELECT Count (leadStatusId) As NoOfLeads, leadStatusID, sum(value) As Value FROM [dbo].[tLead] L Inner Join dbo.tLeadStatus S on linkLeadStatus = LeadStatusID Inner Join dbo.tClick C on linkClick = ClickID WHERE L.DateDeleted Is NULL AND linkAffiliateUser = @UserID AND Month(L.DateCreated) = @month And Year (L.DateCreated) = @Year Group by LeadStatusID Order by LeadStatusID asc
I've been scratching my head on this for quite awhile and it has me stumped.I hope to define a query which I can use to fill a "day planner" type ofcalendar. Although I've see a lot of these, only one has had what I thinkis a really nice feature - it collected into groups all events thatoverlapped into contiguous blocks of time. The net result of this is thatit becomes possible to output a calendar (html table) that is much lesscluttered. So I want to use this same idea for my own little project.The trick is that events may (or may not) start and end such that theyoverlap (completely or only at one end). I only am concerned with events ona given day.My "events" table contains eventtitle, date,starthour and endhour. Hoursare numbered from "0" to "23". I also have a lookup table of the "hours ofthe day" with which I did a JOIN to include the "missing" hours (where therewere no events - making a query that returned events for each hour and nullsfor each hour that had no event scheduled for it. But this makes too many"blank" rows, which is part of the clutter to which I referred.I've been able to construct queries that work in some cases, but not all.I've reread my copy of Celko's SQL For Smarties and came close, but nocigar. Where he discusses hotels and room-nights is part of the solution Ineeded, but my need goes beyond that quite a bit.Basically, I need to calculate one or more "spans" that contain contiguousgroups of start/end times. By knowing the number of hours spanned, I canthen use that for a <TD rowspan='n'> to collect my data like I want.I seem to keep hitting all around the solution. Maybe there isn't one (thatis purely a SQL solution). Or maybe I'm just looking at the problem thewrong way.So I thought I'd see if anyone here might point me in the right (or at least"new") direction. I've been looking at this for so long, I'm probablyoverlooking some simple and obvious trick to do this. Or maybe I'll getlucky and someone has seen or done exactly this already and can provide asolution?Incidentally, I've avoided utilizing a stored procedure or making a numberof temp tables to collect intermediate results, as I might need to port thisto a "dumb" database that does not provide such facilities. Maybe that'simpractical?thanks in advance,--
I had an SSRS 2005 report that had the lowest level grouped instead of printing a detail line (the reports display summary tables that have all the necessary grouping already applied). Every time I tried to clear the grouping, the report designer crashed. In the end I created the report again from scratch.
Should I have been able to clear the grouping or is the conversion from a detail line to grouping, a one-way process?
I`ve created a table in a list that is grouped by years. Result is a Report, that consists of several years (List grouped by years, each containing the table with the data of the year).
Now I want to aggregate some numbers of the table on each page (year) of the list.
The sum()-function - doesn't matter which scpoe I use - always aggregates only the values of the actual list (Year), not considering the years before.
I'm trying out the interactive sort feature (and have searched high and low for this answer), but can not make the sorting work if I have the data in the table grouped. If I remove the group, the sorting works fine but the output is not acceptable due to the duplicated records in the list.
I have a drilldown matrix report similar to the one below...state - drilldowns to sales person which lists the sales for a particular product...in each state there are usually more than 10 names, however i would only like to see the TOP 10 name appear for the sale off that product
My SQL is as follows
SELECT Sales,Consultant, ProductNo, State, Sum(Value) As SumOfValue,
From tblSales where productNo = 2000
ORDER BY SumOfValue Desc;
If I put in the select TOP 10 it returns only the top 10 for all the states however i need the top 10 off EACH state, is there a way to filter this..or better yet a sort button which will show the top 10 only.
I am wondering has anybody ever created a Matrix in a report grouped by Day Part (10-2 Morn, 2-6 Aft, 6-10 Eve etc) and Date?
I would like to see a report with an output such as
13/02/2006 14/02/2006 Total Morn Aft Eve Morn Aft Eve Mr A 2 4 5 2 6 2 21 Miss B 8 8 1 1 4 5 27
I have a DB table which records the datetime each time a viewer changes TV channel. The report is a summary by user of each channel change By Day Part with Each Date
I can do this easily enough by just grouping on Date but now require another level of detail.
I have a table of transaction that includes student ids and dates. I need to select all records from the table and include a new value that is the sequential transaction numbered for each student with the oldest transaction for each student being numbered one, the next oldest numbered two and so on. So the result should look like student1, 10/1/2000, 1, student1, 10/15/2000, 2, student1, 2/12/2001, 3, student2, 9/1/1999, 1, student2 10/2/2000, 2, student2 , 12/15/2000, 3, student2, 11/4/2001, 4 and so on.
What I want to do is update the table so that it populates the PERCENTAGE column on an empref/hrscode/date basis based on the sum of the WRK hours per day and empref.
EG for 2014-09-01 for empref 001 the result would be
IE Sum WRK = 486 so 180 is 37.037 percentage. Each HRSCODE hours total should total 100% (37.037 + 61.728)
I can write a query to do this individually but how can I so this as a query for the full table.
Code: declare @@total as float set @@total=(select SUM(hours) from tmsuser.tmswrhrs where hrscode='worked' and empref='001' and '2014-09-01 00:00:00.000'=procdate) update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/@@TOTAL*100)),12,3)) where empref='001' and '2014-09-01 00:00:00.000'=procdate
I have results from a query that have anywhere from 1-4 results. I have a subid of 1-4 which is grouped by a certain criteria. so for example...
id subid text 1 processing A records 2 1000 records processing 3 importing A records 4 1000 records processed 1 processing B records 2 500 records processing 3 importing B records 4 1000 records processed
Here is what my desired output is giving each group of subids 1-4 an id to be grouped together.
id subid text 1 1 processing A records 1 2 1000 records processing 1 3 importing A records 1 4 1000 records processed 2 1 processing B records 2 2 500 records processing 2 3 importing B records 2 4 1000 records processed
Trying to get the max count grouped by all the fields. All the fields are the same, but trying to get the location for each physician that has the largest number of patients.
SELECT a.attendingmdkey,e.[provider id],e.[first name],e.[last name],e.title,e.specialty,l.locationname,count(a.accountid) as Count FROM accounts a left outer join location l on l.locationid=a.locationid left outer join providers e on e.[ProviderID]=a.attendingmdkey where a.dischargedate>='2014-12-01' and a.dischargedate<'2015-01-01' and a.divisioncode in ('1','2','$')
group by a.AttendingMdKey,e.[provider id],e.[first name],e.[last name],e.title,e.Specialty,l.locationname order by a.AttendingMdKey
What I want to do is update the table so that it populates the PERCENTAGE column on an empref/hrscode/date basis based on the sum of the WRK hours per day and empref.
EG for 2014-09-01 for empref 001 the result would be
IE Sum WRK = 486 so 180 is 37.037 percentage. Each HRSCODE hours total should total 100% (37.037 + 61.728)
I can write a query to do this individually but how can I so this as a query for the full table.
declare @@total as float set @@total=(select SUM(hours) from tmsuser.tmswrhrs where hrscode='worked' and empref='001' and '2014-09-01 00:00:00.000'=procdate) update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/@@TOTAL*100)),12,3)) where empref='001' and '2014-09-01 00:00:00.000'=procdate
Here is my situation. I am building a report that has three different tables each with their own dataset. Example: Opportunities, Leads, Activities. All three of these datasets/tables have a common field - SalesID. I would like the report to show the first SalesID, then all Opportunities for that SalesID in the Opportunities table, followed by all Leads for that SalesID in the Leads table, followed by all Activities for that SalesID in the Activities table, and then rollover to the next SalesID and repeat that for all SalesIDs. Any suggestions on how I could achieve this? Thanks in advance for all help!!!
