Transact SQL :: Finding Gaps And Filled With Last Validate Data?
Aug 26, 2015
currently I am facing a complex escenario related with gaps and sequences, but I was trying with diferent cases but I did not get the correct results, I am sure about the use of windows functions. I have a table with the information grouped by PublicationId, Provider, MetricId and Amount by Date, one row by each month, but in some cases these data don't have a sequencial values, for example I have the data for the next sequence:
I need to get the sequence by each month, in this case I need to project the month from February to May (with the last previous value, for this case of January) , this is:
The data for testing are:
DECLARE @PublicationsByUser AS TABLE
(
Id INT,
PublicationId INT,
MetricId INT,
ProviderId INT,
DateCreated DATE,
Amount FLOAT
CREATE TABLE #TEMP ( TYPE VARCHAR(10), SEQ INT, SUB_TYPE VARCHAR(10))
[Code] ....
Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.
However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.
I have done this:
SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1 CROSS APPLY (SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE GROUP BY t2.TYPE) t2 INNER JOIN #TEMP t3 ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ
And it seems to work. Is this the easiest way to do it or am i missing something?
I'm trying to find gaps in times in a table of sessions where the session endings aren't sequential. That is, session 1 can start at 10:00 and finish at 10:30, while session 2 started at 10:05 and finished at 10:45, and session 3 started at 10:06 and finished at 10:20. Only the starting times are in order; the ending times can be anywhere after that.Here's a bunch of sample data:
INSERT INTO #SessionTest SELECT '1073675','Mar 3 2014 1:53PM','Mar 3 2014 1:53PM' UNION ALL SELECT '1073676','Mar 3 2014 2:26PM','Mar 3 2014 3:51PM' UNION ALL SELECT '1073677','Mar 3 2014 2:29PM','Mar 3 2014 3:54PM' UNION ALL SELECT '1073678','Mar 3 2014 2:29PM','Mar 3 2014 5:47PM' UNION ALL SELECT '1073679','Mar 3 2014 2:30PM','Mar 3 2014 3:37PM' UNION ALL
Planning - contains a list of planned items. Used to define boundaries for a work day and defines based on type what can be done for each item.
Id, TypeId - the type of the planned items BeginTime DateTime - begin date and time of the planned item EndTime DateTime - end date and time for the planned item
In the Planning table we can have as many records per day as we need:
1, First Meeting, 1 Jan 2008 09:00, 1 Jan 2008 11:00 2, First Meeting, 1 Jan 2008 11:00, 1 Jan 2008 12:00 3, First Meeting, 1 Jan 2008 13:00, 1 Jan 2008 15:00 4, First Meeting, 1 Jan 2008 15:00, 1 Jan 2008 18:00
Appointments - contanis a list with appointments
Id, BeginTime DateTime EndTime DateTime
1, 1 Jan 2008 09:00, 1 Jan 2008 09:30 2, 1 Jan 2008 10:00, 1 Jan 2008 11:00 3, 1 Jan 2008 11:00, 1 Jan 2008 11:30 4, 1 Jan 2008 14:00, 1 Jan 2008 15:30
What is needed?
What I need is to a find a way to compare the planned items with the appointments and to return all the periods for which a planned time exists:
Free planned time:
1, 1 Jan 2008 09:30, 1 Jan 2008 10:00 2, 1 Jan 2008 11:30, 1 Jan 2008 12:00 3, 1 Jan 2008 13:00, 1 Jan 2008 14:00 4, 1 Jan 2008 15:30, 1 Jan 2008 18:00
So, having two multitudes of periods,where the one specifies the planning templates and the other real used time, I need to find all the periods which can be used for another appointments.
I've tried several aproaches, but I always faced performance problems.
I have found a lot of examples of problems where I have just a single date column, and then I find the gaps in between that, but I'm having difficulty finding examples where it works with start and end date columns...
Hi all, I have two tables - Planning and Appointments:
Planning - contains a list of planned items. Used to define boundaries for a work day and defines based on type what can be done for each item. Id, TypeId - the type of the planned items
BeginTime DateTime - begin date and time of the planned item EndTime DateTime - end date and time for the planned item
In the Planning table we can have as many records per day as we need:
1, First Meeting, 1 Jan 2008 09:00, 1 Jan 2008 11:00 2, First Meeting, 1 Jan 2008 11:00, 1 Jan 2008 12:00 3, First Meeting, 1 Jan 2008 13:00, 1 Jan 2008 15:00 4, First Meeting, 1 Jan 2008 15:00, 1 Jan 2008 18:00
Appointments - contanis a list with appointments Id,
BeginTime DateTime EndTime DateTime
1, 1 Jan 2008 09:00, 1 Jan 2008 09:30 2, 1 Jan 2008 10:00, 1 Jan 2008 11:00 3, 1 Jan 2008 11:00, 1 Jan 2008 11:30 4, 1 Jan 2008 14:00, 1 Jan 2008 15:30
What is needed? What I need is to a find a way to compare the planned items with the appointments and to return all the periods for which a planned time exists:
Free planned time: 1, 1 Jan 2008 09:30, 1 Jan 2008 10:002, 1 Jan 2008 11:30, 1 Jan 2008 12:00 3, 1 Jan 2008 13:00, 1 Jan 2008 14:00 4, 1 Jan 2008 15:30, 1 Jan 2008 18:00
So, having two multitudes of periods,where the one specifies the planning templates and the other real used time, I need to find all the periods which can be used for another appointments. I've tried several aproaches, but I always faced performance problems.
col1 col2 col3 col4 (No column name) A123 Test 1 Test 1 Y N 0 Y N 0 A125 Test 1 Test 9 Y N 0 N Y 0
but what I would like is col 1 populated As A123 until it hits A125 then populated A125 etc. I was thinking about using the iff but not getting anywhere fast.
I have some records (approx 100K - 500K) in my table (Name, DOB, Zip, Phone). I want to validate each record and every field as given below.
Name - Should not have numerics DOB - Should be in yyyy-mm-dd format, should be < 1995 Zip - Should be 5 char zip, allowed 3 or 4 char zip for some States Phone - Should have 10 numeric digits, should be in xxxx-xxxx-xx format.
After validating these rules, If the record is valid then send email saying valid record, if invalid then send email with validation failure details like which column failed which validation rule suppose DOB might not be in specified format or it might be > 2000 all these details.
How to validate all the records in bulk without any loop or cursor.
I have a report in wich I show a list of countries. The length of this list differs each month and comes from a SSAS datasource. I want to show this list on my report in a matrix like this:
I have a SQL2005 db for tracking the prices of products at multiple retailers. The basic structure is, 'products' table lists individual products, 'retailer_products' table lists current prices of the products at multiple retailers, and 'price_history' table records when the price of a product changes at any retailer. The prices are checked from each retailer daily, but a row is added to the 'price_history' only when the price at the retailer changes.
I have the following query to retrieve the price history of a given product at multiple retailers:
SELECT price_history.datetimeofchange, retailer.name, price_history.price FROM product, retailer, retailer_product, price_history WHERE product.id = 'b486ed47-4de4-417d-b77b-89819bc728cd' AND retailer_product.retailerid = retailer.id AND retailer_product.associatedproductid = product.id AND price_history.retailer_productid = retailer_product.id
This gives the following results:
2008-03-08 Example Retailer 22.3 2008-03-28 Example Retailer 11.8 2008-03-30 Example Retailer 22.1 2008-04-01 Example Retailer 11.43 2008-04-03 Example Retailer 11.4
The question(s) I have are how can I:
1 - Get the price of a product at a given retailer at a given date/time For example, get the price of the product at Retailer 2 on 03/28/2008. Table only contains data for Retailer 1 for this date, the behaviour I want is when there is no data available for the query to find the last data at which there was data from that retailer, and use the price from that point - i.e. so for this example the query should result in 2.3 as the price, given that was the last recorded price change from that retailer (03/08/2008).
2 - Get the average price of a product at a given retailer at a given date/time In this case we would need to perform (1) across all retailers, then average the results
I couldn't find a topic suitable for testing this, so I thought I'd start one.
Here is one way to get the islands without a tally table.declare@test table (symbol char(3), dt smalldatetime)
insert@test select'abc','01/01/1990' union all select'abc','01/02/1990' union all select'abc','01/03/1990' union all select'abc','01/04/1990' union all select'abc','01/05/1990' union all select'def','01/03/1990' union all select'def','01/04/1990' union all select'def','01/05/1990' union all select'def','01/06/1990' union all select'def','01/07/1990' union all select'ghi','01/01/1990' union all select'ghi','01/02/1990' union all select'ghi','01/06/1990' union all select'ghi','01/07/1990' union all select'ghi','01/08/1990'
selectsymbol, min(dt), max(dt2) from( selectt1.symbol, t1.dt, t2.dt as dt2, (select count(distinct t3.symbol) from @test as t3 where t3.symbol < t1.symbol and t3.dt <= t1.dt) AS r from@test as t1 inner join@test as t2 on t2.symbol = t1.symbol wheret2.dt - 1 = t1.dt ) as d group bysymbol, r E 12°55'05.25" N 56°04'39.16"
I have a temp table which is used to store data before inserting to the real permanent tables. All columns of the temp table have nvarchar type.
How do I validate the data in temp table before doing the actual insert? For example, I need to ensure a field is a valid Datetime (currently in temp of nvarchar) before inserting to a Datetime field. Can this be done using Transact-SQL?
Here is the scenario: 1. The table has Three columns 1.ID, 2.Sqno, 3. Adj 2. The values for adj are (0,1,2)
Case1: The Sqno should start at '001000' for adj in (0,2) and increment by 2, i.e the next sqno would be '001002' and '001004' so on. Case2: The sqno should start at '001001' for adj in (1) and increment by 2 i.e the next sqno would be '001003' and '001005' so on.
Finally when you do order by sqno and group by ID it will be a running sqno.
Hi, we are using sql 2000, what would be the good way to validate different databases current data with seconday server's data after logshipping applied? thank you
[This is one of those cases where I think I know the answer, but I hope I'm wrong!]
I have a data flow which is processing data from the XML Source. There are 16 outputs from the XML Source. I have to perform a variety of validations on these outputs: things like "column 1 is required if column 2 has value 'a' or 'b'", or "column 1 or column 2 may be present, but not both".
For lookups and such, I use the Lookup component and its error output, both to redirect rows that fail the lookup, and to capture the data, column number and error code.
But, how do I do the same for "normal" validations?
If I have to use the Conditional Split transform, then I'll have to have one output per validation, and use a Union All to combine the rows again for output to an error file. This will also cost an extra "Derived Column" transform per output, in order to get a column number and possible error code per failed validation.
Worse, it's a pain to have to maintain all the columns in such a large "Union All"!
If I had the time, I might write a "Conditional Error" transform. It might be fun. But I have to be done by the end of this month, and don't even have time to create the UI for evaluating expressions!
Any tips or tricks or pointers to such would be very welcome.
Out of using stored procedure, reports and all this staff, I want to know the possible way to make sure that the data inside my Secondary Server Read only database are same as data in my primary server database.
I have a query I am currently attempting to optimise. The query joins in on a sub table (queries the same table) to garner a maximum date value for each row to display. For example:
SELECT Column1 ,Column2 ,DateColumn FROM Table1 T INNER JOIN ( SELECT Column1 + Column2 AS ConcatColumn, Column3, MAX(DateColumn) AS dt FROM Table1 GROUP BY Column1 + Column2 ) X ON X.ConcatColumn = T.Column1 + Column2 AND X.dt = T.DateColumn
Is there any way I can write this another way (preferably more cleaner/optimised)?
I am facing a problem on validating the data from a flat file while inserting the data into the destination table of sql server 2005 database. In my package, i have to validate the input data whether the values are coming as null or not, before inserting into the destination database. The flat file may not contain data for all NOT NULL columns. I have to find out that row(s) and reject the record. If the rows are coming as Null for the Not Null columns, the OLEDB Destination throws OLEDB exception for the constraint.
To resolve this, i have an script component in data flow, to check whether the input data is coming as null. I have added the output column of boolean type to the script component, it will be assigned to TRUE when there is null for the Not Null column in the script.
And in the follwing conditional split, i am checking the flag for TRUE to reject the record.
I am trying to find the members who are having monthstartdate continuously for 11 months ;
here in my example 123 wont have monthstartdate continuesly for 11 months it has break for february '2014-02-01'; where as 222 and 223 has continus 11 months , so i need to pull such members .finding out the members continuesly(enrolled) having 11 months.
I need to find the last purchase price for each product. If I run the following code, I correctly get 1 result for each productID and the last purchase order number.
SELECT pod.article as ProductID,max(pod.order_ID) as LastOrderfrom apodetail podgroup by pod.articleorder by pod.article
Now I need to add in the price for that product on that orderID. I've tried the following self join query, tried it without the join, and tried adding DISTINCT, but they all return more than 1 row per ProductID.
SELECT pod.article as ProductID,max(pod.order_ID) as LastOrder,pod2.rev_price as UnitPricefrom apodetail podjoinapodetail pod2on (pod2.order_ID = pod.order_id)group by pod.article,pod2.rev_priceorder by pod.article
How can I get it to simply add the price to the first query?
I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.
At first, I wanted to get a count of how many there are.
I need to compare records where these fields are matched:
MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile). MATCHED: (email, firstname, mobile) But not MATCH: (lastname, phone) MATCHED: (email, firstname, lastname) But not MATCH: (phone, mobile)
I cannot seem to find the syntax to combine IN + CASE in a WHERE clause
WHERE ses.BK_MS_SESSION <= '2015-03' AND vis.CAT_DRAW_STATUS = (CASE ses.BK_MS_SESSION WHEN '2015-03' THEN vis.CAT_DRAW_STATUS ELSE CASE stat.BK_MS_VISIT_STATUS WHEN 'T' THEN 'X' ELSE vis.CAT_DRAW_STATUS END END ) IN ('D','R')
I have a scenario here where the data looks like -
ID Date 100 07/01 100 07/02 100 07/03 100 08/01 100 08/02 100 08/15
Now I need to find out unique occurrences of ID - 100 ( where count = unique only if the occurrences are in consecutive days, gap of even 1 day causes it to be a different instance ) - SO with the above data I should have unique occurrences as 3.efficient way to calculate this ?
Hi Guys, we have a database where we have backup and restore done daily basis by a automated task. We are having errors on MSDB the saying database is reached the capacity(as SYSBACKUPHISRY getting populated every day). After I increase the size of MSDB,the error msg disappeared.
Does anyone know rather than increasing the size, Can we somehow store the data upto somany records and then when it reach the max, to rewrite the SYSBACKUPHISRY table all over again?
Is it advisable to truncate the sysbackup history table?
I would really like some one to give an idea as soon as posible. I appreciate it really very much. Thanks, Jay.
I want to be able to return the last date, e.g.(select Max(DateColumn) From Table)However if any of the DateColumn fields are null I want to return anull, is there a simple way to do this?
Upon entering data into the database, my fields fill the entire length specified. For Example a name field with char type and allocated for 50 spaces when entered with SAM returns SAM and 47 spaces. When I have to modify the record, I need to first delete the 47 spaces. Any ideas as to why this is happening or what I can do to alleviate the problem? This happens with all types and lengths. Thanks.
select * from posts where id = @id and author =@author and message like @keyword
in my case a user can choose to search by one or more of this elements.
what will be the best approach to write a statement that will account for the posibility of the user leaving some "parameters" empty, while also allowing for the posibility that all parameters could be filled
one approach I have thought out is this
if (@id IS NOT null) select * from posts where id = @id
if (@author IS NOT null) select * from posts where author= @author
if (@keyword IS NOT null) select * from posts where keyword = (@keyword
but this does not really take care of the posibility that all of them or some of them will be null while others will not
Hi, I'm trying to write a restore script. In the past I've always used the SSMS GUI to restore. When running the script below, the process got stuck at around 90%. Eventually I aborted the restore and found out that my C drive was full. The log file of the database I was restoring was 32GB. Can anyone tell me what I'm doing wrong? Thanks.
Code Snippet ALTER DATABASE [oms] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
RESTORE DATABASE [oms] FROM DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupOMS_Blank_Production.bak' WITH FILE = 1, REPLACE, STATS = 10 GO
