Trouble Putting Sub Report Results In Columns
Apr 30, 2008
Dear all,
I have a sub report that returns a variable amount of results. On occasion many results will be returned and cause one or more extra pages to be created.
The results from my sub report are not very wide so what I would like to do is have the results roll over from the left hand side of the page to the right hand side of the page and then continue this pattern on sunsequest pages until all results are displayed. Very similar to newspaper columns.
Is this possibe? If so how can I go about doing this?
Many thanks in advance,
Trevor Keast.
View 1 Replies
ADVERTISEMENT
Nov 27, 2002
The two queries:
set dateformat 'dmy'
select count(c.id), e.name
from call c left outer join employee e
on c.req_id = e.id
where c.posted between '01/01/2002' and '30/11/2002'
group by e.name
order by count(c.id) desc
set dateformat 'dmy'
select count(ch.id), e.name
from call_hist ch left outer join employee e
on ch.req_id = e.id
where ch.posted between '01/01/2002' and '30/11/2002'
group by e.name
order by count(ch.id) desc
the results:
42NULL
34Dirk Deloof
13Annick Leirman
11Ronny Loosen
9Geert Benoot
9Nicole Ferrari
8FLOCK
8Mosselmans Christoph
7Geert Pets
7Mireille Dutrieue
6johan
6Laurent De Schrijver
5Jeanette De SChrijve
5Marc De Vlieger
5minerva
5Pascal Saesen
5Rik Haghebaert
5Sonja Van Kerckhove
4Bcatron
4Luc Willems
3Brigit Brocken
3euroadmin
3Francine Kopp
3Luc Steyaert
3Marie-Rose Buysse
3Marnix Van Steirtege
3Mattias Denys
3Pieter Frooninckx
3Reserve
3Rik De Scheemaecker
3Thierry Linard
2Carlos Van Alboom
2Dorine Sierens
2Els Poelman
2Jean Claude Vermeir
2Katrien Colman
2Kim Impens
2Kris Lejeune
2MEDreserve01
2Roger De Wilde
1Agnes Lebon
1Carla Van Den Broeck
1Eric Vlaeminck
and
118NULL
58Marie-Rose Buysse
47Dirk Deloof
45Ronny Loosen
43Annick Leirman
41Geert Pets
38FLOCK
38Pascal Saesen
28Teamleiders afwerkin
24Kim Impens
22Ilse Soetens
22Rik Haghebaert
22Severine Balduck
21Teamleiders print
20Mosselmans Christoph
20Jeanette De SChrijve
19Geert Benoot
19Francine Kopp
18Geert Meuleman
17Rik De Scheemaecker
16johan
16Katrien Colman
15Gaby Eloot
14Kris Lejeune
14Gilbert Callebaut
14Laurent De Schrijver
13Els Poelman
13Luc Steyaert
11Marnix Van Steirtege
10Frans Hoogewijs
10Sonja Van Kerckhove
10Dorine Sierens
9Eric Vlaeminck
9Thierry Linard
7Frederic Denis
7Michel Poppe
6Carla Van Den Broeck
6Pieter Frooninckx
5Katlijn Poleyn
5MEDreserve01
5Mireille Dutrieue
5Agnes Lebon
4Guido Antoin
4Onderhoud
4minerva
4Jeanette Van Brussel
3Roger De Wilde
3Sofie Gabriels
3Verf2
3euroadmin
3Marc De Vlieger
2Luc Willems
2MEDRESERVE07
2Regina Decoster
2Monique Kohl
2MEDRESERVE04
2Portier
2Bcatron
2Pierre Hanet
2Tgabriels
2Isabelle Torrelle
2Nicole Ferrari
1Robert Zwaak
1Carlos Van Alboom
1testuser
1Brigit Brocken
1Reserve
1Opleiding
1Verf
How do I put these results in one? I need not two but one Query.
Please help me. Thanks
View 2 Replies
View Related
Sep 23, 1999
Hi,
In 6.5 you could run a query and then make a flat file with whatever format
you wanted.
I cannot find that option in 7.0. Can someone please tell me where it is????
Thanks,
Dianne Watson
View 5 Replies
View Related
Aug 15, 2012
I need to analyze the results from Table 1, count how many records there are where one field matches, and then put some of Table 1's information into Table 2 along with the count of the records that match. This is built to interface with another system, so a lot of the information, names, and relationships can't always be changed.
Table 1: tblResourceAllocation
Fields (Description):ID (Unique)LastName (Unique alphanumeric string for each employee, but one employee can have multiple records in this table)Project (Unique name, can have multiple entries in this table)Owner (Unique alphanumeric string for each owner, but one owner can have multiple records in this table)ResourceStartDate (datetime)ResourceEndDate (datetime)ServiceID [I](this is what I want to aggregate)Status_FC [I]
Table 2: tblGRFM
ID (unique)Pm (the tblResourceAllocation.Owner's actual full name, which I'll look up from an EmployeeData table and concatenate the FirstName and LastName)IdClient (from tblResourceAllocation.Project I will look up the Customer from the Projects table, then look up the Customer ID from the Customers table)US_State_Id (look up State from Projects table then ID from tblStateCodes)Project_name (same as tblResourceAllocation.Project)Status (same as tblResourceAllocation.Status_FC)Id_operlst (same as tblResourceAllocation.ServiceID)Start (earliest start date of relevant entries)end (latest end date of relevant entries)Nb_ress (total number of ResourceAllocation entries that match Id_operlst)Cadence (explanation below)
I need to calculate how many of each serviceID's are in table 1, and then store that calculation and the ServiceID (Id_operlst) in table 2I need to separate those calculations by Owner, IdClient, US_State_Id, and Project_Name
I.E. If there are 5 entries in table 1 with ServiceId = X, and Owner A is listed on 2 of them and Owner B is listed on 3, I want it displayed as [Owner A, X, 2] and [Owner B, X, 3] in table 2.
Same goes for the other fields, which obviously adds more complexityWithin those calculations I also need to calculate the Start and end date for table 2. Using the last example, if one entry of Owner A's service X has the early start date and the other entry has the later end date, those dates need to be displayed in the Start and end for table 2 (should I use Rank here?)
I need to create a string of numbers separated by semicolons that shows how many resources are working on a given week after the start date
Example:I have 3 resources on a projectResource 1: 12/31/2012-1/27/2013 (Week 1-4 of our calendar)Resource 2: 1/7/2013-1/27/2013 (Week 2-4)Resource 3: 1/7/2013-1/27/2013 (Week 2-4)tblGRFM.Start will equal 12/31/2012 and tblGRFM.end will equal 1/27/2013Cadence should be equal to "1;3;3;3" to indicate that one resource will be working the project the first week, and three resources will be working the project for the next three weeks.
View 2 Replies
View Related
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
Apr 8, 2008
I have an OrderDetails table, ShipHeader table and a ShipDetails table in my database. I am trying to add a couple of columns to my OrderDetails table. I need two additional columns in my OrderDetails that are calculated. One should be called "Shipped" and give me the sum of all the ship Qty records for theat line item. The other should be called "Returned" and do the same for the return records. I was able to create this in a View without a problem. However, I need a method of creating this table that will still allow me to edit the information in the other columns in the OrderDetail table while viewing the shipped and returned information for each line item. With a View, I cannot edit any of the information while viewing it. Should I use a function? A stored procedure? If so, how do I code this? The tables are listed below.
CREATE TABLE OrderHeader
(
OrderID int identity primary key,
Status int,
StartDate datetime,
EndDate datetime
)--Use Type 1 = "Quote" Type 2 = "Cancelled" Type 3 = "Confirmed", Type 4 = "Shipped", Type 5 = "Part Shipped", Type 6 = "Part Returned", Type 7 = "Returned, Type 8 = "Mixed"
CREATE TABLE OrderDetail
(
OrderDetailID int identity primary key,
OrderID int FOREIGN KEY REFERENCES OrderHeader(OrderID),
ItemName varchar(30),
Qty int,
SiteID int,
)
CREATE TABLE ShipHeader
(
ShippingID int identity primary key,
Type int,
SiteID int,
ActionDate datetime
)--Use Type 1 = "Ship" Type 2 = "Return" Type 3 = "Lost"
CREATE TABLE ShipDetail
(
ShipDetailID int identity primary key,
ShippingID int NOT NULL FOREIGN KEY REFERENCES ShipHeader(ShippingID),
OrderDetailID int,
Qty int
)
View 3 Replies
View Related
May 29, 2006
I€™m using the ImportExport wizard to export the top 5 lines from a MS Sql table into a fixed format (€œragged€?) file. But I want the first record to contain the column names of the exported fields so I selected the €œColumn names in the first data row€? option of the €œChoose a Destination€? box. When I run the Package I get:
>>>
· Information 0x402090dc: Data Flow Task: The processing of file "C:arkingdogExportWithheader.txt" has started (SQL Server Import and Export Wizard)
· Error 0xc0202095: Data Flow Task: Failed to write out column name for column "CustomerID".
(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Destination - ExportWithheader_txt" (49) failed the pre-execute phase and returned error code 0xC0202095.
(SQL Server Import and Export Wizard)
>>>
When I de-select the €œColumn names€? option, the package works fine. Other than manually, how can I et the column names in output file?
TIA,
Barkingdog
View 5 Replies
View Related
Nov 4, 2007
Hello all:Here is my issue:I have a stored procedure that reads into Reporting Services with one parameter, 'id'. Once the user selects this id from the list, a one page report is generated that gives information (address, country, history, etc.) based on that id parameter. This report currently functions.Now, I've been trying to adjust this, to still produce a one-page report, however without a parameter. So ultimately there would be 110 pages reflecting info for 110 ids (pre-selected). I'm having issues when trying to run the report, for example in visual studio when I select 'view report', only one id appears and 120 'history details' appear. It's very strange, and I'm not sure how to fix it. I think an adjustement in the layout view needs to be made...I'm having great difficulty figuring out what that is. Help? Greatly appreciated!
View 1 Replies
View Related
Feb 26, 2007
I'm having trouble dynamically specifying the name of the report I want to drill through to.
I have the follwing expression in the Jump To Report field on the Advanced Textbox Properties Navigation tab:
=IIF( Fields!Third_Element.Value = "NONE", "Details Report.rdl", "Second Level Service Sub Report.rdl")
I get an error saying I'm missing the true part of the expression... Am I specifying the report name incorrectly? Both reports have the same parameters even though each report uses different parameters:
C:devReportsSLA Request TypeService Desk SLA Performance - First Level Service Sub Report.rdl The expression for the textbox €˜Second_Element€™ contains an error: [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Also, what does the "Omit" parameter do in the paramters set up? I tried dynamically turning parameters on/off based on the report I needed to call and it didn't react as I expected.
Does anyone have any ideas?
Thanks
Carol
View 5 Replies
View Related
Aug 12, 2014
Why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.
My query looks like the following -
Code:
go
declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'
if @whCode = '02'
set @binLoc = 'S%'
[code]....
why my results are doubling up in the two indicated columns.
View 1 Replies
View Related
Oct 18, 2013
I have a query (SELECT * FROM Pricing) that produces the following results
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0001___|123______|22.45_______|1_____________|25.23
SC0001___|123______|21.45_______|2_____________|25.23
I want to convert this to the following :
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|21.45____________|25.23
We only have two types of suppliers but not every product is available from each type of supplier so we might get the following results:
Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0002___|124______|22.45_______|1_____________|25.23
Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|NULL_____________|25.23
View 1 Replies
View Related
Jun 14, 2006
Hi ,I need to place the results of two different queries in the same resulttable parallel to each other.So if the result of the first query is1 122 343 45and the second query is1 342 443 98the results should be displayed as1 12 342 34 443 45 98If a union is done for both the queries , we get the results in rows.How can the above be done.Thanks in advance,vivekian
View 7 Replies
View Related
Mar 12, 2007
Hi,
I have following data coming from prior transformations.
Id Unit Name NewFlag
___ __________ ____________
1 JacuzziBox 1
2 Hummer H2 0
3 Waste dumper 0
and so on.
I want aggregate task to result 2 fields (without any GROUP BY-ing), so that one contains total number of rows or records, and one contain total number of records with NewFlag=1.
Just wondering if it is possible by using Aggregate and how.
Thanks for your help in advance,
View 1 Replies
View Related
Feb 10, 2004
I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)
I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.
I'm lost on both the search and the results code.
I'd like to have a drop down for the search like this:
<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>
Show all Vendors who are: <Select name="?????">
<OPTION value="YES">SBI
<OPTION value="YES">SBE
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
</select>
<Input type="submit" Value="Go">
</form>
Any ideas on how to make this work would be greatly appreciated.
View 3 Replies
View Related
Feb 17, 2004
I have to write a query which extracts everyone from a table who has the same surname and forenames as someone else but different id's.
The query should have a surname column, a forenames column, and two id columns (from the person column of the table).
I need to avoid duplicates i.e. the first table id should only be returned in the first id column and not in the second - which is what i am getting at the mo.
This is what i have done
select first.surname, first.forenames, first.person, second.person
from shared.people first, shared.people second
where first.surname= second.surname
and first.forenames = second.forenames
and not first.person = second.person
order by first.surname, first.forenames
and i get results like this
Porter Sarah Victoria 9518823 9869770
Porter Sarah Victoria 9869770 9518823 - i.e. duplicates
cheers
View 5 Replies
View Related
Aug 12, 2014
I have written some SQL that relates to the (partial) result set below -
I am having an incredibly difficult time trying to work out why I am seeing double ups in the 'Bin Label' and 'Bin Qty' columns.
My query looks like the following -
go
declare @whCode varchar(5), @binLoc varchar(2)
set @whCode = '04'
if @whCode = ''
set @whCode = '%'
[Code].....
View 1 Replies
View Related
Apr 14, 2014
I have a table called approval_levels in which I have 2 columns:
|LEVEL_ID |NAME |
|1 |app_level_1 |
|2 |app_level_2 |
|3 |app_level_3 |
I have a second table called requests, in which I have 3 columns:
|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |
|1 |wd-40 |Acme |
|2 |Windex |Acme |
|3 |Propane |Acme |
I have a third table which links the two called request_approvals which has 2 columns: REQUEST_ID, LEVEL_ID. When a request is approved for a specific level, I insert a value into this table. So, for example, lets say request 1 has been approved for all 3 levels, request 2 has been approved for only level 1, and request 3 has been approved for level 3, the table would show something like this.
|REQUEST_ID |LEVEL_ID |
|1 |1 |
|1 |2 |
|1 |3 |
|2 |1 |
|3 |3 |
Ok, so here the challenge: I need to show in a report all the requests, create a columns for each level and show whether or not that request is approved for that level. The end result has to be something like this:
|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |app_level_1 |app_level_2 |app_level_3 |
|1 |wd-40 |Acme |X |X |X |
|2 |Windex |Acme |X | | |
|3 |Propane |Acme | | |X |
Keep in mind that if another value is added to the approval_levels table (ie app_level_4), I need to add another column to the table call app_level_4 dynamically.Now, how in the world do I do something like this?
View 1 Replies
View Related
Mar 12, 2008
I have 2 columns from 2 separate tables.
Col1 is AccountName from tblOpportunities
Col2 is BillToName from AccountCodes
I am trying to get the results from tblOpportunities.AccountName that DO NOT also exist in AccountCodes.BillToName
Thanks
View 3 Replies
View Related
Apr 24, 2006
I'm writing a FTS query which needs to search on two different columns.
E.g. Table contains "Location" and "LocationDescription" columns.
Both columns are FT indexed.
The query also uses AND/OR operators to filter out the results.
I found the following article which gives the solution to the same problem.
Link: http://support.microsoft.com/default.aspx?scid=kb;en-us;286787
Is this problem associated with SQL Server 2005 also?
Making a third column which hold data from first two column is the
only solution or is there any other way to acheive better results?
Shailesh Patel...
View 3 Replies
View Related
Jan 10, 2008
I have an interesting problem to report that I'm hoping someone will be able to assist in solving.
I have a report that contains a table inside of a list. When I view this report on my local machine via Visual Studio, everything appears normal, the columns are all the correct size. However, as soon as I publish this report to the report server and attempt to view it via Report Manager, one of the columns expands horizontally!
From all of the documentation I have read, I understand that Reporting Services does not provide the ability for columns to expand horizontally, only vertically. Can anyone help explain why this is happening or a possible solution to turn this automatic expanding off?
Many thanks,
canuck81
View 4 Replies
View Related
Jul 20, 2015
I want to repeat line on each page of report on PDF while using 2 columns on report. The problem is that if i use table border it will also repeat after second table. i want a line between two tables.
View 14 Replies
View Related
Aug 22, 2014
We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.
SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,
[code]....
View 3 Replies
View Related
Feb 25, 2015
I am trying to take the results of a query and re-orient them into separate columns.
select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_SUMMARYDETAILS.FACILITY_NAME,
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
From W_SUMMARYDETAILS
full outer join W_DEF_SUMMARYDETAILS
on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and
W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD
As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.
I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.
View 9 Replies
View Related
Mar 7, 2014
I have a query which returns the movements to and from our warehouse stock, as well as the current stock for each depot and how much is on order. What I need is a kind of pivot so that each item is shown just once, and then summarises the movements in 4 extra columns: Last 30 days, 30-60 days, 60-90 days and 90-120 days. How can I achieve this with my query below? A sample of some of the results is also shown.
select
iv.item,
iv.descr,
ts.loc_total_on_hand [Stock],
ts.loc_code [Depot],
po.qty [On Order],
po.office [Order Depot],
[Code] .....
View 2 Replies
View Related
Apr 20, 2008
Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns. Is there a way to do this? My two colums are:title and description
can anyone give any code snippets?
View 1 Replies
View Related
Mar 2, 2012
I am imagining something you might pass the names of 2 stored procs (an old version and new one), and a query to produce valid parameters. It would then fire off each proc for a set number of executions, while storing off the results in temp tables, and at the end it would do a data compare, and store off performance data from dynamic management views.
Now I know how to get the parameters for a stored procedure out of the catalogue views, but is SQL Server aware at all of the schema of the results of stored procedures that return result sets, becuase I was thinking of doing something like...
INSERT INTO #datacompare(col1,col2)
EXEC mystoredprocedure
... but I can not seem to figure out how to dynamically gather the schema of the result set.
View 1 Replies
View Related
Sep 5, 2014
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
[Code] ....
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...
View 8 Replies
View Related
Jan 8, 2007
Hi everyone. I am updating a table with aggregate results for multiplecolumns. Below is an example of how I approached this. It works finebut is pretty slow. Anyone have an idea how to increase performance.Thanks for any help.UPDATE #MyTableSET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChangedelse null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),TerminationDate=(select Max(Case When Type = 'N' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and#MyTable.HRRef=HREH.HRRef ),ReHireDate=(select MAX(Case When Code = 'HIRE' thenDateChanged else null end)From HREHWhere #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )
View 2 Replies
View Related
Aug 10, 2015
I have two queries as below;
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours
FROM dbo.tblEventStaffRequired;
and
SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked
FROM tblStaffBookings
GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
View 4 Replies
View Related
Sep 11, 2014
DECLARE @EmployeeID nvarchar(1000)
DECLARE @FiscalYear nvarchar(1000)
SET @EmployeeID = '101,102,103,104,105'
SET @FiscalYear = '2013,2014'
SELECT Data FROM dbo.Split(@EmployeeID, ',')
SELECT Data FROM dbo.Split(@fiscalyear, ',')
_______________________________________
This is part of a bigger project but I am stuck on this part. I get back 2 result sets
Data Data
101 2013
102 2014
103
104
105
I want to insert the results in a new table 2 columns and get the results below.
New Table
ID Fiscal Year
101 2013
101 2014
102 2013
102 2014
103 2013
103 2014
104 2013
104 2014
105 2013
105 2014
View 2 Replies
View Related
Mar 5, 2015
I have a lookup table, as below. Each triggercode can have several service codes.
TriggerCodeServiceCode
BBRONZH BBRZFET
BBRONZH RDYNIP1
BBRONZP BBRZFET
BCSTICP ULDBND2
BCSTMCP RBNDLOC
I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.
AccountServiceCodeRate
11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3
What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.
I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.
What I would like to get is just the service codes and the appropriate trigger code for each account.
SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode
FROM TriggerTable
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)')
,1,2,'')
[Code] ....
View 1 Replies
View Related
Apr 6, 2008
Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:
Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3
Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road
I'd like the results to be like this:
Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30
Anyone? Thanks in advance!
View 3 Replies
View Related
Feb 12, 2007
I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.
What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.
1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.
The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.
After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.
Hopefully someone can get me pointed in the right direction! Thanks for your help!
View 12 Replies
View Related
