in my relational sql db structure I have some items and its children, grandchildren itemsto print these values do i need to build one sql statement for each tier?into my sproc i tried something as following but it returns me only the last tierSELECT * FROM AINNER JOINB ON A.field1 = B.field2INNER JOINC ON A.filed3 = C.field4WHERE A.filed1 = @myParam
thanks a lot
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies View RelatedI'm trying to build a report to show activity for reservations placed and satisfied by month by branch.
The report has a parameter for the item category and the dataset has the fields
MonthCategory BranchReservations placedReservations satisfied
On the front sheet I've created system wide figures with a two columns per month and a matrix which is also broken down by category (e.g. child fiction, child non fiction, adult fiction, adult non fiction) which has row and column totals.
I want to have a page like this for each branch in the system.
I have 5 drop down lists and 1 text box, and I need to build the WHERE portion of my SELECT statment (stored procedure). the drop down lists are named client, ptype, apptdate, inspdate, state, and the textbox is named text. they all need to be this=something AND that=another AND...AND text LIKE mytext.
How would I go about building this efficiently?
Would I Declare a bit value in the sp called WhereSet = 0
IF @client IS NOT NULL IF @WhereSet = 0 SET @Where = 'WHERE ClientID=@client' SET @SetWhere = 1 ELSE SET @Where = @Where + ' AND CleintID=@client' . . ....
Or would this be a lot easier using adhoc SQL instead of a Stored Procedure?
(note: I am using a SQL DataSource)
Please help, I am going bald from pulling my hair our...
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows
as per data in MS Sql
my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --
2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no
3)Other_project
fields are -- other_proj_id,other_custname,po
for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id custname contract_no field_no
1 abc 234 66
2 xyz 33 20
Other_project
============
other_proj_id other_custname po
1 xxcx 111
2 dsd 222
material
=========
material_id project_type project_id qty
1 AB Corporate 1 3
2 Other Project 2 7
i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project
sample query i write :--
select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op
but this query not work,also it gives errors
i want sql query to show data as follows
material_id project_type project_id custname other_custname qty
1 AB Corporate 1 abc -- 3
2 Other Project 2 -- dsd 7
so plz help me how can i write sql query for to show the output
plz send a sql query
Hi All.. I have an 3 tier web applications and I want to use sql transactions but not in each class.
Example :
I have 3 clases who need to update each table in one transaction how I do use sql transactions. I try to do in this way but not work:
sub cmdupdate_click dim a as new class1 dim b as new class2 dim c as new class3 dim cn as new sqlconnection...... cn.open .....transactions.... scripts a.cn =cn ...I pass the same connection via property to each class a.update b.cn=cn.... b.update c.cn=cn..... c.update
Transaction...scripts to commit cn.closeend sub
maybe that logic is incorrect. I dont now is my explain is clear.
I am trying to allow a user the ability to search a database and select from various fields to search, such as Keywords and Filename. I tried building something like this:
SELECT filenameFROM pictableWHERE (@searchby LIKE @searchwords)
It allows me to enter the two varables (keywords and test), but returns no rows. If I simply replace @searchby with 'keywords' (ensuring no spelling errors), then I get a return of one row. So this works:
SELECT filenameFROM pictableWHERE (keywords LIKE @searchwords)
Can someone tell me what is going on? I have tried all sorts of quotes and parens to no avail.
Thanks in advance.
This may not be a MSSQL-specific question, butI wanted to ask it here first, in case there'sa MSSQL and/or SourceSafe solution that will help.Our dev team is having some difficulty withkeeping the nightly builds in sync with thestored proc mods. I'm wondering if there aresome good case studies on how to avoid this"drift". Something like genning a new DB fromchecked-in SPs, etc. alongside each regular build,then always have a paired enterprise app/databaseduo that is tagged and added to a history.FWIW, we have a 3-tier .NET/C# app, andADO.NET is throwing exceptions every otherday.If the suggestion is to whip the DB guys, thatworks for me as well. ;-)Nah, there's much love there.Thanks in advance,~swooz
View 2 Replies View RelatedHi all,
I'm very new to report builder and I don't even know if this is possible. Some of my users have asked if they can build an ad-hoc report to show say top 50 annual salaries. This has to be done soley with whatever report builder has for building ad-hoc reports as that's all the users have access to.
Any help would be most appreciated. Thanks.
I'm trying to write a select statement that will show me the total payments, last payment date, and last payment amount for each client. I get results but it is all payments. Can anyone help me with this?
Thank you,
Here is what I have tried:
SELECT dbo.tblClients.Client_ID, Sum(dbo.tblPaymentReceipts.[Amount Paid]) AS SumOfAmtPaid, MAX(dbo.tblPaymentReceipts.[Date]) AS LastPaymentDate, dbo.tblPaymentReceipts.[Amount Paid] INTO #temp_UNPaymentsA
FROM dbo.tblPayments INNER JOIN dbo.tblPaymentReceipts ON dbo.tblPayment.Pay_ID = dbo.tblPaymentReceipts.Pay_ID
WHERE (dbo.tblPaymentReceipts.[Date] BETWEEN '1/1/2001' AND '12/31/2003')
GROUP BY dbo.tblPayments.Pay_ID, dbo.tblPaymentReceipts.[Amount Paid]
Select * FROM #temp_UNPaymentsA
GROUP BY Client_ID, SumOfAmtPaid, LastPaymentDate, [Amount Paid]
HAVING SUM(SumOfAmtPaid) BETWEEN 0 AND 1000
Hello,
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
with best regards
Anton Kalcik
Hi all,
I have two problems.
1. I downloaded ChartFXRSTrial and created one chart, and able to deploy it in ReportServer, but the problem is the reports are not showing there, i checked the configuration of .dll files in the help provided by chartFX, but couldn't get anything, so could help me on this.
2. How to give Tooltip for the X- axis or Y- axis values in the ReportServer , i tried using chartproperties of .rdl file, but didnt understand it. can help me on this, and one more i tried with Dundas too, If im giving tooltip as #valy then, it is showing samething in reportserver instead the values of 'Y-axis'.
Thanks,
Mahesh Manthena
Hey folks, I just inherited a machine where select @@version returns this:
Microsoft SQL Server 7.00 - 7.00.710 (Intel X86)
Okay, I know that:
623 is orig sql
699 is sp 1
842 is sp 2
961 is sp 3 -- but what's 710 ?
going to http://search.support.microsoft.com/kb/c.asp?ln=en-us gets me exactly 0 <zero> hits. Is this a hotfix someone's aware of?
Any thoughts/documentation/whatever would be appreciated.
Thanks,
Tom
I know how to use sql to write a block of sql statements by cutting and pasting the results in to the execution window. Now my question is this...
is there a way to create the sql and execute the results. i.e. Automatically cut and paste the results into the execution window and run it??
Something to munch on if your bored. Ideas appreciated.
The common way that we use to delpoy reports in production server is using rsbuild.config file and running the command to execute it. Is this a Microsoft product ? does it have any licensing issue if i need to use this approach in my production environment to deploy the reports?
View 1 Replies View RelatedHi,
I have two tables called Actcodes and a another table called FundBalances...
The Act codes have the following format..
PlanId int,
ClientId int,
StartDate datetime,
EndDate datetime,
ClientActCode char(2)
CodeDescription char(15)
so based on what the user has selected i am getting the names of each codes
and they have the following Rows.
PlanId ClientId Startdate EndDate ClientActCode CodeDescription
111 1 1/1/2008 3/31/2008 01 Begininng balance
111 1 1/1/2008 3/31/2008 02 Contributions
111 1 1/1/2008 3/31/2008 03 something
111 1 1/1/2008 3/31/2008 04 sdkfjdkf
111 1 1/1/2008 3/31/2008 05 dfdfd
111 1 1/1/2008 3/31/2008 06 dfddfs
111 1 1/1/2008 3/31/2008 09 dfdf
111 1 1/1/2008 3/31/2008 15 dfdkfdlfk
and my fund balance i have the following rows..
PlanId int
Participantid int
StartDAte datetime,
end date datetime,
FundId int
Loans
Act1 char(2)
TotAct1 money
.
Act20 char(2)
TotAct20 money
and the data is as follows
PlanId ParticipantId StartDate EndDate fundId Loans Act1 TotAct1 Act2 totact2 ----- Act20 TotAct20
111 1212 1/1/2008 3/31/2008 15 NULL 01 15.15 02 15.48 20 12.4561
111 1212 1/1/2008 3/31/2008 45 0 01 45.12 02 453.123 20 54.00
so on and so so forth
tht Act1 matches the ClientActCode in the Act..
So is there a way i can get those column in my select which have actCodes present in the Act code table. for in... in my act code table for this plan i have 01,02,03,04,05,06, 09, 15 clientAct codes
so can i get the its respective Acts and Totacts column as Act1, Act, Act3, Act Act5, Act6 based on the ClientAct codes.
Any help will be appreciated..
Thanks
Karen
Hi.
I know this querry:
"SELECT TOP 10 * FROM MyTable ORDERBY ..."
will give the top 10 rows.
But how can I build a querry that can get the rows from 40-50 (ie).
Thanks.
Hi,
Please Help me to build this query.
I have got a "User" Table
---------------------
UserID UserName
---------------------
1 Tuffy
Another Table "Groups" Table
---------------------
GroupID GroupName
---------------------
1 Manager
2 Employee
3 Sales
I have got a "UserGroup" Table HOLDING ID'S as Foreign key.
The data in the TABLE is like this
---------------------
UserID GroupID
---------------------
1 1(Manager from "Group" Table
1 2(Employee)
1 3(Sales)
2 2(Employee)
2 3(Sales)
---------------------
Now when a user logged in The Groups have to be returned as a string that contains pipe separated Group names
for example "Manager|Employee|Sales|"
So if User 1 log in I need something like that
UserID (1)-->"Manager|Employee|Sales|"
Please help me how to write this query.
Regards
I'm trying to add a CASE to my statement, I keep getting a syntax error. Can anyone see the issue? Should this CASE work?
Thanks for your thoughts,
Select tmp.Alternate_Name,dbo.tblClient.Salutation
CASE
WHEN (tmp.Alternate_Name > '') Then tmp.Alternate_Name As Card_Name
Else dbo.tblClient.Salutation AS Card_Name END
From dbo.tblClient INNER JOIN dbo.tblCards tmp ON dbo.tblClient.ClientID = tmp.ClientID
Hi there, I have 3 columns that contain the same kind of information and I would like to do a select that would return all the distinct records of all 3 colums. Any help woulb be appreciated.
Thanks
I have two tables linked with the colunm name systemId and I need to get all the systems responding to the condition (t1.software & 0000000001) = 1 and if they have any contacts I would like to see them.
Here is what I have for the moment however my querry do not return the system info if no contact exist and that is wrong.
select customers.customerId,customers.systemId,customers.country
from customers,contacts
where (customers.software & 0000000001) = 1
and customers.SystemId = contacts.SystemId
order by customerid
Systems
-SystemId
-CustomerId
-Name
...
Contacts
-ContactId
-SystemId
-Name
-Tel
...
please help me
i need for example
when my user clicke in the button a new table build in my sql database
please help me
Anyone know the final build # for the RTM version?
Thanks
I swear I saw a stored procedure that would tell me what version service pack and what build my sql servers are...
Does anybody know how to find out? I think its a transact query...
Also, I think I saw a stored procedure that would associate that cryptic string of numbers into text; ex: "SQL 6.5, Service Pack 5"
l would like to intergrate the the two procedures listed below into one procedure or function.
l'm doing it in two steps.This report will have each loan listed and a final line summed on each of the amounts?
enddate and startdate being my parameters and print the results to screen.And l'm failing to do that thats why its in two steps.Hope this makes it clear enough.
-- Exec New '2001-04-01 00:00:00.000','2002-05-29 23:59:59.000'
-- Exec New1 '2001-04-01 00:00:00.000','2002-05-29 23:59:59.000'
Alter procedure new (@startdate datetime,@enddate datetime)
as
begin
SELECT Distinct
Loan_no AS Loan_no,
Date_Issued AS Date_Issued,
Store AS Store,
Product AS Product,
Capital_Amount AS Capital_Amount,
Interest_Amount AS Interest_Amount,
Total_Amount AS Total_Amount,
Insurance_amount As Insurance_Amount,
Admin_Fee AS Admin_Fee,
User_Issued AS User_Issued,
LoanBook AS Company,
Status
FROM Loan
Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000'
End
Alter procedure new1(@startdate datetime,@enddate datetime)
As
Begin
SELECT
Sum(Capital_Amount) AS Capital_Amount,
Sum(Interest_Amount) AS Interest_Amount,
Sum(Total_Amount) AS Total_Amount,
Sum(Insurance_amount) As Insurance_Amount,
Sum(Admin_Fee) AS Admin_Fee
FROM Loan
Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000'
End
Whats the quickest way of building a calender split by day,month,year,day name of week. l want populate the table with that information.
View 1 Replies View RelatedThe Accounting Department needed some reports, and this was back when I was first learning SQL, so I wrote a batch file that used osql to run a procedure. This procedure created a second batch file with several hundred lines to run another stored procedure and output to .rpt files complete with page breaks.
Now I'm revisiting this process having learned alot since then, and would like some input on how to handle this better. The batch file works, but I've never been one of those "if it's not broke" type of people. I'm one of those "If it's not broke, give me 5 minutes and it will be" kind of people
I'm specifically interested in something to replace this CURSOR that builds the batch file:
Code:
DECLARE @Dealer Varchar(20)
DECLARE Dealer_Cursor CURSOR
FOR SELECT Dealer_id FROM Dealer WHERE (Dealer_id != '-1') AND (isnull(Category,'-1') != 'NOBILLING')
OPEN Dealer_Cursor
FETCH NEXT FROM Dealer_Cursor INTO @Dealer
WHILE @@Fetch_Status = 0
BEGIN
/* @Folder is passed as a fully qualified path to the billing folder */
/* Logging and Command Window Notification, to be placed into DealerBilling.bat */
PRINT 'ECHO Creating report for Dealer: ' + @Dealer + ' ...'
PRINT 'ECHO Creating report for Dealer: ' + @Dealer + ' ... >> "' + Substring(@Folder,1,3) + 'Reportlog.txt"'
PRINT '"osql" -SMYSERVER -UMYUSER -PMYPASS -w 103 -Q "EXEC MYSERVER.MYDB.dbo.Proc_Dealer_Listing '''
+ @Dealer + '''" -o "' + @Folder + '' + @Dealer + '.rpt"'
FETCH NEXT FROM Dealer_Cursor INTO @Dealer
END
PRINT ' '
CLOSE Dealer_Cursor
DEALLOCATE Dealer_Cursor
I have a table with 2 columns:
Col1 Col2
stat1,stat2 AV,AD
stat3 TD
I need to build a query like this:
SELECT *
FROM table3
WHERE stat1=AV and stat2=AD and stat3=TD
How do I do this?
I have a stored procedure which expects one parameter @City
Let’s say @City holds pipe delimited value: "New York|London|Paris"
In my stored procedure I need to replace all pipes with OR operator? In other words my select statement needs to look like this :
SELECT * FROM City WHERE CityName= 'New York' OR CityName= 'London' OR CityName= 'Paris'
Thank you
Here is my problem.
I need to build a new SQL query and im almost new with all the sql language. Im searching a lot and I have 2 book but I cant found what I'm looking for.
I need to update the Site and Department in the table 1 with the site/department in the table 3 using the table 2 as a join. The table one is an item owned by the person in the table 3 and I need to set the Site and Department as the same.
Table 1
Asset Name | InstanceID | Site | Department
Table 2
InstanceID | User Name |
Table 3
User Name | Site | Department
Help will be really appreciated!
Thanks
I need to build following query:
Each item has one or more manufacturers:
Is any way to get all the items with all manufacturers where one of manufacturers fit required expression.
I will try to explain in example:
items | manufacturers
------------------------------
ABC1 | golf
ABC1 | toyota
ABC1 | citroen
ABC2 | skoda
ABC2 | subaru
ABC2 | lada
ABC3 | peugeot
ABC3 | renault
ABC3 | dodge
Example for Input: *olf*
Output:
ABC1 | golf
ABC1 | toyota
ABC1 | citroen
Example for Input: *aul*
Output:
ABC3 | peugeot
ABC3 | renault
ABC3 | dodge
Thanks
I would like to build a workflow system where 100 processes are requesting an item from a ~1.000.000 items large DB, process that item and move it to the next state. The problem with the current implementation I tried is that I get deadlocks....
The DB table looks like:
CREATE TABLE Transactions(
itemid CHAR(32),
status TINYINT not null default 0,
result INT not null default 0,
lockby TINYINT not null default 0,
.... (etc.)
PRIMARY KEY(refno)
);
CREATE INDEX IxStatus on transactions (status)
Each process (with his own ID) is doing 4 step:
1) update transactions set status=1, lockby=<ID> from
(select top 1 itemid from transactions where status=0) as t1
where t1.itemid=transactions.itemid
2) select itemid from transactions where status=1 and lockby=<ID>
3) process item
4) update transactions set status=2,result=<RESULT> where itemid=<ITEMID>
Does anyone has a suggestion on how to do this?
HI,
I have developed one application in sql server 2005.
Once i generate the scripts for all the objects, i want to give them to the client as a build.
so that he can execute that build by giving parametres like server name,error log path,build source path and db name.
Please let me know how to create that.
Please do the needful.