I am trying to create to a store procedure that manages to return the last 10 dates from a table i know there is a TOP keyword that return the first rows but is there a keyword that returns the last rows instead?
i want a query which returns all the date between 2 dates . its like an calender.....for example i selected 2-1-2006(dd-mm-yyyy) to 18-03-2006 ....it should returns like this 2-1-2006 3-1-2006 4-1-2006 . . . 16-03-2006 17-03-2006 18-03-2006
I have a database table which contains customer orders. I am trying to code my SQL select statement to:
1) Only return records where the record orderdate is within the last 30 days
2) Between two dates, selected from the datepicker control.
With regards to issue 1, I could fill a table with all the records for the account in question and then for each record do a datediff between the records order date and the current date to determine if the number of days is within 30 days. If yes then add this record to a temp table and then set this table as the datasource for the datagridview.
I currently have a stored procedure that returns a list of dates based on a date range a user enters.
CREATE PROCEDURE sp_GetContactScheduleDates @MonthFrom int, @YearFrom int, @MonthTo int, @YearTo int, @DaysInMonth int AS Select distinct s.ScheduleMonth, s.ScheduleYear From OnCall_Schedules s Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime) >= CAST(cast(@MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@YearFrom as nvarchar) as smalldatetime) And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime) <= CAST(cast(@MonthTo as nvarchar) + '/' + cast(@DaysInMonth as nvarchar) + '/' + cast(@YearTo as nvarchar) as smalldatetime) Order by s.ScheduleYear, s.ScheduleMonth GO
However, this only brings back those dates that are in the table. I need to get ALL dates within the range.
For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?
Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.
Can some sort of function be used? What would the code look like?
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
why the statement "where WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -6, GETDATE()) AND GETDATE()) = 0' is successful, but "WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE())) AND (DATEADD(DD, -3, GETDATE()))" is an invalid statement? I've tried a lot of different syntax variations of this statement and cannot get to work between two lookback dates.
The following SQL query :-SELECT CardHolder.RecordID, History.GenTime, History.Link1FROM History FULL OUTER JOINCard ON History.Param3 =LTRIM(RTRIM(Card.CardNumber)) FULL OUTER JOINCardHolder ON Card.CardHolderID =CardHolder.RecordIDWHERE (Card.Deleted = 0) AND (History.GenTime IS NOT NULL)ORDER BY CardHolder.RecordID, History.GenTime DESCreturns :-RecordID GenTime Link12 04/06/2004 15:30:00 1232 01/06/2004 16:00:00 1232 01/06/2004 08:00:00 1101155 02/06/2004 11:30:00 1231155 02/06/2004 08:00:00 1103925 03/06/2004 09:00:00 1233925 03/06/2004 08:00:00 1104511 06/06/2004 11:30:00 1234511 06/06/2004 10:30:00 110Is there a way of modifying this query to just return the lastestgenTime for each RecordID ??? ie return just rows 1,4,6 & 8.I assume it is something to do with MAX, but I can't seem to get myhead around it.Any help, or pointers would be appreciated.Oh, running query on Microsoft SQL Server 2000.RegardsDave
I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):
Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?
I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
Here is the scenario, I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300) set @tmp = 'SELECT * FROM OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'', ''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT * FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used. Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used. Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction ( ) RETURNS @retTable ( @Field1 int, @Field2 varchar(50) ) AS BEGIN -- the problem here is that I need to call SP1 and assign it's resulting data into the -- @retTable variable
-- this statement is incorrect, but it's meaning is my goal INSERT @retTableSELECT *FROM SP1
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup ( @whichgroup int ) RETURNS @RETTAB TABLE ( TABLENAME VARCHAR(50)
Hi I have a dataset, a sql query, that selects a bunch of opportunities. The result consist of close date, values, names, owners, topics and so on. I have put the result in a matrix and the. The matrix will grow to the right with the months columns. Only the months that have an opportunity in them will be shown. (estimated close date is the date were I select from). So, for example when there are opportunities in Jan, Feb and Aug in 2008 only those 3 months will show the data that corresponds to each opportunity. Now, how can I make my query to show all the empty months as well? I want a matrix that shows Jan, Feb, Mar, €¦ Nov and Dec, even if they don€™t have any opportunities in them.
I have a timestamp in one table that is of datetime type and I need to move it to a table with a Varchar(30) type. What is happening is a date that should be set up like this "11/12/2004" is actually coming across as "Nov 12 2004". I tried using a cast(fieldname) as Varchar(30) on the datetime type, but it didn't help. does anyone have any ideas on how to retain the original date form in moving it to a varchar type? thanks in advance...
Hi,I have a employee attendance table, and Im trying to return the dates on which an employee is absent. There are no table entries for an employee who is absent on a particular day. So can anyone please help on how i can return the dates on which an employee is absent. Any help will be greatly appreciated, Thanks,Najla.
Hi, i have a table with 3 ints that are used to store dates. Thedatetime data type is not used because this data comes from an oldAS400 server.I need to be able to use those 3 columns to build dates within a queryand be able to use them to compare themselves to other datesLet's say the table has the following values:myday mymonth myyear23 5 2006and suppose i want to do a query that displays all rows with dategreater than '20060520'Here is the query i have tried:selectcast(myday as varchar(2))+'/'+cast(mymonth asvarchar(2))+'/'+cast(myyear as varchar(4))from mytableThat query returns the string '23/5/2006' yet i can't use it to compareit with '20060520'Is there a way i can do this in a simple query?This is on sql server 2000
Both are related through vacation_id. Every vacation however has many pictures. I need a query where the result would give me back all records (vacations) from Table 1 with only 1 record from Table 2(one image per vacation).
It is all contained in one table. I have got the earliest years by using the YEAR() function and grouping by it. The only problem is that I am having a problem joining the table back onto itself with the subselect because of it's an aggregate.
Here is the first join I have....
SELECT DT.ClientID, Year1 FROM ( SELECT ClientID, YEAR(MyDate) as Year1 FROM MyTable ) AS DT GROUP BY ClientID, Year1 ORDER BY ClientID, Year1
I want to use that as my derived table and then join back to it... but it's not working...
I would like to reopen this discussion. I need to create this tableusing SQL that will return this dataset.BRAID RATEDEFID MIN(BRADATE) MAX(BRADATE RATECODE PRODNAME614 14 12/1/2002 12/4/2004 Rack BeachTower Terrace618 14 12/8/2002 12/11/2004 Rack BeachTower Terrace622 14 12/15/2002 12/19/2004 Rack BeachTower TerraceI have provided the test data below and the SP I have been working with.The SP works fine as long as there are 4 seqential dates. I getunpredictable results when the count <> 4. I am also concerned on whathappens at the end of the month. For example, 1/31/2004, 2/1/2004.Does this affect the SP? I appreciate your continued support.MichaelCREATE TABLE [dbo].[BASERATEAVAIL_Tmp] ([BRAID] [decimal](18, 0) NOT NULL ,[RATEDEFID] [decimal](18, 0) NOT NULL ,[BRADATE] [datetime] NOT NULL ,[RATECODE] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,[PRODNAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL) ON [PRIMARY]GOINSERT INTO dbo.BASERATEAVAIL_Tmp(BRAID, RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (614, 14, '12/1/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (615, 14, '12/2/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (616, 14, '12/3/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (617, 14, '12/4/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (618, 14, '12/8/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (619, 14, '12/9/2002 ', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (620, 14, '12/10/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (621, 14, '12/11/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (622, 14, '12/15/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (623, 14, '12/16/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (624, 14, '12/17/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (625, 14, '12/18/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (626, 14, '12/19/2002 ', ' Rack ', ' Beach Tower Terrace ');CREATE PROCEDURE usp @n INTASSET ANSI_WARNINGS OFFSELECT MIN( braid) AS "braid",ratedefid, ratecode, prodname,MIN( CASE n WHEN 1 THEN bradate END ) AS "min_date",MAX( CASE n WHEN 0 THEN bradate END ) AS "max_date"FROM ( SELECT t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate,COUNT( * ) % @n AS "n",( COUNT( * ) - 1 ) / @n AS "r"FROM BASERATEAVAIL_Tmp t1INNER JOIN BASERATEAVAIL_Tmp t2ON t2.bradate <= t1.bradateGROUP BY t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate) DGROUP BY r, ratedefid, ratecode, prodnameHAVING MAX( CASE n WHEN 0 THEN bradate END ) IS NOT NULL ;Usage:EXEC usp @n = 4--Anith*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
currently need to re-create an aggregate table in a proc every night to aggregate purchases broken down by person/store in groups of 3, 6 12 etc months.finding the performance of it is very slow as it covers 500,000 million rows.The query looks like
SELECTCusID(int) , StoreID(int) , SUM(L3M) as Last3Months , SUM(L6M) as Last6Months , SUM(L9M) as Last9Months
[code]...
I need to make changes to this because it is using a BETWEEN on a datetime column. I was wondering though, if anyone else has made agg tables like this before an found a better way of doing them?
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
I want to create a table that has a datetime column, data type=datetime and I want the date to start form 01/01/2004 00:00:00 to 01/01/2008 00:00:00 the increment will be by minute like
01/01/2004 00:01:00
01/01/2004 00:02:00
01/01/2004 00:03:00 and so on up to 01/01/2008 00:00:00
I will use this time dimension in bussiness objects.
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
Each month we process 100+ text files into our ERP system. Occasionaly the Voucher Date in the text file does not contain a valid date. I would like to check to see if it is a valid Date, if it isn't replace it with the current date.
I thought I would use a Derived Column transformation, but I don't know how to check a field to see if it is a valid date.
I have a datetime field used to store a date of birth. When inserting the date of birth into the table it works fine. But when the date of birth is in the month of October, it takes an hour off. For example, if the date is 04-OCT-1993, it inserts as 03-OCT-1993 23:00.
It only happens for dates in October. It's being inserted via a .Net table adapter. It just so happens that October is the month that daylight savings kicks in parts of Australia (an hour is added), but I think this must be a coincidence.
And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.
The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.
Has anyone written or come across a routine that validates start and enddate in a slow changing dimension? (eg verifies there is no overlap in any of the records).