I have the following SQL statement that works great for my purpose:
SELECT DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0)) AS TimePeriod, AVG(ZNT) AS Expr1, AVG(SAT) AS Expr2, AVG(RAT) AS Expr3,
AVG(RH) AS Expr4
FROM HVACValues
GROUP BY DATEADD(hh, DATEPART(hh, Date), DATEADD(d, DATEDIFF(d, 0, Date), 0))
ORDER BY TimePeriod
I'd like to do everything above but add to display only those records from 1/1/07 to 1/7/07...so a week's worth.
I'm no SQL expert but I assume that I'd add 'WHERE TimePeriod = ....' Any help is much appreciated!
I need SQL to determine what the date range for the previous week was, ie Start date 11/11/01 for Last Sunday through 11/17/01 the following Saturday. The results will populate a drop down box for user queries. Thanx.
How do i get only the records for everything with only the dates from last week (monday-friday)? I want to have this run every week for the previous week. Here's my stored procudure. Right now i have this to run for the day before, but now i need it for the week before. So its a range. Please Help. Thanks!
Code Snippet CREATE PROCEDURE [dbo].[Testing_Out_Of_Stock_SKUS_WEEKLY] (@Classification varchar(50)) AS BEGIN SELECT RC_STAT.dbo.Brand_Dimension.Report_Level, RC_STAT.dbo.Brand_Dimension.[Cat vs Dog], RC_STAT.dbo.Brand_Dimension.Item_Merged, Qry_Sales_Group.Region_Key, Qry_Sales_Group.Region, Qry_Out_Of_Stock.product_structure_level, Qry_Out_Of_Stock.product_entity_code, Qry_Out_Of_Stock.cycle_day, Qry_Out_Of_Stock.customer_code, Qry_Out_Of_Stock.description, Qry_Sales_Group.Code, Qry_Sales_Group.SR_Name, Qry_Sales_Group.Name AS Territory_Name, Qry_Out_Of_Stock.Store_Name, Qry_Out_Of_Stock.time_log, Qry_Out_Of_Stock.out_of_stock, Period_Code FROM RC_STAT.dbo.Brand_Dimension INNER JOIN dbo.Qry_Out_Of_Stock ON RC_STAT.dbo.Brand_Dimension.Item_Key = dbo.Qry_Out_Of_Stock.product_entity_code COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN dbo.Qry_Sales_Group ON dbo.Qry_Out_Of_Stock.sales_person_code = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code COLLATE Latin1_General_CI_AS Where Classification=@Classification DateDiff(dd,0,dbo.Qry_Out_Of_Stock.time_log) = case when Datepart(Weekday, Getdate()) = 2 then datediff(dd,0,GetDate()) - 3 else datediff(dd,0,GetDate()) - 1 end
I don't know if this can be done, but hopefully one of the experts in this forum cna help me out.
I have a TimeOff table which contains 5 fields: timeoffID, employeename, startdate, enddate, and timofftype. I need to create a report which shows how many people take sick day (one of the timeofftypes) on each week day (i.e. Monday, Tuesday, Wednesday, and etc.) The problem is that I don't know how to convert the date range (from startdate to enddate) into indvidual week day for each timeoff record.
For example, on the table,
TimeoffID employeename startdate enddate timeofftype 1 Andy 11/02/2006 11/03/2006 Sick Day 2 Bill 11/03/2006 11/03/2006 Sick Day
I require some scripting to return data which sits in a rolling weeks date range of the current week.
For example, on a Monday I want to return data from the previous day (Sunday to the previous Monday). On a Tuesday I want to return data from the previous day (Monday to the up coming Sunday). On a Wednesday I want to return data from the Monday (previous 2 days ago to the upcoming Sunday) On a Thursday I want to return data from the Monday (previous 3 days ago to the upcoming Sunday)
And so forth.
So I have my SQL script which works with static, fixed date ranges. But I need this additional dynamic date range logic built in.
We have records in one table that are marked as accepted/rejected based on eligibility start and end dates in another table. We're loading new eligibility data into an ETL table and if the start or end date is going to change, I want to report any records that need to be reviewed to see if their status should change. The new dates could be before or after the existing dates, and the new or existing end date could also be NULL. Currently I'm using 4 > < statements and it seems to catch any scenario, but I'm wondering if there's a better way:
DECLARE @RECORDS TABLE(RecordDate date,ID varchar(8)) INSERT INTO @RECORDS(RecordDate, ID)VALUES('20100101','99'),('20110101','99'),('20120101','99'),('20130101','99'),('20140101','99') DECLARE @ORIGINALDATES TABLE(StartDate date,EndDate date,ID varchar(8)) INSERT INTO @ORIGINALDATES(StartDate,EndDate, ID)VALUES('20100101',NULL,99)
Using SQLServer ver 7.0, two tables:TableA = contains all inventory dataTableB = contains four fields: ID, source, date_from, date_toThis is where multiple range of dates are populated.Sample 1:1,'A','9/1/2004','9/30/2004'Sample 2:2,'A','1/1/2003','3/31/2003'3,'A','10/1/2004','10/31/2004'Data populated on TableB varies.Sample SQL for Sample 1:SELECT *FROM TableAWHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (selectDATE_TO from TableB)Problem: How to approach sql statement based on Sample 2 above?
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'm apologizing in advance for using someone else's brainpower on what is basically a logic problem rather than a creative use of T-SQL, but it's doing me head in.I have a table which contains information on bookings for runs of advertising. This includes a BookingStart dateTime field and a BookingEnd datetime field. Between BookingStart and BookingEnd an ad is considered to be "live".What I need to do is construct a query which, given a start date and and end date returns a count of all the records which were "live" between those dates and - here's the catch - each week must count separately. In other words if my query asks for data across four weeks, and an ad was "live" for two of those weeks, it should return a count of two.Any help much appreciated.Cheers,Matt
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'm trying to add random dates to date column in existing table, but these need to be week days (Mon-Fri).I'm a beginner in TSQL, worked with MS Access many years - in Access I used to do something a bit different:
DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#) Table had ID, I gave a date it would start from (31/08/2015) and then range of ID to apply new date: UPDATE table1 SET table1 .date = DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#) WHERE (((table1 .ID) Between 1 And 5456));
This was applying random dates in range of 31/08/2015 + 5 days, so I could give a starting date of Sunday to get random dates populated over given IDs from Monday to Friday.Now, how can I do it in TSQL?I have a table with ID and dates column. I would like to apply new random dates from some range, but making sure they will be week days.
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 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.
Date 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.
I need to write a user defined function that will return the date of the first day of a week when provided with the week number. I had an idea of using a while loop with that will keep adding 1 day from January 1st until the week number (found via the DATEPART function) is equal to the supplied week number. Not sure if this is the best way though - any ideas?
I'm wanting to see if there is a simpler way to accomplish the retrival of the first day of the week Date. What I currently have is:
CASE WHEN DATEPART(DW, GETDATE()) = 1 THEN DATEADD([D], - 7, GETDATE()) WHEN DATEPART(DW, GETDATE()) = 2 THEN DATEADD([D], - 8, GETDATE()) WHEN DATEPART(DW, GETDATE()) = 3 THEN DATEADD([D], - 9, GETDATE()) WHEN DATEPART(DW, GETDATE()) = 4 THEN DATEADD([D], - 10, GETDATE()) WHEN DATEPART(DW, GETDATE()) = 5 THEN DATEADD([D], - 11, GETDATE()) WHEN DATEPART(DW, GETDATE()) = 6 THEN DATEADD([D], - 12, GETDATE()) WHEN DATEPART(DW, GETDATE()) = 7 THEN DATEADD([D], - 13, GETDATE()) END AS StartWeek1
I'm trying to figure if there might be an easier way to code this. Thanks.
I 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
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)
Have 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 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.
Hey, Im pretty sure this is possible, but let me know if it isnt.I have a table of dates in the format "DD/MM/YYYY", is there a way of converting it to output the day of the week? for example 16/11/2007 would display Friday insted.Thanks in advance John
