T-SQL (SS2K8) :: How Many Users Are Working Till Specify Date
Apr 30, 2014
I have a table with 5000 rows history about working and retired time.
It is the several rows from table(User, StartDate, EndDate):
User1 2011-05-09 00:00 2014-01-17 00:00
User2 2012-07-01 00:00 2012-08-20 00:00
User2 2013-08-26 00:00 2013-09-02 00:00
User2 2013-10-07 00:00 NULL
User3 2013-09-01 00:00 2014-04-07 00:00
User3 2014-04-08 00:00 NULL
How many users have worked and have retired by years and months,
Example:
2011-01 working 2000
2011-02 retired -50
2011-02 working 1950
2011-02 retired -27
and etc,,
Does it need to join to a time dimension table?
View 7 Replies
ADVERTISEMENT
Aug 20, 2015
I have sales from 2010 to today in my cube. my question is i want to see sales amount from 2010 to 2014 if i select 2014. or if I select 201405 ( may 2014), i want to see sales from 20100101 to 20140531. same with date. basically i want to to al the sales til the date/month /year selected. how can i achieve this?
View 2 Replies
View Related
May 6, 2014
I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.
I have got 3 relevant tables:
1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)
2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)
3. Table Z with resource data.
An example to clarify (for fake employee 100):
Table X:
Resource: 100
Starting date: 2012-03-01 (from this date the schedule will be effective)
Daynumber: 4 (2012-03-01 was a Thursday)
Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):
Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30
Record 2: same but daynumber is 2
Record 3: same but daynumber is 3 etc.
...
Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00
Record 9: same as record 2 but daynumber is 9.
etc.
...
Record 14: same as record 7 but day is 14 (= last day)
The weekend days show as 00:00 for the hours (same as day 8 in example)
I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.
I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...
I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):
2014-05-01 = day 4 (= actual daynumber)
2014-05-02 = day 5
2014-05-03 = day 6
...
2014-05-10 = day 13
2014-05-11 = day 14
2014-05-12 = day 1
2014-05-13 = day 2
2014-05-14 = day 3
...
2014-05-24 = day 13
2014-05-25 = day 14
2014-05-26 = day 1
2014-05-27 = day 2
...
2014-05-31 = day 6
With this done I can compare the actual daynumber with the daynumber in Table Y.
The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.
View 0 Replies
View Related
Nov 19, 2015
We have purchased an ERP system from a vendor which uses system DSN for all the reports. The system automatically creates DSN with Sa with SQL Server. The problem is the DSN is not working with AD users.
Active Directory server: Windows Server 2008 32 Bit.
SQL Server: Windows Server 2012 64 Bit. This server is already member of my Domain. e.g. CompDomain.com
What should I need to do in client PCs or Server to avail ODBC to AD users.
View 3 Replies
View Related
Oct 28, 2015
i have written a sql function which returns only number of working days (excludes holidays and Weekends) between given StartDate and EndDate.
USE [XXX]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateNumberOFWorkDays] Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO
[code]...
I need a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date. Example scenario: If i give date as 12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table tblMasHolidayList where all the 2015 year holidays dates and info are stored.
View 18 Replies
View Related
Sep 18, 2014
I am tring to send an email reminder to multiple users using a cursor.
declare emailCursor cursor for
select name,info,default_email, @MyMessage
from My_Temp
open emailCursor
fetch next from emailCursor into @Name,@info, @Recipients,@MyMessage
while(@@FETCH_STATUS = 0)
begin
-- select name,info,ins_default_email,@MyMessage
--from temp
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Hello',
@recipients = @Recipients,
@from_address = '<address.com>',
@Body = @MyMessage,
@subject = 'Subject'
fetch next from emailCursor into @Name,@info, @Recipients,@MyMessage
end
close emailCursor
deallocate emailCursor
Currently I have two users in my table that I want to send email to, but when the stored procedure runs I got two emails for the last user in the table instead one for each user.
View 2 Replies
View Related
Sep 23, 2014
Trying to troubleshoot an CLR/ RTF to Plain Text issue I am having.
I have 3 instances on one SQL server. Only one of the Instances is not working. Even tried deleting everything and resetting it up.
Here is the code I am testing with:
USE Test_Database
GO
DECLARE @RTF varchar(max)
SET @RTF = '{
tfansiansicpg1252uc1deff0deflang1033{fonttbl{f0 Calibri;}{f1 Arial;}}{colortbl
ed0green0blue0 ;
ed255green255blue255 ;}viewkind4paperw12240paperh15840margl1425margr1425margt1425margb1425sectdpgwsxn12240pghsxn15840
marglsxn1425margrsxn1425margtsxn1425margbsxn1425pardfs21sl276slmult1sa180{f1fs21
Test New Progress Notes - Praveen}par}';
SELECT dbo.clr_fn_ConvertRTF2PlainText(@RTF)
Here is what I am getting back:
The operation completed successfully
Not sure what to check as the other two instances are working fine.
I have CLR enabled in sp_Configure
clr enabled 0 1 11
View 2 Replies
View Related
May 29, 2014
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee
SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL
THEN ves.VEmployeeID
END
FROM Employee E
INNER JOIN VEmployeeStaging VES
ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
View 6 Replies
View Related
Aug 18, 2015
IF EXISTS ( SELECT TOP 1 1 FROM sys.tables WHERE name = '#EmpHours' )
DROP TABLE #EmpHours;
CREATE TABLE #EmpHours
[Code]....
View 7 Replies
View Related
Mar 16, 2014
I am trying to insert new records into the target table, if no records exist in the source table. I am passing user specific values for insert, but it does not insert any values, nor does it throw any errors. The insert needs to occur in the LOAN_GROUP_INFO table, i.e. the target table.
MERGE INTO LOAN_GROUP_INFO AS TARGET
USING (SELECT LGI_GROUPID FROM LOAN_GROUPING
WHERE LG_LOANID = 22720
AND LG_ISACTIVE = 1)
AS SOURCE
[Code] .....
View 8 Replies
View Related
Mar 14, 2014
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.
View 9 Replies
View Related
Jul 23, 2014
I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.
=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,
[Code] ....
Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.
SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID
View 9 Replies
View Related
Oct 29, 2014
Attached is a file that generates a sample dataset from which I want to forecast a value for column 'Parcel Count 2014' from tomorrow (30/10/2014) till the end of December (31/12/2014). The forecast should be based on values from column 'Parcel Count 2013' for the same Month.
View 9 Replies
View Related
Feb 13, 2008
I provide default values for parameters, but we need to wait for the user to click "View Report" before the report is run. It is an expensive report to run, and the user needs the defaults, but also needs to be able to change them before execution. I cannot find a setting for this. Is there a way to do this that does not involve adding dummy parameters? (that's too tacky a solution!)
View 4 Replies
View Related
Nov 14, 2007
I have a simple gridview that loads on page load. It uses an on page sqldatasource declaration in which there's a parameter in which value is already available in cookies. I added an asp:HiddenField and set that value on PageLoad() to the value of the cookies. I then set a FormParameter in the sqldatasource mapped to that hidden field. However that appears to have no effect at all. I'm guessing the sqldatasource will only use the form field after postback.
View 2 Replies
View Related
Sep 2, 2015
There is a huge empty space between my tablix which is inside a rectangle to the footer.
I need to be able to fit another row but the other rows go on to the second page. How can I fix this?
View 8 Replies
View Related
Jul 2, 2006
Hi;I'm here for many hours trying to do this but i couldn't find a way.I have a table whith a field called [DOB], where i have people's date of birth.
Now, i need a SQL query to get people who's birthdays are in between two dates "BUT", what about the year on the date?
I use to do this on Access:
SELECT * FROM Members WHERE DATESERIAL(YEAR(NOW()), MONTH(DOB), DAY(DOB)) BETWEEN @startDate AND @endDate
In the query above the year is not a problem because the DateSerial() function add the current year for all birthdates making it easyer to user parameters like: 06/01/2006 to 06/30/2006
Unfortunately, SQL Server does not support DateSerial() function.
I appreciate any help on this.
Thanks a lot.
View 5 Replies
View Related
Apr 17, 2007
Hello Everyone and thanks fo ryour help in advance. I am working on a blog site that stores each blog entry in a SQL Server table. Each entry is date stamped with the time it is created in a datetime field called "TimeCreated". I want to be able to create some type of ability to sort the older blogs as an archive and display links on a pages such as "Archive - January 2007, Archive - December 2006" etc. I'm not exactly sure on how to go about doing this. any help on this topic would be greatly appreciated.
View 8 Replies
View Related
Jul 20, 2004
Hi,
I have a problem with a SQL that check if a date column IS NULL.
On one server the check work ok but on another (the same data is on both - restored copy) the check does not find any NULL values.
If check where datecolumn =convert(datetime,'9999-12-31 23:59:59.000',121) I get the same result as when checking for NULL in the other.
Is there any parameter set somewhere that tell the server to return a value even if NULL is stored in the database?
thank you for reading,
YakoBay
View 3 Replies
View Related
May 23, 2008
Hey Guys, I can’t get this date conversion stuff to work. I have a variable that holds the current date -1 day (@Yesterday). The date that is on the table (DTE) is in the format yyyymmdd (i.e. 20021213), but I cannot get the date to convert.
I have tried:
BEGIN
DECLARE @Yesterday as nvarchar (8);
set @Yesterday = (replace(CONVERT(VarChar(10), DATEADD(d, -1, getdate()), 101), '/', ''))
PRINT @Yesterday
select * from tblxxx
where Replace(Convert(varchar (10), DTE, 101), '/', '') = @Yesterday
END
@Yesterday = 05222008
DTE = 20080522 (after conversion)
Why can’t I get DTE to convert to 05222008
View 6 Replies
View Related
Jan 29, 2015
I need to calculate a date.example it needs to be 20 working days ago compared to today so that means it needs to not include any Saturday or Sunday in between
declare @start_date datetime
declare @end_date datetime
declare @working_days int
set @working_days = 20
[code]...
So I need to calculate @start_date but it needs to exclude any weekend days.@working_days is the number of working day I'm interested in.
View 1 Replies
View Related
Jul 23, 2005
Hello,I am importing data that lists rates for particular coverages for aparticular period of time. Unfortunately, the data source isn't veryclean. I've come up with some rules that I think will work to clean thedata, but I'm having trouble putting those rules into efficient SQL.The table that I'm dealing with has just under 9M rows and I may needto use similar logic on an even larger table, so I'd like somethingthat can be made efficient to some degree using indexes if necessary.Here is some sample (simplified) code:CREATE TABLE Coverage_Rates (rate_id INT IDENTITY NOT NULL,coverage_id INT NOT NULL,start_date SMALLDATETIME NOT NULL,end_date SMALLDATETIME NOT NULL,rate MONEY NOT NULL )GOINSERT INTO Coverage_Rates VALUES (1, '2004-01-01', '2004-06-01',40.00)INSERT INTO Coverage_Rates VALUES (1, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (1, '2004-06-01', '2004-08-01',30.00)INSERT INTO Coverage_Rates VALUES (2, '2004-01-01', '9999-12-31',90.00)INSERT INTO Coverage_Rates VALUES (2, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (2, '2004-08-01', '2004-08-01',30.00)GOThe rule is basically this... for any given period of time, for aparticular coverage, always use the coverage with the highest rate. So,given the rows above, I would want the results to be:coverage_id start_dt end_dt rate----------- ---------- ---------- --------1 2004-01-01 2004-06-01 40.001 2004-06-01 2004-08-01 30.002 2004-01-01 9999-12-31 90.00There can be any combination of start and end dates in the source, butin my final results I would like to be able to have only one distinctrow for any given time and coverage ID. So, given any date @my_date,SELECT coverage_id, COUNT(*)FROM <results>WHERE @my_date >= start_dtAND @my_date < end_dtGROUP BY coverage_idHAVING COUNT(*) > 1the above query should return 0 rows.Thanks for any help!-Tom.
View 9 Replies
View Related
Oct 23, 2007
Hello,
My SQL Server 2005 database table data definition is as follows:
ID int
LST_DATE DateTime
SUBJECT varchar(100)
An example of a record in the database table has a date format like this: 23/10/2007 16:56:00. My VB code as below:
Dim Command As New SqlClient.SqlCommand("SELECT ID,LST_DATE,SUBJECT FROM dbo.LISTS WHERE LST_TIME = GETDATE(), 100)))", conn)
Dim dataReader As SqlClient.SqlDataReader = Command.ExecuteReader(CommandBehavior.CloseConnection)
While dataReader.Read()
Dim NWAlert As New Alert(dataReader("SUBJECT").ToString(),DateTime.Parse(dataReader("LST_DATE").ToString())))
:
LST_TIME should be equals the CURRENT DATE & TIME in the format above to activate.
I have tried using the following:
WHERE LST_DATE = CONVERT(GETDATE(), 103) + ' ' + CONVERT(GETDATE(), 108)", conn)
WHERE LST_DATE = CONVERT(GETDATE(), 107) + ' ' + CONVERT(GETDATE(), 108)", conn)
But for some reason it is not working. Please help!
View 22 Replies
View Related
Dec 29, 2014
I'm trying to get the UTC date from an integer. See my SQL below:
Declare @unix_date bigint
Set @unix_date = 3499288964
SELECT dateadd(day, @unix_date/(86400), dateadd(second, @unix_date
% (86400), '19700101'))
It returns '2080-11-20 00:42:44.000'. This is dead on except for the year (which should be 2014). I was thinking maybe my bigint value of 3499288964 was milliseconds or microseconds so I adjusted the seconds value (86400) in the select statement to reflect milliseconds (86400000) with no success and microseconds (86400000000) with no success as both of those gave incorrect results. Closest I got was with the seconds (86400) which of course returns the incorrect year.
View 2 Replies
View Related
Apr 16, 2006
Hi,
The following INSERT query works in all aspects apart from the date value:
String InsertCmd = string.Format("INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},{1},{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text);
The value of txtPaymentDate.Text is "13/04/2006" but is inserted as a zero value (i.e. "01/01/1900").
In additon to replacing {1} with a string, I've tried changing {1} to both '{1}' and #{1}#, both of which are "caught" by my try/catch on the INSERT.
What am I doing wrong? Thanks very much.
Regards
Gary
View 3 Replies
View Related
Oct 3, 2006
hi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this:
oct 2
oct 3
sep 13
sep 21
sep 22
sep 30
aug 3
aug 5
aug 16
the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help.
also, can you not search here any more? i keep getting timeout errors.
View 7 Replies
View Related
May 20, 2008
I have a table with a single effective date, rather than both a start and stop date. I have to be able to match up this table to another one with service information in it and am not sure how to get the correct record selected.
So in table one I have a personID, effective date, and lots of other fields. There are also multiple records for each personID, so say personID 1 has records with effective dates of 1/1/2007, 6/1/2007, and 1/1/2008.
Table two has personID, Service Date, and lots of other fields.
So if I am looking to match up the effective row from table one to a record in table two with a service date of 8/1/2007, how do I get the db to locate and return the record with an effective date of 6/1/2007, and only this record?
View 6 Replies
View Related
Dec 5, 2007
Hi,
We are using [DATE] [TIME] functions in SQL Server 2000 agent jobs and SQL Server use to translate it to current data and time functions but in
SS2005 it is not replacing the functions and we are getting filename as "test_DATE_TIME" whereas we expect "test_20071204_130000"
Do we have any new functions as replacement?
Thanks
--rubs
Following is the code we are using:
declare @name nvarchar(100)
declare @name1 nvarchar(100)
set @name1 = 'test_[DATE]_[TIME]'
set @name = 'c:ackup' + @name1 + '.bak'
backup database test to disk = @name
View 8 Replies
View Related
Jun 2, 2007
Hi all,
I'm looking to return certain rows from our db into an SSRS based on a user selected date range using the parameters calendar.
My query/analyzer returns all required fields/rows, but how do I
pull the specific rows, (that are based on a date range that the user enters),into the report? I've tried expressions, and vb functions to no avail. The users will be using the calendar parameter to select date ranges .So far the reports pull in all rows from my query.
Thanks,
Scott
View 1 Replies
View Related
May 12, 2014
I have a table containing some events. Those events all have a start date and an end date.
CREATE TABLE #events
(
eventID int,
eventname char(30),
startdate date,
enddate date
[Code] ....
When looking at the data some days have multiple events. Now I want to generate a new table that show all the dates in this month showing the number of running events for that specific day.
View 9 Replies
View Related
Jun 17, 2014
i am trying to convert a string like this 'le dd/mm/yyyy' into a datetime.I have removed the 'le ' part and used covert(datetime, 'dd/mm/yyyy',103) to convert into datetime. This works for example for 'le 22/11/1799' but for 'le 09/11/1716' it does not work.
select convert(datetime,RIGHT('le 22/11/1799', LEN('le 22/11/1799') - 3), 103) -> it works
select convert(datetime,RIGHT('le 09/11/1716', LEN('le 09/11/1716') - 3), 103) -> it does not work
View 3 Replies
View Related
Jun 26, 2014
I would like to get the first and last day of any month in a given date range.
Ex: Display the first and last day of the months between @startDate and @EndDates.
Input Params= @StartDate='2016-06-21 16:57:11.093'
@EndDate = '2016-09-30 00:00:00.000'
OutPut should be:-
MonthStartDateMonthEndDate
1/06/201630/06/2016
1/07/201631/07/2016
1/08/201631/08/2016
1/09/201630/09/2016
View 1 Replies
View Related
Sep 5, 2014
I'm importing dates into a table with Bulk insert
SET DATEFORMAT DMY
it works with dates e.g. "14/01/2009"
However sometimes I get dates in the format
"Fri 14/01/2009"
What is the best way to convert these.
I can only think of putting them in a staging table with all date fields as varchars Then updating these varchar fields
LTRIM(REPLACE(dtField, 'Mon', ''))
LTRIM(REPLACE(dtField, 'Tues', ''))
View 9 Replies
View Related
