SQL Server 2012 :: Getting First And Last Value For Every Group?
May 30, 2014
I have the some data in one of a temporary table inside a stored procedure. I want to take the first and last value for every group and the number of records for every group. For example if you run the following script you will get a set of result.
create table #sampledata
(
userId int,
BaseYear int,
TotalSales float
)
insert into #sampledata
select 1,2008,25000 union
[code]....
From that set of result I want to get the data in the following format:
write the T-SQL to get the top 1 row in the group.
Sample data
Stack RefID Name DM1-102Australia Post DM2-102Australia Post DM6-102Australia Post DM3-102Australia Post DM4-102Australia Post DM5-102Australia Post DMASOS-102StarTrack Express
I would like to do a group by when the timestamp is different from an usual pattern,
create table #tbl ( V1 int, V2 datetime, V3 float) insert into #tbl values (1,'2012-12-12 10:15', 12.5) insert into #tbl values (1,'2012-12-12 10:35', 2.5) insert into #tbl values (1,'2012-12-12 10:45', 1.5) insert into #tbl values (1,'2012-12-12 11:15', 10.5) insert into #tbl values (1,'2012-12-12 11:30', 11.5) select sum(v3) [value], v1 from #tbl group by v1 drop table #tbl
i would like to have result set as, aggregating results of every 15 minutes and when it exceeds 15 minutes then add the same in next group by. This 15 minute interval can be specified in the query itself.
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year Month Date
And the following row groups:
Region Product SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
select top 15 count(*) as cnt, state from table group by state order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
PROD1(cluster 1) Clustered SQL instance1 PROD2(cluster 1) DR 1 (cluster 2) Clustered SQL insatace 2 DR 2 (cluster 2)
I have set an availability group up from the PROD instance to the DR instance.How does the AG behave if a SQL instance fails at PROD? Does it try to fail over to Node 2 on Prod before going over to DR? or bring the Replica at DR online straight away? Can we only use Manual Failover of the AG in this scenario to make use of the High Availability of the Windows cluster?
I am trying to make a query that will group my errors messages together - my problem is that each of the error messages is unique, due to them having an unique id in them.
What I need to do it select the top 80 percent of records per group based on the group total. To be clear I am not trying to just grab the top x percent of rows.
Table 1 has the total number of repair orders per dealer. This can be obtained by simply grouping on DealerID and counting the number of RepairIDs.
Table 2 has information on some of the repair orders and it is needed to select the top 80% of tire sizes. This table will be joined to Table 1 and grouped by DealerID and Tire.
I have column which stores People count based on department, Now I want to keep them in the batch of 1000, If the running summary of (No of people) from departments reached 1000 then it should start sum(no of people) from 0 to 1000
Is there any running summary kind of function which can start sum record with in range of 0-1000
For Ex. My Data stored like this Dept People Count CSE 200 IT 250 EEE 312 ECE 214 MEC 337
Batch Grouping Dept People Count BatchSum CSE 200 200 IT 250 450 EEE 312 762 ECE 214 976 MEC 337 337 (Note here since its crossing 1000, its resetting and starting summary)
I implemented this with While Loop & if condition, But its very slow, is there any other way to achieve it in better way.
--CREATE TEMP TABLE IF OBJECT_ID('tempdb..#TEMP_WK_STOCK') IS NOT NULL DROP TABLE #TEMP_WK_STOCK CREATE TABLE [#TEMP_WK_STOCK]( [WMNUMBER] [int] NOT NULL, [STOCKID] [int] NOT NULL)
[Code] ...
returns just 2 rows and misses the fact that the wmNumbers stop and start a few diffrent times. I cant see how to set up the query to bring back the 6 rows i would be expecting it this case, without going to a cursor which i really don't want to.
I am trying to get a count by product, month, year even if there are is no record for that particular month.
Current outcome: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 XYZ April 2014 34 ...
Desired outcome: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 XYZ March 2014 0 XYZ April 2014 34 ...
The join statement is simple: Select Product, Month, Year, Count(*) As Count From dbo.Products Group By Product, Month, Year
I have also tried the following code and left joining it with my main query but the product is left out as is seen:
DECLARE @Start DATETIME, @End DATETIME; SELECT @StartDate = '20140101', @EndDate = '20141231'; WITH dt(dt) AS ( SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start), 0)) FROM ( SELECT TOP (DATEDIFF(MONTH, @Start, @End) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 FROM sys.all_objects ORDER BY [object_id] ) AS n )
2nd attempt: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 NULL March 2014 0 XYZ April 2014 34 ...
What I want is this (as is shown above). Is this possible?
Desired outcome: Product Month Year Count XYZ January 2014 20 XYZ February 2014 14 XYZ March 2014 0 XYZ April 2014 34 ...
automatically replicates new databases to Availability Group partners - if you do a little prep work on your environment first.To make it work:
1) Create linked servers on all group members pointing to all other servers in the group, with names matching the hostnames they represent. 2) Ensure suitable credentials (or 'current context' impersonation) for linked servers. Also: Enable RPC and RPC OUT 3) Run the DDL code below. 4) Schedule hadr_replicate_queue on [master] to run as often as you want initial syncs to occur. Every 5-10 minutes is plenty for most purposes. 5) Connect to an availability group listener and call CREATE DATABASE :)
I use a slightly more extended version of this code at home to do things like permissions synchronization across replicas - I essentially allow applications to install direct to an availability group replica and then have all the relevant objects replicate to other nodes. I don't really like going through manually and doing things, even though there's an AddIn from SQLSkills for management studio - it still requires manual intervention.
The main use I have for this at home is that I'm using the Azure pack, and want to automatically ensure that my newly created 'SQL Server Cloud' databases are highly available, plus it means when I install a non-alwayson aware product it doesn't require any extra work afterwards to allow failover to another machine.
* AlwaysOn Self-Population Script * By: Steve Gray / steve@mostlyharmful.net * Usage: Free, but buy me a beer if you're ever in Brisbane. **/ USE [master] GO IF EXISTS (SELECT * FROM sys.tables WHERE name='hadr_pending_replicate')
I've inherited a table of members that has the following structure:
CREATE TABLE [dbo].[dimMember]( [dimMemberId] [int] IDENTITY(1,1) NOT NULL, [dimSourceSystemId] [int] NOT NULL CONSTRAINT [DF_dimMember_dimSourceSystemId] DEFAULT ((-1)), [MemberCode] [nvarchar](50) NOT NULL, [FirstName] [nvarchar](250) NOT NULL, [LastName] [nvarchar](250) NOT NULL,
[Code] ....
Based on the way the data loads into the table there's a possibility of some records being near duplicates of each other. For example, we can have a member that has records that have the same first name, last name, SSN, but different addresses, membercodes, subscribercode etc... This can happen in pretty much any variation thereof.
What I want to do, is add a new column and use that to group the similar records under based on comparing on several columns. By this I mean, if a member matches 4 of the 7 values below with another member, we would group these:
First Name (1st 3 characters) Last Name DOB CurrentAddress1 MemberCode SSN SubscriberCode
I'm at a loss of how to structure the SQL to update the new column in the table.
I'm trying to write a query to select various columns from 3 tables. In the where clause I use a set of conditions, but most important condition is that I only want to see all results from the different columns where the ph.ProdHeaderDossierCode contains at least 25 lines of processed hours. I tried this with group by and having, but I constant get error messages on all other columns that I want to see: "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I make this so I can see all information I need?
I've 2 tables QuestionAnswers and ConditionalQuestions and fetching data from them using CTE join and I'm seeing repetitive rows (not duplicate) like, If you have multiple answers for 1 question, the output is like
where london where paris where toronto
why us why japan why indonesia
I want to eliminate the repetitive question and group them as parent child items.
with cte as ( select cq.ConditionalQuestionID from ConditionalQuestions cq inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=5 and qa.IsConditional='Y') select distinct q.Question, a.Answer from QuestionAnswers qa inner join Answers a on a.AnswerID = qa.AnswerID inner join Questions q on q.QuestionID = qa.QuestionID inner join cte c on c.ConditionalQuestionID = qa.QuestionID;
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
I have 2 listeners on the same AG group and i cant change the registry setting for RegisterAllProvidersIP for 1 of them when you reboot it does change back to the default which is 1200.
I have created the file group for my database.First i took backup of individual file group(mdf and ndf) then I tried to restore only secondary(ndf) file group.I got error like
Restore failed for Server 'pcnameSQLEXPRESS'. (Microsoft.SqlServer.SmoExtended) File 'regSQL_dat' was not backed up in file 1 on device 'D:vtndf.bak'. The file cannot be restored from this backup set. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3144)
When i tried to restore only primary file group i got the same error.
Can i restore individual file group? I
For the purpose of data archiveng,i have taken back up of ndf file (it contains very old data) & i have removed this file from database.Now my customer asking these file data.Now i have to again attach/restore this ndf file.how to attach/restore.
Unable to access the 'T' database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation. Changed database context to 'T'.
I have 2 sql servers running in an AG group. SQL 2 is fully syncd and is used for read only reporting.The connection from the listener using ReadOnly works and connects to SQL 2. The problem is, when a database in SQL 2 is suspended from the AG group, the listener does not connect to the SQL 1 (primary).
The AG group is setup exactly like this:
Readable Secondary = Yes for both SQL 1 and 2, Availability Mode = Synchronous
why the listener does not connect to primary server?Connection in primary was set to "Allow read/write connections" for the AG. This meant "Connections where the Application Intent connection property is set to ReadOnly are not allowed. " Setting to "Allow All Connections" worked.
I am using the following query (which works fine):
select min(timex) as start_date ,end_date ,entityid ,entityname ,locationid
[code]....
However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.
Problem 2:I have the following table which shows the location of different people at 1 hour intervals
I've got an availability group with multiple databases, replicating to multiple secondary servers. On one of the secondary servers, some of the databases are not synchronising, and when we try re-establish the sync we get an LSN error. I can't see any obvious way to re-establish only one database on one secondary without affecting all databases on that secondary or affecting that database on all secondary nodes.
The options I seem to have are to either remove the database and then re-add it, in which case this affects all secondary replicas, or to remove the secondary replica and add it, in which case all the DBs are added.
I have set up a couple of servers in a SQL 2012 AlwaysOn Availability Group (non FCI). I have also configured a Listener which enables SQL clients to connect to the server currently servicing the database, as expected.
I would also like non SQL clients to be able to connect to the server currently hosting the database so that they can run scripts sitting in a share. I don't have a shared disk so just have a directory share on each server with the same scripts in each directory.
I am able to ping and RDP to the listener IP address/name and end up on the correct server but am unable to connect to the share ListenerNameShare. Is that actually supported? If it is, any thoughts on what I need to do to get it going. If it isn't what other options do I have?
