T-SQL (SS2K8) :: Date Comparison In Two Table By Returning Nearest Date Of Table A In Table B

Jun 9, 2014

I am having a problem in creating query for this exciting scenario.

Table A

ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000

Table B

IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000

I would like to return the nearest date of Table B in my table like for

ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000

View 3 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Pulling Records From A Table Between Date Ranges In Another Table

Mar 17, 2014

This seems simple enough but for some reason, my brain isn't working.

I have a lookup table:

Table A: basically dates every 30 days

1/1/2014
2/3/2014
3/3/2014
4/3/2014

I have Table b that has records and dates created assocated with each record

I want all records that fall between the 1st 30 days to have an additional column that indicates 30

union

records with additional column indicating 60 days that fall between the 30 and 60 day

union

records with additional column indicating 90days that fall between the 60 and 90 day mark.

Is there an easy way to do this?

View 6 Replies View Related

Transact SQL :: Compare Two Table With Multiple Date Comparison

Oct 20, 2015

I need to take all records from table @A where ID = 1. Also i need to process the records with datewise from table @A. Here is the table structure

DECLARE @A TABLE (ID INT, ACCOUNT VARCHAR(10), EFFDT DATE)
INSERT INTO @A VALUES (1,'AAA','2015-10-01')
INSERT INTO @A VALUES (1,'BBB','2015-10-01')
INSERT INTO @A VALUES (1,'CCC','2015-10-01')
INSERT INTO @A VALUES (1,'AAA','2015-10-05')
INSERT INTO @A VALUES (1,'DDD','2015-10-01')
INSERT INTO @A VALUES (2,'AAA','2015-10-02')
INSERT INTO @A VALUES (2,'BBB','2015-10-02')
INSERT INTO @A VALUES (2,'CCC','2015-10-02')
INSERT INTO @A VALUES (2,'DDD','2015-10-02')

[code]...

how to achieve this in SQL query, i cannot use CTE or temp table as i need to use this code in another tool, it has to be single query, can use subquery or join would be better.

View 4 Replies View Related

T-SQL (SS2K8) :: How To Round A Date To The Nearest Second

Sep 8, 2014

how to round a datetime variable to the nearest second. The solution must NOT just strip off the milliseconds, it needs to round.

Also, the solution should not be too cumbersome because it will be used in a high volume environment.

View 5 Replies View Related

T-SQL (SS2K8) :: Join 2 Table Based On Date

Jul 16, 2014

I have 2 tables:

Table Transaction
-----------------
EmpID TransDate
00001 1/1/2014
00001 1/2/2014
00001 1/3/2104
00001 1/4/2014
00001 1/5/2014
00001 1/6/2014
00001 1/15/2014
00001 2/1/2014
00001 2/2/2014
00001 2/20/2004 ....

Table Master
---------------------------
EmpID EffectiveDateFr Group
00001 1/1/2014 A
00001 1/5/2014 B
00001 1/9/2014 C
00001 2/1/2014 B
00001 2/20/2014 A ....

I want to create query the output should be:

EmpID TransDate Group
00001 1/1/2014 A
00001 1/2/2014 A
00001 1/3/2104 A
00001 1/4/2014 A
00001 1/5/2014 B
00001 1/6/2014 B
00001 1/15/2014 C
00001 2/1/2014 B
00001 2/2/2014 B
00001 2/20/2004 A

View 4 Replies View Related

T-SQL (SS2K8) :: Passing Table Name And Date Value As Variable

Jun 10, 2015

I need building the dynamic sql . When I create below script and executed the procedure passing the table name and date values its giving me error saying "Incorrect syntax near '>'".

Create PROCEDURE sampleprocedure
@tablename AS VARCHAR(4000),
@date as date
AS
BEGIN
declare @table varchar(1000)

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: Find Previous Date From Table

Nov 4, 2015

I want to find previous date from selected date. below is the sample data.

DECLARE @StartDate SMALLDATETIME = '1/11/2016'

declare @tempdat table(repdate smalldatetime)
insert into @tempdat values ('10/26/2015')
insert into @tempdat values ('10/29/2015')
insert into @tempdat values ('11/1/2015')
insert into @tempdat values ('11/27/2015')
insert into @tempdat values ('11/25/2015')
insert into @tempdat values ('11/20/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/10/2015')
insert into @tempdat values ('11/11/2015')
insert into @tempdat values ('11/11/2015')

Now if i pass the date '10/26/2015' then i want select prev date of passed date. in this example no prev date is available, so result set would be nothing.

if i pass the date '11/10/2015' then result should be '11/1/2015' which is prev small date available in table.

View 4 Replies View Related

T-SQL (SS2K8) :: Select Minimum Date From Related Table

Apr 2, 2014

I am working on a query that seems very simple, but I just cannot seem to get it correct.

I want to be able to select Only 1 MemberAddress with the MIN(MoveDate) for each of the 3 Members. The results would be this:

Joe Smith, 2000-03-10, 1034 Sturgis Road, 115, Portland, Or, 77665
Sally Jones, 2001-01-02, 8970 Pierce Road, 25, Clear Bay, Washington, 96547
Beth Moore, 2006-05-30, 456 W. Blane Ave, NULL, Charleston, West Virgina, 56897

DECLARE @Members TABLE
(
MemberRowID INT IDENTITY(1,1) NOT NULL
,FirstName VARCHAR(20)
, LastName VARCHAR(20)

[Code]....

I am not opposed to using CTE, I really like them, but I cannot figure this one out. But I will try most anything.

View 4 Replies View Related

T-SQL (SS2K8) :: Capture Database Sizes Table With Date

Jun 5, 2014

I'm trying to capture the sizes of all Databases into a Permanent Table and include the Date.

It works when inserting into a #Temp Table.

When I try inserting into a permanent table it returns NULL.

The following code needs modified to create a permanent table and store the Date:

CREATE TABLE #databases ( DATABASE_NAME VARCHAR(50), DATABASE_SIZE FLOAT, Date VARCHAR(100) )

INSERT #Databases EXEC ('EXEC sp_databases');
SELECT@@SERVERNAME AS SERVER_NAME, DATABASE_NAME,
DATABASE_SIZE AS 'KB',
ROUND(DATABASE_SIZE / 1024, 2) AS 'MB',
ROUND((DATABASE_SIZE / 1024) / 1024, 2) AS 'GB',
CONVERT(date, getdate()) AS Date FROM #databases

DROP TABLE #databases;

View 9 Replies View Related

T-SQL (SS2K8) :: Calculate 90 Days And 3 Years Ago From Effective Date In A Table?

Sep 30, 2014

What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?

as in

SELECT EffectiveDate
from FL.CEFHistory

I need to return the effective date - 90 days and 1 year from that.

[URL]

View 6 Replies View Related

T-SQL (SS2K8) :: Using A Date Comparison In A Case Statement?

Mar 19, 2015

I am trying to use a date comparison in a statement using the year statement as well. Here is what I have:

Case [LastHireDate]
When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12'
When Month([LastHireDate]) = '1' then '12'
When Month([LastHireDate]) = '2' then '11'
When Month([LastHireDate]) = '3' then '10'
When Month([LastHireDate]) = '4' then '9'

[Code] ....

When I am looking at it [LastHireDate] is showing that red line underneath. The < symbol has a red line and @EndYearlyDate has a red line. I can not seem to get them to clear and am, wondering what I am missing. When I execute the error comes up that it does not like the < sign in there.

Here is the full piece that the Case resides in:

Insert _Test
SELECT
EmpNo,
PersonIdNo,
REPLACE(PersonTaxIdNo,'-',''),
LastName,
FirstName,

[code]......

View 3 Replies View Related

Power Pivot :: Measure Results Limited By Fact Table Dates Instead Of Date Table

Sep 17, 2015

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):

StkOutCnt:=CALCULATE (
COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
FILTER (
Calendar,
[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
)
)

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?

View 6 Replies View Related

Transact SQL :: Retrieve Currently Created Date From Master Table To Load Into Parent And Child Table

May 12, 2015

In Master tabel i have these date datas

2015-05-10

2015-05-11

2015-05-12

SO when i try to load from  Master table to parent and child table i am using using expresssion like 

select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON 
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND 
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE())  and convert(date,B.Ftpdate) = convert(date,getdate()) ;

If i use this Expression i am getting the current system date data's only  from Master table to parent and child tables.

My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and  child tables.

If i run this expression to remote server  i cannot change the system date in server.

while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.

What is the Expression on which ever  date i am trying load in  the master table  same date need to loaded in Parent and child table without changing the system Date.

View 10 Replies View Related

SQL Server 2008 :: Update A Table With Date When There Are New Records In Another Table

Sep 16, 2015

I have a scenario where I have to Update a table with date when there are new records in another table

For example:

I load ODS table with the data from a file in SSIS. the file has CustomerID and other columns.

Now, when there is new record for any customerID in Ods, then Update the dbo table with the most recent record for every CustomerID(i.e. update the date column in dbo for that customerID). Also Include an Identifier that relates back to the ODS table. How do I do this?

View 8 Replies View Related

How To Archiv Table To Another Table With Unique Number For All Rows Once + Date

Apr 28, 2008

need help
how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute)
i need whan i insert to the another table add 2 more fields (unique number , date_time )

this is the table 1 i select from
ID fname new_date val_holiday
----------------------------------------------------

111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1

222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3

333 ccc 03/04/2008 4
333 ccc 04/04/2008 4

this is the table 2 i insert into
----------------------------------
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 666 15/04/2008 17:03
111 aaaa 16/03/2008 1 666 15/04/2008 17:03
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 666 15/04/2008 17:03
111 aaaa 20/03/2008 1 666 15/04/2008 17:03
111 aaaa 21/03/2008 1 666 15/04/2008 17:03

222 bbb 02/05/2008 3 666 15/04/2008 17:03
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3 666 15/04/2008 17:03
222 bbb 05/05/2008 3 666 15/04/2008 17:03
222 bbb 06/05/2008 3 666 15/04/2008 17:03
222 bbb 07/05/2008 3 666 15/04/2008 17:03
222 bbb 08/05/2008 3 666 15/04/2008 17:03
222 bbb 09/05/2008 3 666 15/04/2008 17:03

333 ccc 03/04/2008 4 666 15/04/2008 17:03
333 ccc 04/04/2008 4 666 15/04/2008 17:03

for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)

next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 667 15/04/2008 17:15
111 aaaa 16/03/2008 1 667 15/04/2008 17:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 667 15/04/2008 17:15

.........................
.....................................................................667 15/04/2008 17:15


next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 668 15/04/2008 08:15
111 aaaa 16/03/2008 1 668 15/04/2008 08:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 668 15/04/2008 08:15

.........................
.....................................................................668 15/04/2008 08:15



TNX

View 3 Replies View Related

Transact SQL :: Update Table Based On Available Date Range In Same Table

Dec 2, 2015

I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.

Update TableName
SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)'
Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range

Note: ROW is Partition By ColumnA,ColumnB

TableName: PromotionList

ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 0 NULL
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 0 NULL
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL

Expected outcome after performing update on the table

ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 1 Overlap with row ID 4
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 Overlap with row ID 5
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL

View 4 Replies View Related

Update Table 1 To Table 2 Depending Date Time &#043; ID

Apr 26, 2008

need help please on update only if .

i need to update only the field "val_holiday " (in table B from table A)

and olso to check on table B the "ID" + "new_date" only if exist

and update the field "val_holiday " (in table B)

and at the end of update change the field "field_check_update_if _ok" from 0 to 1
only the row that update in table B


select from table A

update table B

WHERE ..........................HOW ?




----------------------------------------------------------------- table A

ID fname new_date val_holiday field_check_update_if _ok

---------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 999 0

111 aaaa 16/03/2008 888 0

111 aaaa 18/03/2008 77 0

111 aaaa 19/03/2008 9 0

111 aaaa 20/03/2008 111 0

111 aaaa 21/03/2008 12 0



222 bbb 02/05/2008 15 0

222 bbb 03/05/2008 16 0

222 bbb 04/05/2008 9 0

222 bbb 05/05/2008 3 0

222 bbb 06/05/2008 90 0

222 bbb 07/05/2008 3 0

222 bbb 08/05/2008 3 0

222 bbb 09/05/2008 3 0



333 ccc 03/04/2008 4 0

333 ccc 04/04/2008 4 0



----------------------------------------------------------------- table B

ID fname new_date val_holiday

----------------------------------------------------

111 aaaa 15/03/2008 1

111 aaaa 16/03/2008 1

111 aaaa 18/03/2008 1

111 aaaa 19/03/2008 1

111 aaaa 20/03/2008 1

111 aaaa 21/03/2008 1



222 bbb 02/05/2008 3

222 bbb 03/05/2008 3

222 bbb 04/05/2008 3

222 bbb 05/05/2008 3

222 bbb 06/05/2008 3

222 bbb 07/05/2008 3

222 bbb 08/05/2008 3

222 bbb 09/05/2008 3



333 ccc 03/04/2008 4

333 ccc 04/04/2008 4

------------------------------------------------------------------------------

TNX for the help and for all

View 1 Replies View Related

Update Table A To Table B Depending Date Time + ID

Apr 25, 2008

need help please on update only if .
i need to update only the field "val_holiday " (in table B from table A)
and olso to check on table B the "ID" + "new_date" only if exist
and update the field "val_holiday " (in table B)


and at the end of update change the field "field_check_update_if _ok" from 0 to 1
only the row that update in table B




Code Snippet
select from table A
update table B
WHERE ..........................HOW ?




----------------------------------------------------------------- table A
ID fname new_date val_holiday field_check_update_if _ok
---------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 999 0
111 aaaa 16/03/2008 888 0
111 aaaa 18/03/2008 77 0
111 aaaa 19/03/2008 9 0
111 aaaa 20/03/2008 111 0
111 aaaa 21/03/2008 12 0

222 bbb 02/05/2008 15 0
222 bbb 03/05/2008 16 0
222 bbb 04/05/2008 9 0
222 bbb 05/05/2008 3 0
222 bbb 06/05/2008 90 0
222 bbb 07/05/2008 3 0
222 bbb 08/05/2008 3 0
222 bbb 09/05/2008 3 0

333 ccc 03/04/2008 4 0
333 ccc 04/04/2008 4 0


----------------------------------------------------------------- table B
ID fname new_date val_holiday
----------------------------------------------------

111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1

222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3

333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
------------------------------------------------------------------------------
TNX for the help and for all

View 5 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

Help Needed Little Urgent---how To Convert The String Date To Standard Date Format In SQL Table

Sep 28, 2007

Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,

I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.

I have no choice of making date column in SQL to string or Varchar etc,

is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).

many many thanks,

View 2 Replies View Related

Transact SQL :: How To Select Rows From Table Where DATE Column Is Today's Date

Aug 31, 2015

So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.

SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()

Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?

View 2 Replies View Related

Integration Services :: How To Load Multiple Date Format Column Date Into Table Using SSIS

Jun 15, 2015

i have a excel file in which i have a date column it having the below date formats below 

Install Date

20140721

31.07.2014

07.04.2015

20150108

20140811

20150216

7/21/2014

11.08.2014

07.08.2014

So using SSIS how we would load this date column into the table into one format like dd/mm/yyyy or any single date format

View 6 Replies View Related

Extract Date,month, Year From The Date Getting From Sql Table

Apr 14, 2008

Hello All,
i have three textboxes in a page and i want fill those textboxes  with the date, month,year respectively.....
i have a datecreated column in discount table in a mm/dd/yy format ...how to extract the date, month, year from this format and put the value in textboxes..?
Any help..
Thanks..
Anne

View 3 Replies View Related

How To Populate Fact Table With Date Id From Date Dimension

Dec 28, 2007

can someone help me with th best way to look up a date in date dimension and populate the date id in fact.
in the source date is dd/mm/yyyy
and in date dimension columns are date id , year , quarter , month, day

View 11 Replies View Related

T-SQL (SS2K8) :: Returning Stored Procedure Results Into CTE Or Temp Table?

Aug 20, 2013

Is it possible to return the results of a stored procedure into either a CTE or temp table?

In other words, is it possible to do this:

with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???

View 9 Replies View Related

Is A Temp Table Or A Table Variable Used In UDF's Returning A Table?

Sep 17, 2007

In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
 

View 1 Replies View Related

Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table

Nov 1, 2007

I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
 
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date' 
 
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return.  Same goes for the last_name field.   Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..

View 7 Replies View Related

SQL Server 2012 :: Table Returning Function With Input Table Name As Parameter

Nov 19, 2014

I'm using SS 2012.

I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.

In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.

The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.

This means switching to the multi-line function, which I will if I have to, but those are more tedious.

Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?

A simple example of what I'm trying to do is below:

Create FUNCTION [CSH388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)

Error I get is:

Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".

If I use "table" as the parameter type, it gives me:

Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".

The input table can have several thousand rows.

View 9 Replies View Related

T-SQL (SS2K8) :: Procedure That Create Views With Table Name And A Table Field Parameter?

Aug 4, 2015

I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).

However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.

Below code.

ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

[code]....

View 9 Replies View Related

List By Nearest Date?

Oct 7, 2006

Hi, I'm making a birthdays database where I want to list everyone in it ordered by the firstcoming birthdays according to the current date.

I have all the birthday records stored in a table called bursdager and the person name is stored in the navn column and the persons birthday date is stored in the dato column.

I'm having some problems, currently I have this statement:


Code:


DECLARE @tbl TABLE (navn VARCHAR(60), dato DATETIME)


INSERT INTO @tbl
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) >= DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)


INSERT INTO @tbl -- those are the one who allready have had birthday this year
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) < DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)



SELECT * FROM @tbl





It works *allmost* as it should-- except, it still lists the last persons who had birthday first, even the days after their birthday if the month is still the same.

I thought about adding an additional check:
Code:

AND DATEPART(day, dato) >= DATEPART(day, getdate())

in the WHERE clause of the SELECT statement but that won't be correct either because it then just lists everyone based on whether the day number the person was born is higher or less than the day number of the current date.

Anyone have any suggestions? Is there an easier way to do it?

Dag

View 7 Replies View Related

T-SQL (SS2K8) :: Insert Causing Delay From Table To Table

Oct 28, 2014

I am trying to move data from one table to the another (staging to real time) in a stored procedure.

There are no indexes or primary keys on the target table and it is still taking ages to execute it (30 minutes approx.). There are no defaults, no constraints as well. There is one identity int column though.

There are some 500000 odd rows in the target table.

I am using the

Insert into..
Select from..

method.

View 2 Replies View Related

T-SQL (SS2K8) :: Insert Subset Of Self-referencing Table Into That Table

Mar 19, 2015

IF OBJECT_ID('tTable') IS NOT NULL
DROP TABLE tTable
GO

CREATE TABLE tTable
(nRow_IdINTEGER IDENTITY NOT NULL PRIMARY KEY,
nParent_IdINTEGERNULL,

[Code] ....

gives:

nRow_Id nParent_Id cGroup cValue
----------- ----------- ------- ------
1 1 One A
2 1 One B
3 2 One C

I want to insert a copy of this data, but w/ group = 'TWO', so the table will contain the additional rows

4 4 Two A
5 4 Two B
6 5 Tow C

View 5 Replies View Related

Optimal Search For The Nearest Date

Nov 23, 2005

I have the following tableCREATE TABLE Readings(ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY,Reading int NOT NULL)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050301', 15)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050401', 31)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050801', 51)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20051101', 106)GO-- list the tableSELECT ReadingTime, Reading FROM ReadingsGOIt is a table of readings of a free-running counter that istime-stamped. I need to determine the value of the reading thatcorresponds to the closest date to the supplied dateAre there more optimal/efficient ways of accomplishing this than thefollowing?DECLARE @when DATETIMESET @when = '20050505'SELECT TOP 1 ReadingTime, Reading FROM ReadingsORDER BY abs(DATEDIFF(minute, ReadingTime, @when))The above gives me the desired result of ('20050401', 31).Any suggestions would be appreciated

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved