|
Hi Shaz
I have done the SQL for the opening-balance and stock-added results:
SELECT SD.productId,
SUM(CASE
WHEN SD.deltaDate < @StartDate THEN SD.Delta
ELSE 0
END) AS OpeningStock,
SUM(CASE
WHEN SD.deltaDate >= @StartDate AND SD.isStockAdded = 1 THEN SD.Delta
ELSE 0
END) AS StockAdded
FROM StockDelta SD
WHERE deltaDate < @EndDate
GROUP BY productId
ORDER BY productId I'll let you figure out how to do the remaining columns.
The @StartDate and @EndDate bits refer to the parameters that you need to supply. The above should work for SQL-Server and Oracle. If you are using MS-Access then you will need to use the IIF function. An alternative approach would be to use sub-queries.
Regards
Andy
|
|
|
|
|
Hi,
I have a problem with a user.
I have the connection string:
MsSQL_conn1.ConnectionString = "Server = " & frmMain.txtMyServer.Text<br />
MsSQL_conn1.ConnectionString += "; User=RWIN" & "; Password=AVM" & "; Database =" & frmMain.txtMyDatabase.Text & ";Connect Timeout=30; Integrated Security = SSPI"
When I try to connect from my computer where I have installed the Sql Server it works.I can log on Sql Server with my user.
But when I try to connect with my program to another computer where is installed Sql Server I have the error:
Login failed for user 'LEXI-DEVELOP4\Guest'.
Please help.
Thanks
|
|
|
|
|
|
Can you please tell me about a query which I could use for the purpose of removing all the repetition(Repeated Records) in a table. I am trying with this one but its not working properly.
delete from EXCHANGE_STOCKS
where STOCK_ID not in (select distinct SYMBOL,EXCHANGE,(select top 1 STOCK_ID from EXCHANGE_STOCKS where SYMBOL = es.SYMBOL and EXCHANGE = es.EXCHANGE) as STOCK_ID
from EXCHANGE_STOCKS as es)
|
|
|
|
|
Add an Identity column to the table. for ex. colA
then execute this query
SELECT * FROM TblA a WHERE ColA < (SELECT Max(ColA) FROM TblA b WHERE b.Stock_ID = a.Stock_ID) ORDER BY a.Stock_ID
this will fetch all records except one from the duplicates
Regards
KP
|
|
|
|
|
How to authenticate the sql server
UNIQUE MUSKAN
|
|
|
|
|
what does it mean by authenticate the sql server?
Regards,
Sylvester G
sylvester_g_m@yahoo.com
|
|
|
|
|
You mean how do you authenticate a user using sql server authentication as aposed to windows authentication?
Or how do you authenticate against sql server?
Either way:
http://www.connectionstrings.com/[^]
|
|
|
|
|
i want insert like this text.mohan's.how can i insert this in sqlserver.plz send me.its urgent
|
|
|
|
|
use 'mohan'''s' and insert the value just play with apostrophee until the entire value becomes a single string!
Gautham
|
|
|
|
|
thank u .but how can i concate in asp.net for insert
|
|
|
|
|
refer to message below with subject
"Problem with special characters in SqlParameter"
similary usage is explained as what you are looking for,
in different ways ...
Regards
KP
|
|
|
|
|
if you are using asp.net with C# then try
TextBox1.Text.Replace("'","''");
I Love SQL
|
|
|
|
|
Please help
I want to create stored procedure using two tables &
Want to commit data of first table & rollback .
Thnx
CARe CAREER
|
|
|
|
|
are the DML operations are done one after the other in batch or jumbled
Regards
KP
|
|
|
|
|
I want to create a stored procedure in which while inserting or updating a record if a flag is set then i have to insert some other values in the parent table and based on the id which is auto generated by the parent table i have to insert that value into the child table How can i perform this.
I have got already two procedures for inserting and updating the data into the tables.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
IF @flag = <value>
BEGIN
<insert data into master table>
<insert data into child table>
END
@@IDENTITY returns last generated value in Identity column.
Regards
KP
|
|
|
|
|
Hi guys i have doubt in max values...,
See this is my table...,
salary
====
1000
2000
3000
4000
4500
1500
my expectation is produce only the 4000 from the given table..
which means second maximum salary..?
How to do that...,
Plz help me..,
Magi
|
|
|
|
|
select * from tblname where sal=(select max(sal) from tblname
where sal < (select max(
sal) from tblname));
Magesh
|
|
|
|
|
DECLARE @nth as bigint
SET @nth = 10
SELECT * FROM TblA AS a WHERE @nth = (SELECT COUNT(ColA) FROM
TblB AS b WHERE b.ColA >= a.ColA)
This will return 10th Max.
change value of the variable @nth to get the required Max.
The same i've given earlier for finding Nth Max value
in your case assign 2 to @nth variable
Regards
KP
|
|
|
|
|
DECLARE @nth as bigint
SET @nth = 10
SELECT * FROM TblA AS a WHERE @nth = (SELECT COUNT(ColA) FROM
TblB AS b WHERE b.ColA >= a.ColA)
This will return 10th Max.
change value of the variable @nth to get the required Max.
The same i've given earlier for finding Nth Max value
in your case assign 2 to @nth variable
Regards
KP
|
|
|
|
|
hey its very simple man.
select top second salary from table_name order by salary desc.
Since in your table the 4000 is the second top salary.
Regards,
Satips.
|
|
|
|
|
Hi satips plz check ur query in ur query analyzer...,
It willn't work...,
Regards,
Magi
|
|
|
|
|
select max(salary) from mytable where salary < (select max(salary) from mytable)
Regards,
Sylvester G
sylvester_g_m@yahoo.com
|
|
|
|
|
C# And Access DB.
There is an Autonumber field in the table, 'PP_WF_ID'. Every field is Text Type except Personnel_Id And Company_Id
<br />
bool InsertWorkFlowPP()<br />
{<br />
if (Sel.IsAuthenticated())<br />
{<br />
try<br />
{<br />
using (OleDbConnection myconn = new OleDbConnection())<br />
{<br />
OleDbCommand mycomm;<br />
string sSQL = "INSERT INTO PP_WF(Personnel_ID,Photo_Location,pp_Name,Position,";<br />
sSQL += "Highlights,Education,Projects,";<br />
sSQL += "Full_Description,Summary,Company_ID,WF_STATUS,Is_CEO)";<br />
sSQL += " VALUES(@Personnel_ID,@Photo_Location,@pp_Name,@Position,";<br />
sSQL += "@Highlights,@Education,@Projects,@Full_Description,@Summary,@Company_ID,@WF_STATUS,@Is_CEO);";<br />
myconn.ConnectionString = ConfigHelper.GetWFString();<br />
mycomm = new OleDbCommand(sSQL, myconn);<br />
mycomm.Parameters.AddWithValue("@Personnel_ID", System.Convert.ToInt32(Labelcode.Text));<br />
mycomm.Parameters.AddWithValue("@Photo_Location", picture.ImageUrl);<br />
mycomm.Parameters.AddWithValue("@pp_Name", name.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Position", position.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Highlights", highlights.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Education", education.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Projects", projects.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Full_Description", longbio.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Summary", shortbio.Text.Trim());<br />
mycomm.Parameters.AddWithValue("@Company_Id",Sel.GetCookies("Company_id"));<br />
mycomm.Parameters.AddWithValue("@WF_STATUS", "NA");<br />
mycomm.Parameters.AddWithValue("@is_CEO", chkCEO.Checked.ToString());<br />
myconn.Open();<br />
mycomm.ExecuteNonQuery();<br />
return true;<br />
}<br />
}<br />
catch (Exception)<br />
{<br />
}<br />
finally<br />
{<br />
}<br />
}<br />
return false;<br />
}<br />
|
|
|
|