I have two columns, where I have the start and stop numbers (and each of them ordered asc). I would like to get a query that will tell me the missing range.
For example, after the first row, the second row is now 2617 and 3775. However, I would like to know the missing values, i.e. 2297 for start and 2616 for stop and so on as we go down the series. Thanks in advance to any help provided!
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.
I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.
SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME) ,d.fmc_prodlnk, d. fmc_cost INTO #TestTable FROM ( SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL
KEYIDGROUP 1 1 a 2 1 b 3 2 a 4 2 b 5 3 a 6 3 b 7 4 a 8 5 a
This is my simple table I need a query that will identity the ID's that are missing the group "b" but I don't want ID 1,2,3 to come up because they are part of a and b. I just need to see anything missing only "b" but not if it's part of a and b.
query should reveal answer should be missing the group b KEYID 7 4 8 5
I tried the NULL search but since the records don't exist it cant find a null. I am writing a query to identify the missing ID without B but exclude ID that are part of A and B
If a button is never clicked in a given month, it never gets logged to the table. In this example, the 5th button was not clicked during the month of December, so it does not appear in the results. I want to modify my query so it displays the name of the button and a zero (in this case "5th Button 0") in the results for any buttons that were not clicked. For some reason I am drawing a blank on how to do this. Thanks in advance.
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!
I want to display some stats using captured information. the x-axis is the date on which something occured. the y-axis is the number of occurance on that day.
The problem is that the x-axis need to be of continous range, so say from 1st Mar 2006 to 8th Mar 2006, I need to display every date within that range.
I've gathered the neccessary stats with a group statement but I was hoping I can fill in the gaps without having to loop through the returned data to identify missing dates, is there any built int SQL Server function which will allow me to do this within my SELECT statement?
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.
I re-created a publication that was having problems and it gives this error when I start the snapshot agent from SQL Server Management Studio: I am stuck on how to resolve - any ideas?
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. Transaction count after EXECUTE indicates that a commit or ROLLBACK TRANSACTION statement is missing. Previous Count = 1, current count = 2."
select * from MSmerge_identity_range returns 19 entries but I don't know how to fix.
I have tried deleting and re-creating but always get this error.
My other publications and subscriptions are working fine and I was able to create a new test publication that worked but can not get this one to work that worked fine up until today.
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000 Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000 W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000 A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000 B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000 C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000 B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000 C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000 Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
hi! i have one datatable with date and some more fields are there in that. i want to query to return values from a particular date to another date. how can i do this? please help me! thanks in advance!
create table tick ( ID bigint identity (1,1) primary key not null , price money not null )
and I want to know 3 things
Starting with ID = 1 through ID = (last) give me the low and high price (that satisfies the below WHERE clause), and the last ID WHERE high price - low price = 0.10 and the last ID (last) is the minimum ID to satisfy: high price - low price = 0.10
So the last ID will coincide with the record containing either the low or high price, the problem is you don't know which record in that range has the corresponding high/low price, it could be the first record or the 10,000th record.
I am thinking I need to create two summary tables, maybe calculate the min(ID) that goes down 0.01 then the min(ID) that goes down 0.02, etc... Then calculate the min(ID) that goes up 0.01 then up 0.02, etc..finally join against these two summary tables to figure out which combination of downSummary and upSummary have a difference of 0.10.
I have this query that calculates the next possible shipping day, based on 3 conditions:
- It has to be a workingday (WORKTIMECONTROL: 1 workingday, 0 holiday) - marked green - There might be extra days (@xdays) required by the process - marked blue - Customer wants their goods to be shipped on special days - marked red:select TOP 1 Transdate from WORKCALENDARDATE where Transdate > @startday and WORKTIMECONTROL = 1 and DATEPART(WEEKDAY,TRANSDATE)-1 in (2,4) and
[code]...
So customer 123456 accepts shipping of goods only on tuesday and thursday as in the above example "... in (2,4)". Multiple shipping days means that the Subquery returns more than one record, which gives me a headache as I don't see how to integrate this portion into my query. I tried to use the stuff function as I formally need a result that can be provided that way; but the format is incorrect as it in varchar, while an array of integer is needed.
DATEPART(WEEKDAY,TRANSDATE)-1 in (select stuff((select ',' + CAST(DELIVERYDAY as nvarchar) from CollectiveShipment where custaccount = '123456' for xml path('')),1,1,''))
I have a field which is currently of the "date time" data type. i want to convert it to smalldatetime, but everytime I try, i get an error of the "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. " sort.
I have tried to find the values which are out of the smalldatetime range, with the following query
SELECT *
FROM midmar
WHERE bday BETWEEN '01/01/1900' AND '06/05/2079'
Which doesn't quite work, and gives me values that are actually between those two values listed.
I have also tried having the WHERE clause read:
WHERE bday <'06/06/2079' AND
bday>'01/01/1900'
and that doesn't really work either.
What's going on? Is there likely another problem besides the structuring of my queries?
Hoping someone may be able to help with a problem I'm having with SSRS parameters....
My report has 2 parameters - the User Id used to login to the application and the Department(s) within the organisation. Based upon the User's role, the user may have access to data for one or many Departments.
Thus, the first parameter needs to be set in code based upon the User's login, however, the range of the second parameter (i.e. the range of Departments that the user can access) is controlled by the value of the first parameter.
The second parameter is to appear as a drop-down of Departments to which the User has access.
The report is to be produced for the selected Department.
Are you able to advise how to restrict the range of values for the second parameter based upon the value of the first parameter?
We have a couple of tables that can have quite a bit of data each day prior to replication. Can we increase the default values for a table for each subscription? For example we have a table called table1 and on the sqlexpress client they could enter in 10000 rows a day, on table2 it's just 100 rows a day. How can we increase the values to where we do not get the error for table1 stating that the insert failed because it conflicted with the identity range check constraint. Thanks in advance.
I have a table that keeps track of click statistics for each one of my dealers.. I am creating graphs based on number of clicks that they received in a month, but if they didn't receive any in a certain month then it is left out..I know i have to do some outer join, but having trouble figuring exactly how..here is what i have:
select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0) from tblstats s(nolock) join tblDealer d(nolock) on s.dealerid=d.id where d.id=31 group by right(convert(varchar(25),s.stamp,105),7),d.name order by 2 desc,3,1
this dealer had no clicks in april so this is what shows up: joe blow 10-2004 567 joe blow 09-2004 269 joe blow 08-2004 66 joe blow 07-2004 30 joe blow 06-2004 8 joe blow 05-2004 5 joe blow 03-2004 9
I used Microsoft clustering for grouping my data. Even though i already cleaned the data and have no null values i get one cluster with missing values in every attribute. (i set CLUSTER_COUNT=3 and i'm using Scalable k-means algorithm)
Does "missing" mean that the algorithm cannot group that particular tuple in another group so it consider it as missing?
I am in the midst of writing a query to return a range of product BIN LOCATIONS from a warehouse stock levels program.I know the start and end BIN LOCATIONS for the warehouse, e.g.: Start - #00000, and End - Z10000.However I cannot hard code these into the program, instead I want my operator to designate the first letter of both the start and end BINS locations, and then hit the 'go button'.Towards this end I have declared a couple of variables in my SQL, as follows -
Code: declare @strBin varchar(10) set @strBin = '' if @strBin = '' set @strBin = '#%'
declare @endBin varchar(10) if @endBin = '' set @endBin = 'z%'
My challenge is writing a relevant WHERE clause for my select statement, I have tried the following -
Code: where BINLOCAT.BINLABEL between @strBin and @endBin order by BINLOCAT.BINLABEL
Whilst the clause above does not cause any errors, neither does it return any results.My first thought is that the BETWEEN operator does not accept wildcards. But if this is correct then how do I go about allowing an operator to enter the start, and end BINS without typing the entire BIN string (e.g.: #00000)? I have tried >= @strBin and <= @endBin, but I am having a similar issue with no errors, and no data returned.
Hi! I have table with complex index on 5 fields. One of them is string filed. I want to implement some sort of filtering, by setting SetRange() in my SQLCeCommand. But i need to fileter only by one string field and to get the values starting with the input string value. I tried to use such code: ... command.SetRange(DbRangeOptions.Prefix, new object[] {null, null, null, "Com", null}, null); resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable); .... But it doesn't work. As a result i've got an empty result set. Usage of simple index on one field and setting the correspondent range will solve problem, but i can't have such index due to project restrictions. Is there any way to set prefix range only by one value of complex index? If not, please, explain me how does Prefix Range works.
Currently I report our monthly fees broken down into 4 weeks per month by using 4 separate datasets with the following code
SELECT SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT FROM Practice.ibvSalesByJob INNER JOIN Practice.idvJobType
[Code] ....
The second dataset then has the date code changed toÂ
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/08/' + Cast(@DateYear AS char(4)) + ' 00:00:01' AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:00'
The third
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:01' AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:00'
The fourth
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:01'
Now I was hoping so that I could report the above data in one chart and do an expression on the category and group the dates so I would just have one dataset like below but four separate columns saying Week 1, 2 3 and 4 and then the sum filtered in line.
SELECT SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT FROM Practice.ibvSalesByJob INNER JOIN Practice.idvJobType
[Code] ....
If I could somehow with SQL tie in all 4 separate datasets and display them as Week 1, 2, 3 and 4 underneath the dataset and selectable.An even simpler solution maybe just understanding how charts work, I can get it so I display the 4 separate weeks in the chart however I can't get the bottom line (Category Group) to display Week 1, 2, 3 and 4.
I've got a field that might have spurious values in it (say, an admin adds a new row but doesn't have an entry for this field). I'm trying to swap in the string no_image_EN.jpg if the value in the db does NOT end in .jpg. That way, any value rreturned is either a valid filename or no_image I'm having trouble with the CASE statement, particularly testing just the last few cahracters of the string: select product_code, CASE can_image_en ?? When (can_image_en LIKE '%.jpg') then can_image_en Else 'no_image_EN.jpg' End as can_image_en, none of these do the trick either (some are bad syntax obviously): ? When (can_image_en LIKE '%.jpg') then can_image_en ? When LIKE '.jpg' then can_image_en ? When '%.jpg' then can_image_en ? When right(can_image_en,4) = '%.jpg' then can_image_en This is the one that has correct syntax, though it seems to return false in ALL cases CASE can_image_en When '%.jpg%' then can_image_en Else 'no_image_EN.jpg'
We are facing the following issue, several machines/users that are executing very often a command similar to :
INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo'); SELECT SCOPE_IDENTITY() AS Table_ID;
Where TableName has a primary key defined as identity(1,1).and that Table_ID is being used as reference in others tables
These queries are executed using different dababase users and among several diffrent apps..The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.why the Scope_Identity returns the Inserted ID if the INSERT action had failed.
I can't figure out how to get my line chart to break when there isn't a value. For example, I have a trend line over 4 time periods. The 3rd time period is missing a value. Instead of the line ending at the 2nd period and picking up again at the 4th time period, it's connecting the line 2nd to the 4th period. I'd like it to break and for there to be no line appearing in the 3rd period. I bet that's as clear as mud, but let me know if you have any questions.
Write the query that produces the below results. I'm not ale to join the two sets in a way so that it displays NULLs if no purchase was made on a given day for a particular product. I need NULLs or s so that it shows up correctly on my SSRS report.
;with testdata as( SELECT 1 AS Id,'1/6/2014' AS Date, 21 As Amount UNION ALL SELECT 1 ,'1/8/2014', 25 UNION ALL SELECT 1 ,'1/9/2014', 30 UNION ALL SELECT 1 ,'1/10/2014', 60 UNION ALL SELECT 1 ,'1/5/2015', 3800 UNION ALL SELECT 1 ,'1/6/2015', 7120 UNION ALL
I have several databases set up for transactional replication to another instance of SQL Server 2005 for fail over purposes. Today, I restored one of those replicated databases to my development machine and discovered two surprising problems:
1) The Default Values settings in the replicated tables are missing. They are there in the publishing tables, just as they were before I set up replication. However, they are not in the subscribing tables. Now, this is not such a big issue, since I tend to send all default values in insert queries as necessary.
2) The second problem is a more of an issue, since I use auto-numbered Identity columns in my tables (yes, I know that's just plain lazy...). Anyway, in the replicated tables, €œIs Identity€? is indeed set to yes, but despite that fact that there are thousands of records with incrementally unique IDs, SQL server is trying to insert a record starting with 1. This, of course, throws a PK constraint error.
Obviously, if I am use them for failover purposes, these replicated databases need to be identical in every way.
So, what did I do to cause this situation, and how to I fix it?
Just really wonder what is the good idea to deal with missing values? Should we leave the missing values there in the traning data set ? Or replace it with other values?
What I am really concerned is that if we simply replace those missing values with other values, then how will it really affect the correctness of the training models?
I am looking forward to hearing from you for the above issue and it will be really great if we have any kind of best practices of dealing with this issue.
