I want to be able to pull the records with the earliest date 2011-02-06 ...
There were other records with this same customer and item number. I used this script to return the two above.
select *
from price
where end_dt > getdate()
Now I need to add something so it only returns the record with the earliest date. I'm going to run this on a table that has many customer and item combinations.
I want to update the STATUS column based on min date in my table.
For example – If minimum BOOKING_DATE of any RecieptID is below to 2015-10-01, then Status of that RecieptID should be 0 for all records pertaining to dedicated RecieptID So I need an output in this way.
This is the code I have written and I am trying to retrieve minimum count of PQpageId for every hour for a given date of range.
WITH CTE AS ( SELECT PQIM.PQPageID ,PQIM.PageURL as PageDescription ,CONVERT(Date,NCPI.RequestDateTime) AS [Date] ,DATEPART(HOUR,NCPI.RequestDateTIme) AS [HOUR] ,ISNULL (COUNT(NCPI.PQPageID),0)AS HourlyPQPageIdCount FROM dbo.NewCarPurchaseInquiries AS NCPI WITH (NOLOCK)
For ex: Pqpageid 1, at 8am on date 4-11-2015 has a count of 2359 and on 9-11-2015 at 8 am it has count 54then it should return date 9-11-2015 and count 54 for hour 8 like wise for every pageid and hour from 8 to 23 it should return the min count from given date of range.
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID StartDate 1 2014-01-01 1 2014-02-01 1 2014-05-01-------After Gap Restarted 1 2014-06-01 1 2014-09-01---------After last gap restarted 1 2014-10-01 1 2014-11-01 2 2014-01-01 2 2014-02-01 2 2014-03-01 2 2014-04-01 2 2014-05-01 2 2014-06-01 2 2014-07-01
For Id 1 the start date after the latest gap is 2014-10-01 and for id=2 there is no gap so i need the minimum date 2014-01-01
My Expected Output id Startdate 1 2014-10-01 2 2014-01-01
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
need help help me -CURSOR backward insert from End Date > to Start Date how to insert dates from end to start like this SELECT 111111,1,CONVERT(DATETIME, '17/03/2008', 103), CONVERT(DATETIME, '01/03/2008' i explain i have stord prosege that create mod cycle shift pattern and it working ok now i need to overturned the insert so the first insert is the '17/03/2008' to '16/03/2008' ..15...14..13..12...2...1 so the first insert be '17/03/2008' next '16/03/2008' ...........................01/03/2008
tnx
Code Block DECLARE @shifts_pattern TABLE ([PatternId] [int] IDENTITY(1,1 ) NOT NULL, [patternShiftValue] [int]NOT NULL) declare @I int set @i=0 while @i < 5 BEGIN INSERT INTO @shifts_pattern ([patternShiftValue] ) SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 set @i=@i+1 end declare @empList TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL) INSERT INTO @empList ([empID], [ShiftType],[StartDate],[EndDate]) SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '17/01/2008', 103) -- create shifts table declare @empShifts TABLE ( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL ,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL) DECLARE @StartDate datetime DECLARE @EndDate datetime Declare @current datetime DEclare @last_shift_id int Declare @input_empID int ----------------- open list table for emp with curser DECLARE List_of_emp CURSOR FOR SELECT emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp OPEN List_of_emp FETCH List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate SET @current = @StartDate ----------------- -- loop on all emp in the list while @@Fetch_Status = 0 begin -- loop to insert info of emp shifts while @current<=@EndDate begin INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate]) select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate from @shifts_pattern as shift where PatternId=@last_shift_id+1 -- if it is Friday and we are on one of the first shift we don't move to next shift type . if (DATENAME(dw ,@current) = 'Friday' ) and EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3)) -- do nothing --set @last_shift_id=@last_shift_id print ('friday first shift') ELSE set @last_shift_id=@last_shift_id+ 1 set @current=DATEADD( d,1, @current) end FETCH List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate -- init of start date for the next emp set @current = @StartDate end CLOSE List_of_emp DEALLOCATE List_of_emp select empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift RETURN
hi, I am getting a data in a dbf file and they have a StartDate and end Date for where the Statments are valid for.. How can i incorporate them into the database.. right now We are doing with PeriodId.. like while the user imports the data they select which period they want to import the data into.. But now if i have to get those details from the Database how do i store it in the Database..and i need to store them in multiple tables.. Thanks Karen
Let's say the first row returned has StartDate = 1/1/2014 and EndDate is 1/2/2014. The next row I want the StartDate to equal the previous row EndDate so it would be 1/2/2014 as StartDate. This compounds every row basically the third row StartDate would be the second row EndDate. All in one select statement if it can be done. Using SQL2008r2.
Hi all I have an application which takes start time from a different date. My actual requirement is "How to take start time someday in august or other month if the current day is in the month". I will be thankful to any help provided.
In my report I need two parameters, StartDate and EndDate to display a certain period of sales for example. How can I enforce the fact that once the user picks a StartDate, the EndDate cannot be prior to StartDate and vice versa?
I have a set of MS SQL reports, that need to always run on a certain day of the month. Generally the 20th. If the report was to run few days before the 20th, say on the 10th, I wish to retrieve those days between the 20th from the previous month, till the current date.
e.g: '2015-4-10' should only return 20 days worth of data.
I have tried the following query:
SELECT DATEADD(D, 1, MAX(CAST(DateTimeStamp AS DATE))) As EndDate, MIN(CAST(DATEFROMPARTS(DATEPART(YEAR, DateTimeStamp),DATEPART(MONTH, (SELECT CASE WHEN DATEDIFF(DAY,DATEPART(DAY, GETDATE()),28) <0 THEN (SELECT DATEPART(MONTH, GETDATE())) ELSE (SELECT DATEPART(MONTH, GETDATE()) -1) END AS Date)),28)AS DATE)) AS StartOfMonth
FROM tbLogTimeValues WHERE DATEPART(YEAR, DateTimeStamp) = DATEPART(YEAR, DATEADD(M, -1, GETDATE()))
Which parses ok and managed to test all individual queries, however, as a whole, I get the following error message "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
I am having some probelms getting script to give me the first and last date a customer had an outstanding item. The data is as follows:
Customer StartDate EndDate A 4/1/04 4/15/04 A 4/15/04 5/1/04 A 5/1/04 5/15/04 A 5/16/04 5/28/04 A 5/28/04 6/5/04 B 5/1/04 5/15/04 B 5/16/04 5/20/04
The results I am looking for would be as follows:
Customer A : Outstanding 4/1/04 - 5/15/04 Customer A : Outstanding 5/16/04 - 5/28/04 (Theres a one day gap between prior sting, so this would be a new string ) Customer B : OUtstanding 5/1/04 - 5/15/04 Customer B : Outstanding 5/16/04 - 5/20/04
I want to include any strings where the start of one item and the end of another are on the same day as one continuis string. Any ideas on how to do this??
how to generate date backward from end to start like this begin -- loop to insert date backward while @end_date>=@start_Date begin INSERT INTO @tb_temp
from middle of the month to end of the month
serial date ------------------------------ 1 19/03/2008 2 18/03/2008 3 17/03/2007 .............. 19 01/03/2007
and put it in this part of code tnx
Code BlockDECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
SET @current = @StartDate
-----------------
-- loop on all in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
while
@current<=@EndDate
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
I got a DATE question here... In Oracle, all dates are based upon a fixed point in time. So internally a specific date is stored as a REAL number (which is something like the number of seconds from that specific date onwards).
How can I change that start date upon which all dates in Oracle are based?
SQL Server 7 has something similar. How can I change the start date for SQL Server?
I have a scenario where i need to get the starting and ending date time based on the crieteria. The criteria is I always have my start date as NS or GS in the data column and my end date as GX so i need NS or GS to be my strart date based on ts Ascending and my end date as GX to be displayed in the same columns .
Create Table Test (Tsq INT IDENTITY (1,1), Data Varchar (150), ts datetime, Tpkt_type int) insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2')
I'm new to sql. Can someone help me to write a script to select overlapping start dates for each client records. For example: Clientid 1 have 3 episode as below(I only want to see the first two records with overlapping start date records)
I have weekly billing cycle. And the billing cycle can start on any day of the week.
Assumption: Start day of week is MONDAY
Hence, the following
DayNumber Day 1 Monday if start day is 1, then billing cycle is Monday - Sunday 2 Tuesday if start day is 2, then billing cycle is Tuesday - Monday 3 Wednesday if start day is 3, then billing cycle is Wednesday - Tuesday 4 Thursday if start day is 4, then billing cycle is Thursday - Wednesday 5 Friday if start day is 5, then billing cycle is Friday - Thursday 6 Saturday if start day is 6, then billing cycle is Saturday - Friday 7 Sunday if start day is 7, then billing cycle is Sunday - Saturday
For a given date, i need find start date and end date of current billing cycle.
For example, if my billing cycle starts on 3rd day of week (wednesday), then for the input 09-Oct-2015, i need to get two output 07-Oct-2015 and 13-Oct-2015
I have a transaction log that tracks issues from a call center. Eachtime an issue is assigned to someone else, closed, etc. I get a timestamp. I have these time stamps for the beginning of an issue to theend of an issue and I'd like to determine how many business hours theseissues were open.Issue BeginDt Enddt Total hours1 3/29/05 5:00 PM 4/1/05 2:00 PM 69Basically, this is the type of data I'm looking at and my hours of workare from 7:30 - 5:00 weekdays. I need to come up with a way to removeall nonbusiness hours, weekends, & holidays from the difference of thetwo dates. Issues can span for 2-3 days or 20-30 days.Please let me know if anyone has any ideas or has done something likethis before.Thanks!
I use MS certificate server to request/make server certs but the "not before", or start date is tomorrow for a 1 year cert. I dont care how long but I want the cert to start immediately (today).
I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1
I have a SSRS report using 2008 R2. It prompts the user for the start and end dates. This all works. But now I want the start date parm to default to the first day of the current month and the end date parm to default to the last day of the current month.In the new query window in SQL Server Management Studio, I can run this chunk of code to get the first day of current month:
I have a detailed report in ssrs in which data can come from start date and end date parameters.
but the problem is. for example i gave startdate as 01/01/2015 and end date as 09/31/2015 then the data must be in displayed in such a way that jan month in one tab and feb month data in one tab and sooo on to sep month data in new tab when i export to Excel.
I have a ssrs report which has a parameter start date.i have made 12 columns acc to client req which i cannot change:
jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec
These columns are dynamic acc to start date for which i have used this exp for first cell:
=monthname(month(Parameters!Startdate.Value),true) and for next 11 cells accordingly.
These 12 columns are grouped quaterly which i have made statically again for clien req which i cannot change so they are grouped staticaly by me in report like:
Now when i select my start date of months jan,apr,july and oct report work fine.... but when i select my start date months of feb,may,aug and nov then reportis something like: (lets say i choose feb)
I am trying to use the custom code in the report but I don't think I am understanding how this is being used.
I have a function to get starting months for a report parameter. The function is below:-
-------------------------------------------------------------- Shared Function GetStartingMonths() as String dim strDefault as string dim CurrentMonth as String Dim SqlString as String
'strDefault = Month(Now) & "/1/" & Year(Now) CurrentMonth = "5/1/2002" Do While CDate(CurrentMonth) <= Now SqlString = SqlString + "Select " & CurrentMonth & " as value, " & MonthName(Month(CurrentMonth)) & " " & Year(CurrentMonth) & " as MonthYear" CurrentMonth = dateadd("m",1,CDate(CurrentMonth))
if Cdate(CurrentMonth) = Now then Exit Do else sqlString = SqlString & " Union " end if
Loop
return SqlString
End Function --------------------------------------------------------------- what i am trying to do here, and hopefully produce a sql string that would fill my dataset of dates and their representation.
In the dataset, I had put the following expression =Code.GetStartingMonths()
However, I can't seem to get the parameter to display the dates. shows up as disabled in my report.
Am I doing something wrong here or is there a better way to doing this ?
Additionally, I was wondering whether there is a better SQL code that would achieve the same thing I am doing ?
