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)
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 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
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.
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.
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
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.
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..
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.
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???
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......
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.
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)
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.
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
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).
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,
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
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
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.
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.
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.
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
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
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
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.
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:
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_]