WITH summary AS (SELECT tu.SequenceNumber, tu.trialid, tu.SBOINumber, tu.DisplayFlag,
[Code] ....
I am having trouble with the RowNumber Over Partition By portion of the query. I would like the query to return only the first occurrence of each sboinumber in the table for each trial id. It is only giving me the first occurrence of each sboinumber. I tried including the trialid in the partition by clause, but that is not working.
I have a field in a table that contains addresses e.g
15 Green Street 5F Brown Steet 127 Blue Street 1512 Red Road
I want to output the numbers into one column and the address to another column as i need to produce a report that only shows streets and roads but no numbers.
So basically no matter how many characters before the first space which can be numbers or letters i want these output into two columns.
I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters. Currently am using the below function and getting the parameters by each in select statement as mentioned below.
select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1, [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2 CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex] ( @CustomProp varchar(max),
I have a view in my database detailing the expiry date of each credential for each employee. The view is designed as to display one record per employee and in that record is the expiry date of each credential and the days remaining. So the columns are as follows:-
Employee CodeExpiry Date (x8 columns) (named as credential e.g. [Passport]) Days Remaining (x8 columns) (named as "TS_" + Credential)
I'm trying to use the CASE function to compare each DATETIME column with one another and retrieve the minimum. How can I return the minimum date as a run-time column and sort the view by this column? My code is as follows:-
SELECT [Passport],[TS_Passport],[Visa],[TS_Visa],[Civil_ID],[TS_Civil_ID],[KOC_Pass],[TS_KOC_Pass],[JO_Pass],[TS_JO_Pass], [Ratqa_Pass],[TS_Ratqa_Pass],[Driving_License],[TS_Driving_License],[Health_Book],[TS_Health_Book], CASE WHEN Passport <= Visa AND Passport <= Civil_ID AND Passport <= KOC_Pass AND Passport <= JO_Pass AND
[code]....
I've been told that this is the most efficient given the number of records in my database. The Min_Date is always NULL. I need the minimum of the 8 dates to be the Min_Date.
I have a TableA where data get inserted from Excel(IMPORT/EXPORT wizard)
TableA; ID(identity) Date (NOT NULL Defaulyt Getdate() ) Name Phone 1 2014-06-17 17:28:21.190 Nick 12345678910 2 2014-05-17 17:28:21.190 Stan 00045678910 3 2015-08-17 17:28:21.190 Kim 11111678910 4 2015-08-17 17:28:21.190 Tom NUll
3,4 are the rows i have inserted now , you can see by date, likewise i have 100,000 rows(old and new combination) and now the data from excel to TableA can be imported/exported daily , hourly, weekly basis.
Now i want to find out only the rows which are imported to tableA today, or hours back, or yesterday .....
the reason is ,
Step1:get data from excel and import to tableA( this is a manual Step) and i know when the data is inserted with exact date and time.
Step2: get newly inserted rows from TableA and pass them as Parameters in Stored Procedure.( i may run step 2 after 1 hour, or after 1 day or after 1 week ,but i want only rows that are inserted )
I tried with using where Datecreated, but did work.
I am using SQL 2005 SP2. I have a table partitioned on date range. I am trying to SWITCH, MERGE and SPLIT partitions. My SWITCH and MERGE work great. When the SPLIT query is executed, an error 9002 is thrown....
"The transaction log for database is full. To find out why space in log cannot be resued, see log_reuse_wait_desc column in sys.databases."
Below are more details...
- All SWITCH, MERGE and SPLIT are executed in one TRANSACTION. - After SWITCH and MERGE, I execute a query set the partition scheme "NEXT USED [PRIMARY]". - Finally i execute SPLIT statement.
i have a table with dob and test results , i am trying to pull the data from the table and converting rows columns , below is the table i am using . i used to pivot to do this .
create table #TEST_RESULTS (ID INT,NAME VARCHAR(10),DOB DATETIME,DAYS_SINCE_BIRTH_TO_TEST INT,TEST_RESULTS INT ) INSERT INTO #TEST_RESULTS VALUES(1,'A','2015-01-01' , 0 ,1) ,(1,'A','2015-01-01' , 0 ,1) ,(1,'A','2015-01-01' , 1 ,3) ,(1,'A','2015-01-01' , 2 ,6)
I am trying to implement a sliding window on a table in SQL Server 2005 but i am having some problem. I have two tables, "Letture" and "LettureStorico". The first one receives data on a few seconds basis, some thousands of rows each day. The second is the historical record and should store all the records till midnigh of two days before, that is, if today is November 21st, LettureStorico stores rows till November 19th 23.59:59.997.
At some time during morning of each day i want to run a stored procedures that takes the records older than midnight of two days before in "Letture" and switch them as a partition in "LettureStorico"
Here's what i do:
/*----------------------------------------------------------*/ CREATE PARTITION FUNCTION [partizioneLive](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')
CREATE PARTITION FUNCTION [partizioneStorico](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')
CREATE PARTITION SCHEME [schemapartizioneLive] AS PARTITION [partizioneLive] ALL TO ([PRIMARY]) ALTER PARTITION SCHEME [schemapartizioneLive] NEXT USED [PRIMARY];/*(1)*/
CREATE PARTITION SCHEME [schemapartizioneStorico] AS PARTITION [partizioneStorico] ALL TO ([PRIMARY]) ALTER PARTITION SCHEME [schemapartizioneStorico] NEXT USED [PRIMARY]; /*(1)*/
CREATE TABLE [dbo].[Letture]( [IdLettura] [bigint] IDENTITY(1,1) NOT NULL, [IdTag] [int] NOT NULL, [IdGatewayBox] [int] NOT NULL, [IsEntrata] [bit] NOT NULL, [Data] [datetime] NOT NULL, [IsRettifica] [bit] NOT NULL, CONSTRAINT [PK_Letture] PRIMARY KEY CLUSTERED ( [Data], [IdLettura] ASC )WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneLive(data) ) ON schemaPartizioneLive(data)
ALTER TABLE [dbo].[Letture] WITH CHECK ADD CONSTRAINT [CK_Letture] CHECK (([Data]>='20061115 00:00'))
CREATE TABLE [dbo].[LettureStorico]( [IdLettura] [bigint] IDENTITY(1,1) NOT NULL, [IdTag] [int] NOT NULL, [IdGatewayBox] [int] NOT NULL, [IsEntrata] [bit] NOT NULL, [Data] [datetime] NOT NULL, [IsRettifica] [bit] NOT NULL, CONSTRAINT [PK_LettureStorico] PRIMARY KEY CLUSTERED ( [Data], [IdLettura] ASC )WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneStorico(data) ) ON schemaPartizioneStorico(data)
ALTER TABLE [dbo].[LettureStorico] WITH CHECK ADD CONSTRAINT [CK_LettureStorico] CHECK (([Data]<'20061115 00:00'))
Every morning i run a stored procedure that, after dropping the check constraints (i'll recreate the at the end), does the following:
/*-----------------------------------------------------------*/ SET @NewBoundary = dateadd(dd,-1, @dateOfToday)
--this new partition contains the rows i want to switch ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@NewBoundary) --this new partition is empty ALTER PARTITION FUNCTION PartizioneStorico() SPLIT RANGE (@NewBoundary)
--this works fine, rows are moved ALTER TABLE Letture SWITCH PARTITION 2 TO LettureStorico PARTITION 2
--these two merges lead to two tables partitioned in two partitions each ALTER PARTITION FUNCTION PartizioneLive() MERGE RANGE (@OldBoundaryLive) ALTER PARTITION FUNCTION PartizioneStorico() MERGE RANGE (@OldBoundaryStorico)
Till now, everything is working as expected. Now, when i try to run the same Stored Procedure " a day later" (NewBoundary moved on 1 day) i receive, when i do the "ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@NewBoundary)" i receive a 7707 error message: "Msg 7707, Level 16, State 1, Line 1 The associated partition function 'PartizioneLive' generates more partitions than there are file groups mentioned in the scheme 'schemapartizioneLive'."
How is this possible if i used the "ALL TO [PRIMARY]" and specified which file to use next as in (1) ? Why all this succeeds the first time (when i have 3 partitions) but not the second (again i have just three partitions, i checked) ?
I have persons who speaks multiple languages and they are in one table, each row is added if he/she speaks multiple languages. Instead I want to add additional columns and load the data.(what I have shown in the desired output)
name language ------------- ron english ron french ron spanish andy english andy hindi kate english
Desired output
name language1 language2 language3 language4 language5 language6 ----------------------------------------------------------------- ron english french spanish andy english hindi Kate english
What I want the eventually solution to be is that for each unique/distinct client, reference and sequence there is one record (row). Based on the row_number, I want the first 'AppearanceDate' renamed to 'AppearanceDate1' with the first AppearanceDate as the data and the first Outcome to be renamed 'Outcome1' with the first Outcome as the data. Any subsequent rows would follow as columns:
Only four AppearanceDates and outcomes are required based on the unique/distinct Client, Reference and SequenceIDs. How I can loop through the Appearances based on the row_number and show each record in one row?
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.
In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
Below. I have also pasted the current result of this query and the desired result.
Query can be updated to get the desired result as given below?
Query: Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT 5501 07/13/2015 CO 11 23.87 5501 07/13/2015 PR 12 3.76 5501 07/13/2015 OT 32 33.45 2032 07/14/2015 CO 12 23.87 2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
we can easily load a file into db tables. However, my main concern here is the number of columns in the file. A text file TEXT_1400.txt has 1400 columns. I am unable to load data to my db table using BCP or BULK INSERT commands, as maximum of 1024 columns are allowed per table in SQL Server 2008.
We can still go ahead and create ‘Wide Table’ (a special table that holds up to 30,000 columns. The maximum size of a wide table row is 8,019 bytes.). But when operating on wide table, BCP/BULK INSERT commands still fail. After few hours of scratching my head over BCP and BULK INSERT, I observed that while inserting BCP/BULK INSERT commands are unable to identify SPARSE columns and skip these columns, which disturbs column mapping and results in data conversion and trancation errors.
Is there any proper way to load this kind of files into the db table?
Is this possible? I have tried using row over partition but I'm not sure how group it correctly, so basically every time there is a new 1 in new_commsstream within a personid the row number goes up by one.