Hello,
I have a table that keeps track of users subscription and unsubscription. If a user has unsubscribed, i need to retrieve the last time the user has subscribed given the field creationdate??????
e.g
User1 Subscribed 2008/02/20
User2 Subscribed 2008/02/20
User1 Unsubscribed 2008/02/23
User1 Subscribed 2008/02/27
User2 Subscribed 2008/02/27
User1 Unsubscribed 2008/02/28
Please help!!!
Hi All
How can I checked the publication are subscribed to all subscribers?
is there any stored procedure or method to do this?
Thanks alot
Tolga
i have a report that is on a subscription to my team members that i want to be rendered in the actual email that they open up. i do not want them to have to click on the pdf or excel attachment to open it up, i just want the content of the report to be in the email itself in outlook.
is this possible in SSRS 05?
Hye all,
I need help on this.
Let say my source file in excel format. Currently i'm using foreach loop to execute those source files. The problem is that , i want to retrieve a date from the source(filename) itself. i've tried using derived column. but it doesnt work.
eg: Source file name Email_Jan_05.xls, i want to grab the string JAN and 05 which later i'm going to put it in my database.
any suggestion? Thanks in advance.
I am trying to write a Stored proc to retrieve only records from my table tat do not have a DateTime value ie DateTime is NULL and for some reason simply adding a DateRemoved = NULL does not work. Can anyone please help me with solution to this issue please??? Thanks in Advance
View 3 Replies View RelatedHI
I want to retrieve data in between two date formats using a query in SQL?
can i do it w/o using GUI tools?
For Exp i have sales data from date 11/11/2000 to 11/2004.
now as a user i want to give Input paramete value ranging between 06/06/2002 and 07/07/2002?
Is there any SQL query which i can use to retrieve the above date values?
Thanx in Advance
VS
I Apologize if this isn't the forum to ask this...
I have a MS Access (MDB) file with a table with 2 date fields, i want to read from a dialog on my app (on MS Visual .NET Studio 2005), here's the code I've been using do far:
Code Snippet
hr=theApp.m_cs.Open(theApp.m_ds);
if(SUCCEEDED(hr)) {
theApp.m_cs.StartTransaction();
theApp.m_cs.Commit();
CCommand< CDynamicAccessor > cmd;
CComBSTR query(_T("SELECT NumContrato, NumClie, FechaC, FechaCob, Inversion, NoCobrador, NoVendedor, Total, Plazo, Pagos FROM Contrato"));
CString string(query.m_str);
cmd.Open(theApp.m_cs,string);
hr = cmd.MoveFirst();
query=static_cast< BSTR >(cmd.GetValue(1));
CString csres(query.m_str);
this->m_numc=(int)*(query.m_str);
query=static_cast< BSTR >(cmd.GetValue(2));
m_numcte=(int)*(query.m_str);
query=static_cast< BSTR >(cmd.GetValue(3));
//m_fecc=(int)*(query.m_str);
MessageBox(csres);
theApp.m_cs.Close();
}
FechaC, FechaCob, are the two Dates I want to retrieve, but when I debug, it reads a 0 (zero) from the date fields, is there a limitation? can they be read? is there a special way to read them?
> thanks in advance!
-----
Me!
hello all
pls tell me
how can i insert, retrieve date from ASPX(vs2003) page in table in sql server 2000??
i m in trouble
pls help me
Hi
I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 .
Any ideas?
Thanks...
Below is the query in which i want to retrieve another column (exchange rate) from a particular date for the sub query.
Actually PurchaseOrderDet table get records related to purchaseorder but for each row in purchaseorderdet need info from the same table for all rows on a particular date.
how i can achieve this query without using RANK() and other functions.
"SELECT Supplier.Uniid AS SupplierID, Supplier.Name, PurchaseOrder.Uniid AS PurchaseID, PurchaseOrder.OrderNo, PurchaseOrder.FormDate, StockItem.Uniid AS StockID, StockItem.StockCode + N' - ' + StockItem.Description1 AS StockItem,
PurchaseOrderDet.ExchangeRate, PurchaseOrderDet.UnitPrice, PurchaseOrderDet.Discount, SUM(PurchaseOrderDet.OrderQty) AS SumOfOrderQty,
[Code] ....
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.
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies View RelatedIn SQL Server 2000:
How do I convert a Julian date to a Gregorian date?
How do I convert a Gregorian date to Julian?
Examples please.
Many thanks in advance.
Gary Andrews
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
HI Chaps
very simple question this time
Is there any method availabe to retrieve AM or PM value from date, IN TSQL
hoping will get answer shortly
regards,
Anas
I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC
Thanks
Steve
I need to retrieve points column from my database for the specific user that is signed on and sum all of them. How can I do it using the Gridview and also code in vb for a label? Thanks.
View 4 Replies View RelatedI am trying to populate some controls on a web page with values retrieved from a sql server database recordset. The text type controls work fine. However I have a Check Box on my form. I get a runtime error when I try to write into it. So tried wrting the value into a text control and was suprised to see the value retrieved was "S00817". Heres the relevant line of code
Message.Innerhtml=MyDataset.tables(0).Rows(0)(12)
I would expect this to come up with a 1 or a 0. I get S00817 if the database record holds a 1 or a 0. So what is going on here?
Hi all,
Is it possible to retrieve a particular row from a result set? For eg if my query returns 5 rows and i want to just retrieve the 3rd row from the result....is it possible? If yes...can someone tell me the syntax for it....would appreciate the gr8 help...
Thanks,
SQL Novice
Here is my sql procedure:
ALTER PROCEDURE dbo.SoftWareShow /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ @SoftID uniqueidentifierAS SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount],ltrim(rtrim([SoftUrlOne])) SoftUrlOne, ltrim(rtrim([SoftUrlTwo])) SoftUrlTwo, ltrim(rtrim([SoftUrlThree])) SoftUrlThree, ltrim(rtrim([SoftUrlFour])) SoftUrlFour FROM [SoftWare] WHERE ([SoftID] = @SoftID) RETURNwhere I retrieve data using sqldatasource, an error appear. how can do ?
i have a SQL backend and some of the data in my tables is more or less configuration data and some times i only want to pull one row of data which i can do just fine with a SQL query but whats the best method to pull it.
assume i have a query like this.
Select A, B, C from Table1 where ID=xyz
whats the easiest way to get A, B and C in to string variables?
I know i can use the sqldatasource control but i just feel there is too much overhead there.
whats your suggestions?
Thanks
Justin
I want to do something similar to ExecuteScalar in ADO.net but instead in T-SQL.Basically I want to do a query that will return the first value in the table queried and put it into a variable. How do I do this?Thanks in advance.
View 3 Replies View RelatedCould you help me out?
I am interested in retrieving certain record among outputs.
For example, if I use the following sql,
select * from info order by name asc
====================================
then I can retrieve 25 rows from info table.
What I want to do is that I want to retrieve 15th record only among possible 25 records.
How can I solve this problem?
Thanks in advance.
Hello!
is it possible to have the count(*) to display 0 when there is no matching hits for that n_id?
my query looks like this but only displays the n_id and it's respective count(*) when count(*) is more than 0...
select n_id, count(*) from tblTable
where nSomething > nSomethingElse AND nSomething IS NOT NULL
group by n_id
any idaes?
Hi all,
I want to retrieve the second maximum value of a column data present in SQL table.Please help....
A
----
10
25
23
15
here I want 23 as the result.
Hi folks,
Does anyone know how to retrieve the date when data has been inserted
thnx in advance
i have a table called tblpictures which look something like this..
filename|ID
----------
1 |p1
2 |p1
3 |p2
4 |p2
5 |p3
is there a way to retrieve only 1 row for each ID? how will the select statement looks like?? please help me..
hi gurus,
how to get " using bcp
e.g
select '"'+name+'"' from sysobjects
results
"name"
"name1"
I want to get the same using bcp ,so that i can populate into a file
something like
set @cmd='BCP "select '"' +name +'"' ,uid from sysobjects" QUERYOUT "' + 'vrs.txt' + '" -w -T -S -t , '
-- Executing the BCP Command
EXEC MASTER..XP_CMDSHELL @Cmd
I have a question, one user made mistake that she delete one record from a database. How can i retrieve this only one record. I just know how to restore the database.
Thanks.
Can any one please tell me how to retrieve values from timestamp column.
I am getting 1900-01-01 00:02:09.457. It is not storing current timestamp when record is created or modified.
Is there anything I need to set it up.
thanks
Hi, all.
I tried to get result of sp.
Dim rst As Recordset
Set rst = New ADODB.Recordset
rst.Open sp, cnn
doesn't get result.
when i call rst.EOF, it thows error: Can do this since rst is closed...
I found it's problem of sp which is little complex.
But, still I think it should work!
My question is how can I get the returned value from following sp in VB?
--- return list of tables that needed to update
--- list is one string separated by '&' delimeter
--- @listOfUpdateTime: [TableName=UpdateTime]&[...] eg) tblDeptList=12/25/2004&tblHoliday=12/24/2004&...
CREATE procedure spGetListOfTableToDownLoad
@listOfUpdateTime varchar(500)
as
SET NOCOUNT ON
Declare @listOfTable varchar(300), @item varchar(300)
Declare @tbl varchar(50), @uptime datetime
Declare @list varchar(500)
Declare @sep varchar(1)
SET @list = ''
SET @sep = '&'
DECLARE cur CURSOR FAST_FORWARD FOR
SELECT * FROM fnSplit(@listOfUpdateTime, @sep)
OPEN cur
FETCH NEXT
FROM cur
INTO @item
Declare @re bit, @tp varchar(50)
WHILE @@FETCH_STATUS = 0
BEGIN
-- get tablename, update time
Declare cur2 CURSOR FAST_FORWARD FOR
SELECT * FROM fnSplit(@item, '=')
OPEN cur2
FETCH NEXT FROM cur2 INTO @tbl
Print @tbl
FETCH NEXT FROM cur2 INTO @tp
print 'tp:' + @tp
SET @uptime = CAST(@tp as datetime)
print @uptime
-- @re =1: true, 0: false
EXEC spIsUpdate @tbl, @uptime, @re output
IF @re = 1
SET @list = @list + @tbl + @sep
CLOSE cur2
DEALLOCATE cur2
FETCH NEXT
FROM cur
INTO @item
END
if LEN(@list) > 0
SET @list = LEFT(@list, Len(@list)-Len(@sep))
CLOSE cur
DEALLOCATE cur
SELECT @list as Result
SET NOCOUNT OFF
GO
__________________
--- PARAM:: @tbl: table name,
--- @uptime : update time (passed from local db) that will be compared on HQ table
--- return 1 if Max(UpdateTime) of @table > @uptime
--- otherwise return 0
CREATE Procedure spIsUpdate
@tbl varchar(50), @uptime datetime, @result bit output
as
BEGIN
Declare @bit bit
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
declare @uptimeHQ datetime
/* Build the SQL string once.*/
SET @SQLString = N'SELECT @tp = MAX(UpdateTime) FROM ' + @tbl
SET @ParmDefinition = N'@tp datetime OUTPUT'
EXECUTE sp_executesql @SQLString, @ParmDefinition
,@uptimeHQ OUTPUT
If @uptimeHQ > @uptime
SET @result = 1
ELSE
SET @result = 0
--RETURN @bit
END
GO
I am trying t get output for the following querry but I know am missing something. Can anyone help me out with it.
select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity
from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)
In the above query am getting the productnum and quanity and it looks like this
productnum quantity
abc 6
ttt 3
sss 1
What am tring to do to this query is that . From another table 'product' i want all the data to be retrieved with this productnum(the table 'product' has a column called prductnum). I don't know how to write a query for this.
my query is
select * from product where productnum in (
select distinct productnum, (SELECT SUM(quantity) FROM orderlineitem w WHERE w.productnum = e.productnum) as Quantity
from orderlineitem e where parentOrderlineitemid is null order by Quantity desc)
Thanks.
Hi all
I've two tables
community, community_related.
I need to get community names for comm_relatedID from community table
where comm_id = 21 , please see my tables below
Community table Community Related
commID, comm_name comm_id comm_relatedID
21 test1 21 24
22 test2 21 22
23 test3 21 26
24 test 5 21 27
26 test6
27 test7
result am trying to display from community related table after joining community table on COMM_ID = 21 is
comm_id comm_relatedID comm_name
21 24 test5
21 22 test2
21 26 test6
21 27 test7
can anybody please help..with a query, am stuck at this point.