I have a report with the Month attribute as the column group and specific measures as the row groupings. Now, here's my delima. The months are not being displayed in order. They look like this:
I have a requirement from my users to be able to drill down for a single column in a matrix. I've been able to implement drilldown for all of the measures (all columns grouped at the same time) in my matrix but they now want to be able to group different columns independently of each other. In other words they want to be able to group the data in different columns by different things.
An example might make my question clearer, the report would need to look like this:
Client Name Sales - Costs + Revenue -
Client A + Europe + Middle East + Asia Pacific + $12,000,000 Products + Investments +
Client B + $77,000,000 $16,000,000 $9,000,000 $22,000,000 $8,000,000 $32,000,000
As you an see, the Sales measure has been expanded so it's grouped by region so the sales figures can be seen for all clients and the 3 regions. The revenue column has also been expanded/grouped so that you can see revenue figures for products and investments for all clients. Costs is not expanded but it could be by clicking on the '+' which would group the data in that column by something else. They have asked for more than 1 level of drilldown, so clicking on "Europe" above would allow another level for the sales measure which might be country. There will also be regular measure columns that are not drilldown/groupable columns.
Is it possible to have multiple tables or matrixes under one header grouping. I'm having a case where two tables need to be under one grouping (like "Sports vehicle) and under that "sports vehicle" I have two very different tables and on it goes for each grouping (next one like "Off road vehicle), etc.
Is there anyway to do this. I can make this work with one table using the table grouping.
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'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 got the following code to add a column in a matrix with a variance:
IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing, ( (Fields!Amount.Value) /Previous(sum(Fields!Amount.Value)) ) )
This code works fine, except that the first row of the matrix shows an #error
This happens with each matrix where I use this expression. A warning emerges:
rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.
Attempted to divide by zero.
The strange thing is that the part
Fields!year.Value=First(Fields!year.Value,"Category") should prevent an error and I expect it to show 'nothing'
An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)
As you can see, all other 2013 rows show a blank cell, except the first row.
I have a Matrix table that expands to the right when choosing an amount of months to be shown. Under this matrix I have to Charts. The two charts are situated together, that is no space between them, and to the left of the report.
Now, if I choose a lot of months, say three years the matrix diagram will be huge to the right. The problem I have is that the second diagram, the one on the right, moves to the right depending on how big the report gets, and this is not good at all. The two charts are supposed to be all the way to the left.
Hello. I hope to explain myself well - I want to make a matrix with two rows. Lats say my data is this: I hava a list of months and in every month I have a number of pepole and there age. How can I show this in a matrix? It need to be in a matrix since I need the columns to expand acording to the month but I don't know how to create two diffrent rows in my matrix.
I have a report thats fully functional. I just want to add a filter so that my "Visits" field only displays the Visits per day that are less then 6. When i try to filter out the matrix or the group, it tells me the datatypes are different . Something about int32. Its in a matrix, but i have seen this happen in a table too, so i guessing thats not the problem. I just want to be able to display the information for Sales Reps with less then 6 Visits. Any help, will be greatlly appreciated.
Adding more columns in a matrix report that don€™t belong to the columns drilldown dimensions€¦
That is, for example, having the following report:
Product Family
Product
Country City Number of units sold
Then I would add some ratios, that is, Units Sold/Months (sold per month) and other that is the average for Product Family (Units Sold/Number of Product Family), for putting an example€¦ some columns should be precalculated prior to the report so do not get into it, the real problem I don€™t see how to solve is adding one or two columns for showing these calculated column that doesn€™t depend on the column groups but they do for the rows groups€¦
Any guidance on that?
The only way I am seeing by now is to set it as two different reports, and that is not what my client wants€¦
I have a matrix in my report that is based on an MDX query. I copy-paste the matrix and then see the two matrices in the report and all is fine.
I then insert a group into the second matrix. When I run the report the second matrix appears as I would expect, but the cells are blank in the first matrix. Sometime all the data cells are blank, but not the totals.
I tried a similair thing with a table. I added a table based on the same MDX dataset and the cells in the matrix were also then blanked out.
I made sure that the matrices were not sitting on top of each or even in the same space.
The goal here is to show the same dataset but with different groupings. I thought of doing drilldowns, drillthroughs, etc. but this is the way they want to see the data.
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.
I am writing a report from an historical database that uses posting_times. The way the posting works is if something has a posting time of 01/01/97 00:00:00 it means that the record covers from 12/31/96 00:00:00 to 01/01/97 00:00:00.
This is where my problem is at. The report i am writing needs to be grouped by Month. Before i figured out how the posting dates worked i was using ... DATEPART(month, posting_time) AS monthDate ... for grouping purposes in the report.
Now that i have figured out how the posting dates work i am at a total loss as how to group this. I am using SQL Reporting Services 2000 to write this report. Any help would be greatly appreciated. Thanks Mitch
its showing the promotion group not grouped right can you help
--#ttTransaction SELECT ShipToID, Trans.StoreID, TransactionNumber, BatchNumber, [Time], CustomerID, CashierID, CASE WHEN Trans.StoreID IN (172,173) THEN Total*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4)) ELSE CASE WHEN Trans.StoreID = 113 THEN CASE WHEN [Time] < '5/21/2005' THEN Total*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4)) ELSE Total END ELSE Total END END AS Total, CASE WHEN Trans.StoreID IN (172,173) THEN SalesTax*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4)) ELSE CASE WHEN Trans.StoreID = 113 THEN CASE WHEN [Time] < '5/21/2005' THEN SalesTax*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4)) ELSE SalesTax END ELSE SalesTax END END AS SalesTax, Comment, ReferenceNumber, Status, ExchangeID, ChannelType, RecallID, RecallType, CASE WHEN Trans.[Time] >= SO.ShiftDate THEN 'Same' ELSE 'New' END AS SalesType INTO #ttTransaction FROM QSCHQ.dbo.[Transaction] Trans LEFT JOIN Reporting.dbo.RetailSalesComparison_StoreOpenings SO ON Trans.StoreID = SO.StoreID WHERE YEAR([Time]) >= YEAR(GETDATE())-1
--#ttTransactionEntry SELECT TE.Commission, CASE WHEN TE.StoreID IN (172,173) THEN TE.Cost*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4)) ELSE CASE WHEN TE.StoreID = 113 THEN CASE WHEN [Time] < '5/21/2005' THEN TE.Cost*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4)) ELSE TE.Cost END ELSE TE.Cost END END AS Cost, CASE WHEN TE.StoreID IN (172,173) THEN TE.FullPrice*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4)) ELSE CASE WHEN TE.StoreID = 113 THEN CASE WHEN [Time] < '5/21/2005' THEN TE.FullPrice*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4)) ELSE TE.FullPrice END ELSE TE.FullPrice END END AS FullPrice, TE.StoreID, TE.[ID], TE.TransactionNumber, TE.ItemID, CASE WHEN TE.StoreID IN (172,173) THEN TE.Price*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4)) ELSE CASE WHEN TE.StoreID = 113 THEN CASE WHEN [Time] < '5/21/2005' THEN TE.Price*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4)) ELSE TE.Price END ELSE TE.Price END END AS Price, TE.PriceSource, TE.Quantity, TE.SalesRepID, TE.Taxable, TE.DetailID, TE.Comment, TE.AutoID, TE.DiscountReasonCodeID, TE.ReturnReasonCodeID, TE.TaxChangeReasonCodeID, TE.SalesTax, TE.QuantityDiscountID INTO #ttTransactionEntry FROM QSCHQ.dbo.TransactionEntry TE INNER JOIN #ttTransaction Trans ON TE.StoreID = Trans.StoreID AND TE.TransactionNumber = Trans.TransactionNumber WHERE YEAR([Time]) >= YEAR(GETDATE())-1
--#ttTransactionWithPesoConversionAndCustomerAccount SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company, Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType INTO #ttTransactionWithPesoConversionAndCustomerAccount FROM #ttTransaction Trans INNER JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID AND 1 = Customer.GlobalCustomer INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID
UNION ALL
SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company, Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType FROM #ttTransaction Trans INNER JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID AND Trans.StoreID = Customer.StoreID AND 1 <> Customer.GlobalCustomer INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID
UNION ALL
SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company, Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType FROM #ttTransaction Trans LEFT JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID AND Trans.StoreID = Customer.StoreID INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID WHERE Customer.[ID] IS NULL AND Trans.CustomerId < 100000
UNION ALL
SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company, Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType FROM #ttTransaction Trans LEFT JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID WHERE Customer.[ID] IS NULL AND Trans.CustomerId > 100000
--#ttRetailSales_ByStore_ByCustomer_ByDay SELECT CONVERT(DATETIME, CONVERT(CHAR, [Time], 101)) AS [Date], Trans.StoreID, Trans.StoreGroupID, Trans.AccountNumber, Trans.Company, SUM(TE.Price*TE.Quantity) AS Sales, COUNT(DISTINCT Trans.TransactionNumber) AS TRXCount, SUM(TE.Quantity) AS UnitsSold, Trans.SalesType INTO #ttRetailSales_ByStore_ByCustomer_ByDay FROM #ttTransactionWithPesoConversionAndCustomerAccount Trans INNER JOIN #ttTransactionEntry TE ON Trans.StoreID = TE.StoreID AND Trans.TransactionNumber = TE.TransactionNumber WHERE (Trans.Total - Trans.SalesTax) <> 0 GROUP BY Trans.AccountNumber, Trans.StoreGroupID, CONVERT(DATETIME, CONVERT(CHAR, Trans.[Time], 101)), Trans.StoreID, Trans.Company, Trans.SalesType
-- DROP TABLE #ttTransaction -- DROP TABLE #ttTransactionEntry -- DROP TABLE #ttTransactionWithPesoConversionAndCustomerAccount
-- ***** #ttTransGroup ***** SELECT [Date], PromotionGroupID,AccountNumber, SUM(Total) AS Total, SUM(TRXCount) AS TRXCount, SalesType INTO #ttTransGroup FROM (SELECT [Date], QSTStore.PromotionGroupID, Trans.AccountNumber, Sales AS Total, TRXCount, SalesType FROM #ttRetailSales_ByStore_ByCustomer_ByDay Trans INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID WHERE Trans.AccountNumber LIKE 'C-%'
UNION ALL
SELECT TransactionDate AS [Date], QSTStore.PromotionGroupID, AccountNumber, SUM(Amount) AS Total, 0 AS TRXCount, CASE WHEN QuickSellSalesAdjustment.[TransactionDate] >= SO.ShiftDate THEN 'Same' ELSE 'New' END AS SalesType FROM DelSol.dbo.QuickSellSalesAdjustment QuickSellSalesAdjustment INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON QuickSellSalesAdjustment.StoreID = QSTStore.StoreID LEFT JOIN Reporting.dbo.RetailSalesComparison_StoreOpenings SO ON QuickSellSalesAdjustment.StoreID = SO.StoreID WHERE YEAR(TransactionDate) >= YEAR(GETDATE())-1 GROUP BY AccountNumber, QSTStore.PromotionGroupID, TransactionDate, CASE WHEN QuickSellSalesAdjustment.[TransactionDate] >= SO.ShiftDate THEN 'Same' ELSE 'New' END) ttTemp GROUP BY AccountNumber, PromotionGroupID, [Date], SalesType HAVING SUM(ttTemp.Total) <> 0
-- ***** #ttCSVF ***** SELECT CSVFlag.TransactionDate, CSVFlag.StoreGroupID, CSVFlag.AccountNumber, CSVFlagType.[Description] INTO #ttCSVF FROM DelSol.dbo.CruiseShipVisitFlag AS CSVFlag INNER JOIN DelSol.dbo.CruiseShipVisitFlagType AS CSVFlagType ON CSVFlagType.ID = CSVFlag.FlagTypeID
-- ***** Main ***** SELECT ttTransGroup.[Date] as TransactionDate, QSTPromoStoreGroup.[ID] AS PromoGroupID, QSTPromoStoreGroup.StoreGroup AS PromoGroup, ttTransGroup.AccountNumber AS AccountNumber, CruiseShip.ShipName AS ShipName, CruiseShipGroup.[Description] AS ShipLine, CruiseShipCompany.[Description] AS PortLecturerCompany, Sum(ttTransGroup.Total) AS GroupSales, (CSVP.AdjustedProjection) AS Projection, CruiseShip.Capacity, CASE WHEN CruiseShip.Capacity = 0 THEN 0 ELSE ttTransGroup.Total/CruiseShip.Capacity END AS ASPP, CASE WHEN CONVERT(DECIMAL, CruiseShip.Capacity) = 0 THEN 0 ELSE CONVERT(DECIMAL, ttTransGroup.TRXCount)/CONVERT(DECIMAL, CruiseShip.Capacity) END AS Yield, ttTransGroup.TRXCount, CASE WHEN ttTransGroup.TRXCount=0 THEN 0 ELSE ttTransGroup.Total/ttTransGroup.TRXCount END AS ADPT, CruiseShipPortLecturer.[Name] AS PortLecturer, CSVF.[Description] AS Flag, ttTransGroup.SalesType FROM QSCHQ.dbo.QSTPromoStoreGroup QSTPromoStoreGroup INNER JOIN #ttTransGroup ttTransGroup ON QSTPromoStoreGroup.[ID] = ttTransGroup.PromotionGroupID INNER JOIN DelSol.dbo.CruiseShip CruiseShip ON ttTransGroup.AccountNumber = CruiseShip.AccountNumber INNER JOIN DelSol.dbo.CruiseShipCompany CruiseShipCompany ON CruiseShip.PromotionCompany = CruiseShipCompany.[ID] INNER JOIN DelSol.dbo.CruiseShipGroup CruiseShipGroup ON CruiseShip.GroupID = CruiseShipGroup.[ID] LEFT JOIN DelSol.dbo.CruiseShipPortLecturer CruiseShipPortLecturer ON CruiseShip.AccountNumber = CruiseShipPortLecturer.AccountNumber AND ttTransGroup.[Date] BETWEEN CruiseShipPortLecturer.StartDate AND CASE WHEN CruiseShipPortLecturer.EndDate IS NULL THEN '12/31/2099' ELSE CruiseShipPortLecturer.EndDate END
LEFT JOIN DelSol.dbo.CruiseShipVisitProjection CSVP ON CSVP.TransactionDate= ttTransGroup.[Date] AND CSVP.StoreGroupID = QSTPromoStoreGroup.[ID] AND CSVP.AccountNumber = ttTransGroup.AccountNumber LEFT JOIN #ttCSVF CSVF ON CSVF.TransactionDate = ttTransGroup.[Date] AND CSVF.StoreGroupID = QSTPromoStoreGroup.[ID] AND CSVF.AccountNumber = ttTransGroup.AccountNumber WHERE QSTPromoStoreGroup.ReceivesCruiseShips = 1 AND CSVF.[Description] IS NULL Group By ttTransGroup.AccountNumber, QSTPromoStoreGroup.[ID],QSTPromoStoreGroup.StoreGroup, ttTransGroup.[Date], CruiseShip.ShipName, CruiseShipGroup.[Description], CruiseShipCompany.[Description], (CSVP.AdjustedProjection), CruiseShip.Capacity, CASE WHEN CruiseShip.Capacity = 0 THEN 0 ELSE ttTransGroup.Total/CruiseShip.Capacity END, CASE WHEN CONVERT(DECIMAL, CruiseShip.Capacity) = 0 THEN 0 ELSE CONVERT(DECIMAL, ttTransGroup.TRXCount)/CONVERT(DECIMAL, CruiseShip.Capacity) END, ttTransGroup.TRXCount, CASE WHEN ttTransGroup.TRXCount=0 THEN 0 ELSE ttTransGroup.Total/ttTransGroup.TRXCount END, CruiseShipPortLecturer.[Name], CSVF.[Description], ttTransGroup.SalesType Order by QSTPromoStoreGroup.StoreGroup
SQL2000. I have a table as follows. Table shows a log of solutions posted by agents. For example, Ticket#5 was replied once by each agent A,B and C. ========================== ticket_number || agent || solution ==========================
5 || A || test 5 || B || test 5 || C || test 7 || B || test 7 || C || test 8 || C || test 9 || B || test
I need to find out number of all ticket solutions/counts (how many ticket was touched by all agents) but in format related to the single agent:
AGENT TICKET_NUMBER COUNT A 5 3 B 5 3 C 5 3 B 7 2 C 7 2 .....
The report I'm designing has a number of records in it, but two main fields that I'm concerned with Budget, and spent.
for example
Budget Spent
75 5 75 10 75 8________________ Total 225 23
The spent records are all unique, but the budget record is the same field repeating itself over and over. I've used a grouping to eliminate the repetition of the Budget amount, but the total still comes out to be incorrect. It now looks like this
Budget Spent 75 5 10 8________________ Total 225 23
Is there a way which I can add the values that are displayed rather than the underlying values?
I'h having some throughput problems, and so, I decided to create a view with the info I need. The problem now is this: I have to select all the days where there were associations in my clients website in this format: dd/MM/yyyy (xx) where XX is the number of associations on that day. Here is the first code, wich worked but resulted in timeout:SELECT DISTINCT (CONVERT(varchar, buy_date, 103) + ' (' + CONVERT(varchar(10), (SELECT COUNT(*) FROM user_plan up2 WHERE CONVERT(datetime, CONVERT(varchar, up2.buy_date, 101)) = CONVERT(datetime, CONVERT(varchar, up1.buy_date, 101)))) + ')') AS 'text',CONVERT(datetime, CONVERT(varchar, buy_date, 101)) AS 'value'FROM user_plan up1WHERE CONVERT(varchar, buy_date, 101) <= CONVERT(varchar, getdate(), 101)ORDER BY value DESCThen I tried to create a view in wich I intented to save the number of associations to avoid the n² complexity of my query... This is the create view script: CREATE VIEW quadro_social AS SELECT COUNT(1) AS total, CONVERT(VARCHAR, buy_date, 103) as buy_date, CONVERT(datetime, CONVERT(varchar, buy_date, 101)) AS 'value' FROM user_planGROUP BY buy_dateBut what happens is, becaus the "buy_date" column is datetime, they are not beign grouped because they have different times, but the same days... How can I group the registers with the same date (dd/MM/yyyy) ignoring the hour, minutes and seconds??? Thanks a lot!Guilherme Bertini Boettcher
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
I am having a problem. See, I have a small table that tracks users visits to my site. And I want to have it show all the people visiting first on the bottom of the page, people just visiting on top. I can solve that with a simple ORDER BY DateVisited clause. My problem is that I also want to group peoples IP addresses together too. If I don't, when two people visit the website around the same time, browse around, I get overlapping of IPs.
Example how it is now: 1. 127.0.0.1 /default.asp 12:15PM 2. 215.394.293.494 /default.asp 12:16PM 3. 127.0.0.1 /contact/default.asp 12:17PM
Example of how I would like it: 1. 215.394.293.494 /default.asp 12:16PM 2. 127.0.0.1 /default.asp 12:15PM 3. 127.0.0.1 /contact/default.asp 12:17PM
I can ORDER BY IpAddress first, but then my dates are wacked out and the people with lower initial IP numbers appear first, which is meaningless. Any advise?
Hello, I am trying to group the date by minutes. The date is in datetime format. My problem is when grouping the seconds throws me off. eg. id date 1 03-02-1999 10:23:12.000 2 03-02-1999 10:23:11.000 what I want is when I run count(date) and group by date the above example should give me 2. In short it should group 1 & 2 because I want to ignore the seconds. How can I do that. Everything else is working except this is kicking my brains. Any and all help aprreciated. It is also sorts urgent. Thanks in advance. HP
