Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.
ID PersonID Date Type Qty 1 1 01/01/2011 Accept 5 (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis same) 3 2 02/01/2010 Accept 10 (show Accept row=3,ignore deny row)
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
I have a report that has three levels of grouping including headers (containing titles) and footers (containing summary values). I would like to programatically show/hide these header/footer sections based on a Show/Hide paramter. Is that possible? Currently I work around the problem by just having two physical reports (one with the header/footers, one without)
I tried my syntax below, but it said that the field were not in the group by. This is syntax and for each of the 2 names (only small subset of real data) I need a Total column to display between each different person like so:
I'm running SQL Server 2008 Feb08 CTP and I've got a tablix with column groupings and row groupings which works nicely, I'm a becomming a big fan of the tablix.
However sometimes the filter I have on a column group returns no records and the whole column group disappears. Is there any way to make this column appear with but with empty cells as I have another tablix down the page with the same columns and I want the columns to all line up on the multiple tablix.
In Version 2005 I used to achieve the equivalent of a column group within a table by adding a list with filter to the cell, this meant the column always was shown, the tablix allows does this much more elegantly but I'll have to go back to the old method if I can't make the column remain.
This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008
Create Table SanitationGarbage ( saleid int ,projectname varchar(200) ,typeofsale varchar(200)
[code]....
Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to. How does this need to be written so the data is displayed in the proper formatting?
Select projectname ,Count(saleID) As [Total Sales] ,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales] ,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales] FROM SanitationGarbage GROUP BY projectname order by projectname asc
i'm trying to capture data for every hour and would like to display the hours of the day in a column.
select case when (datepart(hh,calldate) between 0 and 7AM then 1 else 0 end as [12-7AM], case when (datepart(hh,calldate) = 8 then 1 else 0 end as [8AM] from table1 where cast(calldate as date) = cast(calldate as date)
I made a select query which shows following output as shown in picture .
Now I want to add one more column in this query to show current bags and Bags in these 2 columns i want to show calculation like in first rows currentbags column (Receivedbags-DeleiveredBags) and in currentWeight column RecivedWeight+loss-gain-Deliverdweight) which is 1400 and 697.5 after that in secound row i want to add frist rows currentbags value+ second rows (Receivedbags-DeleiveredBags) and same in weight like daily stock register so output looked like below image
There is one more column common date according to which i have to make calculation like
rid commondate recdate recbags recweight loss gain delbags delwght
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.
-Whenever terminatedate is not null -Also with in the ID group if one record terminatedate is null then set flag=yes
Rule-2: Set flag=No - with in the ID group Whenever terminatedate is null
My output should look like this..
ID WorkId HireDate TerminateDate Flag 1 123 2006-01-05 2013-01-13 yes ( Rule-1: set flag = yes within the group for ID =1 because all the 3 records terminatedate is not null) 1 456 2006-02-23 2013-04-13 yes 1 789 2005-12-12 2013-06-13 yes 2 913 2004-12-15 2012-01-13 yes (Rule-1: set flag = yes within the group for ID =2 because all the 2 records terminatedate is not null)
I have 3 columns of data CustomerNum, Newsletter, and NumSent.
I need to return the only the CustomerNumber and Newsletter combinations having the Max(NumSent) So it should be a unique customernumber with the newletter having the most numsent.
Is this possible? I have tried using row over partition but I'm not sure how group it correctly, so basically every time there is a new 1 in new_commsstream within a personid the row number goes up by one.
I want to set up stored procedures that let me group data into months. I use the data to produce charts so I need to be able to group into month and year like '01, 2000', '02, 2000'. What is the best way to produce data grouped into month and year from a date field? Using the Month and Year functions I get data like 1,2000 and 11,2000 which don't stand up to a text sort.
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad
I'm trying to pull records from a source/staging table where there is a duplicate row in it.I don't need that as the requirement is to garbage in /garbage out.when I do that from mart and use joins btw fact and dimensions, Im not getting this duplicate record as Im using distinct/group by. If I removed it, then it returns more than 3000 rows which is not correct. Is there a way I can keep these duplicates without removing group by...Im using correct joins and filters.
I have a table which I would like to group on several columns, and for the Contract number, I'd like a maximum of four different columns which would contain pivoted information.
Here is my DDL:
CREATE TABLE [dbo].[SV00403]( [CUSTNMBR] [char](15) NOT NULL, [ADRSCODE] [char](15) NOT NULL, [Contract_Number] [char](11) NOT NULL, [WSCONTSQ] [int] NOT NULL,
How can I display 0 when using COUNT and GROUP BY?I'm using SELECT CASE in my query. I was trying to use COALESCE but no result, COUNT result = 1. (there should be 0).
COALESCE((COUNT(DISTINCT (CAST((CASE WHEN CurrStat = @Stat AND LogDate = @LogDate THEN Enumber ELSE 0 END) AS int)))), 0) AS InTrack,
I have a query taken from a Crystal Report, and I've been working to modify it for a slightly different purpose. The initial report was designed to take an input of an end date, go back to the last day of the previous month, get Actuals (sales totals), then add up all sales, costs, and purchases up to the end date, then display assorted data.
The new query needs to take a begin date and and end date, go back to the last day of the previous month for actuals, add all sales, costs, and purchases to those actuals until it gets to the begin date. That is now the new Actual, and we need to sum up all transactions from then to the end date.
My problem lies in the fact that I can't get the query to add up the numbers.
The query is here:
SELECT DailyReport.Store, DailyReport.Report_Date, sum(dailyreport_Detail.sales) as Sales, sum(dailyreport_detail.actual) as Actual, sum(dailyreport_detail.cost) as Cost, CompanyGroups.Category, DailyReport_Detail.Product, CompanyGroups.Retail_Inv FROM (`DailyReport` `DailyReport` INNER JOIN `DailyReport_Detail` `DailyReport_Detail` ON
Below is my SQl which just counts the number of appointments and grouped by clinic. This is great but what I'd like to add is the percentage within each clinic.
For example Clinic BRESRAD1 has a total of 61 appointments, of which 75.41% are Normal Appointments and 24.59% are Diagnostic, Ideally I would like the percentage in the next column.
BRESRAD1 Normal Appointment 46 BRESRAD1 Diagnostic Appointment 15 BRESRAD2 Normal Appointment 17 BRESRAD2 Diagnostic Appointment 12 BRESRAD3 Normal Appointment 34 BRESRAD3 Diagnostic Appointment 43
My SQL is as follows:
SELECT ClinicCode, CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END AS [Diagnostic Appt], COUNT(OPAppointmentID) AS CountOfOPAppointmentID FROM dbo.OP_APPOINTMENT WHERE (AttendStatusNatCode IN ('5', '6')) AND (ApptFinYr = '2014/15') GROUP BY ClinicCode, CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END ORDER BY ClinicCode
How can I add a group number to the following query?
For example, I want to be able to have all rows that have Category = 'Field Sales' and Division = 'CA BDM' to be given a unique group number (GN):
RN ReportDate Category Division TotalBalance -------------------- ---------- ------------------------------ ------------------------------ --------------------- 1 2015-06-08 Field Sales CA BDM 299743154.3912 2 2015-06-07 Field Sales CA BDM 299765954.0354 3 2015-06-01 Field Sales CA BDM 297902654.4172 1 2015-06-08 Key Accounts Life Office 49954981.74 2 2015-06-07 Key Accounts Life Office 50016989.22 3 2015-06-01 Key Accounts Life Office 50169967.26 4 2015-05-31 Key Accounts Life Office 50169918.01
Becomes
GN RN ReportDate Category Division TotalBalance -------------------------- ---------- ------------------------------ ------------------------------ --------------------- 1 1 2015-06-08 Field Sales CA BDM 299743154.3912 1 2 2015-06-07 Field Sales CA BDM 299765954.0354 1 3 2015-06-01 Field Sales CA BDM 297902654.4172 2 1 2015-06-08 Key Accounts Life Office 49954981.74 2 2 2015-06-07 Key Accounts Life Office 50016989.22 2 3 2015-06-01 Key Accounts Life Office 50169967.26 2 4 2015-05-31 Key Accounts Life Office 50169918.01
i.e. each combination of Category+Division results in a new GN.
The query is:
selectROW_NUMBER() over (partition by Category, Division order by ReportDate desc) 'RN' , ReportDate , Category , Division , sum(BalanceGBP) as 'TotalBalance' FROM FlowsAndOpenings group by ReportDate, Category, Division order by Category, Division, RN
I am trying to find a count on group of our memberid`s who were active within a year since 2010 till today for particular memberships in my table I have memberid int effectivedate datetime termdate datetime Membershiptype varchar(10) ='GOLD','Silver' and 'Platinum'. I haven't used sql in a long time..
I am using SQL 2012. I have a GROUP BY and I want to select two other fields from my table at the same time: One column that is a string (account_code) and one that I need to perform a count on (customer_number). I know the code COUNT(DISTINCT customer_number) works for getting that. I need to select both of those fields on top of what I have. I have the following:
DECLARE @Providers TABLE (ID INT IDENTITY(1,1), Provider_Name VARCHAR(20), Uniq_Id VARCHAR(10), Total_Spent MONEY, Total_Earned MONEY) INSERT INTO @Providers (Provider_Name, Uniq_Id,Total_Spent, Total_Earned)
I have a query that pulls back task and user assigned. Each task can have multiple users assigned. I want to pull back the single task and all the users assigned in one row.
Current Query:
select t.Name 'Task', d.FirstName + d.LastName 'User' from [dbo].[Tasks_TemplateAssignTo] a join Task_Template t on a.template_id = t.ID join Doctor d on d.id = a.provider_id
Results from query above:
TaskUser Call CustomerJohn Smith Call CustomerBetty White Call CustomerTammy Johnson Order suppliesGreg Bullard Order suppliesJosephine Gonzalez
Expected Results:
TaskUser Call CustomerJohn Smith, Betty White, Tammy Johnson Order SuppliesGreg Bullard, Jospehine Gonzalez
I've got this set of registers (just an example) after ordering by the first 3 columns:
value_A value_B value_C ID date 1 2 3 YVIR 29/08/2015 1 2 3 ANTE 27/04/2015 1 2 3 REGO 20/02/2015
I need to get as a final result:
value_A value_B value_C ID date 1 2 3 REGO 29/08/2015
In other words, I need to get, after ordering the result by the date field, the most recent date but at the same time the oldest ID in the list.
I've been trying to do this with the group by clause:
select value_A, value_B, value_C, min(ID), max(date) -- or max(ID) from table group by value_A, value_B, value_C
But in the field ID I'm getting the wrong result because this value is been associated with the alphabetic order.
In access this query involves the function LAST, but in SQL I have not found a good way to perform this. And I am asking because I have seen some possible solution but almost all of them involving the UNION operation, but my problem is, this table can have more than 350.000 registers.
This table is update by some one else, I just can access the information and use it as a source.
I have a large number of jobs that I run using a cursor (All jobs start with PS_ and each job loads a single table from ORACLE) so they can run in parallel. I want to be able to be notified when the last job completes. Should this done using a trigger? Or should I just add a loop statement to the stored procedure that fires this job? I know which individual takes the longest and I could simply just add a notification to send email on success but I want it to be more robust. Below is what I am using to trigger the jobs.
BEGIN SET NOCOUNT ON; DECLARE @year CHAR(4) DECLARE @month CHAR(2) DECLARE @day CHAR(2) SET @year = CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR)
