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 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.
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
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.
Hi Guys, Can anyone please let me know How to find the first day of the week, if I know the weekno and the year. For ex: If I know the week no is 22 and Year is 2003 then I should find a way to output the date. which is the first day (monday) of that week for the above query the date will be eg:26/06/2003
I really appreciate your Help
Thanks:confused: Find the Date if I know the week and year
I want to convert date to week consider saturday is first day of week and friday is end of week. for input is orderdate and output should be week. example orderdate 11.04.15 to 17.04.15 week should be 16. orderdate 18.04.15 week should change to 17.
Order DateWeek 11-Apr-201516 12-Apr-201516 13-Apr-201516 14-Apr-201516 15-Apr-201516 16-Apr-201516 17-Apr-201516
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 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 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.
I need to run a select on Mondays to pull data for 7 days prior to the Thursday of last week; i.e. Friday - Thursday inclusive. I'm sure this is simple, but I work with dates so infrequently that I need a refressher.