I need a script to pull the above records ONLY if the date repeats more than one within the same SEQ number. The proper script would output as follows:
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.
We have some requirement in PowerBI reports. Here we have a table and having Date, Events columns. Below is the sample data..we are creating a measure to calculate the average of the event count for month.We need a measure for calculating Average of Event count per month= sum(Events for a month)/numberofdays in the month.Example for January month : sum(343423)/31 (31 number of days in January)
When we write this measure using DAX query in Excel we are getting semantic error.Tried sample formula : Average:=SUM([Events])/EOMONTH([EventDate],1)
writing this DAX command for measure.After having this data ready, we are creating PowerBI reports on this data.
I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.
1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for? 2) CTE doesn't work on SQL 2000. I need a query that does. 3) I cannot modify the database itself in the process.
;WITH cte AS ( SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number() OVER(PARTITION BY [LogProcessName] ORDER BY [LogDate] DESC) AS RN FROM [LOGTABLE] WHERE [LogTypeGUID] IN ( SELECT LogTypeGUID FROM LOGTYPE WHERE LogTypeName = 'Process Status Start' OR LogTypeName = 'Process Status End' ) ) SELECT * FROM cte WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6 ORDER BY [LogProcessName] DESC, [LogDate] DESC
Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?
I am trying to get 10 appointment dates for people. I can pull the data but im pulling all appointments. I need to find a way to just pull 10 appointments per instructor, as I have multiple instructors. Here is what I have. DECLARE @STARTDATE AS DATETIME DECLARE @ENDDATE AS DATETIME SET @STARTDATE = DATEADD(m, DATEDIFF(m,0,GETDATE())+1, 0) SET @ENDDATE = DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, GETDATE()))+1, -1) SELECT INSTRUCTOR STUDENT_NAME, STUDENT_NUM, CONVERT(CHAR(10),[DATE],101) AS Appt_Date, APPOINTMENT_TIME, TEL,ADDRESS1, ISNULL(ADDRESS2,'') AS ADDRESS2CITYST, ZIP
FROM APPTS WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE
I'm trying to pull all records from one table and just a single record from another. I have this join, (see below). It works ok, but the problem is if a blog record doesn't have a corresponding image record it doesn't return. The end result should be the blog record and a single corresponding image record. But always a blog record.
Table 3: ID RoleID Time 1 1 09:14 2 1 09:15 1 2 09:16
Now I want all the records which belongs to RoleID 1 but if same ID is belongs to RoleID 2 than i don't want that ID.From above tables ID 1 belongs to RoleID 1 and 2 so i don't want ID 1.
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
I am looking to pull all records for current & previous calendar year in one query. I know how to pull the current calendar year, but how would I pull current & previous?
Can someone please help me with this? I need a query that will pull clients that made payments last year but not this year. I need the query to use date parameters so I can select any date range for the past year and the current year. I have listed the tables and fields that might be needed: I'm hoping to do this without temp tables. Date range is based on the tblPaymentReceipts.PaymentDate tblClients, ClientID tblPayment, PmtID, ClientID, Paystartdate, Paygroup tblPaymentReceipts, PmtRcptID, PmtID,CleintID,PaymentDate,PaymentAmount
I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.
What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).
I have tried 2 statements:
#1: SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#)) GROUP BY tblTable1.Name, tblTable1.INDate ORDER BY tblTable1.Name;
#2: SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#)) UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate FROM tblTable1 WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));
Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.
Example:
NameINOUT John Smith1/1/20141/2/2014 John Smith1/1/2014(blank)
I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?
I have two tables and I am trying to pull a value closest to another date. There could be instances where the Eval_date in TableA matches the date in TableB so how would that be handled?
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
I have the following table called BADSANTA: varchar(30) datetime NAME WHENBAD OJ Simpson 2007-1-12 xx:xx:xx:xxx OJ Simpson 2007-4-2 xx:xx:xx:xxx Monica Lewinsky 2006-7-4 xx:xx:xx:xxx Monica Lewinsky 2006-10-31 xx:xx:xx:xxx Bill Clinton 2006-7-4 xx:xx:xx:xxx Bill Clinton 2006-10-31 xx:xx:xx:xxx Bart Simpson 2006-11-2 xx:xx:xx:xxx Bart Simpson 2006-2-25 xx:xx:xx:xxx Bart Simpson 2005-07-27 xx:xx:xx:xxx
I want the records of the persons latest WHENBAD date. It should return one recordset for each person.
'17686568 - Bill Statement - 11/16/2006 - Stm. Date - 10/27/2006'
Above is the data string that I am trying to pull the information from. Here is the function I'm currently using:
cast(substring(c.itemname,charindex('Bill Statement - ',c.itemname)+18,10) as varchar)
...which gives me what i want most of the time, but beacuse the date is not equally formatted through out the database and the date can look like 5/4/2012 and using the above formula it will show up as 5/4/2012 -
So my question is how can i trim off the dash part when the data shows up like 5/4/2012 - ?
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.
as you can see, the records have a 30minutes time interval. i need to create a query to know if there are missing records in the table. so basically the result should be this:
HI EVERYBODY This is my procedure " CREATE PROCEDURE SP_SAMPLE_SEARCH @Title nvarchar(256) AS
SELECT ID,Title,Price FROM [tbl_Sim] WHERE ([Title] LIKE '%' + @Title + '%') Order by Price desc GO " I exec procedure and it returns 12 results with diffirents ID and want to get these values 1,2,3....12 How do I get these... I am a beginner. Thanks for help..
Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005 --i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here? -i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how? cmd.Parameters.Add(new SqlParameter("@returnVal", SqlDbType.Int)); cmd.Parameters["@returnVal"].Direction = ParameterDirection.ReturnValue; Please help me out with this.Thanks
hi can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.
Hi... I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn´t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ¿How i can khow the number of records of a RecordSource gets?... and ¿How I can store in a variable a field value retrived for a RecordSource?... I´m using VWD and SQL Express that means ADO.NET 2.0... cheers.
I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
