Transact SQL :: Add Group Number To Similar Rows?
Jun 9, 2015
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
View 2 Replies
ADVERTISEMENT
Sep 23, 2015
I've unpivoted some data and stored it in a temp table variable
idNumFreqDtFreqrn
16100120120101M2
16100120120101M3
16100120100101M4
16100120100101M5
16100120060101M6
16100120000929Q7
16100119990101A8
16100119970101M9
Using the above data, if two rows have the same FreqDt, I want to see the record with the lowest row number.
So it should look like the below
idNumFreqDtFreqrn
16100120120101M2
16100120100101M4
16100120060101M6
16100120000929Q7
16100119990101A8
16100119970101M9
I've used the below code to accomplish it
SELECT DISTINCT
CASE WHEN t2.idNum IS NULL THEN t1.idNum ELSE t2.idNum END,
CASE WHEN t2.FreqDt IS NULL THEN T1.FreqDt else t2.FreqDt END,
CASE WHEN t2.freq is null then t1.freq else t2.freq end
FROM @tmptbl as t1 LEFT JOIN @tmptbl as t2
ON t1.idNum = T2.idNum
AND t1.FreqDt = t2.FreqDt
AND t1.rn = (t2.rn-1)
After all this, I'm supposed to condense the result set to only include sequential frequency dates with unique frequencies.should look like below (this is where I'm stuck)
idNumFreqDtFreq
16100120060101M
16100120000929Q
16100119990101A
16100119970101M
answer is below:
SELECT T1.*
FROM @t as t1 LEFT JOIN @t as t2
ON t1.idnum = T2.idnum
AND t1.freq = t2.freq
AND t1.rn = (t2.rn-1)
WHERE t2.idnum IS NULL
View 5 Replies
View Related
Apr 30, 2015
I have a table Transaction that looks something like the following :
TransactionID
Currency Credit Debit
1 USD 500 0
2 Afcu 6000 0
[Code] ....
I write query like this
select SUM(credit)-SUM(Debit)as [Balance] ,Source from Transaction group by Source
And it came like
Balance Source
1500 USD
6000 Afcu
6800 INR
7000 Pfc
-200 AUD
But I also want to add Afcu , Pfc with USD and want output like
Balance Source
14500 USD
6800 INR
-200 AUD
View 3 Replies
View Related
Oct 8, 2015
I have a SELECT statement with multiple JOINs. I want to number the rows returned by this SELECT statement. What's the easiest way to do this?
My current SELECT statement returns this:
ProjectId -- TaskId -- TaskName
123 - 111 -- Do something
123 - 222 -- Do something else
123 - 333 -- Do one more thing
I want to return this:
ProjectId -- TaskId -- TaskName -- Sequence
123 - 111 -- Do something -- 1
123 - 222 -- Do something else -- 2
123 - 333 -- Do one more thing -- 3
View 2 Replies
View Related
Sep 2, 2015
I am using Sql Server 2008 R2.I have a existing query that basically says
Select Top 50 Subscriber_ID, Member_Name, Group_ID
from my_table
order by rand(checksum(newid()))
However the client now wants to have at least two from each group_id. There are 17 different groups. When I run this as is I get about six of the 17 groups in the results. How can I change this to get at least two results from each group_id?
View 6 Replies
View Related
Oct 26, 2015
I have a table Customer with below column
CustomerNumber , FName,LName,DOB
I have either 2 or 1 row for each customer number I want to write a SQL to pull data into 1 table based on same CustomerNumber, if only one row is present for the particular customer number it should pull null in FName2,LName2,DOB2 columns.
example:
CustNumber Fanme Lname DOB
1 Sam tiller 08/26/1981
1 Joe timy 01/02/1986
2 jack niks 09/09/1990
Result I want:
CustNumber Fname1 Lname1 DOB1 Fname2 Lname2 DOB2
1 Sam Tiller 08/26/1981 Joe timy 01/02/1986
2 Jack niks 09/09/1990 null null null
View 7 Replies
View Related
Jun 17, 2015
Using the following tables and data---
CREATE TABLE tblRiskReviewHistory(RiskReviewID int, RiskReviewHistoryID int, Name nvarchar(20), Description nvarchar(50), Date date)
INSERT tblRiskReviewHistory(RiskReviewID, RiskReviewHistoryID, Name, Description, Date)
VALUES(1,1,'Customer A','Profile Assessment','01/01/2015'),
(1,2,'Customer B','Profile Assessment','02/20/2015')
[Code] ...
And currently outputs;
Name Description Date Question Answer
Customer A Profile Assessment 01/01/2015
How complex is the structure?
Customer A
Profile Assessment
01/01/2015
The total value of assets?
Less than GBP 1 million
Customer A
Profile Assessment
01/01/2015
The volume of transactions undertaken?
Low (-1 pmth)
[Code] ....
However, I would like it to output;
Name
Description
Date
How complex is the structure?
The total value of assets?
The volume of transactions undertaken?
How was the client introduced?
Where does the Customer reside?
[Code] ....
The number of questions are unknown for each RiskReviewID and they can be added to in the future.
View 7 Replies
View Related
Jul 1, 2015
A vehicle loading confirm after that what time its gated out so i want to take the time duration between finish loading and gate out, find sample table records , i want to take more than 5 hrs difference between finish loading and gate out.
tld_tripno
tld_sno
tld_activitycode
tld_location
tld_actualdate
TLM3004242015
[Code] .....
I want to take the result like this
Tld_tripno
Finish Loading
Gate Out
Date and Time difference
TLM3004242015
2015-05-11 19:58:00
2015-05-12 08:42:00
12:44:00
View 10 Replies
View Related
Nov 16, 2015
is it possible to use the window functions to count the number of rows in the previous 24hours from the current row.
I have a table of events like:
User, TimeStamp, etc...
I want to identify the row which is the event number 50 in the past 24 hours.
does the window functions can do this? and how?
the ROW PRECEDING etc... appear to not have enough feature to support time related function.
Stream-insight is able to create this type of query, but I have to do it directly in SQL.
View 6 Replies
View Related
Apr 10, 2008
Hey Gurus,
I have a problem on getting the sql statement which will group similar column name and sum their number together(another column).
A million thanks in advance.
View 5 Replies
View Related
Mar 30, 2015
We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.
So some sample records of a complete package would look like this:
ItemId | ParentId | Name | QtyAvailable
----------------------------------------
1 | NULL | Package A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
ItemId's 2 & 3 are items contained within the ItemId 1 package.
Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:
Name | Available Qty
--------------------------
Package A | 15
Package B | 100
How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?
View 6 Replies
View Related
Sep 28, 2015
What I am working with unfortunately is a very poorly designed and non-normalized database. Please don't criticize the design. I didn't design it, but I have to write queries against it.I have 2 tables. 1 is called EnterVolume. The other is ExitVolume. Similar columns exist in each.
CREATE TABLE [EventPortion].[EventEnterVolume](
[SequenceNumber] [int] NULL,
[TrialID] [nvarchar](255) NULL,
[TimeOfEvent] [int] NULL,
[UniversalTime] [nvarchar](255) NULL,
[SBOINumber] [int] NULL,
[SEntityPosUpdateIndex] [int] NULL,
[VolumeIndex] [int] NULL,
[code]....
The rules of the database state that for every EnterVolume row (for a given TrialID, SBOINumber, and OwnerBOI) there must be a corresponding ExitVolume row in the ExitVolume table.What I need to do is to capture the paired TimeOfEvent entries from each table for each paired row. Nothing says that an SBOI cannot enter and exit a OwnerBOI's volume several times during the same Trial.Every time a SBOI Enters an OwnerBOI's volume during a certain trial, a row is created in the EnterVolume table. And Likewise when Exiting a OwnerBOI's volume during a certain trial, a row is created in the ExitVolume table.
So here is a query that I attempted, but gave undesirable results:
SELECT EV.TimeOfEvent AS [Enter Time], XV.TimeOfEvent AS ExitTime
FROM IntegratedTest1.EventPortion.EventEnterVolume AS EV INNER JOIN
IntegratedTest1.EventPortion.EventExitVolume AS XV ON EV.TrialID = XV.TrialID AND EV.SBOINumber = XV.SBOINumber AND EV.OwnerBOI = XV.OwnerBOI
Here is some sample data:
EnterVolume
TrialID SBOI OwnerBOI TimeOfEvent
1 A D 5
1 A D 2000
[code]....
View 8 Replies
View Related
Nov 20, 2015
I have to run this 3 times for similar but slightly different parameters. I only change the very top peice of code slightly each time:
1. Partition by Forename, Surname, DOB, Postcode
2. Partition by Forename, DOB, Postcode
3. Forename, Surname, DOB.
As you can see very subtle change, ideally I'd like to find a way to run just one report where all of the above occur, the issue I face running separately is one person may appear on 1 or more giving duplicates.
USE HealthBI_Views;this bit below is basically grouping my output on Forename, Surname, DOB & Postcode. What the whole process is trying to achieve is to show where a patient which has all of the above fields identical but has a different patient identifier suggesting that the service has allocated to unique identifiers to the same person which will result in both records needing to be merged.
WITH cte
AS (SELECT *,
COUNT(HEYNo) OVER (
PARTITION BY Forename, Surname,
DOB
[code]...
--- this bit below is simply showing all instances where the above criteria is met and there are more then one instances.WHERE countOfHeyNo > 1
--- The final output display all patient identifiable information from the MF_PATIENT table so that the report can be created in SSRS and run routinely by the Data Quality Team who can then investigate each occurance.
View 5 Replies
View Related
Jun 17, 2015
I have a table which contains single legs. E.g.
Row Leg-ID From To On DateFrom DateTo DOW
1 ABC123 AAA BBB CCC 01JAN15 01JAN15 1
2 ABC123 AAA BBB CCC 07JAN15 07JAN15 1
3 ABC123 AAA BBB CCC 14JAN15 14JAN15 1
4 ABC123 XXX YYY ZZZ 21JAN15 21JAN15 1
I now want to merge those single records, which follow a pattern. For the above case this would be Row 1+2+3, so the result should be:
Row Leg-ID
From To
On DateFrom
DateTo DOW
1 ABC123
AAA BBB
CCC 01JAN15
14JAN15 1
3 ABC123
XXX YYY
ZZZ 14JAN15
14JAN15 1
The pattern is, that the legs from Row 1 and 2 have identical attributes (Leg-ID, From, To, On, DOW) and are on consecutive weeks on the same weekday. I was doing this through a while-look:
check if there is a record matching the following week (a.DateTo = dateadd(d, 7, b.DateFrom))if there is a match, then update the previous week record DateTo with the following week DateFromdelete the following week record but this is very slow, for 50T rows it runs approx. 6 hrs to shrink everything..
View 4 Replies
View Related
Sep 24, 2007
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
View 1 Replies
View Related
Jun 23, 2015
Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
View 4 Replies
View Related
Jan 29, 2007
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
View 9 Replies
View Related
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies
View Related
Jul 24, 2015
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 3 Replies
View Related
Oct 13, 2013
I have the following data:
POL# POL_EFF_DATE
123 1-1-2012
123 1-1-2012
123 1-1-2012
123 1-1-2013
123 1-1-2013
456 1-1-2012
456 1-1-2012
456 1-1-2013
456 1-1-2013
I want to add sequence number to each group with same Pol# and Effective Date like this:
POL# POL_EFF_DATE Seq
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2012 1
123 1-1-2013 2
123 1-1-2013 2
456 1-1-2012 1
456 1-1-2012 1
456 1-1-2013 2
456 1-1-2013 2
is there a set based function to accomplish this in SQL 2008?
View 2 Replies
View Related
Mar 14, 2008
How would I get the rownumber() for a Group Footer in report designer not in my query?
I put the syntax rownumber(nothing), but it is giving me the row numbers for the detail section.
Any help would be appreciated.
View 1 Replies
View Related
Apr 22, 2008
Like many others, I have a large report that groups on invoices, and does a page break between each invoice. From reading other posts in this forum, I was able to reset the page number on the grouping, however, there are some things that I need to display only on the last page of each invoice, but I don't see how I will be able to determine when I'm on the last page for a particular invoice. Can I somehow determine up front the number of pages for a group so I know when I'm at the last page?
Any help is greatly appreciated!
View 1 Replies
View Related
Jun 11, 2008
SOURCE TABLE
ID________COMMENT
123_______I am joe
123_______I am programmer
124_______I am Wang
124_______I am programmer
124_______I like cricket
DESTINATION TABLE
ID_____SEQ______COMMENT
123_____1_______I am joe
123_____2_______I am programmer
124_____1_______I am wang
124_____2_______I am programmer
124_____3_______I like cricket
can somebody please advise the easiest way to do this in sql 2000?
View 3 Replies
View Related
Aug 4, 2013
I have created a table:
CREATE TABLE [dbo].[Student](
[StudentNumber] [varchar](50) NOT NULL,
[Name] [char](50) NOT NULL,
[Contact] [int] NOT NULL,
[Address] [char](50) NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[YearEnrolled] [int] NOT NULL,
[Year] [int] NOT NULL;
And insert the following data:
INSERT INTO Student VALUES('IT123456X', 'Ahmad Adam','05-18-1997', '33 Mangis Rod', 19970518, 2013, 1);
INSERT INTO Student VALUES('IT334455U', 'Mary Tan', '01-23-1996', '51 Koon Seng Road', 23-01-1996, 2012, 1);
INSERT INTO Student VALUES('BS123456X', 'Samuel Lee', '03-30-1997', '2 Joo Chiat Lane', 30-03-1997, 2013, 1);
INSERT INTO Student VALUES('BS234234Z', 'Nathaniel Koh', '12-08-1997', '5 Stll Road', 08-12-1997, 2013, 1);
INSERT INTO Student VALUES('BS987987F', 'Siti Faridah', '07-04-1995', '3 Duku Road', 04-07-1995, 2011, 3)
How do i calculate the age and how can i group by the StudentNumber like 'IT%'
View 3 Replies
View Related
Sep 12, 2006
I have 1 table named BookMe,
with fields
$Date, $Div, $Name
I want to select the top N number of Div's (ie highest Div value) per distinct Date.
i know how to get the distinct date like this:
SELECT DISTINCT $Date FROM BookMe ORDER BY $Date
How do I combine a TOP keyword to get the TOP n rows per distinct date???
Thx!
View 5 Replies
View Related
Apr 18, 2000
Is there any way of configuring this parameter?
A work around?
Or have I reached the limits of scalablilty on this issue?
Please assist me
Thanks
View 1 Replies
View Related
Aug 30, 2013
I try this sql query:
Code:
SELECT
[NAMES], [NUMBER]
FROM
[CV].[dbo].[T40]
WHERE
[NUMBER] = '44644'
GROUP BY
[NAMES], [NUMBER];
The output is:
Code:
NAMESNUMBER
BENCORE S.R.L.44644
BENCORES.R.L. 44644
I need instead this other output:
Code:
NAMESNUMBER
BENCORE S.R.L.44644
View 2 Replies
View Related
Mar 23, 2015
I need a new field added 'Field1' which will add SEQUENCE number 1,2,.. based ON GROUP BY MasterID..AND another field TotalCount which will COUNT total masterID (here it will be 2)
CREATE TABLE #Temp1
( MasterID INT,
ClientName VARCHAR(10),
ProductName VARCHAR(50)
[code]...
View 0 Replies
View Related
Sep 26, 2006
I know how to reset the page numbers with each group, but how do you reset the total page number within each group.
EX. Code for page of total pages
="Page " & Globals.PageNumber & " of " & Globals.TotalPages
EX. Code to reset within a group
Custom Code:
Shared offset as Integer
Shared currentgroup as object
Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If not (group = currentgroup)
offset = pagenumber - 1
currentgroup= group
end if
return pagenumber - offset
end function
=Code.GetGroupPageNumber(ReportItems!Category.Value(grouping),Globals!PageNumber)
What I need is code for a combination of the two...to display code for page of total pages that resets within a group.
Any help is greatly appreciated.
Thanks!
View 4 Replies
View Related
Nov 17, 2006
Hi,
I have a situation where I have to write a SQL to generate a sequence number from the count of the group by.
Example:
I have src data :
---------------------------------------------------------------------------------------------------------------
SUBSCRID col1 col2 col 3 col4
1001 val1 val2 val3 val4
1002 vala valb valc valc
1001 ................
1002 ..........
1001 ...........
1003 ................
---------------------------------------------------------------------------------------------------------------
So my result should be :
---------------------------------------------------------------------------------------------------------------
SUBSCRID SUBSCRCNT col1 col2 col3 col4
1001 1 val1 val2 val3 val4
1001 2 ...................
1001 3 .....................
1002 1 vala valb valc valc
1002 2 ................
1003 1 .................
---------------------------------------------------------------------------------------------------------------
Any help on how to write a query will be appreciated?
Thanks,
Venkat
View 5 Replies
View Related
May 7, 2008
Sorry if this question has been answered before--i tried a search but nothing came close to what i needed.
I have a report I am creating where I need to group on whether a number is positive or negative. The number will never be zero. This number is a decimal (currency), if that makes a difference.
I have tried creating a formula field with the following expression:
Code Snippet
IIf(Fields!Num.Value > 0, 1, 0)
I tried creating a group using this field (essentially, positive = 1 and negative = 0), but it gave me no results at all.
If you need any more information, please let me know and I will provide what I can. Thanks for your answers!
View 3 Replies
View Related
Apr 21, 2008
Hi,
I have a report where i do a page break for each supplier. I want to reset my page number to '1' when there is a group break.When i googled for this functionality i found the following chris Hay's blog which provides the solution.
http://blogs.msdn.com/chrishays/archive/2006/01/05/ResetPageNumberOnGroup.aspx
But if i follow the same, I get #Error instead of page number during the run time.How do i resolve the error?
Also, I want to display the "page 1 of totalpages in group" like that. Is that possible?
Thanks in advance
View 1 Replies
View Related
Mar 11, 2014
Given the following example;
declare @CustIfno table (AccountNumber int, StoreID int, Distance decimal(14,10))
insert into @CustIfno values ('1','44','2.145223'),('1','45','4.567834'),
('1','46','8.4325654'),('2','44','7.8754345'),('2','45','1.54654323'),
('2','46','11.5436543'), ('3','44','9.145223'),('3','45','8.567834'),
('3','46','17.4325654'),('4','44','7.8754345'),('4','45','1.54654323'),
('4','46','11.5436543')
How can I show the shortest Distance by AccountID and StoreID. Results would look like this;
AccountNumberStoreID Distance
1 44 2.1452230000
2 45 1.5465432300
3 45 8.5678340000
4 45 1.5465432300
View 7 Replies
View Related
