Viewing Job History More Than A Week?
Jul 29, 2013How to see the job history more than a week?
View 4 Replies
ADVERTISEMENT
Power Pivot :: Cumulative Total For Current Week Days And Previous Week
Nov 30, 2015We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week.
I'm trying to produce a line chart with 2 axis:
- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc)
- y axis; the cumulative number of orders
The chart needs two series:
Previous Week. The running count of orders placed that week.
Current Week. The running count of orders placed this week.
Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day.
I have two tables:
Orders TableCalendar Table
The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc.
My measure for counting orders is simply; # Orders: = countrows[orders].
How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:
- [URL] ...
- [URL] ...
But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.
SQL Server 2008 :: Get Week Ending Date Given Week Number
Apr 9, 2013How can I get Saturday's date given the week number?
This is my week number, SELECT DATEPART(WEEK, DATEADD(MONTH, +3, ApptDt2)). I need to get Saturday's date from this week.
T-SQL (SS2K8) :: Get Week Numbers From Dates - Saturday Being Start Of Week
Sep 17, 2015i have the following table I need to select dates grouping them by weeks, my week start is Saturday to Friday
CREATE TABLE weekdays
(
datevalue datetime NOT NULL
, numericvalue INT NOT NULL
);
INSERT INTO weekdays (datevalue, numericvalue) VALUES
[code]....
The output should look like this
weeknototalvalue
362015-09-01 00:00:00.000
362015-09-02 00:00:00.000
372015-09-07 00:00:00.000
372015-09-08 00:00:00.000
382015-09-12 00:00:00.000
382015-09-13 00:00:00.000
382015-09-14 00:00:00.000
392015-09-19 00:00:00.000
Create A Yesterday, This Week, Last Week Calculated Member
May 23, 2007Hi all,
I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.
At the moment, I'm doing this:
Code Snippet
create member [MIA DW].[DATE TIME].[Date].[Yesterday]
as [DATE TIME].[Date].&[2007-01-01T00:00:00]
select [Measures].members on 0,
non empty [DATE TIME].[Date].members on 1
from [MIA DW]
But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?
So I guess there's two questions:
1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)
2)How come the Yesterday member is not returned by the .members function?
Thanks
Greg
How To Stop Week Hours To Continue To Next Week
Jan 29, 2014I am adding week hrs using while loop , but it can continue next week hrs also.
I can get every week start hrs while update @tem1 table
employeeidreportdatereportatleftattoaccountwrhrn
12902014-01-29 00:00:00.00009:3019:1556001
12902014-01-28 00:00:00.00009:0018:4555802
12902014-01-27 00:00:00.00009:0018:455585583
12902014-01-25 00:00:00.00008:0010:0012004 -- week end
12902014-01-24 00:00:00.00009:1718:4554105
12902014-01-23 00:00:00.00009:1918:4654606
12902014-01-22 00:00:00.00009:1718:4754507
12902014-01-21 00:00:00.00009:1618:3552608
12902014-01-20 00:00:00.00009:1818:555435439
My loop statement
while(select MAX(wrh) from @tem1 where wrh = 0) < 1
begin
update @tem1
set wrh = (select toaccount from @tem1
where reportdate = (select min(reportdate) from @tem1 where wrh = 0))+(select max(wrh) from @tem1)
where wrh = (select max(wrh) from @tem1 where wrh = 0 )
and reportdate = (select min(reportdate) from @tem1 where wrh = 0)
end
this is the result while executing loop statement .
employeeidreportdatereportatleftatdehdrhwehwrh
129029 Jan 201409:3019:15008:0009:20024:00065:54
129028 Jan 201409:0018:45008:0009:18016:00056:34
129027 Jan 201409:0018:45008:0009:18008:0009:18
129025 Jan 201408:0010:00005:0002:00045:00047:16 -- week end
129024 Jan 201409:1718:45008:0009:01040:00045:16
129023 Jan 201409:1918:46008:0009:06032:00036:15
129022 Jan 201409:1718:47008:0009:05024:00027:09
129021 Jan 201409:1618:35008:0008:46016:00018:04
129020 Jan 201409:1818:55008:0009:03008:0009:03
How to update only that week hrs , don't continue next week...
How To Get Data Untill Last Week From The First Week Of The Year
Apr 29, 2008In my reports I am extracting the data of number of people joined in all the weeks of the year. And in one of reports I have to extract the data of the number of people joined until the last week from the first week. I am trying out all the logics but nothing is working for me as such. Can any one help me with this issue??????
View 4 Replies View RelatedTransact SQL :: Start Week Of The Month For A Given Week
Jul 23, 2015I need a Select sentence that return me the first week of the month for a given week.
For example If I have week number 12 (Begins 2015/03/16 and Ends 2015/03/22) I need that returns 9, I mean Week number 9 wich is the first week of march (having in mind @@DATEFIRST).
I only need give a week number of the year and then returns the week number of the first week of that month.
Convert Week Number To Date Of 1st Day Of That Week...
Feb 2, 2008
I'm looking for a way to convert the [week number, year] back to the date for the 1st day of that week.
Example (assuming DATEFIRST has been set to 1) : Week 3, 2008 = January 14, 2008
Is there a SQL function that will do this?
Thanks,
Mike
Date Of First Day Of Week For Current Week
Jun 6, 2006seriously now
View 1 Replies View RelatedConvert Getdate To Week And Day Of Week
Aug 15, 2006I have a query that run every day to update a summary table which has week number and day of week. what I currently do is delete all records from the summary table and then summarize all the data availabe from four tables adn then populate the table daily. I want to know if I can run the update query to run only for the week number and day of week depending on getdate. Can I do this?
Please help..
ISO Year Week Day Of Week Function
Jan 18, 2006Function F_ISO_YEAR_WEEK_DAY_OF_WEEK returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed. W01 represents the week of the year from W01 through W53, and D represents the day of the week with 1 = Monday through 7 = Sunday.
The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.
This code creates the function and demos it for the first day, first date+60, and first date+364 for each ISO week/year from 1990 to 2030.
drop function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK
GO
create function dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK
(
@Datedatetime
)
returnsvarchar(10)
as
/*
Function F_ISO_YEAR_WEEK_DAY_OF_WEEK
returns the ISO 8601 Year Week Day of Week
in format YYYY-W01-D for the date passed.
*/
begin
declare @YearWeekDayOfWeekvarchar(10)
Select
--Format to form YYYY-W01-D
@YearWeekDayOfWeek =
convert(varchar(4),year(dateadd(dd,7,a.YearStart)))+'-W'+
right('00'+convert(varchar(2),(datediff(dd,a.YearStart,@Date)/7)+1),2) +
'-'+convert(varchar(1),(datediff(dd,a.YearStart,@Date)%7)+1)
from
(
select
YearStart =
-- Case finds start of year
case
whenNextYrStart <= @date
thenNextYrStart
whenCurrYrStart <= @date
thenCurrYrStart
elsePriorYrStart
end
from
(
select
-- First day of first week of prior year
PriorYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aaa.Jan4))/7)*7,-53690),
-- First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aaa.Jan4)/7)*7,-53690),
-- First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aaa.Jan4))/7)*7,-53690)
from
(
select
--Find Jan 4 for the year of the input date
Jan4=
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
) aaa
) aa
) a
return @YearWeekDayOfWeek
end
go
-- Execute function on first day, first day+60,
-- and first day+364 for years from 1990 to 2030.
select
DT= convert(varchar(10),DT,121),
YR_START_DT =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT),
YR_START_DT_60 =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+60),
YR_START_DT_365 =
dbo.F_ISO_YEAR_WEEK_DAY_OF_WEEK(a.DT+364)
from
(
select DT = getdate()union all
select DT = convert(datetime,'1990/01/01') union all
select DT = convert(datetime,'1990/12/31') union all
select DT = convert(datetime,'1991/12/30') union all
select DT = convert(datetime,'1993/01/04') union all
select DT = convert(datetime,'1994/01/03') union all
select DT = convert(datetime,'1995/01/02') union all
select DT = convert(datetime,'1996/01/01') union all
select DT = convert(datetime,'1996/12/30') union all
select DT = convert(datetime,'1997/12/29') union all
select DT = convert(datetime,'1999/01/04') union all
select DT = convert(datetime,'2000/01/03') union all
select DT = convert(datetime,'2001/01/01') union all
select DT = convert(datetime,'2001/12/31') union all
select DT = convert(datetime,'2002/12/30') union all
select DT = convert(datetime,'2003/12/29') union all
select DT = convert(datetime,'2005/01/03') union all
select DT = convert(datetime,'2006/01/02') union all
select DT = convert(datetime,'2007/01/01') union all
select DT = convert(datetime,'2007/12/31') union all
select DT = convert(datetime,'2008/12/29') union all
select DT = convert(datetime,'2010/01/04') union all
select DT = convert(datetime,'2011/01/03') union all
select DT = convert(datetime,'2012/01/02') union all
select DT = convert(datetime,'2012/12/31') union all
select DT = convert(datetime,'2013/12/30') union all
select DT = convert(datetime,'2014/12/29') union all
select DT = convert(datetime,'2016/01/04') union all
select DT = convert(datetime,'2017/01/02') union all
select DT = convert(datetime,'2018/01/01') union all
select DT = convert(datetime,'2018/12/31') union all
select DT = convert(datetime,'2019/12/30') union all
select DT = convert(datetime,'2021/01/04') union all
select DT = convert(datetime,'2022/01/03') union all
select DT = convert(datetime,'2023/01/02') union all
select DT = convert(datetime,'2024/01/01') union all
select DT = convert(datetime,'2024/12/30') union all
select DT = convert(datetime,'2025/12/29') union all
select DT = convert(datetime,'2027/01/04') union all
select DT = convert(datetime,'2028/01/03') union all
select DT = convert(datetime,'2029/01/01') union all
select DT = convert(datetime,'2029/12/31') union all
select DT = convert(datetime,'2030/12/30')
) a
Function Test Results:
DT YR_START_DT YR_START_DT_60 YR_START_DT_364
---------- ----------- -------------- ---------------
2006-01-18 2006-W03-3 2006-W11-7 2007-W03-3
1990-01-01 1990-W01-1 1990-W09-5 1991-W01-1
1990-12-31 1991-W01-1 1991-W09-5 1992-W01-1
1991-12-30 1992-W01-1 1992-W09-5 1992-W53-1
1993-01-04 1993-W01-1 1993-W09-5 1994-W01-1
1994-01-03 1994-W01-1 1994-W09-5 1995-W01-1
1995-01-02 1995-W01-1 1995-W09-5 1996-W01-1
1996-01-01 1996-W01-1 1996-W09-5 1997-W01-1
1996-12-30 1997-W01-1 1997-W09-5 1998-W01-1
1997-12-29 1998-W01-1 1998-W09-5 1998-W53-1
1999-01-04 1999-W01-1 1999-W09-5 2000-W01-1
2000-01-03 2000-W01-1 2000-W09-5 2001-W01-1
2001-01-01 2001-W01-1 2001-W09-5 2002-W01-1
2001-12-31 2002-W01-1 2002-W09-5 2003-W01-1
2002-12-30 2003-W01-1 2003-W09-5 2004-W01-1
2003-12-29 2004-W01-1 2004-W09-5 2004-W53-1
2005-01-03 2005-W01-1 2005-W09-5 2006-W01-1
2006-01-02 2006-W01-1 2006-W09-5 2007-W01-1
2007-01-01 2007-W01-1 2007-W09-5 2008-W01-1
2007-12-31 2008-W01-1 2008-W09-5 2009-W01-1
2008-12-29 2009-W01-1 2009-W09-5 2009-W53-1
2010-01-04 2010-W01-1 2010-W09-5 2011-W01-1
2011-01-03 2011-W01-1 2011-W09-5 2012-W01-1
2012-01-02 2012-W01-1 2012-W09-5 2013-W01-1
2012-12-31 2013-W01-1 2013-W09-5 2014-W01-1
2013-12-30 2014-W01-1 2014-W09-5 2015-W01-1
2014-12-29 2015-W01-1 2015-W09-5 2015-W53-1
2016-01-04 2016-W01-1 2016-W09-5 2017-W01-1
2017-01-02 2017-W01-1 2017-W09-5 2018-W01-1
2018-01-01 2018-W01-1 2018-W09-5 2019-W01-1
2018-12-31 2019-W01-1 2019-W09-5 2020-W01-1
2019-12-30 2020-W01-1 2020-W09-5 2020-W53-1
2021-01-04 2021-W01-1 2021-W09-5 2022-W01-1
2022-01-03 2022-W01-1 2022-W09-5 2023-W01-1
2023-01-02 2023-W01-1 2023-W09-5 2024-W01-1
2024-01-01 2024-W01-1 2024-W09-5 2025-W01-1
2024-12-30 2025-W01-1 2025-W09-5 2026-W01-1
2025-12-29 2026-W01-1 2026-W09-5 2026-W53-1
2027-01-04 2027-W01-1 2027-W09-5 2028-W01-1
2028-01-03 2028-W01-1 2028-W09-5 2029-W01-1
2029-01-01 2029-W01-1 2029-W09-5 2030-W01-1
2029-12-31 2030-W01-1 2030-W09-5 2031-W01-1
2030-12-30 2031-W01-1 2031-W09-5 2032-W01-1
(43 row(s) affected)
CODO ERGO SUM
How To Get Starting Datetime(monday) Of The Week And Ending Datetime Of The Week(sunday)
May 2, 2007hi friends,
how to get the date of the first/last day of the week in sql...
can any one help me
Cheers,
raj
Viewing SQL Currently Being Ran
Apr 2, 2008Hi all,
I'm very new to SQL Server having previously worked with Oracle 10g for the last couple of years.
Anyway, in Oracle there is a view called V$SQL that provides information about SQL that is or has recently been executed. I'm trying to find a similar sort of functionality in SQL Server.
Basically, what I'd like to do is list all of the SQL statements that are running at a given moment. Or even better would be a way of listing all statements that have run in the last 10 minutes.
Appreciate any help for a newbie.
We're on SQL Server 2000 8.00.194
Cheers
Kloid
Viewing Last SQL Statement
Aug 17, 2006Is there any way, in enterprise manager or otherwise, for me to view the last few SQL statements executed against my sql server?Thanks
View 1 Replies View RelatedViewing Data
Dec 9, 2003I just installed SQL Server on my 2003 box. Up till now I've been using MS Access for all my database needs. When I wanted to work on fields in a table, all I had to do was open Access and there you go. How do I view and modify Fields in SQL?
Chill
Viewing The Transaction Log
Dec 4, 2000Is it possible to view the transaction log to identify events run against a database.
I know that I could create a trace file to log events.
But what I really want to know is whether there is any way to actually view the live .ldf transaction log file and make sense of it.
It is possible to do something similar in Informix.
Viewing The Transaction Log
Aug 17, 1999How can I view the transaction log for a particular database. We are trying to track down when a delete occurred and wanted to look there to find it, unless there is a better place to look.
Thanks
Viewing Transaction Log
Jun 28, 1999Can any one tell me how you view the transaction log records since we don't have a syslogs anymore?
View 1 Replies View RelatedViewing Transaction Log
Apr 30, 1999How can I view the contents of a transaction log? Is there a better way to determine who deleted a record and when?
View 1 Replies View RelatedViewing A Table From Another Db
Dec 13, 2004Holy MOLY I've been banging my head up against the wall on this matter for months now. I have two databases and I need to be able to see the tables from different different databases. Usually I create a view like this
LicensingActions.dbo.License_Suspensions
But it wasnt working kept telling me that the License_Suspensions table didnt exsist and at the time the name was in all caps. So I decided to change the name, and low and BEHOLD IT WORKS. The funny was that I wasnt having that problem any of the other tables just that one. Well live and learn. Just thought I'd share that with you guys
Viewing Indexes
Aug 22, 2005I just got some great code from this site for viewing constraints, but I can't find the equivalent for indexes (and I couldn't modify the constraint sql), any suggestions?
View 2 Replies View RelatedViewing Sql Server
Jun 17, 2007why can't i see SQL server 2005 the same way i see Visual Basic 2005 Express edition. i'm able to see VB by going under the all Programs menu and select it, when i try to find SQL 2005, i see configuration tools and some submenu of configuration tools. is that the way it is? if not, how do i get SQL 2005 to open up like VB so i can play with it. Also, at home i do not have a server, do i have to have a home server to play with SQL
hens
Viewing A View...
Feb 17, 2006I am trying to view a "View" that is in one of my databases, and I am having a problem seeing the scripting that is inside of it. In sql2000 all I needed to do to view a "View" is double click on it. If I double click on a "View" in sql2005 it will open more folders showing the layout, but I want to see the scripting. If I right click on it and then click open view, sql2005 returns the values that the view would bring across. Does anybody know how to view the scripting of a "View" for sql2005?
Also if I select Modify, sql2005 returns scripting but it does not look right.
Thanks for the help!
SSIS Log Viewing
Mar 28, 2006I would like to know the best way to view Package Logs that are being stored in SQL Server? Right now I select out of sysdtslog90 to see what the log file has. I would like to know if there is a way like SQL 2000 where there is a drop down for the date and you could view the entire package data.
View 2 Replies View RelatedAccess DB And Viewing A Bit Field
May 4, 2001I have a linked table in Access to my SQL 7 database. But one field who's values are either 0 or 1 now show up as 0 or -1 when I view the linked table in Access?
Any ideas why? Thanks..
Permission For Viewing Jobs
Jan 2, 2001Hi,
What minimum level access/permission/role is required for a database user to view scheduled job listing?
Thanks in advance..
Viewing Object Permissions
Jun 19, 2001I would like to be able to get a listing of the object permissions for a particular group in a database. I can't find a procedure for doing this. I know I must be overlooking an obvious solution. Does anyone have any suggestions for doing this? Thanks.
View 1 Replies View RelatedViewing Passwords In Version 6.5
Oct 22, 1998Hi,
Can anybody tell me is it possible to view user passwords in SQL Server v6.5 as the syslogins table shows the passwords as encrypted.
Thanks,
Phil
Log Viewing On SQL7/2000
Oct 25, 2001It was possible to read the transaction log on SQL 6.5 with DBCC LOG and find out what was going on. Is there any way to do this in SQL 7.0/2000 or do you now have to rely on SQL Profiler? I have noticed that SQLProbe has a log viewer in it and would like to know how they did it.
View 1 Replies View RelatedViewing Query In EM Is Blocking
Aug 9, 2007EDIT: Oops in the title. I meant "EM" not "IE".
I'm on a Dev database (full copy). In EM (that is running on the Dev server) I run a view that I used to build the list of rows to be updated.
SQL 2000 Std SP3 on Win 2000 Server.
When I cut-n-Pasted the clause to my "Update" statement in Query Analyser and try to run it, to my surprised, it got hung up.
I looked in EM to see Locks, and to my surprise my EM View Display is blocking the Update! So; I press "Cancel" on my EM View display and, whammo, the update goes.
I tested and it's consistant and does not matter what column I'm updating.
Is this normal behaviour for EM?
EDIT: I tried the exact same scenerio, in the exact same EM and QA screens, but this time remoting over to the Production box (since it's midnight). The blocking did not occur. Weird!
Error While Viewing All Rows Through EM
Oct 4, 2006hello..
i am getting strange error while opening design through EM..when i tried to open it i m getting error like..
Action Canceled
Internet explorer was unable to link to the web page you requested....
why this error i am getting...also i am unable to view all rows through EM... :(
T.I.A
Viewing Errors During Batch Job
Aug 2, 2007Hi all,
How to view the errors occured during the job execution (than viewing the Job history).Is there any files available to refer the errors?.
Thanks in advance.