Transact SQL :: Grouping Data Within A DB Table?
Jul 9, 2015
I have a table with couple of hundred thousand records, i have added a new column to the table named MD_Group.
Now i need this column populated, so that every 10,000 row set in that table gets a numeric (incremental number) assigned starting from 1.
So the first 10,000 rows will have MD_Group = 1, next 10,000 rows will have MD_Group = 2 , next 10K MD_Group = 3 etc.
For testing purpose here is a results table with total of 11 rows where we want data grouped every 3 rows.
MD_Group
CustomerNumber
AmountBilled
1
12
15243
1
1231234
15243
[code]....
-- Create Sample Table
Declare @GroupRelation_Test TABLE (
MD_Group [varchar](20),
CustomerNumber [varchar](20) ,
AmountBilled [varchar](20) ,
[code]....
-- Test data
INSERT INTO @GroupRelation_Test
( CustomerNumber, AmountBilled,MinAmounttBilled)
SELECT'12','15243','' UNION ALL
SELECT'1231234','15243','' UNION ALL
SELECT'463','15243','' UNION ALL
SELECT'442','15243','' UNION ALL
[code]....
View 22 Replies
ADVERTISEMENT
Dec 5, 2015
I have a table like this.
AS-IS
Column1 Column2 count
a b 20
a b 25
c d 12
c d 22
And I need to update the same as below.
TO-BE
Column1 Column2 count
a b 45
c d 34
How to do it?
View 4 Replies
View Related
Jun 6, 2007
All,
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?
View 5 Replies
View Related
Jul 12, 2015
I was working on what I was told was SQL 2012 and it turns out it is SQL 2005. I wrote two procs that I need to convert to 2005. Here is the code:
SELECT
era_provider_name AS Provider,
RIGHT([era_upi], 5) AS 'ERA Upi',
[era_fy] AS 'ERA FY',
ProcGrp,
COUNT(DISTINCT UCI) AS 'Client Count',
[Code]....
I'm not finding an efficient way to do this. I cannot use GROUPING SETS with 2005. Here is the code for the second proc:
SELECT
CASE
WHEN GROUPING(era_provider_name) = 1 THEN 'TOTALS'
ELSE era_provider_name
END AS era_provider_name,
CASE WHEN GROUPING(era_fy) = 1 THEN 'TOTALFY'
[Code] ...
The results as in SQL 2012 are exactly as I would like them. I want to mimic those results in 2005.
View 6 Replies
View Related
Oct 3, 2015
I have a table that have customer name and their bill amount
a) tblBilling
which shows records like below (Billing Dates are shown in Year-Month-Day format)
Invoice Customer BillingDate Amount Tax
--------------------------------------------------------------------------
1 ABC 20015-10-2 1000.00 500.00
2 DEF 20015-10-2 2000.00 1000.00
3 GHI 20015-10-2 1000.00 500.00
4 JKL 20015-10-3 5000.00 2500.00
5 MNO 20015-10-3 1500.00 750.00
6 PQR 20015-10-4 500.00 250.00
7 STU 20015-10-4 1000.00 500.00
8 VWX 20015-10-4 2500.00 1250.00
I want to perform a query that should SUM Amount and Tax Colums by date basis, so we could get the following result
BillingDate Amount Tax
--------------------------------------------------------------------------
20015-10-2 4000.00 2000.00
20015-10-3 6500.00 3250.00
20015-10-4 4000.00 2000.00
View 3 Replies
View Related
Jul 8, 2015
I needed to add in the Fiscal Year (FY) to group my results by FY. I changed my code to the following:
/*
EXEC ADAMHsp_BSS_GetNonMedicaidReportTotals
@pstrProviderName = 'FAM SER-WOOD'
*/
ALTER PROCEDURE [dbo].[ADAMHsp_BSS_GetNonMedicaidReportTotals]
@pstrProviderNameVARCHAR(100)
AS
BEGIN
[Code] ....
See my result set in the picture below. The rows with NULL in the 'ProcGrp' column have the totals of the groupings by FY that I am looking for - that's great. What I want to do now is have another row that contains the sums of the values from any row where 'ProcGrp' is null so that I have a totals row.
View 3 Replies
View Related
Nov 4, 2015
I have a table as below
ItemID ParentID
6 NULL
7 NULL
8 7
11 7
12 8
Here ParentID null means it does not have the parent its the master. I want result in below format.
ItemID ParentID
6 NULL
--No parent no child
7 NULL
--No parent but has child
8 7
--7 is the parent of 8
12 8
--8 is the parent
11 7
--7 is the parent
Basically it should be in parent id then child id after that child's childID , In a recursive way.
View 5 Replies
View Related
Jun 23, 2015
I have a table with duration values for different machine states. I 'm trying have a sum of the duration value of each state ( the duration sum , was an earlier question).
declare
@tblDurations
TABLE
(StateName
nvarchar(30),Duration
nvarchar(30))
[code]...
I want the output to have sum of the duration ( from my function ), grouped by the state.So output should be :
Breaks 00:02:03:40
Meetings 00:00:01:50
Running 15:21:07:16
I think this can be done with windows functions, but how I don't know.
View 10 Replies
View Related
Nov 6, 2015
I have two tbles that have ItemName and their bill amount
a) tblLunch
which shows records like below
Invoice Item Amount
--------------------------------------------
1 COFFEE 1000.00
2 TEA 2000.00
3 ICE CREAM 1000.00
b) tblDinner
which shows records like below
Invoice Item Amount
------------------------------------------------------------
1 COFFEE 1000.00
2 TEA 2000.00
3 PASTA 1000.00
I want to perform a query that should SUM Amount Columns by Grouping the Item from both the tables, so we could get the following result
Item Amount
------------------------------
COFFEE 2000.00
TEA 4000.00
ICE CREAM 1000.00
PASTA 1000.00
View 3 Replies
View Related
Aug 9, 2015
In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.
So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.
I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?
select
case when @include_browser =
1 then browser_name end as browser_name,
case when @include_browser_version
= 1 then browser_version end
as browser_version,
[Code] ....
View 4 Replies
View Related
Jul 29, 2015
I am trying to generate XML path from a SQL Server Table. Table Structure and sample data as below
CREATE TABLE #OfferTransaction
( [OfferLoanAmount1] INT
,[offferid1ProgramName] VARCHAR(100)
,[Offer1LenderName] VARCHAR(100)
,[offerid1LenderNMLSID] INT
[code]....
what changes do I need in my query so that the XML looks like the one above ( DESIRED XML). Is it possible via query changes?
View 3 Replies
View Related
Nov 5, 2015
Below is the data I have in table name
TeamStatus
T 1 Complete or Escalate
T 2 Pick Up
T 2 Resolve Case
T 1 Pick Up
T 1 Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate
I want to get he group based of Resolve Case value in Status Column. Anything before Resolve case will be considered as Group 1 and after Resolve Case status should be considered as Group 2. Below is desired new Group column,
Group TeamStatus
Group 1 T 1Complete or Escalate
T 2 Pick Up
T 2 Resolve Case
Group 2 T 1Pick Up
T 1Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate
View 7 Replies
View Related
Oct 25, 2015
I have a temp table like this
CREATE TABLE #Temp
(
ID int,
Source varchar(50),
Date datetime,
CID varchar(50),
Segments int,
Air_Date datetime,
[code]....
Getting Error
Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.
View 4 Replies
View Related
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
Aug 14, 2015
Below is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
View 5 Replies
View Related
Oct 13, 2015
I am stuck in the following scenario.
Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01
[code]....
View 5 Replies
View Related
Aug 4, 2015
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL
SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD
INNER JOIN TESTCEP FD
ON AD.SCHOOL_NAME = FD.School_Name
View 10 Replies
View Related
Oct 4, 2015
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
View 4 Replies
View Related
Jun 15, 2015
How do you incorporate a case statement so that you can add "sub total" and grand total" to each grouping set section? Trying to see how to incorporate case.
[URL] ....
SELECT
CustomerID,
SalesPersonID,
YEAR(OrderDate) AS 'OrderYear',
SUM(TotalDue) AS 'TotalDue'
FROM Sales.SalesOrderHeader
[Code] .....
View 4 Replies
View Related
Nov 18, 2015
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
[code]...
View 7 Replies
View Related
Oct 5, 2015
I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.
Actual Data
Expected Result
1000
1-Aug
1000
1-Feb
1000
1-Jul
1000
[code]....
View 3 Replies
View Related
Oct 22, 2015
I have a simple table data i want want to show row data in to column data.
SELECT clblcode,mlblmsg
FROM warninglabels
My expected result will be
0001 0002 0003 0004
------- ------- -------- --------
May Cause.... Important..... Take madi........... Do Not Take.......
View 16 Replies
View Related
Aug 22, 2015
we have a table below
TABLE1
ID Roll Name Amount . . . . . . so on
---------------------------------------
1 2 Alex 500
2 5 Jones 600
3 2 Ales 400
and we have TABLE 2
ID Roll Name Amount . . . . . . . so on
In both the Tables ID Field is Identity Field and rest all the columns are identical in both the tables.I want to perform a query in such a way that SQL Should,
1) Remove all data from TABLE2 First
2) Select those records from TABLE1 where Roll = 2
3) Then, Insert the results to TABLE 2
So we should get the result similar to below:
ID Roll Name Amount . . . . . . . so on
---------------------------------------
1 2 Alex 500
3 2 Ales 400
how can I do this in a same sql statement.
View 3 Replies
View Related
Nov 4, 2015
We have staging table in which data is dumped from files . The staging table is truncated for every load . In order to retain data from staging table we are creating staging_purge table which hold the staging data. what is the fastest way to copy data from staging to purge table without impacting the load process.
View 13 Replies
View Related
Aug 19, 2015
I have 2 tables: Source Table - IncidentDimNew and Destination Table - IncidentDimNew with identical columns: Id and CreatedDate but the data is different.
I would like to insert distinct data from those 2 columns of 1st table into the same 2 columns into the 2nd table but I onlt want to replace the values in the 2nd table where the Created Date is > 2015-04
I compiled the code in order to get the information I want from 2 tables:
Source Table
SELECT
COUNT(Id),
LEFT(CONVERT(VARCHAR(10)), CreatedDate, 126), 7)
FROM
IncidentDimNew
[Code] ...
This is the code I wrote in order to do it
INSERT INTO IncidentDim
[Id]
,[CreatedDate]
SELECT
[Id]
,[CreatedDate]
FROM
IncidentDimNew
where left(CONVERT(VARCHAR(10), CreatedDate, 126),7) > '2015-04'
GO
But what it does it's gives the SUM of the values in the corresponding rows where the values are not null which I don't want.
So how do I leave the values as is from table IncidentDim and insert new values as it is from IncidentDimNew where the Created Date is > 2015-04?
View 2 Replies
View Related
Aug 10, 2015
I am still learning T-SQL .Lets consider the table below, ID 1-3 shows our purchase transactions from various Vendors and ID 4-6 shows our payments to them
Table 1 - VendorTransactions
ID PARTY AMOUNT VOUCHER
---------------------------------------
1 A 5000 Purchase
2 B 3000 Purchase
3 C 2000 Purchase
4 A 3000 Payment
5 B 1000 Payment
6 C 2000 Payment
7 A 1000 Payment
Now we have a blank table Table 2 - Liabilities
ID PARTY AMOUNT
I want that SQL should look for each individual party from Table 1 and Calculate TOTAL PURCHASE and TOTAL PAYMENTS and then deduct TOTAL PAYMENTS from TOTAL PURCHASE so we get the remaining balance due for each party and then add the DIFFERENCE AMOUNT alongwith PARTY to the TABLE 2 so I can get the desired result like below
ID PARTY AMOUNT
-------------------------
1 A 1000
2 B 2000
3 C 0
View 3 Replies
View Related
Oct 26, 2015
I have a table which has data like shown in below image , I want to get count of case numbers which has responses 123 AND 125 ( case should have both responses), like this I have requirement for other responses, how can I do it in Stored procedure .
View 8 Replies
View Related
Sep 25, 2015
I am having two table i.e( tbl_oldEmployee , tbl_NewEmployee ),which is having Column name Employee Name and City same in both table but inside column data is different in different table.but i want to view the Employee name and City from tbl_NewEmployee to tbl_oldEmployee which is having EmployeeId common with tbl_oldEmployee extra record also required (i.e tbl_NewEmployee having 6 record and tbl_oldEmployee having 10 record,so i need to display data from tbl_oldEmployee but first 6 record which id match with tbl_NewEmployee id should be replaced and extra data from tbl_oldEmployee also display).
View 3 Replies
View Related
Jun 17, 2015
I have a table, dbo.Table1(Id,Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8) that I need to split between two tables dbo.Table2(Id, Col1, Col3, Col4) and dbo.Table3(Id,dbo.Table2_Id,Col5,Col6,Col7,Col8). But in dbo.Table3 I need to have the Id column from dbo.Table2 populated since its a foreign key constraint in dbo.Table3. How do I go about doing this?
View 3 Replies
View Related
Jun 4, 2015
I have 900000 rows of data in a table and a sample of it is as shown below.Now I need to convert data into the format shown.
View 6 Replies
View Related
May 10, 2015
I would like to know what is the preferred format for form submissions to a SQL table.
View 5 Replies
View Related
Jul 1, 2015
I am wanting to fire-off an email with the failed jobs anytime they are deposited into a table. My syntax fires off an email even when the table does not contain data, it just sends a blank email. this will only generate an email if teh table contains data?
if exists (Select from FailedJobs)
exec msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMail'
@recipients = 'asdfasdfsdf@aafas.com'
@from_address = 'asdfasdfacasca@cc.com'
@query = 'Select * from failedjobs'
@subject = 'List Of Failed Jobs'
@attach_query_result_as_file = 1;
View 3 Replies
View Related
May 29, 2015
I have table with about 10000 rows, there is a column named Raw_XMLData is defined as varchar but data is xml format.
I try to insert into another table, if Raw_XMLData column has is valid xml data?
Is it possible to do in T sql?
View 2 Replies
View Related
