This Sql Command Make Me Sick
Jun 1, 2006
hello everyone...
after several days trying to find the right command.. i have to ask you
people...
this is the deal:
i have two tables related...
the first one is the master, the second the slave...
the first has fields like code, name, department and periodicity of
payment...
the second has the payment data... code, date of payment, amount to
pay...
not every one in the first table has the payment in the same month...
because a few people receives its pay one time in the month, and other
receives payment every two or three months (periodicity of payment)...
my trouble is when i want to see all the payments in one month of the
people from one specific department, because if somebody doesn't get
his payment (different periodicity) i have to see the code and name of
the worker AND the date and amount of payment in blank (in that
case)...
i thing that i must to be a simple LEFT JOIN but it doesn't work in
Access (i have a web asp page with and access database in this case)...
i don't know what to do.. please.. if somebody has some ideas.. i need
it...
thanks..
Enzo..
ps/.. my left join code:
SELECT table1.name, table2.date, table2.amount
FROM table1 LEFT JOIN table2 ON table1.code = table2.code
WHERE (((table1.departm)=53) AND ((table2.date)=#2/15/2006#));
View 3 Replies
ADVERTISEMENT
Dec 10, 2004
I am probably dreaming, but I am stumped on this!
The LY data comes back and balances perfectly and the CUR balances perfectly, but when I join them together I have a few columns that are off and I cant find theproblem, I have been staring at this for a day now!
Not sure of any good ways to give more detail to help diagnose problem without access to db....
here goes:
SELECT [Reverse] as Reversed, Descr, LINES.LineNu as LineNu, LineType,
Amt01 = Sum(isNull(Amt01,0)),
Amt02 = Sum(isNull(Amt02,0)),
Amt03 = Sum(isNull(Amt03,0)),
Amt04 = Sum(isNull(Amt04,0)),
Amt05 = Sum(isNull(Amt05,0)),
Amt06 = Sum(isNull(Amt06,0)),
Amt07 = Sum(isNull(Amt07,0)),
Amt08 = Sum(isNull(Amt08,0)),
Amt09 = Sum(isNull(Amt09,0)),
Amt10 = Sum(isNull(Amt10,0)),
Amt11 = Sum(isNull(Amt11,0)),
Amt12 = Sum(isNull(Amt12,0)),
Amt13 = Sum(isNull(Amt13,0)),
Bud01 = Sum(isNull(Bud01,0)),
Bud02 = Sum(isNull(Bud02,0)),
Bud03 = Sum(isNull(Bud03,0)),
Bud04 = Sum(isNull(Bud04,0)),
Bud05 = Sum(isNull(Bud05,0)),
Bud06 = Sum(isNull(Bud06,0)),
Bud07 = Sum(isNull(Bud07,0)),
Bud08 = Sum(isNull(Bud08,0)),
Bud09 = Sum(isNull(Bud09,0)),
Bud10 = Sum(isNull(Bud10,0)),
Bud11 = Sum(isNull(Bud11,0)),
Bud12 = Sum(isNull(Bud12,0)),
Bud13 = Sum(isNull(Bud13,0)),
LyAmt01 = Sum(isNull(LyAmt01,0)),
LyAmt02 = Sum(isNull(LyAmt02,0)),
LyAmt03 = Sum(isNull(LyAmt03,0)),
LyAmt04 = Sum(isNull(LyAmt04,0)),
LyAmt05 = Sum(isNull(LyAmt05,0)),
LyAmt06 = Sum(isNull(LyAmt06,0)),
LyAmt07 = Sum(isNull(LyAmt07,0)),
LyAmt08 = Sum(isNull(LyAmt08,0)),
LyAmt09 = Sum(isNull(LyAmt09,0)),
LyAmt10 = Sum(isNull(LyAmt10,0)),
LyAmt11 = Sum(isNull(LyAmt11,0)),
LyAmt12 = Sum(isNull(LyAmt12,0)),
LyAmt13 = Sum(isNull(LyAmt13,0)),
LyBud01 = Sum(isNull(LyBud01,0)),
LyBud02 = Sum(isNull(LyBud02,0)),
LyBud03 = Sum(isNull(LyBud03,0)),
LyBud04 = Sum(isNull(LyBud04,0)),
LyBud05 = Sum(isNull(LyBud05,0)),
LyBud06 = Sum(isNull(LyBud06,0)),
LyBud07 = Sum(isNull(LyBud07,0)),
LyBud08 = Sum(isNull(LyBud08,0)),
LyBud09 = Sum(isNull(LyBud09,0)),
LyBud10 = Sum(isNull(LyBud10,0)),
LyBud11 = Sum(isNull(LyBud11,0)),
LyBud12 = Sum(isNull(LyBud12,0)),
LyBud13 = Sum(isNull(LyBud13,0))
FROM (
SELECT [Reverse], Descr, LineNu, LineType
FROM shelbydb.shelby.glReportLines
where (reportcounter = 65)
) LINES
LEFT OUTER JOIN (
SELECT AcctNuHigh, AcctNuLow, LineNu, DeptNu, FundNu
from shelbydb.shelby.glReportLinks
where (reportcounter = 65)
) LINKS
on LINKS.linenu = LINES.linenu
LEFT OUTER JOIN (
SELECT * FROM (
SELECT s.conu as CoNu, s.deptnu as DeptNu,
s.fundnu as FundNu, s.acctnu as AcctNu,
Amt01 = Sum(isNull(Amt01,0)),
Amt02 = Sum(isNull(Amt02,0)),
Amt03 = Sum(isNull(Amt03,0)),
Amt04 = Sum(isNull(Amt04,0)),
Amt05 = Sum(isNull(Amt05,0)),
Amt06 = Sum(isNull(Amt06,0)),
Amt07 = Sum(isNull(Amt07,0)),
Amt08 = Sum(isNull(Amt08,0)),
Amt09 = Sum(isNull(Amt09,0)),
Amt10 = Sum(isNull(Amt10,0)),
Amt11 = Sum(isNull(Amt11,0)),
Amt12 = Sum(isNull(Amt12,0)),
Amt13 = Sum(isNull(Amt13,0)),
Bud01 = Sum(isNull(Bud01,0)),
Bud02 = Sum(isNull(Bud02,0)),
Bud03 = Sum(isNull(Bud03,0)),
Bud04 = Sum(isNull(Bud04,0)),
Bud05 = Sum(isNull(Bud05,0)),
Bud06 = Sum(isNull(Bud06,0)),
Bud07 = Sum(isNull(Bud07,0)),
Bud08 = Sum(isNull(Bud08,0)),
Bud09 = Sum(isNull(Bud09,0)),
Bud10 = Sum(isNull(Bud10,0)),
Bud11 = Sum(isNull(Bud11,0)),
Bud12 = Sum(isNull(Bud12,0)),
Bud13 = Sum(isNull(Bud13,0))
FROM shelbydb.shelby.GLSummary S
LEFT OUTER JOIN shelbydb.shelby.GLBudget B
on
(s.begindate = b.begindate)
and (s.acctnu = b.acctnu)
and (s.conu = b.conu)
and (s.deptnu = b.deptnu)
and (s.fundNu = b.fundNu)
WHERE
s.begindate = '1/1/2004'
group by
S.conu, S.deptnu, S.fundnu, S.acctnu,
b.conu, b.deptnu, b.fundnu, b.acctnu
) CUR
LEFT OUTER JOIN
(
SELECT
LyS.conu as LCoNu, LyS.deptnu as LDeptNu,
LyS.fundnu as LFundNu, LyS.acctnu as LAcctNu,
LyAmt01 = Sum(isNull(Amt01,0)),
LyAmt02 = Sum(isNull(Amt02,0)),
LyAmt03 = Sum(isNull(Amt03,0)),
LyAmt04 = Sum(isNull(Amt04,0)),
LyAmt05 = Sum(isNull(Amt05,0)),
LyAmt06 = Sum(isNull(Amt06,0)),
LyAmt07 = Sum(isNull(Amt07,0)),
LyAmt08 = Sum(isNull(Amt08,0)),
LyAmt09 = Sum(isNull(Amt09,0)),
LyAmt10 = Sum(isNull(Amt10,0)),
LyAmt11 = Sum(isNull(Amt11,0)),
LyAmt12 = Sum(isNull(Amt12,0)),
LyAmt13 = Sum(isNull(Amt13,0)),
LyBud01 = Sum(isNull(Bud01,0)),
LyBud02 = Sum(isNull(Bud02,0)),
LyBud03 = Sum(isNull(Bud03,0)),
LyBud04 = Sum(isNull(Bud04,0)),
LyBud05 = Sum(isNull(Bud05,0)),
LyBud06 = Sum(isNull(Bud06,0)),
LyBud07 = Sum(isNull(Bud07,0)),
LyBud08 = Sum(isNull(Bud08,0)),
LyBud09 = Sum(isNull(Bud09,0)),
LyBud10 = Sum(isNull(Bud10,0)),
LyBud11 = Sum(isNull(Bud11,0)),
LyBud12 = Sum(isNull(Bud12,0)),
LyBud13 = Sum(isNull(Bud13,0))
FROM shelbydb.shelby.GLSummary LyS
LEFT OUTER JOIN shelbydb.shelby.GLBudget LyB
on
(LyS.begindate = LyB.begindate)
and (LyS.acctnu = LyB.acctnu)
and (LyS.conu = LyB.conu)
and (LyS.deptnu = LyB.deptnu)
and (LyS.fundNu = LyB.fundNu)
where
LyS.begindate = '1/1/2003'
group by
LyS.conu, LyS.deptnu, LyS.fundnu, LyS.acctnu,
LyB.conu, LyB.deptnu, LyB.fundnu, LyB.acctnu
) LY
ON
(CUR.acctnu = LY.Lacctnu)
and (CUR.conu = LY.Lconu)
and (CUR.deptnu = LY.Ldeptnu)
and (CUR.fundNu = LY.LfundNu)
) GL
ON
LINKS.acctnuLow <= GL.acctnu
and
LINKS.acctnuhigh >= GL.acctnu
and
LINKS.deptnu =
( case when links.deptnu <> -1
then GL.deptnu
else LINKS.deptnu end )
and
LINKS.fundnu =
( case when links.fundnu <> -1
then GL.fundNu
else LINKS.fundnu end )
GROUP BY [Reverse], Descr, LINES.LineNu, LINES.LineType
ORDER BY LINES.linenu
View 3 Replies
View Related
Jul 29, 2010
When I run the parse command in SSMS, it merely does a syntax check. When I run through the export data wizard by right clicking on a table, it allows a query as the data source. When I click on the parse button in the window that accepts that query, it resolves object names and notifies me of invalid ones. I'd really like the same thing to happen when I parse in SSMS...
View 5 Replies
View Related
Nov 20, 2006
Hi,
I have a database with a giant logfile which I am having trouble shrinking - 25GB log file for a 10MB database. I cannot back up the database because I get some sort of disk failure in the application log file (there is plenty of room on the disk I think - 35GB). (Note: the software RAID system on this box is suspect.)
I cannot detach the database because it is being replicated.
I cannot remove the replication because the server the publication refers to no longer exists and I get an error about only being allowed to use real server names (a real Catch 22 there).
If I stop the server and rename the log file, the database becomes "suspect". Renaming it back fixes it.
This is an inherited database and I don't really know what it does. It is related to our (old but not quite redundant yet) Citrix farm (the database is called "Citrix"), but I do not know whether it is necessary.
Any suggestions appreciated - especially if you have knowledge of what the Citrix database does. E.g. would the simple recovery model be adequate for this application??
If you think it is a lost cause, say so politely please.
Thanks in advance
View 6 Replies
View Related
Sep 10, 2006
This is exactly what I do with the MS SQL 2005 Express-Advanced installer.
Accept>Next
Required Components Installed Successfully>Next
"Welcome to the....">Next
13 Success, 2 Warning: Minimum Hardware, IIS Feature Requirement>Next
Set name/company+hide advanced options>next
Accepted default install options>next
Windows Authentication>next
none checked>next
Install
ERROR: An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'.
Setup Failed: SQL Native Client, SQL Server Database Services.
I got the same problem with the non advanced installer, and I even selected all the options for the install using both. Why am I getting this error?!
View 3 Replies
View Related
Feb 23, 2007
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View 2 Replies
View Related
Nov 4, 2006
Hi All,
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Please let me know where am going wrong?
Thanks in advance.
View 12 Replies
View Related
Aug 30, 2004
Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks
View 1 Replies
View Related
Sep 19, 2006
Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
Connections oPkgConns = oPackage.Connections;
// Foxpro Connection
ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");
oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere
oFoxConn.Name = "SourceConnectionOLEDB";
oFoxConn.Description = "OLEDB Connection For Foxpro Database";
// SQL Server Connection
ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");
oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere
oSQLConn.Name = "DestinationConnectionOLEDB";
oSQLConn.Description = "OLEDB Connection For SQL Server Database";
// Add Prepare SQL Task
Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");
TaskHost thSQLTask = exSQLTask as TaskHost;
thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");
thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);
thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);
thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql");
thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);
thSQLTask.FailPackageOnFailure = true;
// Add Data Flow Tasks. Create a separate task for each table.
// Get a list of tables from the source folder
arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");
for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)
{
// Get the name of the file from the array
sDataFile = Path.GetFileName(arFiles[iCount].ToString());
sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);
oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;
oDataFlow.AutoGenerateIDForNewObjects = true;
// Create the source component
IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();
oSource.Name = (sDataFile + "Src");
oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper srcDesignTime = oSource.Instantiate();
srcDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oSource.RuntimeConnectionCollection.Count > 0)
{
oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;
oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);
}
// Set Custom Properties
srcDesignTime.SetComponentProperty("AccessMode", 0);
srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);
srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);
// Re-initialize metadata
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
// Create Destination component
IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();
oDestination.Name = (sDataFile + "Dest");
oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper destDesignTime = oDestination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oDestination.RuntimeConnectionCollection.Count > 0)
{
oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;
oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);
}
// Set custom properties
destDesignTime.SetComponentProperty("AccessMode", 2);
destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");
// Create the path to link the source and destination components of the dataflow
IDTSPath90 dfPath = oDataFlow.PathCollection.New();
dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);
// Iterate through the inputs of the component.
foreach (IDTSInput90 input in oDestination.InputCollection)
{
// Get the virtual input column collection
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the column collection
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
}
//Map external metadata to the inputcolumn
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();
externalColumn.Name = inputColumn.Name;
externalColumn.Precision = inputColumn.Precision;
externalColumn.Length = inputColumn.Length;
externalColumn.DataType = inputColumn.DataType;
externalColumn.Scale = inputColumn.Scale;
// Map the external column to the input column.
inputColumn.ExternalMetadataColumnID = externalColumn.ID;
}
}
}
// Add precedence constraints to the package executables
PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);
pcTasks.Value = DTSExecResult.Success;
for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)
{
pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);
pcTasks.Value = DTSExecResult.Success;
}
// Validate the package
DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);
// Check if the package was successfully executed
if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))
{
string sErrorMessage = "";
foreach (DtsError pkgError in oPackage.Errors)
{
sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "";
sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "";
sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "";
sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "";
sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "";
sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "";
sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "";
sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;
}
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
// return a successful result
return true;
}
View 2 Replies
View Related
Jun 30, 2006
Can somebody help me convert this SQL2000 Function TO C# Function pleaseCREATE Function dbo.CalculateNextRentDate ( @Rent_Payment_Date datetime, @Frequency varchar(50) , @Number int)RETURNS DATETIMEASbeginDECLARE @NextPaymentDate DatetimeSET @Number = @Number - 1.IF @Frequency = 'month' IF @rent_payment_date = dateadd(month, datediff(month, 0+@Number, @rent_payment_date) + 1, -1) BEGIN SET @NextPaymentDate = dateadd(month, datediff(month, 0, @rent_payment_date) + 2, -1) END ELSE BEGIN SET @NextPaymentDate = dateadd(month, 1+@Number, @rent_payment_date) END return @NextPaymentDateend
View 1 Replies
View Related
Aug 16, 2007
Our company has its Departments And Services.
Now We are making it online.
Both have separate email list, phone numbers, and more.
Will I make one table and adds the field Type (Values: D or S).
Or make them separate.
Remember one thing If we merge them then Email And PhoneNumber Table will also me merge
other wise they will also separate.
What is better.
View 13 Replies
View Related
Mar 29, 2004
How to make our own DTS package I have to split the data on my own.. Data is very biig almost 30 to 40 million. I need to splitt them into 10k chunks in database and with my desired table name.
Waiting for a +tive reply.
Regards
Shani ;)
View 6 Replies
View Related
Feb 12, 2008
hi i have a tabel in my database i tray to generat report for this tabel
this tabel have all this fields:
company_id
emp_no
seq_no
interval_date
in_time
in_type
out_time
out_type
wage_code
status
i want all his colums can be search
but with company_id
like if he enter company_id and emp_no okay give him result if he enter Company_id and interval_date okay give him result
i write this
Code Snippet
SELECT company_id, emp_no, seq_no, interval_date, in_time, in_type, out_time, out_type, wage_code, status
FROM interval
WHERE (company_id LIKE @CompanyID) AND (emp_no LIKE @EmployeeID) OR
(company_id = @CompanyID) AND (interval_date = @IntervalDate) OR
(company_id = @CompanyID) AND (in_time = @InTime) OR
(company_id = @CompanyID) AND (in_type = @InType) OR
(company_id = @CompanyID) AND (out_time = @OutTime) OR
(company_id = @CompanyID) AND (out_type = @OutType) OR
(company_id = @CompanyID) AND (wage_code = @WageCode) OR
(company_id = @CompanyID) AND (status = @Status)
but in report preview it tell me i must enter intrevalDate ?
View 8 Replies
View Related
Apr 5, 2006
Hi have have this problem, I have a table called PABX that has all the callings registry and what I need to do is for each client(PABX.cod_client) I have 2 types of calls (VC1, VC2) , and for these types I need to select all the registries chaging the dialed number(PABX.NRTELEFONE) for the new one (TROCAR.NRTELEFONE) and for those client that doesn't need to change select the PABX.NRTELEFONE
is it possible through SQL Server 2000(via stored procedure) or I'll need to do it by my application using a vector ?
Thanks
View 4 Replies
View Related
Jan 25, 2008
hiiiiiiiiii I am creating a web application using vb.net in which i m using the concept of classes. now i am done all the code for inserting the values in the database using the class but it is difficult to fetch the values from the database using select command and sending them to a WebForm . i want to know how i send send the values coming from the select command to a datagrid or another web controlif possible provide me a sample code thanks for your help
View 2 Replies
View Related
Feb 2, 2006
hello all i have a 2 questions hope that u can help me my first question is: in ms access there was a data type named yes/no and it was a checkbox is there a checkbox data type in sql server 2005?
my second question is i need the connection code between asp.net 2005 and sql server 2005 i searched in here for that code but i got more confuse i found two codes and both are not working so hope u can give me the right connecting code. that's all thanks
View 11 Replies
View Related
Sep 28, 2001
hi all,
Now i want to log some information (e.g.time,count...)during SP execution,how can i do it in Sql?
Thanks
View 1 Replies
View Related
Sep 28, 2005
Can anyone tell me how I would go about making my SQL server accessable from the Internet, or know of any good tutorials to get me started, I haven't had much luck looking on google.
I need to access an SQL database from one server on another server for a web application.
Thanks
View 4 Replies
View Related
Jun 28, 2004
Hi guys, I have a car_race table which has these fields
car_id int
race_id int
b_car_won varchar // can have 'y' or 'n'
I need to know if the car lost the 1st race but won the next race
And example of that table for car_id 1:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 2
b_car_won 'YES'
Now this is the tricky part, the database has some data integrity issues, so this can occur:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
So I cant used a fixed race_id value, need to use the race_id > 1 to know whats the next race. But this raises another issue if I have this in the database:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
car_id 1
race_id 4
b_car_won 'YES'
If I query I'd get 2 rows where race_id > 1. And I only need the first one, because 3 is the next race.
I need to fetch in a single row if possible, the result of the 1st race and the 2nd race. How can I do this?
View 11 Replies
View Related
Dec 15, 2004
Hello!
I have two tables
users and pictures.
table users have a clustered (PK) index on userid
table pictures have a clustered (PK) index on userid
when I do this query:
"select userid from pictures where userid=123"
then It will do a clustered index seek
But If I do any of those:
"select t2.userid from users t1 left join t2 on t1.userid = t2.userid"
or
"select (select userid from pictures where usedid = t1.userid) from users t1"
It will do a clustered index scan.
How can I force it to seek my index instead of scan?
Thanks!
View 1 Replies
View Related
May 26, 2006
i want to make a database for pharmacy using sql server and VB.NET
so any body can give me illuminations
thanks in advance
george albert
View 4 Replies
View Related
Aug 1, 2006
I have a T-SQL query that is used to pull up some data for once-a-day export, just out of curiosity more then anything, is there a way to make this shorter?SELECT DISTINCT u.userId,u.lastName,u.firstName,u.address1,u.address2,u.city,u.state,u.zip,CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)= 0 THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)<> 0 THEN 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) <> 0THEN 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0))WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) <> 0THEN 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0))END CPGkeyFROM [...] JOIN [...]I'm hoping there is something in a way of....If userClass Between 1 and 3 ThenIF COALESCE(u.RetailerNumber_fk,0)= 0 Then 'Corporate'ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))End IF as CPGKey,Thanks in advace.
View 1 Replies
View Related
May 29, 2007
I need to restore a copy of database to a new db name
i'm trying by creating a new db and restoring by doing
RESTORE DATABASE [userr] FROM DISK = N'D:sqlbackupsuser.bak' WITH FILE = 4, NOUNLOAD, STATS = 10
GO
i'm getting an error
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'userr' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
What's the best way to do this?
View 10 Replies
View Related
Jan 28, 2015
I thought this would be somewhat easy but I'm having trouble with this one. I have a statement that if 'ACTLABCOST' or 'ACTMATCOST' has a value of 0.00 then I need to make it .00.
Here's the statement:
Select
CONVERT(VARCHAR(10), xn_approveddate, 101) + ' ' + convert(VARCHAR(8), xn_approveddate, 108)as "Approved Date",
WORKTYPE,
[Code]....
View 5 Replies
View Related
Jan 31, 2007
If following code, it is ok if I execute part1 and q1 together.
but if I try to execute Q2, I got error
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@str'.
I guess I have to execute part1 and Q2 at same time.
Is there a way to avoid that. I mean after I execute part1 @str will be kept in memory, and I can execut q2 without a problem? (like in SAS)
Thank
/* Part1*/
/* how to make @str global*/
declare @str nvarchar(20);
set @str='%subway%';
/*Q1*/
select *, case regionname when 'telesales' then 't' else 'o' end as rn
from dbo.RPT_ContractDetails
where businessname like @str
/* Q2*/
select contracttypename,regionname, count(*)as cou, sum(fundingamount)as Dollar
from dbo.RPT_ContractDetails
where businessname like @str
group by contracttypename,regionname
View 3 Replies
View Related
May 5, 2007
Hello!
I would like to backup a database through t-sql into a file.
I do this :
EXEC sp_addumpdevice 'disk', 'mydb','c:mydb.bak'
it tells "Command(s) completed successfully."
but I can not find the file c:mydb.bak ...
but if I do it again I get :
"Msg 15026, Level 16, State 1, Procedure sp_addumpdevice, Line 74
Logical device 'mealmanager' already exists."
:-(
Any idea? I simply want to get a .bak file of the db, that's all.
Thanks a lot for any feedback!
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
View 12 Replies
View Related
May 29, 2007
hello !
Could someone tell me which collation I have to use for a text field with a unique index, but which should make the diffrence between œ and oe. (Latin1_General_CS_AS does not make a diffrence between them)
Thanks a lot for any feedback !
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
View 4 Replies
View Related
Aug 28, 2007
select contractnbr,affcontractnbr,tdd,affrtramount,affturn,
pt=datediff(d,afffirstfundingdate,affRTRBalanceZeroDate),ratio=datediff(d,afffirstfundingdate,affRTRBalanceZeroDate)/afftrun
from #targetfinishedaff
datediff(d,afffirstfundingdate,affRTRBalanceZeroDate) appears two times in this code, Is there a way to make it appear only once?
Thx
View 1 Replies
View Related
Jan 31, 2008
I have this code:
USE BDPrincipal
GO
IF OBJECT_ID(N'aquery') is not null
DROP FUNCTION aquery
GO
CREATE FUNCTION aquery()
Returns nvarchar(500)
as
Begin
Declare @var nvarchar(500);
Set @var = 'Select Distinct Description from dbo.tblScanners';
Return @var
end
GO
exec sp_executesql aquery;
I created that code to prove if it works.
But the result doesn´t appear, and the message is:
Command(s) completed successfully.
I need it to work.
Because I need to create a very dinamic query.
Please help me!
View 4 Replies
View Related
Feb 14, 2008
I want to make query for average sales price, the records are like this
item, Sales_qty, UnitPrice
A, 10, 1000
A, 20, 1100
A, 5, 1500
B, 1, 1000
B, 2, 1200
and display like this
A, 35, 1128 (35 FROM total qty, 1128 from average sales price)
B, 3, 1133
Thanks in advance
View 8 Replies
View Related
Feb 26, 2008
i was wondering if anyone could help me write all this into one or two quries...
alter table testdata add areacode varchar(3),
countystate varchar(35),
citystate varchar(30),
county_name varchar(30),
ltv float
go
UPDATE t1
SET t1.county_name=t2.county
FROM testdata t1
INNER JOIN master.dbo.zip t2
ON t1.statecode+t1.countycode=t2.fips
go
update t1 set
maritalstatus = case maritalstatus
when '1' then 'M'
when '2' then 'S' end,
homeowner = case homeowner
when '1' then 'R'
when '2' then 'T'
when '3' then '9'
when '4' then 'H' end,
areacode = substring(phone,1,3),
citystate = rtrim(city)+' '+state_abbrv,
countystate = rtrim(county_name)+' '+state_abbrv,
ltv = case when estcurhomeval >0 then cast(oo_mtg_amnt as float)/cast(estcurhomeval as float) end
from testdata t1
go
basically, i have to run this against some fairly large tables, so i want to try and get it down to one pass,
instead of three.
Thanks in advance!
View 5 Replies
View Related
Jun 27, 2007
Hell All,To reproduce one of our cusotmer's probem, I need to make the SQL torun for more than a minutes before it returns the result set. I do nothave large amount of data in the database to simulate the dealy.Is there a way in SQL to cause the delay while returning the resultsetThanks for the help.RegardsRaj
View 7 Replies
View Related
