Hi,I have 2 tables, Mail_subject and Mail_Usage.Mail_Subject contains the subject, body and some other bits of info.CREATE TABLE [Waterford_MailSubject] ([ID] [int] NOT NULL ,[MailSubject] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailCategory] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailBody] [ntext] COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,[MailCreateDate] [smalldatetime] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOMail_Usage records the amount of times a certain mail was sent.CREATE TABLE [Waterford_MailUsage] ([ID] [int] NOT NULL ,[RepScreenName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MemberScreenName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailSubject] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[TimeDate] [smalldatetime] NULL ,) ON [PRIMARY]GOThey are joined by Subject (not my idea, its a DB ive inherited).What i need is to get the Mail Subject and the number of times thatMail was sent. Ive Joined them using an INNER JOIN which gave me acount of the number of times each one occoured except for Mails thathave not been used. I need to get zero as the count of Mails notsent. Ive tried a LEFT OUTER JOIN but it didnt work either.Can someone point out what i need to do ?
I m using SQL Server 2000. I have Tabel named Topic and have a column name lineage. lineage has data like following: //////546707//546707//546707/43213/ Now I want to get records who has only one "/" in it's crreponding lineage column. Can somone tell me how to do that in SQL Server 2000? Thanks Khushbu
I want my query to list all SSNS that have more than one record in the table. I have this query:
Code:
SELECT SSN, name4, count(*) from [1099_PER] group by SSN, name4 having count(SSN) > 1
It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.
For example
SSN NAME4 COUNT 123445555 WALTER - 4
I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.
I have these 3 tables in a database for printed chamber music works:
Table INSTRUMENTS: List of musical instruments   Column ID      -- int, primary key   Column sInstrument -- string
Table PTRS_INSTRUMENTATION:   Column ID      -- int, primary key   Column lInstrument -- int, points to a row in table INSTRUMENTS via ID   Column lMainItem  -- int, points to a row in table MAINITEMS via ID   Column lInstrSeqNum -- int, sequential number of like instruments within one Main Item    Table MAINITEMS: List of chamber music works   Column ID      -- int, primary key   Column sItem    -- string    Each chamber music work (Main Item) requires an ensemble of musicians playing several instruments. Some of them may be the same, such as 2 violins. A classical quartet, for instance, consist of 2 violins, 1 viola and 1 cello. I am concerned here only with one of the instruments (although it may occur several times).
I want to extract a list of Main Items where the instrument is given (e.g., 'violin'),
And the maximum of PTRS_INSTRUMENTATION.lInstrSeqNum is a given number (e.g., 2).
Recently to help improve search times across one of our catalogs we implemented a full text index. So far this has worked wonders and improved our speeds 20 fold!
However recently we hit a snag with certian searches. Our catalog contains parts with descriptions such as 3'x2'. We have taken out single characters and numbers from our noise files, but they don't seem to contain special characters (ie . , " ' / ). So it seems that each one of these characters acts as a work breaker. Is there a way we can structure our search to include these characters, or is there another file to modify to allow these characters?
Currenty our call, simplified, looks like this:
select * from catalog where freetext(description,'3''x2''')
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
Hello, Can anyone tell me, how to convert an rtf string to text string using a function in SQL Server? I got a table in SQL Server database with field datatype text(16), which holds an rtf. I've to extract it as a text using a function. thank you,
I am having a problem trying to do some conditional formatting on the text color.
This textbox is in the Group Footer so i essentially want change the color to red if the SUM of one field in the group is less than the SUM of another field in the same group.
I am doing this: =Iif(Sum(Fields!YTDChargeHours)< Sum(Fields!YTDForecast), "Red", "Black")
but i am getting this error:
[rsRuntimeErrorInExpression] The Color expression for the textbox €˜YTDChargeHours€™ contains an error: Operation is not valid due to the current state of the object.
I currently have an Ungrouped Dataset being displayed in a Table. One of the columns include AMOUNT. This is an example of what my report looks like atm: ================== **Report Name**
No Name Amount
Signed By XXXX ==================
I would like to display a SUBTOTAL of the Amounts for each page and, on the final page, a TOTAL of the Amounts. Yes the Last Page's SubTotal and Total will be the same. Looking like: ===============================
pg1 **Report Name**
No Name Amount Sub Total Amount Signed By XXXX
(lastpage) **Report Name**
No Name Amount
(<-----no huge spaces between Table/Subtotal) Sub Total Amount
Total Amount
Signed By XXXX
=================================
Issues: I've found a way to get a Page's SubTotal, but the way I found requires this to be put in a field on the Page Footer Section. This means that on the final page, the SubTotal will be at the top of the Page Footer and not neccessarily directly beneath the table.
So: 1. Is there a way to move the Page Footer so that its printed DIRECTLY under neath the Page Body instead of at the bottom of the Page? 2. If not, is there a way to have the SubTotal of each Page and a Final Page Total in the Page Body section. Cheers.
I have a client that requres me to add a header line and trailer line to a flat file. The trick is the header and footer row is required to have a length of 120 with 5 colunmns while the data included in the query has a length of 1125 and about 70 columns. How can I append a header row to a file through SSIS.
I am exporting SSRS report to Excel I am aware that excel doesn't show footer , It shows in print preview but my footer has text box which have text disclaimer more than 255 characters, the data getting truncated.
Im try to use SQL server 2000 and use Enterprise to cript a table But my result is only table structure in the script (myfile.sql) (something like "create table Tb1, column..") and not have any data in my table included. how good it is if it can write some insert statement (depend on number of records existed). Could I have any way to do it with MS SQLServer 2000? Thank you much. DongMT
I have a report with a group total and a report total. The report total is the sum of all group totals. When I run the report in report manager (sql server 2005 SP2) it shows the report total on the last page of the report with the group total for teh client. for instance the first page has Report Title Details Group Total
Second Page Details Group Total
Last Page Details Group Total Report Total
When I export the report to pdf the Report Total is printing on its own separate page which is not what I want. I would like the report total be on the last page of teh report just like shown above. The report total is in the table footer.
I have a table with say 10 rows. How can I sum up the first 10 rows and subtract that total from the 10th row value?
Rows 1-9 have a specific value in the dataset and row 10 has a different value so I can distinguish the rows. However I can figure out the expression I should add to the table footer?
So I am saying, if the row has a "recordType" value of something other than 1 then sum it and then subtract any value where the "recordType" is equal to 1.
But I just get am "#error" on the report footer when I run it.
How can i pass my .net application's Userid to the trigger? I have a audit trail trigger on myTable. I dont know how to pass the userid (not the sql server user) to the trigger when a user delete a record from the application(.NET Application).
The trigger saves the modifications on the table including the userid of one who does the changes.
I have a sum , which is filtered within its table group, and then i have a fields in the footer. How would i aggregate these too together and use the result in another row within the footer.
[rsInvalidAggregateScope] The Value expression for the textbox €˜textbox129€™ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
I'm using SSRS SP2. I have a table with 1 group defined. I have a group footer that includes sub totals for each group. I have a table footer that includes my grand totals, but this footer will NOT print on a separate page even though I have 'Page Break At End' checked for the group. My groups correctly start on a new page, but when I get to my Grand Total footer line, it prints just after the last group sub-total line. Any ideas what I might be doing wrong? Thanks in advance,
I have a report (bills of lading for shipments) that uses a table to organize the data coming in and groups by customer. Each customer grouping has a header, body and footer in the table. A print run might generate 100 bills of lading, each between 1 and 4 pages long.
I have marked the group to force a new page when each new group occurs.
As it is the report runs beautifully EXCEPT that the footer prints wherever it happens to end up -- midway down the final page of the bill and looks quite goofy.
What I really need (and my client is really surprised that SSRS won't me) is to print the group footer at the bottom of the final page of each bill of lading.
Bills of lading may be several pages per customer, so until the group footer is hit, I don't want any footer info at all. As I said, it all works fine except for the final page of each bill, where the group footer occurs whereever it has ended up.
Jim
12/4/07
This post didn't get much of a response. So perhaps I should amend it -- If what I am trying to do is impossible, what would be the preferred way?
I have a table that contains a column for a calcuated member (x) of type decimal number. When I tried to display the total of this calculated member in the table footer (=sum(x)), I am getting "#Error" instead of the sum of all displayed calculated values.
Column X
--------------
0
0.67
0.10
0.23
#Error (footer cell, expression -> =Sum(x))
=First(x), =Last(x) and =Max(x) worked fine, not sure why Sum failed. Please help...
How can I calculate a subtotal for a Report Item? I have a textbox(lets call it "PlusMinus") in the detail section of my table, which is a calculated textbox of two others (lets call them "Budget" and "Spent"). So, PlusMinus = (Budget - Spent). What I would like to do is get a subtotal for PlusMinus. I have tried several ways, using Sum() or RunningValue, even tried to write code, but I can't seem to get it right. Any ideas??
What I want to do is to query this table to return me a list of product type and category, and the total number of products in each category, and the count of number of instock, and locked.
i.e. Type, Category, TotalCount, NumberInStock, NumberNoStock, NumberLocked, NumberUnlocked.
I need to perform a summation on the results of a COUNT but I'm not sure what's the best way to do it.
Example: TableA --------------- CaseID (int) FollowupCorrespondence (int) (not guaranteed to be sequential) CustomerName (varchar)
/**Get the total number of followups for each customer case, and display the customer's name.*/ select T.caseid, sum(T.cnt), T.CustomerName, T.FollowupCorrespondence from (select CaseID, count(FollowupCorrespondence) as cnt, CustomerName, FollowupCorrespondence from tableA group by caseID, CustomerName, FollowupCorrespondence; ) as T group by caseid, customername, followupcorrespondence
I get the following output, but what I need is to get '4' in each row of the SumCases column. CASEID SUMCases CUSTOMERNAME FOLLOWUPCORRESPONDENCE ---------------------------------------------------------------------------------------------------------------- 1.................1.............John Doe................1 1.................1.............John Doe................2 1.................1.............John Doe................3 1.................1.............John Doe................4
I have created the following query... and need to get the total records display for my report. I have tried adding in the count(*) function to my select list, but I get errors. Any help is appreciated.
select t.value,sum(t.countvalue) as totalcount from (
select
sm.value,count(sm.value) as countvalue
from subjectbase s join stringmap sm on s.organizationid = sm.organizationid
inner join audit a on s.subjectid=a.subjectid
inner join incidentbase i on i.subjectid=s.subjectid
where a.auditid= @audit and (i.modifiedon between @startdate and @enddate) and sm.attributename = 'contractservicelevelcode' and sm.ObjectTypeCode = 112
group by sm.value
) t
group by t.value
)
value totalcount ------------------ NHLBI Employee329 NIH Employee329 Public329 VIP329
instead of different values i m getting same... there is something wrong in joins..can anyone help me?
