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?
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.
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.
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.
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.
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.
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
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.
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
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.
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!)
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.
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.
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.
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?
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);
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.
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.
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!
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.
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
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.
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?
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
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.
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.
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
