Help Pls? Out Of My Depth...
Mar 22, 2007
Hi there, I'm new here and quite green when it comes to SQL in general - I havent used it in any depth for some years and, in the absence of our DBA I've been asked to produce a report... I'm just using query analyser to extract some data as I don't have access to Crystal or anything similar...
I have a table laid out as follows - the PK is REC_ID. It basically stores all the contract start dates for each client we deal with.
RECID------Company-----Product-----------Date
445as------ABCLtd------ICT-Hardware------2007-01-14 00:00:00.000
253s1------ABCLtd------SOFT-Mainstream---2007-01-18 00:00:00.000
567o8------ABCLtd------SOFT-Maintenance--2007-01-18 00:00:00.000
809b9------Gen4Ltd-----ICT-Hardware------2007-01-14 00:00:00.000
098xc------Gen4Ltd-----SOFT-Maintenance--2007-01-18 00:00:00.000
551df------SteetsPLC---ICT-Hardware------2007-01-14 00:00:00.000
919sd------SteetsPLC---SOFT-Upgrade------2007-01-18 00:00:00.000
010qr------SteetsPLC---SOFT-Maintenance--2007-01-18 00:00:00.000
124vv------SteetsPLC---PERS-Allocations--2007-01-18 00:00:00.000
I can easily extract, for example, how many companies have taken up individual services, or many times individual services have been employed... what I'm having difficulty with is trying to extract, for example, all those companies who have had say taken up ICT AND SOFT services, whether explicit or using a LIKE statement.
For instance, if I wanted to find all those companies that had taken up both 'ICT-Hardware' AND 'Soft-Maintenance' and literally just get back the results...
Company
ABCLtd
SteetsPLC
Or a numeric result(in this case 2) would be ok...
How would I do that?
Sorry, this may seem to be very obvious to you but I'm at a loss...
Hope you can help.
View 7 Replies
Dec 19, 2007
Hello,
My 3D bar charts don't look as good as they could because the bars are too thick when displayed in 3D (depth-wise). If I have many bars, the bars are very thin on the front, and then go back far too much. Has anyone played with the settings to see how to optimize this? Even with 5% rotations, it is still too thick for my taste and doesn't look like the previews in the designer.
Mike
View 3 Replies
View Related
Jul 24, 2006
I'm having a brain freeze.
I seem to recall that there is a system stored procedure that lists table objects and the "depth" of their dependencies. Something like:
table name 3
table 2 name 3
table 3 name 2
table 4 name 1
table 5 name 0
The results show the name of the table object and the numerical depth of dependencies (ie, if the table had no foreign keys, the depth would be 0).
Does this ring a bell with anyone? I've googled, but my google-shui is weak today.
Regards,
hmscott
View 4 Replies
View Related
Nov 22, 2015
I have a table of Affiliates, each Affiliate can have a ParentId, which refers to another Affiliate. How can I retrieve all children of a given Affiliate, along with their depth level? I have this query which selects the total number of descendants of a given Affiliate, but I need to select all the children + their depth level, up to a certain level.
WITH Descendants AS(
SELECT RootId = Id, Id
FROM Affiliates
UNION ALL
SELECT RootId, Af.Id
FROM Descendants
[Code] .........
What I want to achieve is a statement that has 3 parameters: RefererId, StartLevel, and EndLevel.
I need a statement that selects all the specified Affiliate.ReferrerId's descendants including their level distance from the referrer, filtered by the StartLevel and EndLevel (WHERE CurrentLevel >= StartLevel AND CurrentLevel <= EndLevel ).
View 2 Replies
View Related
Dec 13, 2007
Since installing Reporting Services on my machine, I have been getting an error whenever I try to execute a reader.read line.
When I ran the same exact code on another machine using someone else's credentials who did not have Reporting Services it ran fine. When I tried to run it on someone else's machine who did not have Reporting Services using my own credentials, I got the same error. Also, when the person who's pc and credentials worked fine on his machine ran the same code back on my machine using his own credentials, he got the same error. When another person who also had Reporting Services tried to run it on his own pc, he got the error as well.
When the reader is first executed (MyDataReader = sSql.ExecuteReader) these were the property values:
depth = 0
fieldcount = 5
hasrows = true
isclosed = false
Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.
recordsaffected = -1 (even though there are records)
VisibleFieldCount = 5
When the reader is loaded into a table (MyTable.Load(MyDataReader)) then the property values changed to:
Depth = {"Invalid attempt to Depth when reader is closed."}
FieldCount = {"Invalid attempt to Depth when reader is closed."}
HasRows = {"Invalid attempt to Depth when reader is closed."}
IsClosed= True
Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.
RecordsAffect = -1
VisibleFieldCount = {"Invalid attempt to Depth when reader is closed."}
And when I execute the the following code:
dtReader = MyTable.CreateDataReader()
While (dtReader.Read())
I get this error:
"DataTableReader is Invalid for Current DataTable"
Since the code works with someone else's credentials on a machine that does not have SSRS, and the same problem happens for me regardless of what machine I am on as long as I am using my own credentials what could be causing my problem?
View 1 Replies
View Related
Dec 6, 2007
has anyone out there found a way to get the best of both recursive hierarchy and drill down in the same report, ie without needing to know how many levels there are in your hierarchy, still being able to report them like a tree view with collapse and expand capability at each level?
View 3 Replies
View Related
