With mstrTable(ItemNo, Sales) as ( Query1 Union All Query2 ) Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales From mstrTable order by ItemNo
The results from Query1 and Query2 overlap sometimes.
I only want the first occurrence of each item. The desired result set is:
1 Item1 10000 1 Item2 20000 1 Item3 30000
I am not able to add a "Where RowNo = 1" to the query. SQL returns an "invalid field name".How would I obtain a record set that returns just the first occurrence of the ItemNo field?
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 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 ***
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?
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.
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.
I'm using sql server 2005, I created a customer table and set the customer_id as an uniqueidentifier that is a NEWSEQUENTIALID()...when a user is created I want to obtain the last customer_id so that I can insert it in another table that has a one to many relationship with the customer table. Please help...how do i do this?.Thanx
I'm working on a search engine right now, and I am querying out a set amount of information at one time. How can I grab the last row count of a query, and use that as a starting point, or index when a "Next 100" is pressed. For instance, a person searches through a user table, but only wants 25 users at a time. So the beginning index is 0 to 25. In my query, I have "SELECT TOP 25 from users", but now when the person searching want's to move to the next 25 users, so the index would be 26 to 50, how can I hold the last row displayed? It gets a little more complex also, if a user is searching by last name, the rows will be staggered. Is there a SQL function which will grab the number of the last row displayed? I've looked through the SQL Book, and was unable to find a function that does this, or anything close to it. I hope I made sense of this question, if you need more information, email me and I will try to clarify. Thank you for any information or hints.
If I have say 20 records of sales employees, for example, how can I get the top 3 locations for $$$sales for EACH employee? Each employee can have multiple locations where they have sold(let's say up to 50). I only want the names of the top 3 locations. The closest I can get is filtering the dataset by a HAVING clause > a dollar amount but this still gives me between 3 - 12 records for each plus I have to literally enter each salesperson's number as it stands now. Is this a loop or a cursor? Thanks.
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!!!
