Let's assume that I input a gap value of 5. I need to fill in the data between the Record 1 and Record 2 by increment of 5 as specified in the input parameter on the counter column.
For example using Record 1 and Record 2, here are the additional data needs to be inserted into the table.
1234510.1 --> Record 1
1235010.1
1235510.1
1236010.1
1236510.1
1237010.5 --> Record 2
1237510.5
1238010.5
1238510.5
123909.7 --> Record 3
Currently, I am using a cursor to read from the table and select MIN counter from the table. Then use a LOOP to fill in the gap and insert it into another table. I have over 10000 records and after fill up the gap, I might end up with even more records. Just want to see if I can get any other efficient way to achieve this.
why I want to fill in the gap, I need to calculate the average value for my record set after considering all valid data points in between.
--From the rows I want to know how many number of days a person was active for the given date range.
create table [dbo].[personstatus] ( id int identity(1,1), name varchar(100), DateAdded date, InactivationDate date ) ; insert into [dbo].[personstatus] values
[Code] ....
--The output I am looking for. /* 1) FromDt = '2014-01-01' ToDt ='2014-01-30' KRISS = 7 VDENTI = 7 days
Is it possible to show the number of rows and the range for each partition in a table ?
This shows me the range but not the row count per partition
SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf INNER JOIN sys.partition_range_values sprv ON sprv.function_id=spf.function_id WHERE (spf.name=N'myDateRangePF') ORDER BY [ID] ASC
I have been away from SQL for a couple of months, and for the life of me cannot work out a simple way to do what i want to do.
select bu1.name,bu2.name, bu3.name, bu4.name, bu5.name from Sale INNER JOIN Sysuser on sale.userid = sysuser.userid INNER JOIN businessunitBU1 on BU1.Businessunitid = sysuser.businessunitid left JOIN businessunitBU2 on BU2.Businessunitid = bu1.parentbusinessunitid left JOIN businessunitBU3 on BU3.Businessunitid = bu2.parentbusinessunitid left JOIN businessunitBU4 on BU4.Businessunitid = bu3.parentbusinessunitid left JOIN businessunitBU5 on BU5.Businessunitid = bu4.parentbusinessunitid
So effectively i am trying to go through the hierarchy to find all the levels. Unforunately not every entry has the same amount of levels.
Using Microsoft query analyser SQL version 8, I believe.
Got a SQL table with a column that has NULLS in it. What I want to do is fill down the value in the row above based on a condition in another column (recordtype). Came up with the following code but unfortunately it only fills in the record directly below. Not those further down (variable number of NULL rows). Any idea how to get these to fill down? Suspect the answer may lie with block cursors but 1) I'm told they're slow (I have 2 and 1/2 million records in this table) and 2) I've never used cursors before!
________________
Update dbo.NSTS
Set dbo.NSTS.Number = NSTS_1.Number
FROM dbo.NSTS LEFT OUTER JOIN dbo.NSTS AS NSTS_1 ON dbo.NSTS.[Id] = ((NSTS_1.[Id])+1)
WHERE dbo.NSTS.RecordType like '101' AND (dbo.NSTS.Number is null or dbo.NSTS.Number like '')
I did get a cursor loop working but can only retrieve 1 row and (try as I might with the so called help examples) can't seem to pull down 2 rows at a time. Just can't seem to get it to accept this command at all: SQLSetStmtAttr(hStmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 2 ,0).
I'm trying to create a report and chart for a a manufacturing resource's activity for a given period (typically 30-90 days)
Jobs are created for the length of the run (e.g. 4 days). If the weekend is not worked and the above jobs starts on a Friday, the resource's activity needs to show 1 day running, 2 days down, 3 days running without the production scheduler having to make it two jobs. (A job can have multiple interruptions due to downtime). I have the jobs' schedules in one table and the downtimes in another (so think of the downtime as a calendar table--non working hours). Unusually, the end time is supplied with the downtime factored in.
So I need the query to create 3 datetime ranges for this job: Fri running, Sat,Sun down, Mon,Tues,Wed Running. Been going round in circles on this for a while. i'm sure there's an elegant way to do it: I just can't find it. I've found several similar post, but can't apply any to my case (or at least can;t get them to work)
Below is some sample date and expected results. I hope the explanation and example data is clear.
-- Create tables to work with / Source and Destination CREATE TABLE #Jobs ( ResourceID int ,JobNo VARCHAR(10) ,startdate SMALLDATETIME ,enddate SMALLDATETIME
[Code] ....
Below is some sample data
|--------------------------J1------------------------------------| running |----D1-----| |-------D2-------| down |--J1--|----D1-----|-------J1------|-------D2-------|-----J1-----| result
|-----------------J1-----------------------| running |----D1-------| down |-----------------J1-----------------------| |----D1-------| result
On a new project i need to create possible ranges between a specific interval.
suppose i have this main product:
main product : LY E67F
Also, in first level i have a table classify by Group depending on Intensity.
table group Group intensity AA 1120 AB 1400 BA 1800 BB 2240 CA 2800
I need to create these diferent options:
1 option : AAAB or AABA or AABB or AACA 2 option : ABBA or ABBB or ABCA 3 option : BABB or BACA or BBCA or beginning from the end 1.option CABB or CABA... or beginning from the middle 1.option BBBA or BBAB and so on.
I fact, i need to find all available options possibles to build article code, like a matrix.
Guys Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number of periods betwen the startdate and enddate so for example
-- Initial data insert @Periods ( StartDate , Enddate , NumberOfMonthsInPeriod , TotPeriods-- for this illustration, initialised to 0 but need to be UPDATEd as per detail below )
select '30-Sep-2005', '10-Apr-2009', 1 -- 1 month period 0 union all select '30-Sep-2005', '10-Apr-2009', 3 -- 3 month period 0 union all select '30-Sep-2005', '10-Apr-2009', 6 -- 6 month period 0
The following rules regarding periods apply Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie 01-Jan - 31 Jan 01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...) and so on to December
Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year 01-Jan - 31-Mar 01-Apr - 30-Jun 01-Jul - 30-Sep 01-Oct - 31-Dec
Each 6 (NumberOfMonthsInPeriod) month period is one of the following 01-Jan - 30-Jun 01-Jul - 31-Dec
I need to derive TotPeriods as follows:
For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44
For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16
For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8
I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:
select * from dbo.ACCTING_TRANSACTION_hISTORY where ath_postype = 'NTC' or ath_postype='NTD' and
ath_postdate >= '2013-01-01 00:00:00' and ath_postdate <= '2013-01-05 23:59:59'
I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling records from 2009, etc.
I have an Orders table which has the following fields:
OrderID (PK, int, auto increment, not null) CustomerID (FK, int, null) PaymentDate (datetime, null) UserID (uniqueidentifier)
(and other irrelevant fields)
Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.
creating the missing records in a date/time range.
However, I need to return different groups for each span of records.
here's some data....
aaa1 aaa7 bbb2 bbb5 bbb6
The numbers are the hour of the day.
I need to return
aaa 0 0 aaa 1 1 aaa 2 0 aaa 3 0 ... bbb 0 0 bbb 1 0 bbb 2 1 ... and so on.
I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.
Po Table is having a datatime field called "ShipDate", OrderNo and a field called PoNo (which is having number of PO).
PoDet is child table having PoNo and PoQty
Suppose following records are in both tables
Po:-
OrderNo : 1 PoNo :- Po No 1 ShipDate :- Oct 5, 2007
OrderNo : 1 PoNo :- Po No 2 ShipDate :- Dec 5, 2007
OrderNo : 1 PoNo :- Po No 3 ShipDate :- Oct 5, 2007
PoDet:
PoNo :- Po No 1 PoQty :- 2000
PoNo :- Po No 2 PoQty :- 3000
PoNo :- Po No 3 PoQty :- 4000
I want to generate a Delivery No. which will be generated in this way :-
1. Earlier Shipdates should be assigned a Lower Number 2. If Shipdates are same, like in case of PoNo 1 and PoNo3, the higher Qty will be assigned a lower number
So meeting the above two conditions, a single SQL should return
PoNo :- Po No 3 DelNo : 1 (As Dates are same for PoNo1 and PoNo3 but PoNo3 Qty is higher, so this will come first)
The code below is almost there but falls over on Recordno 8:
select curr.recordno,curr.speed ,CASE WHEN curr.speed >= ISNULL(prev.speed,0) THEN curr.speed ELSE ( SELECT MAX(speed) FROM speedtest WHERE recordno between (CASE WHEN curr.speed >= prev.speed then curr.recordindex else prev.recordno end ) and curr.recordno
Ok, I have two parameters - @StartDate and @EndDate. We only care about the date part of these paramters. What I would like to do is create a table with one record for each date between these two values. For example:
@StartDate = '01/01/2008' @EndDate = '01/8/2008'
Should yield a table with 9 records in it for every day between @StartDate and @EndDate like so:
I know I could just do a WHILE (@StartDate <= @EndDate) loop and insert records into a temp table but I'm looking to see if there are any new methods/techniques to achieve this with a more simple statement.
SQL Server (TSQL) how to divide a number by either 2,3,4, or 6 and spreading it across that number of rows depending on what we are dividing the number by where it comes back to the original number...That is confusing I know so let me break it down...
Bill for 143.23 that will be paid out through 2 months...When you divide that by 2, you come back with 71.62, but if you multiply that number by 2, you come back with 143.24, not the amount for the bill...the end result has to be 71.62 for month1 and 71.61 for month2...Basically when there is a remainder, that has to be applied to the first month...
143.23: Month1 = 71.62 Month2 = 71.61
Another example...Same amount but have to divide by 6
I need creating date of birth using ID number the ouput that im looking is a follows
e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01 e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01
The desired format I need is to take the above and create date of birth with the below format as required by the application used.
I seem to always get the "Fun Stuff" to try and figure out. I have an entire table that was pumped out of Oracle. I even hate saying that word!
There are a couple columns that are Float data type, and they are storing phone numbers as a Float data type. I am not able to CAST these into anything that is legible.
This is one of the values that I made up that look like some of the others.
In t-sql 2008 r2, I would like to know how to select a specific string in a varchar(50) field. The field in question is called 'CalendarId'.
This field can contain values like:
xxIN187 13-14 W Elem HS321 13-14 D Elem IN636 13-14 C Elem 030 13-14 clark middle.
What I am looking for is the first position that contains a number value for the length of 3. Thus what I want are values that look like the following: 030, 636, 187.What I know that I want is substring(CalendarId,?,3).The question mark is where I want the starting location of a number value (0 to 9) of the value in CalendarId . I tried pathindex but my syntax did not work.
I'm trying to do a simple insert into a table, something like this:
insert into sometable (ID, somecolumn) select 'Task-ID', somevalue from SomeOtherTable where something = 'someothervalue' (or something to that effect)
So, the SELECT would generate something that looks like this:
What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.
Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.
What I'm hoping to do is rewrite my SQL like this:
insert into sometable (ID, somecolumn) select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable where something = 'someothervalue'
I am using SQL Server 2008 as a back end for a Microsoft Access front end. I have created a report that is essentially a Bill Of Lading. The detail section lists all the purchase orders that are being shipped on a single load. The problem with the Access Report is that I always need a set number of records (8) so that the layout is consistent. So, if the query returns 5 records, I need an additional 3 blank records returned with the recordset. If there are 2 records, I need an additional 6, and so on. For simplicity sake the query is:
SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber=@BOLNumber;Now, I can get the results I want by using a union query for the "extra" records.
For instance, if there are 6 records returned for BOLNumber '12345', I can get the expected results by this query:
SELECT tblBOL.PONumber FROM tblBOL WHERE tblBOL.BOLNumber='12345' UNION ALL SELECT '12345',Null UNION ALL SELECT '12345',Null;
Another solution would be to create a temporary table with the "extra" records and then have only one Union statement. Not sure which is better, but I'm not really sure how to programmatically do either of these. I'm guessing I need to do it in a stored procedure. How do I programmatically create these extra records? One other note.... If there are more than 8 records, I need to return 8 of these "blank" records and none of the real records (hard to explain the reason behind this, but it has to do with the report being only a summary when there are more than 8 records while the actual records will go on a different supplemental report).
with Sample(Size) as ( select '16.3 Oz.' union all '1' as union all '2 Tablespoons' union all '46. Oz. Each' )
Iwant to separate number and alphabets. But i wanted to keep the dot to have decimal values. Expected output
select '16.3' as val1 'Oz' as val2 union all '1' val1 as union all '2' as val1 'Tablespoons' as val2 union all '46' as val1 'Oz Each' as val2
Please note that i need to remove the extra dots at the end of the the val1 and no dots in val2
I rid some of the functions like dbo.fn_StripCharacters and dbo.fn_GetAlphabetsOnly ffound in the internet. evey with my own logic. i couldn't remove the dot which appear at the end of val1
I'm writing a query that will be used in Jasper Ireports, but prefer to have the values done ahead of time using SQL rather than relying on the report to do the lifting.The fields are pretty straight forward, only the display is where I have a question.
Fields Used: PERIOD ('MON-yyyy') and VALUE
The results must start with the CURRENT PERIOD (AUG-2014) in one column and the VALUE for the current period multiplied by 1/12 (VALUE*(1/12)).The next column should return the VALUE for CURRENT PERIOD - 1 (JUL-2014) and multiply by 2/12 (VALUE*(2/12))
This should continue for the last 11 months and would end with OCT-2013 with the value being multiplied (VALUE*(11/12)).Is the easiest solution to this a CASE statement looking at PERIOD then PERIOD minus one month, minus two months...etc?
Split function. I have records of multiple users, the last value of every record is a contact number (10 Digits- Numeric), I want a split function which can take the whole text and split the records on the basis of contact number.
In order words i want SQL to locate the contact number and move to the next record after that and so on till the end of the text.
create table tbl_1 (txt varchar (max))
insert into tbl_1 values ('john asfasdf 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive, finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
[Code] .....
Output john jimenez 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596 jeffrey galione 57 allen dr wayne nj 15810 562_434_0710 na na 5,8/12 executive, technical sales and support good 8137 91630 8.25 126 eileen oneal 8258364083
I have written a query to search for a string in an expression by the number of it's appearance. Script is like this:
DECLARE @Expression VARCHAR(8000) = 'abcd_e_fgh', @SearchString VARCHAR(10)= '_', @OccuranceNumber SMALLINT = 1 DECLARE @SearchIndex INT = 0, @SearchIndexPrevious INT = 0, @Sno INT = 0 WHILE @Sno < @OccuranceNumber BEGIN
[Code] .....
Here i'm trying to search "_" in expression "abcd_e_fgh" where it is appearing for first time. it gives me 5 correctly. Now when i change the @OccurenceNumber to 2 or 3, it gives correct values 7 and -1 respectively. However now when i change it to 4, it gives me 5. So when it's trying to check for fifth appearance of "_", it's not actually giving 0 or -1 but repeating the value 5.