Transact SQL :: Grouping Similar Rows After Unpivot?

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


ADVERTISEMENT

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 View Related

WANT TO SKIP ROWS AAND ALSO UNPIVOT

Jun 12, 2007

hI,

i have this particular problem with the unpivot.The below is my flat file source.The dates can go upto 130 columns.this count can also vary.SM,SR,SB are again values repeating for diff instrument.They are the values of the instrument on the particular dates.This is a snap shot of one feed.Other feeds may have the dates differing.How do i read this file.

Problem 1:If i skip the first row and unpivot the 2nd row,then with the new feed,with new dates my SSIS package will bomb as it will not find the col names.

Problem 2:IF i uncheck the "Use first row as column headers" then the problem 1 is solved but the o/p will be

20080101

20061102

20061103
1.2

1.3

1.2.

1.5

.....and so on..

IS there any other way to fix this.These are feeds with the spread values of instruments on particular dates.Please help.



RUN 2.01E+11 132238 0 45
INSTRID DATATYPES 20081101 20061102 20061103
Z03369 SM 1.1 1.2 1.3
Z03369 SB 1.3 1.3 1.7

Z03369 SR 2 3 4

Z81910 SM 1.1 1.2 1.3
Z81910 SB 1.3 1.3 1.7

View 7 Replies View Related

Transact SQL :: Unpivot With Dynamic Columns Or Redesign Relational Table?

Sep 3, 2015

I want to use column name to be join another tables.

I have invoice table to store detail of invoice and post some column ' s record to another table .

Invoice table
Invoice_Name  |   Invoice_Amount |  Invoice_Vat | Invoice_Total
Inv001            |          1000           |       70          |     1070             

Account_table
Account_No |  Account_Number | Data_Source
JV001          |     1111               |    Invoice_Amount  ---->1000
JV001          |     1112               |    Invoice_Vat         ---->    70
JV001          |     1113               |    Invoice_Total       ----> 1070 

I want to join  Invoice table to Account_table

ON  Invoice_Amount , Invoice_Vat , Invoice_Total with Data_Source

The way i got so far I unpivot  Invoice table  column into row and join with Account_table .

The problem is ,  if the column in Invoice_table are created , I must used dynamic columns to do this in sql query.

View 7 Replies View Related

Transact SQL :: COLUMN NAME Conflicts With Type Of Other Columns Specified In UNPIVOT List

Jul 31, 2015

I am trying to convert all columns to rows in sql, but giving an error.

SELECT Employee, Orders
FROM
(SELECT CisId, [Z_Id], [ModuleType]
FROM CIS) p
UNPIVOT
(Orders FOR Employee IN
(CisId, [Z_Id], [ModuleType])
)AS unpvt;

Error: The type of column "Z_Id" conflicts with the type of other columns specified in the UNPIVOT list.If I remove "Z_Id" from selection then giving same error for ModuleType also.

View 6 Replies View Related

Transact SQL :: Joining 2 Similar Tables

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

Can You Unpivot On Multiple Keys With The Unpivot Transform

Oct 17, 2006

Hi all,

I have more than 1 set of columns I'm trying to normalize, can an unpivot transform handle 2 unpivots around DIFFERENT keys in the same transform?

If not, would the suggestion be unpivot into temp tables for whatever needs normalizing, than run an update query to generate the row that is desired?

Thanks in advance.


Mark

View 2 Replies View Related

Transact SQL :: Combining Multiple Similar Queries

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

Transact SQL :: Script To Merge Similar Content?

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

Transact SQL :: How To Use GROUPING SETS

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

Grouping Rows By Just The Date

Mar 2, 2001

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???

View 1 Replies View Related

Restricting The Rows And Grouping

Dec 14, 2004

Hi
I have a table strcutre as follows.This is like a many to many relationship between category and Article. Now i need to pick 3 recrord from each category which has a relavancy 1 sorted by Article date Desc. ie.. from the recent articles..
I can fire 4 diff queries and restrict it using top keyword..
Can i do this in a Single query or in a better way..can anyone help me plz......

Rgds
jinu

Category
---------
catID - PK
CatName

Article
-------
ArticleID - PK
Name
Date

Category_Article
----------------
ArticleID - Composite Key(ArticleID,CatId)
CatID
Relavancy

View 8 Replies View Related

Grouping Rows By CSV Column

Aug 24, 2013

I have a table with following sample data:

**CategoriesIDs RegistrantID**
47 1
276|275|278|274|277 4
276|275|278|274|277 16261
NULL 16262
NULL 16264
NULL 16265

[code]....

I need to know :

1). which category has how many regisrtantids (like 277 has how many registrantids) 2). group the registrants by category so that i can find which registrants are in category 277 for example)

Do I need to create a function which generates a table from csv ? I have created a function but not sure if it will work in this situation with IN clause.

View 2 Replies View Related

Grouping Rows By Customer

Nov 8, 2006

my source flat file has many rows per customer,
but I need to transfer it to database with only one row per customer and accumulated sales (and probably do other calculations and lookups).
I understand how to do stuff with derived columns, but how can I read source file first, calculate, group and then save to database?
As I understand, the script offers only processing row by row: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)



Thanks

Vlad

View 4 Replies View Related

Transact SQL :: Sum Amount By Grouping Date

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

Transact SQL :: How To Get Sum Of Results From GROUPING SETS

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

Transact SQL :: Parent Child Grouping

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

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 View Related

Transact SQL :: Summing With A Function In Grouping

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

Transact SQL :: Sum Values By Grouping Name From Two Tables

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

Transact SQL :: Grouping With Case Statement

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

Transact SQL :: Grouping And Updating The Table

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

Transact SQL :: Query To Generate XML And Grouping?

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

Transact SQL :: Grouping Based Of Column Value

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

Need Help On GROUPING Sets Of Rows And Comparison

Jan 24, 2007

Hi folks. Hope all the gurus including Brett,Pat Phalen, RdjBarov, r937 are fine. ;)
been so long to ask stupid question. Here's the question and i really need help on this.

i have data that tracks patterns of bus stops from one point to another.
like point a, to point b, point b to point c forms one pattern.
point a to point c , point c to point b should be a different pattern.


create table #journeypatterns (patternid int ,points varchar(100))
go
insert #journeypatterns
select 1 ,'a' union all select 1 ,'b' union all select 1,'c'
union all select 2,'a' union all select 2,'c' union all select 2,'b'
union all select 3 ,'a' union all select 3 ,'b' union all select 3,'c'


select * from #journeypatterns


patternid points
1 a
1 b
1 c
2 a
2 c
2 b
3 a
3 b
3 c


what i want is to get unique pattern value of sequence of points by grouping on patternid. if the sequence of points change, i need a unique value against that pattern.
like for patternid 1, sequence of points a,bc for example should be abc.
for patternid 2, sequence of points a,c,b for example should be acb.
again patternid 3, sequence of points a,bc for example should be abc.

i tried CHECKSUM_AGG which brutally failed in production because the checksum values for each single point when summed produce SAME result for different patterns.

select checksum_agg(binary_checksum(points)) ,patternid
from #journeypatterns
group by patternid

961
962
963

here patternid 2 should be different because sequence is acb. i know checksum is not the right approach for what i need.

I NEED A GENERIC FUNCTION, that marks the pattern differences, my ultimate goal was to create a procedure, whom a patternid should be passed, and it would result the NEXT patternid in the table which has the SAME ORDER OF point sequences.

now folks, i can do this holding all data into a temp table and write a cursor to traverse through each patternid and concatenate the sequence of points.
BUT, using this approach is the ugliest, as it has slow down the process badly and boss is not happy with the performance. the table holds a lot of data.
I NEED a query rather than a cursor on the fly to resolve this.
Here's the query i am using to get the current sequence of a pattern and then i have to search all sequences similarly against it.

declare @patternid int
set @patternid =1
declare @sequence [varchar] (100)
declare @id varchar(10)
declare cr_sequence cursor fast_forward for select points from #journeypatterns where patternid=@patternid
open cr_sequence
fetch next from cr_sequence into @id
while @@fetch_status = 0
begin
select @sequence = isnull(@sequence,'')+@id
fetch next from cr_sequence into @id
end
print @sequence -- next i have code to find the similar sequence for another patternid.... which is not mentioned here but is similar

View 5 Replies View Related

Removing Duplicate Rows In Grouping

Feb 14, 2012

i've to generate a notepad using this query in vb.net :

strSql = "Select count(*), d.ShareholderId, d.UsufructId, d.BnkAccount, b.SBMCode, " & _
"LTRIM(ISNULL(d.TitleCode + ' ', '')) + LTRIM(ISNULL(d.Forename + ' ', '')) + d.Surname as ShName," & _
"d.BankCode, (select count(*) from (select ShareholderId from dividend " & _
"where CompCode = 'L1' and PaymentMode = 'B' group by ShareholderId, UsufructId, " & _

[Code]....

In the select statement i need to select "d.amount" as well. When i do so, it ask me to insert it in the group by option or in an aggregate function.

Grouping by "d.amount" returns extra field as there can be 2 similar "d.shareholderId" but with different amount.

how to group the "d.amount" without having repetition in the "d.shareholderid" ??

View 5 Replies View Related

Grouping Consecutive Rows In A Table

Feb 21, 2013

SQL Server 2008.

From individual event logs I have generated a table where arrivals and departures at a location are registered per device. As there are multiple registration points, there might be multiple consecutive registrations per location.
If this is the case I need to filter those out and have one registration per location and in the result I need to get the earliest arrival and the latest departure of these consecutive rows.

Part of the table:

logIDdeviceIDArrivedDepartedLocationIDGrp1Grp2Grp
3485441082013-02-07 17:51:05.0002013-02-07 17:51:15.0005110
3492041082013-02-07 17:51:15.0002013-02-07 17:51:26.0005220
3500241082013-02-07 17:51:27.0002013-02-07 17:51:37.0003312
3508941082013-02-07 17:51:41.0002013-02-07 17:51:54.0004413

[Code] ....

So as long the field LocationID is the same in the next row, it needs to be grouped.

I have added the rows Grp1, Grp2, Grp in an attempt to get an unique grouping number with the following script in the select statement:

,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID) AS Grp1
,ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp2
,ROW_NUMBER() OVER(PARTITION BY DeviceID
ORDER BY logID)
-
ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID
ORDER BY logID) AS Grp

By subtracting Grp2 from Grp1 (Grp = Grp1 - Grp2) I hoped to get an unique group number for each set of equal consecutive locations, however the Grp2 column does not restart from 1 each time the LocationID changes: Grp2 in line 7 should have been 1 again, but it is 2 because this is the second row with LocationID = 3 in the list.

View 12 Replies View Related

How To Basically Copy Tables With New Names Rather Than Create Similar Tables From Similar Manual Input.

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

Transact SQL :: How To Add Subtotal / Grandtotal Using Case And Grouping Sets

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

Transact SQL :: Grouping Records With A Date Range Into Islands

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

Transact SQL :: Table Trigger To Delete All Rows Before Inserting Rows

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

Transact SQL :: Selecting Rows As Column Name And Other Column Values As Rows

Jun 25, 2015

I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.

View 28 Replies View Related

Query Or Grouping Problem (some Kind Of Parallel Grouping?)

Nov 26, 2007

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:

WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc


Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

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_]

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved