Transact SQL :: Finding Continuous Members Script?
Aug 11, 2015
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.
Below is the sample data i am referring.
memid    MonthStartDate
123 2014-01-01
123 2014-03-01
123 2014-04-01
123 2014-05-01
123 2014-06-01
123 2014-07-01
[code]....
View 28 Replies
ADVERTISEMENT
Oct 2, 2015
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)?
View 19 Replies
View Related
Apr 30, 2015
I would like to know how I could code so that I can get the ending inventory of each day. The following is my data
CREATE TABLE #TEMP
(
DATERCVD DATETIME
,ACCTID INT
,DATESENT DATETIME
)
INSERT INTO #TEMP VALUES ('01/01/2015', 1 , '01/05/2015')
[Code] ....
So my query results should be something like this:
InventoryDate Received Sent Ending
1/1/2015 4 1 3
1/2/2015 5 0 8
1/3/2015 3 2 9
1/4/2015 0 4 5
1/5/2015 0 5 0
TOTAL 12 12
View 5 Replies
View Related
Sep 29, 2015
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?
View 10 Replies
View Related
Nov 20, 2015
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)
View 9 Replies
View Related
Nov 6, 2015
I have 2 tables A, B with 1 to many relationship
Table A(ProductID), TableB(ProductID, FileID)
I need to find only the records in Table A that may have more than one FileIDs in Table B, since some ProductIDS have multiple FileIDs in Table B...
View 8 Replies
View Related
Oct 29, 2015
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')
View 7 Replies
View Related
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
[code]....
View 14 Replies
View Related
Jun 4, 2015
Problem setting is a geography dimension with multiple user defined hierarchies in SSAS 2008.Â
Ex.:Â
Hierarchy 1 (political territory): level 6 --> level 5 --> level 4 --> level 3 (state) --> level 2 --> level 1
Hierarchy 2 (sales territory): level 4 --> level 3 --> level 2 (sales region) --> level 1
...
Hierarchy 9
The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states.  Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state.Â
So far what I've got is:
CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL;
SCOPE (Measures.[weighted measure state and above],
Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE),
Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE),
... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;
This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.
View 5 Replies
View Related
Jul 9, 2015
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 ?
View 5 Replies
View Related
Apr 21, 2015
I'm trying to figure out how to identify the calling database within a sproc on a different database without using a parameter.
View 2 Replies
View Related
May 26, 2008
Hi,
does anyone know if it is possible to set an alert like that ?
if cpu is over 85% and stay for more then 1min continuous over 85%then alert.
THX
View 15 Replies
View Related
Jun 20, 2008
hai,
set dateformat dmycreate table tbl_sampemptable(employeeid int,StartDate datetime)
declare @employeeid intset @employeeid=1declare @startdate datetime,@enddate datetimewhile(@employeeid<=1000)begin set @startdate='01/05/2008' set @enddate='31/05/2008' while(@startdate<=@enddate) begin if(@employeeid<>1 and @startdate<>'02/05/2008') insert into tbl_sampemptable values (@employeeid,@startdate) else if(@employeeid=1) insert into tbl_sampemptable values (@employeeid,@startdate)z set @startdate=dateadd(day,1,@startdate)
endset @employeeid=@employeeid+1end
select * from tbl_sampemptabledrop table tbl_sampemptableset dateformat mdy
i have to select records depending on @count parameter to this table.Depending on this parameter value it should fetch sequential dates.For example
if @count=2then result should be like this,
EmployeeID FromDate ToDate
1 01/05/2008 02/05/2008
1 03/05/2008 04/05/2008
.
.
2 03/05/2008 04/05/2008 //note that here 01/05/2008 is not selected
because 02/05/2008 is missing
2 05/05/2008 06/05/2008
.
.
3 03/05/2008 04/05/2008 //note that here 01/05/2008 is not selected
because 02/05/2008 is missing
3 05/05/2008 06/05/2008
.
.
if @count=3 then result should be like this,
EmployeeID FromDate ToDate
1 01/05/2008 03/05/2008
1 04/05/2008 06/05/2008
.
.
2 03/05/2008 05/05/2008 //note that here 01/05/2008 is not selected
because 02/05/2008 is missing
2 06/05/2008 08/05/2008
.
.
3 03/05/2008 05/05/2008 //note that here 01/05/2008 is not selected
because 02/05/2008 is missing
3 06/05/2008 08/05/2008
.
. how can i do this.please help me.thanks in advance
View 8 Replies
View Related
Mar 7, 2008
I am fairly new to SQL 2005 and have taken over a migration project from 2000 to 2005 and one of our scheduled jobs seem to run forever, but do not have errors. This did not happen in the past so I was wondering if the agent settings for Replication Merge has the -Continuous parameter will the job ever complete or does it really run "continuously"?
View 1 Replies
View Related
Jun 6, 2006
Hi,
l've a series of day which record the date of an event. l would like to count the # of continuous days for the event. In this case, it would be 14/5, 15/5, 16/5, 17/5, 18/5, 19/5 and 20/5. Any idea to do this in SQL?
Date
-----
20/5
19/5
18/5
17/5
16/5
15/5
14/5
09/5
07/5
06/5
05/5
View 7 Replies
View Related
Nov 22, 2006
Hi,
The time column type is SMALLDATETIME. Data is inserted every minute, but it isn't occured always.
When I display the contents, I want show every time(minute). If a specific time is skipped, it should be display as data zero.
Someone help?
View 1 Replies
View Related
Oct 23, 2007
Hello,
I have a question regarding whether or not Data Mining can be utilized in a specific problem I have to solve.
Situation: I€™m going to simplify the problem by explaining it in terms of a €œpizza manufacturer€?. Suppose I wanted to predict the run minutes + downtime minutes (I use these to get an hourly rate: Pizzas/(run hrs + delay hrs) = Pizzas per hour) by looking at a set of input properties.
My properties could be something like the following:
# of Toppings
# of Special Pricing Stickers
Cardboard Box Indicator
Case Indicator (0 represents auto-casing, 1 represents putting in case by hand)
Machine Type (0 or 1€¦ 0 represents an older €“slower machine, 1 is newer)
Quantity of Run
(there could be up to 15 other properties that may or may not impact our rate)
Measured Values:
Run Minutes
Delay (down) minutes
Steps I€™ve Done So Far:
I€™ve created a couple different data mining models for this as I was unsure which one(s) to use. I checked the lift chart while feeding back in the original data set and my scatter plot appeared fairly inaccurate.
I've attempted to use Excel to create a linear regression, however my r squared value was always around .30. I decided to try to use SQL Server Data Mining to see if it could be something to help predict our accuracy better than a linear formula.
I've played with a couple different algorithms in Data Mining, and it appeared that none of them did exceptionally well with prediction. I even checked the lift chart using the same table as I used to train the model.
What algorithm(s) might work the best?
Can I reasonably expect a prediction within a fairly strict tolerance (I'm guessing the answer to this is: "yes, if your source data represents a consistent pattern")?
How can I best utilize Data Mining to give an answer like "historically, your run rate has been between these 2 values with a probability of X". I'm thinking I can utilize the predictprobability and stdev to some extent.
Any suggestions would be greatly appreciated.
If anyone needs further clarification, please let me know.
Thank you.
Regards,
Dan
View 2 Replies
View Related
Apr 27, 2007
I am having one question about discretization of continous attributes
values. How does it work? I need this information for my thesis. I have a
continous attribut, namely SKS, with range 0-20. When I use Microsoft
Decision Tree algorithm, this attribut split in SKS <= 18 and SKS > 18. I
want to know how does it find 18 as a number to split not the other.
One question again about Microsoft Decision Tree algorithm, about
Complexity_Penalty parameter. How does it affect the algorithm? For example,
if I set this value=0.1 what does it mean and how does it correspond with
growth tree?
Thanks a lot before for your kindness to answer my questions.. :-)
View 1 Replies
View Related
Sep 19, 2007
Hi All,
I was wondering if there was a way to specify a range when training a model to predict continuous variables. For instance, the predicted variable can only have a range of 1 - 10.
Thanks
View 1 Replies
View Related
Jan 29, 2002
Does anyone know exactly how to create a trace that runs continuously on a server and writes the data to a table? Now I know how to create a trace file with the profiler, but I want something set up so that I don't have to have the profiler running on the server all the time. As well as soemthing that will restart itself if the server is rebooted. I have been looking at these x(xp_trace.*) procedures. Is this the way to do it?
I have to trap login information in a table and have a scheduled job that runs once a month and look for specific data in the table and send out e-mails based on certain values.
I have written the procedure which does this I just need to know how to set up the trace so it runs in the background continuously.
Can anyone help?
Thanks............
View 1 Replies
View Related
Oct 4, 2012
I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.
create table t1(name varchar (100))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO
EXPECTED RESULT:
1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS
drop table t1
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
View 9 Replies
View Related
Mar 21, 2008
Hi...My first post.
I'm currently creating a SSIS package that takes data from 3 unique databases. A SQL DB, FoxPro DB, and an Oracle DB. The data is pulled, cleansed and put into a single SQL 2005 table. The data is then pulled from this table every 15 minutes, formated in a given specification and uploaded to an ftp site. This part is done. My question is this:
This package needs to run around the clock, non-stop. How can package be set up to do this? It needs to pull data from the 3 DBs and put it in the common table, wait 15 minutes and do it again. Wait 15 more mintues and do it again. And so forth. A problem I'm having is I don't see a way to set up a SSIS package so that it runs around the clock.
On same premise, I have another issue. When I try to take data from the common table and there is nothing there, it causes an error. Is there some way that you can run a test like
SELECT * FROM _table_ WHERE is_sent = 0
if results == 0
{
wait 15 minutes and test again.
}
else if
{
write flat file, wait 15 minutes.
}
This has to be done in the Control Flow scope, so I can't use a conditional split. This is a pretty big deal as this needs to run around the clock. Thank you in advance for your assistance.
View 4 Replies
View Related
Apr 11, 2007
I was wondering if anybody knows how to solve this problem. Here's the setup.
There is an ASP.NET application running on a local web server at the customer's location, it currently uses a MSDE backend database. There is a copy of the application on the customer's webhosting company so it can be accessed from outside the customer's location it is running on a full version of SQL Server 2000. We have this setup because the customer's ISP is not very reliable and the customer needs to be able to use the application even when their web connection is down. It is also used from outside their location by sales people and management and remote offices. The problem is we want to keep both databases synchronized together. We had been using Merge Replication which was working fine until the local ID jumped because it had run out of allocated identities. This causes a problem for their accounting because now there is a gap in the document's numbers.
Is there a way to have the identity field (or a generated document number) to remain continuous and unique across both databases? This needs to also work if one of the databases were to go down for a time or lose connectivity between the two servers. I'm looking for any option. We also have the option of upgrading the application to SQL Server 2005 if needed. Any ideas are appreciated.
Thanks,
Seth
View 6 Replies
View Related
Aug 22, 2006
Hi
I have set up merge replication and it works nicely.
I have set it up to work continuously, because I thought that if it can't find the subscriber or is offline then that's fine it will just sync again when it's back on line.
This is true
BUT it keeps throwing lots of messages into the event log to tell me the merge has failed.
SO
a. Can i just turn off the error reporting
or
b. How can I get it to sync this way automatically on connection without the error messages
thanks
ICW
View 1 Replies
View Related
Mar 4, 2007
I have one data pump in a series that was pumping in too many records. Doing an independent query of the source table, I found there was about 140,000 records. My pump uses a variable for the source query, nothing fancy just a simple SELECT * FROM table WHERE DateField > '4/6/2006 12:00:00AM'. The Destination is local on the SQL Server and is set by a variable, and does a fast load. When I went away and checked in BIDS while it was running (the data flow tab where you can see the record count) it was at 28,000,000 and still going!
Any ideas what could be causing this? As I say there are only 140,000 records and no joins in the query--is this a bug someone has run into before?
Thanks,
Kayda
View 2 Replies
View Related
Dec 18, 2007
It seems i face a problem with the Microsoft Decision Trees model when i have a predictable variable that is continuous.
I have created the whole model according to the AdventureWorks tutorial (and it informs me that the same procedure is followed with a continuous variable) and i have flagged the variable as continuous. Even though everything seems be going well, the results i get are not correct (after a cross check with another project already done and checked).
Is there something i am missing or i skipped while creating the model? Any suggestions that may help me are appreciated
Thank you in advance
View 1 Replies
View Related
Jan 17, 2008
hi all
how to find the continuous date from the given date range in sqlserver 2005
e.g.
2007-01-27 and 2007-02-02 and output should be
2007-01-27 2007-01-282007-01-292007-01-302007-01-312007-02-012007-02-02any suggestion?
View 1 Replies
View Related
Apr 23, 2015
I am getting SQL Time out exception after long run of 15-20 Hours, Please find the attachment for more details. My SQL queries  are not taking much time for execution , simple Update /Insert statements to local database.
Observed Activity manager also, looks fine. One more application connected to same database (Insert statements) works fine. In connection string I have modified Connection string ConnectTimeout = 2147483647; (max)
View 4 Replies
View Related
Jun 5, 2007
I have SQL 2000 Server that had a database called ABC and it has been moved to another server on 5-15-2007
I kept ABC database in Read_only mode for few days (just in case) on old server and finally dropped it on 5-20-2007 and I think I forgot to drop the associated logins. I started seeing login failure for user 'xyz' in error logs
When I first noticed the login failure error in SQL Server log for login xyz, I deleted xyz login but it did not stop the errors.
I have been trying from then and no luck in identifying the cause/ resolving this issue.
I have ran SQL profiler trace and caught the user hostnames, NTusername in few cases and Application Name and contacted the Application owner & user (who are in the trace) to stop windows service/ schedule jobs..anything that is pointing to old server but the bad luck is they are not aware of anything running or pointing to old server. The worst part is the user whose hostnames are shown in the trace have never used ABC database and do not have any idea.
Here is what I found in the profiler trace:
TextData LoginName NTUserName HostName ApplicationName
Login failed for user 'xyz' xyz AB00007 WAB000007 Microsoft (r) Windows Script Host
Today I have created the xyz login in the server and assigned model database with reader permission to see if log some different error but nothing new (the same login failure error)
Finally, I had no solution other than restoring the ABC database back to my old server and set it in to Read_Only mode to stop these errors and Now I see the login 'xyz' firing the query against the database
* Any hint or pointers why this is happening and any possible solution on this?
View 1 Replies
View Related
Aug 9, 2006
We have a table in our legacy database system representing health insurance polices. The customer can, and usually does, renew the policy after 12 months. The legacy database uses the renewal string "99/99/9999" to signify "continuous until cancelled", in other words, "forever", or until cancelled
We need to convert this legacy table into a sql 2005 table, which supports the concept of "forever". But how can we do this? ("99/99/999" is not a valid sql date type and we don't want to use varchar for dates.)
TIA,
barkingdog
View 1 Replies
View Related
Jun 26, 2014
Discuss the following sql query with respect to performance in an applicaiton involving more number of concurrent users creating and deleting records. The objective is to create continuous primary key integer values.
Table name: SitePage
Column DataType
--------- -----------
PageID BigINT
PageName nchar(10)
Query to insert new record
DECLARE @intFlag INT
SET @intFlag = 0
WHILE (@intFlag =0)
BEGIN
BEGIN TRY
[Code] ....
We don't want to use auto increment integer value for primary key because of the following reason
[URL] .....
We also don't want to use SEQUENCE as we have to create 50 sequence for 50 tables
We can't do trace flag 272
View 9 Replies
View Related
Aug 21, 2015
Paper is 21cm x 9cmPrinter is Epson LX-300..When I set this paper size, SSRS turns orientation to landscape and prints as if clockwise right rotated!I tried creating custom paper on print server options without success. I also tried setting the same paper size in Report Builder and Print Server but failed again.
View 3 Replies
View Related
Nov 6, 2007
I have a report that has one subreport. I am finding that if the entire content of the subreport will not fit within the space remaining on the page that it will not start displaying data from that subreport until the next page of the report, leaving a blank section in the report. I would like it to display as much as possible on the first page and then continue on subsequent pages.
Is there a way?
View 11 Replies
View Related
