Noob here. Trying to group by date in report and it is not working as expected. The date fields include a date/times i.e. 12/23/2006 9:45:00 AM. I can change the date properties to make the data appear as 12/23/2006, but when I group on date, it returns multiple rows showing the same date instead of just one row with the one date. The data is aggregated for the grouping so it seems like it should work properly. Here is an example of what I am trying to do.
I store data in a table using a column named InsertTimestamp which is a datetime format. I now want to report on rows by just the date. I have been able to do this by converting the datetime like such: convert(varchar, inserttimestamp, 107) This reurns the data correctly however is very ineffiecnet. DOes anyone know another easy way around this dilemna???
Example table: PeopleID Date Status 1 2004-01-01 True 1 2005-01-01 True 1 2006-01-01 True 2 2004-01-01 True 2 2005-01-01 False 2 2006-01-01 True
I'm trying to find a way to query whether or not someone has had a specific status for 3 years in a row. As you can see from the table above, PeopleID 1 has had a "Status" of "True" for 3 years in a row, whereas PeopleID 2 hasn't--there was one year where they had "False".
I'm wondering I can query this, or if I'm going to have to scan the records manually. :(
I suppose I could write a stored procedure and do some looping too.
I have this query which I pasted below. I need to find the earlist effective date (pcsp_eff), but I need to show all of the fields below in my report like a flat file. I know ususaly when you use Max/Min you have to have a group by, would I group by everything that is in my select statement?
SELECT Distinct dbo.pcs.pcs_id1, dbo.pcs.pcs_lname AS [Last Name], dbo.pcs.pcs_fname AS [First Name], dbo.pcsp.pcsp_eff AS [Effective Date for Provider], Min(pcsp_eff) as "earliestEffectivedate", dbo.pcst.pcst_trm1 AS [Tracking Thru Date], dbo.pcst.pcst_dat3 AS [Re-cred Letter Sent Date], dbo.pcst.pcst_dat7 AS [Re-cred Complete Date], PRO_STATE as "State", PRO_COUNTY as "County"--, PCSP_NET
FROM dbo.pcs INNER JOIN dbo.pcst ON dbo.pcs.pcs_id1 = dbo.pcst.pcst_id1 INNER JOIN pcsp ON pcs.pcs_id1 = pcsp.pcsp_id1 left Join dbo.pro ON pcs.pcs_id1 = pro.pro_id1
WHERE (CONVERT(CHAR(10), dbo.pcst.pcst_dat3, 110) <>'03-23-1977') and (CONVERT(CHAR(10), dbo.pcst.pcst_dat7, 110) = '03-23-1977') and (pcsp.pcsp_prd = 'DGH') AND --(pcsp.pcsp_id2 = '0001') and PCS_CTL = 'I' and pcsp_NET <> 'DACFP' and pcs_id1 = '00004307'
My company is unusual in that our accounting periods are not actual months. We have what we call "Red Fridays." These are spaced 3-5 weeks apart. So, my company doesn't care what happened in the month of April, but they care what happened between April 4 and May 2 because these are the Red Fridays.
So, I have created a database with a table called "RedFridays" with the dates for this year. I want to combine this with various tables in our ERP database. I use a Left Outer Join between the Red Friday Dates and the corresponding date in the ERP database.
I need to create a custom grouping formula which accomplishes the following:
1. Subtract a certain number of months from today's date to determine which Red Friday would be the correct starting date. 2. Group records by date between that Red Friday and the second one. This would be listed as something like "Month 1". 3. Continue grouping in this way to the present date and that Red Friday range.
Can anyone point me in the right direction? Any help would be greatly appreciated.
SELECT tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect, COUNT(*) AS Expr1 FROM tblFileRequests LEFT OUTER JOIN tblFileRequestDetails ON tblFileRequests.MovementId = tblFileRequestDetails.MovementId GROUP BY tblFileRequests.Division, tblFileRequests.Sect, tblFileRequests.Dept ORDER BY tblFileRequests.Dept, tblFileRequests.Division, tblFileRequests.Sect
I also want to include tblFileRequestDetails.DateOut but this cause my grouping to go haywire. How can I include it ? Ultimately I want to create a stored procedure and crystal report based on it. Thanks
I'm adding up quantities of an item that are entered in one after another, so the date is the same, but the time differs.
I used the "first" function in access which works the way I want, but sql7 doesn't use the same function. The "convert" function does not work in access but Here is the gist of the query:
SELECT first(CONVERT(varchar,transactions.tran_date,101)) AS [trandate], transactions.tran_type, SUM(transactions.qty) AS totqty,...etc.
I have a requirement to be able to select and group records by the date portion of a datetime field. ie ignore the time when grouping so that all records lodged on a particular day are seen together.
I have been able to do this by
- converting the datetime data to the number of days since a given date - inserting this into a temporary table - retrieving the the data from the temporary table - convert the data back to a date using DATEPART to display dd/mm/yy
This then gives me the data grouped as required but seems to be a very difficult solution - Is there an easier way??
Masters, The below queries return the data that I seek, but I have no idea how to combine them into a single query.
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [0 to 30] FROM MDM2 WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '0' and '30'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [31 to 60] FROM MDM2 WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '31' and '60'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [61 to 90] FROM MDM2 WHERE DATEDIFF(day, SERVICEDATE, getdate()) between '61' and '90'
SELECT SUM(TOTALSVCAMT) - SUM(TOTALPAYMENTAMT) - SUM(TOTALADJAMT) as [90+] FROM MDM2 WHERE DATEDIFF(day, SERVICEDATE, getdate()) > '90'
Any assistance that can be provided will be greatly appreciated. Grasshopper
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.
The goal is to group all this transactions by Month and Year.
Something like this....
MS030_A MS030_F MS036_A MS036_F MS040_A MS040_F Month Year
3 2 2 2 2 2 1 2006
4 4 7 9 8 9 2 2006
10 10 6 8 8
3 2006
4 4 5 5 3 2 4 2006
5 6 8 3 7 1 5 2006 For just one date column it is pretty straight forward i.e., just do a select count and group by DATEPART ( Mm, DateField) but for multiple columns i am in a total fix....
I tried to ask a similar question yesterday and got shot down, so I'll try again in a different way. I have been looking online at the gaps and islands approach, and it seems to always be referencing a singular field, so i can't find anything which is clear to get my head around it.In the context of a hotel (people checking in and out) I would like to identify how long someone has been staying at the hotel (The Island?) regardless if they checked out and back in the following day.
Data example: DECLARE @LengthOfStay TABLE ( PersonVARCHAR(8) NOT NULL, CheckInDATE NOT NULL, CheckOutDATE NULL
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 connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:
DateKey: - Type: System.Integer - Value: yyyyMMdd Date: - Type: System.DateTime DateStr0: - Type: System.String - Value: dd/MM/yyyy (note: I am not using US culture) - Example: 01/11/2015 DateStr1: - Type: System.String - Value: %d/%M/yyyy (note: I am not using US culture) - Example: 1/11/2015
Filtering on date is working fine:
Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:
I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:
But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.
For date values of my data coming from cube, excel consider them as 'General'
2.1. But value here is same as it was in simple test.
2.2. 'Date Filter' works just fine.
2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell.
2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.
2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.
So, I don't know, what is the puzzle piece that I am missing.
1. Date filter works, group does not work
2. Excel consider it as 'General' string.
3. SSAS does not allow to change 'NameColumn' to Date.
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
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
