I would like changing the date range to be -1 year from today until today
This is the current WHERE, AND
WHERE [entry].[Posting Date] >= DATEADD(YEAR, YEAR(GETDATE()) - 1900, 0)
AND [entry].[Posting Date] < DATEADD(YEAR, YEAR(GETDATE()) - 1899, 0)
I have some location assignment data that I need to convert. I need to know how long each account spent in a certain location for each month of it's overall startdate/enddate period.
E.g.
Account 1 stayed in USA for 31 days in January, and 15 days in February.
Account 1 stayed in UK for 13 days in February and 26 days in March.
Etc.
create table #temp(account int, loc varchar(10), startdate datetime, enddate datetime)
insert into #temp select 1,'USA','2014-01-01','2014-02-15'
insert into #temp select 1,'UK','2014-02-16','2014-03-26'
insert into #temp select 1,'AU','2014-03-27','2014-06-07'
insert into #temp select 2,'UK','2014-08-15','2014-09-01'
insert into #temp select 2,'AU','2014-09-02','2014-10-17'
select * from #temp
drop table #temp
I have a table with EmployeeID, StartDate, and EndDate with a PK of EmployeeID, StartDate. How can I check to see that there's no overlap for StartDate and EndDate for a given employee? That is, on any given day there must only be 1 row for an employee where Getdate() is Between StartDate and EndDate. For an active employee their EndDate is set to 06/06/2079.
I've tried it using Row_Number() with Over() but am returning too many rows indicating overlap when none exists.
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
[Code] ....
Expected outcome
fmc_terminalfmc_prodlnkfmc_cost StartDateEndDate
1A2.25 NULL2014-12-03 00:04:00.000
1A2.26 2014-12-03 00:04:00.0002014-12-03 11:33:00.000
And so on….
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
Table B
Item ProcessStartDate ProcessEndDate
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
I am using Sql Server 2012.
This is how I calculate the ratio of failures in an order:
31 Days Table 1 query
sum(CASE
WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .
How do i loop and pass dates dynamically in the Datediff?
31 Failures Table 2 query
SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102))
AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2
((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.
I want a similar kind of calculation done for day wise and month wise.
2. what approach should I be using to achieve day wise and month wise calculation?
I do also have a table called Calender with the list of dates that i can use.
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
I have two tables, customers and appointments.
I want to bulk insert records for a date range and a day of the week.
For example, John Smith has an appointment on every monday for the next three months. How do I accomplish this?
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!!
View 6 Replies View RelatedI've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
I have a table namely "Information" , it has one field namely " Lastupdate" when i insert or update data , lastupdate column takes takes current datetime value. and this data i am displaying in another page . Here i want to display the information upto 21 days from last updated dates. means if i save data today (12/04/2008) then the "lastupdate" value will be (12/04/2008) and when i display this data should display upto 21 days means upto (23/04/2008) when date 24/04/2008 will come, this data should not display. and i want this from sql server query , if anybody have idea please write query for that , its very urgent ..............
View 2 Replies View RelatedI am having some difficulty getting a query to output an alpha numeric range grouping.
I have this data set:
Despatch_id Sample_ID
MIR00831 MCR0005752
MIR00831 MCR0005753
MIR00831 MCR0005754
MIR00831 MCR0005755
MIR00831 MCR0005756
MIR00831 MCR0005757
[code]....
Output:
DESPATCH_ID SAMPLE_ID_FROM SAMPLE_ID_TO
MIR00831 MCR0005752 MCR0005762
MIR00831 MCR0005803 MCR0005806
MIR00831 MCR0005808 MCR0005813
They need to be grouped by range specific to the alpha numeric part, which can vary within the same despatch. I was thinking of using a row over partition after splitting the numeric and alpha part and to check if they are consecutive and build the range. But I am thinking that this approach is an overkill and there may be a better way to achieve this in SQL 2012.
I have included the create table scripts and example data below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SAMPLE_TABLE](
[DESPATCH_ID] [nvarchar](30) NOT NULL,
[SAMPLE_ID] [nvarchar](30) NOT NULL
[code]....
I have column which stores People count based on department, Now I want to keep them in the batch of 1000, If the running summary of (No of people) from departments reached 1000 then it should start sum(no of people) from 0 to 1000
Is there any running summary kind of function which can start sum record with in range of 0-1000
For Ex. My Data stored like this
Dept People Count
CSE 200
IT 250
EEE 312
ECE 214
MEC 337
Batch Grouping
Dept People Count BatchSum
CSE 200 200
IT 250 450
EEE 312 762
ECE 214 976
MEC 337 337 (Note here since its crossing 1000, its resetting and starting summary)
I implemented this with While Loop & if condition, But its very slow, is there any other way to achieve it in better way.
Given a Table1 with two columns 'Name' with some N rows of data and another Table2 with one column 'SeqNo' with N rows, each of which contains a unique integer which can be ordered monotonically, I want to do an INSERT into some Table3 with two columns 'Name' and 'SeqNo' such that each INSERT'd row gets one of the unique integers.
E.g. -
Table1 contains 'Fred','Tom','Mary','Larry'
Table2 contains 6000978,6000979,6000980,6000981
INSERT INTO Table3
SELECT Table1.Name
Table2.SeqNo
FROM Table1
And I want to get Table3
'Fred',6000978
'Tom'.6000979
'Mary',6000980
'Larry',6000981
How can I reference Table2 so that Table2.SeqNo will 'line up' properly? Note that the ordering of the SeqNo values isn't mandatory as long as each SeqNo is assigned to one and only one row.
On edit: Table2 isn't required, it's just the way I started thinking about it. It would be nicer to just have two integer vars, @StartSeqNo = 6000978 and @EndSeqNo = 6000981 for he example above. Either way is fine.
I wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.
The data looks like:
ABCD-001869202
ABCD-001869203
ABCD-001869204
ABCD-001869205
ABCD-001869209
ABCD-0018692010
ABCD-0018692011
ABCD-001869309
ABCD-001869310
ABCD-001869311
ABCD-001869312
ABCD-001869313
ABCD-001869314
Desired out put:
From To
ABCD-001869202 ABCD-001869205
ABCD-001869209 ABCD-0018692011
ABCD-001869309 ABCD-001869314
I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:
SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher
INTO #tempVoucher
FROM LEDGERJOURNALTRANS
where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'
[Code] ...
I need to provide a minimum value over a 12 hour time range of data. I'm struggling with performance issues due to the amount of data. Currently I log about 100 devices reporting once per minute into a table. Also about once per minute I need to pull the minimum value reported for each device in the last 12 hours. Currently I'm maintaining a separate table with entries for just the last 12 hours and just performing a Select Min(Temp) Where DeviceID=x, but it already holds about 700,000 records at any given time. The number of devices will increase substantially and this will no longer be viable.
Sample Table
ID DeviceID Temp InsertDate
1 10 55 04-28-2015 8:00 AM
2 65 74 04-28-2015 8:00 AM
3 44 23 04-28-2015 8:00 AM
4 10 87 04-28-2015 8:01 AM
5 65 65 04-28-2015 8:01 AM
When I run this query:
SELECT orderno, *
FROM _order
WHERE order_date >= '5/14/2015 00:00:00'
AND order_date < '5/15/2015 11:59:59'
ORDER BY order_date asc
I get a result of:
A1G7222015-05-14 13:00:11.143
A1G7232015-05-14 13:33:35.407
A1G7242015-05-14 13:39:16.657
A1G7252015-05-14 14:25:43.507
A1G7262015-05-14 14:29:18.050
A1G7272015-05-14 15:38:12.263
But I know there is one more record that falls into 05/15/2015
A1G7282015-05-15 12:26:52.807
Can you see what I am missing in my query in order for me to retrieve the missing record A1G728?
We have a job running the SSIS Package. I am working with that SSIS Package. The SSIS package has a script task. In the script task they used the variable of source path. I want to change the value of that variable (Need to modify the source path).
I modified the variable value in that package and in the config file but getting error message
Exception has been thrown by the target of an invocation.
I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:
What I want to accomplish:
I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.
For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.
I have a ssis package stored in file system and I need to modify this package.
View 7 Replies View RelatedDate parameter. I created a report that allows a drop down for a date range to be selected. However, whenever I preview the report, I get an error. I know my error stems from my date fields being in this format "201301" , and the "date/ time" in SSRS being mm/dd/yyyy on the drop down calendar in SSRS.
I know the direction I want to go in, but just a little confused on where would I use the convert or cast function. Would it be in the data parameter itself, or a part of the query before the @start date and @End date?
I would like to be able to search by a single date, @StartDate, or by a date range , between @StartDate and @EndDate. I am having a hard time with the logic on this for a report in SSRS.
View 5 Replies View RelatedI have to display the last temperature reading from an activity table for all the dates in a selected date range.So if I select the date range from 09/01/2012 to 09/30/2012, the results should look like this:
Date Temperature
09/01/2012 73.5
09/02/2012 75.2
09/03/2012 76.3
09/04/2012 73.3
09/05/2012 77.0
09/06/2012 74.5
and so on.
I am using this to get the dates listed:
WITH CTE_DatesTable
AS
(
SELECT CAST('20120901' as date) AS [Date]
UNION ALL
SELECT DATEADD(dd, 1, [Date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [Date]) <= '20120930'
)
SELECT [Date]
FROM CTE_DatesTable
How could I get the temperature if I did a sub-query here?
I have a report that I need to run on 2 different date ranges.
Both report's data is 2 days behind today's date.
so...
WHERE reportdate between dateadd('d',date(),-2) and dateadd('d',date(),-2)
OR SOMETHING LIKE THAT, NO BIGGIE HERE
The 2nd report is a month to date report. This is the 1 I can't figure out.
WHERE reportdate between (the first day of this month) and dateadd('d',date(),-2)
So that would look like
WHERE reportdate between 1/1/2007 and 1/21/2007
My problem is, if today is the 1st day of the month... how can I get my critiera to NOT do this
WHERE reportdaye between 2/1/2007 and 1/30/2007
Any help would be greatly appriciated!
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)
View 2 Replies View RelatedHave seen other questions here about modifying date pickers supplied by reports created in BIDS. The answer is usually NO. But this does not involve a format change, simply want to limit say to a specific year.
Any ideas?
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
[code]...
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'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?
The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.
Is this even possible?
Hi;
We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.
I have a CommisionAudit table that tracks changes in commission rate with the following values.
ID | Commission Change | UpdatedOn
----------------------------------------------
1 | Change from 20->25 | 03/07/2007 09:00:00
----------------------------------------------
2 | Change from 25->35 | 10/09/2007 17:00:00
----------------------------------------------
3 | Change from 35->20 | 01/10/2007 16:00:00
----------------------------------------------
4 | Change from 20->26 | 11/10/2007 10:00:00
----------------------------------------------
with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.
I have a order table which has a orderdate and despatchdate
i want to write a query in such a way that i want to get all the details from the table for a range specified date as the oderdate and despatchdate are user
interactable. I search the google to solve this problem but could not find a answer
What is the best way to do a where clause that includes a date range. Ex. WHERE date1 BETWEEN @Begin Date AND @EndDate. I want to include all of the @EndDate.
View 6 Replies View RelatedI am working on a report for staff productivity, and have to get a summary figure for how much productivity was expected for a date range. The problem is that the amount expected from an employee can change if they move from full time to part time etc.
So I have a view that has the begin date, end date, expected daily production # by employee, and have to figure out how to get the multiplication to work correctly.
Example:
Employee 1 had a daily production # of 10 from 1/1/07-3/31/07 and daily production of 5 from 4/1/07 - now
If I run the production report for 1/1/07 - 6/30/07 what I want is one summary figure of for the entire range which would be 10*Datediff(d,1/1/07,3/31/07)+5*Datediff(4/1/07,6/30/07) or 890+450=1340.
Of course the actual date range for the report will be a variable, and the dates for the begin and end of a production date range will be all over the place.
Any quick and easy way to do this?