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 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.
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 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'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 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.
I am using sql table named as product which is having columns partno,partnm,weight,surfacearea,totalhr,type
I want sum of weight,surfacearea,totalhr and grouping on partno,partnm,type
If I use query select partno,partnm,sum(weight),sum(surfacearea),sum(totalhr) from product GROUP BY partno,partnm then its working correctly with sum and grouping but if I use query select partno,partnm,sum(weight),sum(surfacearea),sum(totalhr),type from product  GROUP BY partno,partnm,type then it is not grouping as expected.
why if third column included in group by clause its not working correctly...Is there any way to group as I want.
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.
Hi Fellows I am trying to organize these information.the data come form two tables that are not relating, but I did a join and my primary key is the filed polygon. I have a list of points(geomseq) for each polygon but the number of points(geomseq) can change. I have this inofrmation in a data base.
I have what at first site should be a simple reporting services issue but cannot resolve:-
I have a complex report comprising over 90 pages of various sections but when analysed in detail , 80% of it follows a simple pattern i.e. it comprises around 100 instances of the same sub-report bound to the same data source BUT grouped and filtered on different groupings and filter values.
The pattern is as follows:-
Each sub-report instance is bound to an SSAS cube which has fields L1, L2...L7
Each sub-report instance groups the data dynamically by one or more groups G1,G2..G7, the actual fields to be used being defined by the parent report (i.e. one instance may group by fields L7, L3, L4, another by L2,L3 etc)
Each sub-report instance has up to 7 filters defined (F1..F7) may filter the data i.e. the parent may pass a filter stipulating that L7="A", L3="B". To do this, the parent sets filter parameters F7="A" and F3="B"
The approach I have adopted is to set up parameters in the sub-report for dynamically grouping the data i.e. the sub-report has 7 parameters defined G1..G7 and the parent report populates G1..G7 with the grouping breakdown required for any given instance. This works fine!!!!
However what I am trying to do is prevent each sub-report performing major round trips to the underlying cube for each sub-report instance by specifying the filter as part of the MDX query by defining the 7 filter parameters as query parameters. (I could of course not filter the query and place the filter in the report but this would almost certainly lead to performance issues). Instead what I am struggling to do is to create the MDX query in such a way that when a filter is not supplied by the parent, the MDX query does not filter the data.
Can anyone advise on this. I guess my question is more of an MDX question than a reporting services one.
I am developing an SSIS solution where the Data Flow task extracts data from a source csv file, then performs several transformations on the source data and then starts inserting the cleansed data into several destination tables.
The Data Flow task is getting too large!
Question: Is there a way (best practice) for grouping components in the Data Flow - similar to the Container concept in the Control Flow?
I know this question sounds too luxurious, but I really loose the overall picture, when the Data Flow canvas gets too crowded.
INSERT INTO @GroupRelation_Test ( CustomerNumber, AmountBilled,MinAmounttBilled) SELECT'12','15243','' UNION ALL SELECT'1231234','15243','' UNION ALL SELECT'463','15243','' UNION ALL SELECT'442','15243','' UNION ALL
I am working on a set of reports where I am summing/averaging data elements based on what period they are in. For example, the report output should look something like this:
Period Sum
May '07 41
April '07 14
Q2 '07 55
March '07 36
February '07 28
January '07 22
Q1 '07 86
June '07 N/A
YTD '07 141
December '06 33
November '06 27
October '06 42
Q4 '06 102
September '06 58
August '06 84
July '06 52
Q3 '06 194
June '06 40
May '06 41
April '06 14
Q2 '06 95
March '06 67
February '06 38
January '06 N/A
Q1 '06 105
YTD '06 496
For each of the items I am summing, all I have is a datetime of when the event happened. This is a relational database (not a cube), so I am struggling with how to create the 'buckets' based on period. I think the best way is to dynamically create the buckets based on a given date. Is there a way in RS that it can do this bucketing for you?
I've seen one other post on this topic from October 2005 and I thought I'd bring it up again. I've a Fuzzy Grouping component in my data flow. The output data from it appears to be the result of records spliced into other records. This includes pass-through columns, not merely "clean" or similarity columns. For example (I've added the suffixes for illustrative purposes):
Is it possible to Group data in a Matrix exactly similar to Table Control. For example, my table control would group data as follows:
Region Country City $ales
North America
USA
Chicago 4 MM
LA 10 MM
NYK 6 MM
Canada
Toronto 4 MM
while the matrix would display as:
North America USA Chicago 4MM
LA
NYK
Canada Toronto ...
Do you see the problem? The matrix is starting the subgroup at the same level as the parent group. How do I make a sub group start at the row below the parent group row in matrix just like in the table above?
I am creating a report that shows orders created by our employees but I am having trouble with one employee. Â She changed her last name this year and so in the report, it shows a line her with her maiden name and one with her new name. Â
Is there a way to combine all the data from both names into one name so it appears on one line and looks like a single person instead of two.Â
SELECT TOP (100) PERCENT StockCode, Warehouse, QtyOnHand, QtyAllocated, QtyOnOrder, QtyOnBackOrder, DateLastSale, DateLastStockMove, DateLastPurchase FROM dbo.MBL_VW_AgedStock_Sales ORDER BY StockCode
This basically shows a list of stock codes (there are multiple stock codes the same) and the last sold date. What i need to do is group the stock codes which are the same together, and show the latest date.
I'm having some difficulty getting the appropriate results for my scenerio. I have two different datasets that I'm using. One is consisting of two joined tables and the other consisting of one sp. The sp's parameters rely on two things- one is the companyNum (inputed when the user runs the report) and two is the ContactNumType. The ContactTypeNum comes from the dataset of tables. I need to have a table consisting of this format:
ContactNumType1 (From the Tables) File_Name1 (From the sp) File_Name4 (From the sp) File_Name3 (From the sp)
ContactNumType2 (From the Tables) File_Name2 (From the sp) File_Name7(From the sp)
ContactNumType3 (From the Tables) File_Name5 (From the sp)
ContactNumType4 (From the Tables) File_Name6 (From the sp)
File_Name10 (From the sp) File_Name8(From the sp) File_Name9 (From the sp)
So essentially what is going on is that every returned File_Name is grouped based upon the type of ContactNumType. My table returns the appropriate ContactNumTypes and the appropriate number of File_Names but returns only the first File_Name for each row. The File_Names should only grouped by the ContactTypeNums and each be unique. Is there any way to do that?
------------------------------------------------------------------------------------------- Edited: I still am trying to work this out. I've tried a few run-arounds but none have worked. Adding custom code apparently is too risky at this point because of the security precautions that I've been instructed to take. Any help would be greatly appreciated as this project has been going on for days now....
To give you some context we have a new amendments application (nothing fancy, excel based with SQL Server back end) that allows users to submit amendments to product data (Product Info, PO Prices, Dates etc.). There is also an admin tool that a team uses to action these amendments in the various systems.
The old version of this tool, users submitted amendments by style and could if need be submit multiple amendments against one product at the same time. The new tool, I believe for audit reasons, users submit by amendment type, so for example I would submit a cost price change for a given style.
The issue now is that on the occasions where a user has multiple amendments, they now come through separately. So cost price would be Amendment 1 and a date change would be amendment 2 even though they could be the same product. This could potentially mean that the admin team would be duplicating work if the paperwork is updated and sent after each amendment, whereas before they would make both changes and only send the paperwork once.
Having not built either of these tools, I've been tasked with trying to fix this, my two thoughts being either to amend the user form to somehow capture/ allow users to submit amendments together or try to use the existing data and doing the grouping dynamically in the back end. Use that lag to look at grouping any submitted amendments that occur within 30mins of the first occurrence of that style
This grouping would then be given a joint time so when the 'time lag' period passes the amendments will be visible together.I've tried a few things and a few head on desk moments trying to get a set based approach but haven't been able to get where i want, its either an issue where amendments span an hour, such as 9:59 and then 10:03 or grouping together amendments that happen after the 30mins of the first one.
Here is some sample data
USE FF_Winning_Together; IF OBJECT_ID(N'tempdb..#AmendTest',N'U') IS NOT NULL DROP TABLE #AmendTest; CREATE TABLE #AmendTest ( AmendmentIDINT IDENTITY(1,1)NOT NULL, StyleCHAR(1)NOT NULL, AmendmentStatusVARCHAR(10)NOT NULL, DTDATETIMENOT NULL
Hi there, i have an SQL query which select the number of products and display them per sub_cat_id e.g. 7 french dressers3 spanish dressers1 english dresser However what i want to do is then group those under the the main category (cat_id) All descriptions are held in tbl_cat and and main category and sub category can be found by the cat_cat colum, if there is an M in this colum it means it is a main category e.g. beds, tables, if there is the name of a M category then it is a sub cat e.g. double beds, single beds etc. Can anybody help me with my grouping nightmare? this is the code i have so far SELECT COUNT(tbl_stock.Stock_ID) AS TOTALOFSTOCK, tbl_stock.sub_cat_id, tbl_cat.Cat_DescriptionFROM tbl_stock INNER JOIN tbl_cat ON tbl_stock.sub_cat_id = tbl_cat.Category_IDGROUP BY tbl_cat.Cat_Description, tbl_stock.sub_cat_id
I have a table which lists manufacturers' products. Users are allowed to vote for the most popular item belonging to the manufacturer. Moderators assign the products to manufacturers so on occassion they may accidently (or deliberately) get it wrong. For this reason, the table also acts as the ChangeLog so if choose, I could instantly decide whether all changes made by a specific user should be dismissed. Product | Manufacturer | NoOfVotes | ChangedBy | ChangedTime ============ ============== =========== =========== ============= XBOX 360 Microsoft 25* Dic 05/03/2008 XBOX 360 Sony 1 Tom 04/03/2008 Playstation Sony 100* Dic 03/03/2008 XBOX 360 Microsoft 25* Dic 02/03/2008 XBOX 360 Sony 1 Harry 01/03/2008
I'm using ID fields for Product, Manufacturer and ChangedBy which are obtained from different tables but have simplified the table to make it easier to read. I need to retrieve each product in the table, the last Manufacturer assigned to it, with a total of the votes where the product-manufacturer match. The results should be: Product | Manufacturer | TotalVotes | LastChanged ============ ============== ============ ============= XBOX 360 Microsoft 50 05/03/2008 Playstation Sony 100 03/03/2008
Any help would be greatly appreciated. Thanks, Pete.
SELECT LEA, Count(SSN) FROM WorkData2003_04.dbo.Students --Database and Table Name WHERE Grade_Level='10' AND SSN NOT IN(
SELECT SSN FROM WorkData2003_04.dbo.Students --Database and Table Name WHERE Grade_Level='09')
However, SQL returns the following error: Msg 8120, Level 16, State 1, Line 1 Column 'WorkData2003_04.dbo.Students.lea' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I'm trying to generate a count of SSNs by LEA (LEA is the same thing as a school) that exist one school year but do not exist in the next school year (essentially students that transfer OUT of a school district).
Could someone provide assistance? I think I need to incorporate the HAVING clause but I'm not for sure...
I'm trying to write a report and need to group columns that are alike. For example.
DeptID JobTitle
40344 Sales Clerk 1st Assistant Store Manager
40666 Sales Clerk 2nd Assistant Store Manager
Sorry, it will not output correctly, I hope you get what I'm trying to do. I can get the results above but it puts the deptid for each job title. I do not want it. Any help will be appreciated. Thanks in advance.
