How To Query A View From The Master DB?
Apr 7, 2004
Is there a way to access your views in x database from the master database?
I'm getting this error
Invalid object name 'v_StatisticsScalars'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'v_StatisticsScalars'.
View 4 Replies
ADVERTISEMENT
Nov 17, 2006
What system view do i use to determine is a database has a master key?
Thanks,
Ed
View 1 Replies
View Related
Jan 10, 2006
I have a webpage that should display the database names from the master.sysdatabases in a dropdownlist.
The code for the stored procedure works fine in Query Analyzer and returns the list of all database names under that instance of SQL-Server 2000.
The code:
select name from master.dbo.sysdatabases order by 1doesn't return the sysdatabase names to the ASP.NET webpage when executing the stored procedure. It returns: System.Data.DataRowViewWhy does it do this and how can I fix it?Tx
View 5 Replies
View Related
Sep 11, 2007
Hi,
We are using SQL Server 2005 Management Studio.
I created a Minimal User in an application DB. The user will access tables through stored procedures.
I do not want this user to view any other objects including objects in the Master DB.
I can prevent the minimal user from viewing objects from our application DB.
How do you prevent the minimal user from viewing objects in the Master DB?
Thanks.
Tim.
View 7 Replies
View Related
Mar 25, 2008
Hello,
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
Hope this makes sense.
How can I do this?
GrkEngineer
View 9 Replies
View Related
Jul 19, 2015
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
View 3 Replies
View Related
Mar 9, 2006
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies
View Related
Jul 27, 2007
I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below.
OrderNo OrderDate
1001 7/27/2007
+ <div to show/hide>
Item Description
AAA desc_for_itemAAA
BBB desc_for_itemBBB
+ </div>
1002 7/26/2007
+ <div to show/hide>
Item Description
CCC desc_for_itemCCC
+ </div>
I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?
View 5 Replies
View Related
Feb 28, 2002
I need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table:
ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel:
ColID as longint, ColA as int, ColB as int
Child table -- Door:
ColID as longint, ColA as int, ColB as int
Child table -- Window:
ColID as longint, ColA as int, ColB as int
..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
Many thanks,
Leonard
View 1 Replies
View Related
Nov 20, 2011
I have a query with 2 subqueries, and no error message is reported, but, my problem is that the 2 subqueries do not follow the GROUP BY rule and show the total instead of by vendor...
Code:
SELECT Table1.agents AS Vendor
, Count(Table1.carS) AS Car_Sold
, Sum(Table1.carP) AS Car_Price
, Count(Table1.busS) AS MortBus_Sold
, Sum(Table1.busP) AS busPRice
[Code] ....
View 1 Replies
View Related
Sep 25, 2007
Hi,
I have a program in VB.NET 2003 where I have query "SELECT COUNT(*) FROM master..sysservers WHERE srvname = '" & strServerName & "'". This program copies data from .MDF file to another .MDF file. This query is written since I have created linked server.
Issue 1:
But now I to copy data from .SDF (SQL Server Mobile Edition) to desktop .MDF file. For this I cannot fire the above query on .SDF file. What is the other alternative?
Issue 2:
In the existing prgram that copies data from .MDF to .MDF, I have created linked server. Can I do the same with mobile edition?
Regards,
MI
View 4 Replies
View Related
Sep 15, 2006
Hi,
I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:
ITEM MASTER
--------------
NCODE ITEMNAME
1 A
2 B
3 C
4 D
5 E
STOCKDETAILS
-----------------------------------
NCODE ITEMCODE OFFICEID
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 4 2
7 5 3
I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1
--------------------------------------------
FOR OFFICE - 1
--------------------------------------------
ITEMCODE ITEMNAME OFFICEID
--------------------------------------------
1 A 1
2 B 1
3 C 1
4 D NULL
5 E NULL
i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.
Thanks in advance
View 8 Replies
View Related
Jun 12, 2015
I want to setup a link from an SSRS report to Master Data Manager so when the click on the SSRS report item, it will pull up Master Data Manager filtered to the particular member that the user clicked on from the report. I cannot find any references to that being possible. Is it possible? What is the query key value to pass a member codeid or other filter criteria to Master Data Manager explorer?
MID is Model ID
VID is Version ID
EID is Entity ID
Are there other possible parameters? Can a pass a member ID or code?
View 2 Replies
View Related
May 19, 2008
Greetings,
I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.
I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?
Thanks in advance for your replies.
View 3 Replies
View Related
Jun 19, 2007
I got a File with sales orders and their details.
Step 1. First I am filtering the Sales Order information and inserting it in my Sales Orders table.
Step 2.Then I am filtering the details from the sales Order and inserting them in the respective table.
My Problem is that the Sales Order File does not contain the Sales Order key (ID), this is generated by the SQL Server. How can get it in order to use it in the second step? I need it because it is a foreign key in the details table.
Any Idea?
View 4 Replies
View Related
Jun 28, 2005
Hello,I have a View called View1 with the field ID, F1, F2, F3. Now I need to check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records (so if condition matches, I need to bring rows, not only totals) how can I write my view query to handle this?Thanks,
View 1 Replies
View Related
Apr 25, 2005
Hi all!
I have a table that looks like this:
m_id | s_id1 | s_id2 | dt
m1 | 1 | 2 | 2001-01-01
m2 | 3 | 2 | 2001-01-02
m3 | 4 | 1 | 2001-01-03
m4 | 2 | 3 | 2001-01-04
m5 | 1 | 2 | 2001-01-05
m6 | 5 | 3 | 2001-01-06
...
I need to create a view that displays ONE m_id for every s_id, and that m_id should be the latest one...
ie:
s_id | m_id
1 | m5
2 | m5
3 | m6
4 | m3
5 | m6
I've been trying hard for a while, but I simply can't get it do display only one row per s_id...
any helponthis one would be greatly apreciated...
cheers.
View 3 Replies
View Related
Oct 5, 2006
Hi all, I ran a query, pasting in my sql text. I received this error.
Msg 8163, Level 16, State 3, Procedure All_Participants, Line 7
The text, ntext, or image data type cannot be selected as DISTINCT.
Msg 208, Level 16, State 1, Procedure Distinct_Proposals, Line 7
Invalid object name 'Proposal_Details'.
But, I cannot see the msg numbers in my script. How do I find Msg 8163? Excuse the ignorance....only my second day using this :P
Thanks,
Steve
View 3 Replies
View Related
Feb 17, 2006
I have a View which does what I need it too-select distinct d.deptid, d.[description], d.supervisor, d.approvedby, case when c1.cnt = c2.cnt then 'reviews are complete' else 'reviews still in progress' end as deptstatusFrom department dLeft join (Select Count(*) cnt, deptid from employee group by deptid) c1 on (c1.deptid = d.deptid)Left join (Select Count(*) cnt, deptid from employeeeval join employee on (employeeeval.employeeid = employee.employeeid) group by deptid) c2 on (c2.deptid = d.deptid)Brings back data as follows:Cast - Cast - 00999 - 09246 - reviews still in progressCMP- Copper Metal Products - 03315 - 09246 - reviews are completeCNS- Copper Narrow Strip - 07530 - 09246 - reviews still in progressCRW- Copper Rod and Wire - 01950 - 09246 - reviews still in progressSo I did the following: select e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName, CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status, d.deptstatusfrom vw_DeptStatus d Left OUTER JOIN Employee e ON e.deptid = d.deptid LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') -- and (ev.PersonalScore is null)ORDER BY e.DeptID, e.LastNameNow this isn't bringing back what I want--If run the query w/out joining it up w/the view it brings back the following:
Cast - Atkins, Carl - 09935 - Chris Burke - Not StartedCast - Bridges, James - 09929 - Chris Burke - In Progress CNS - Cunningham, Kenton - 02100 - Kahle Rummer - Not StartedCNS - Mitchell, Bill - 06200 - Kahle Rummer - In ProgressNow what I really need it to do is (below are the results from my View joined in with my query) -- return all the dept's--regardless if they are finished. If they are complete I only need ONE row, not all the rows for that dept. (highlighted in RED) I added these rows in-- Doesn't have to be NULL but maybe a blank string.Cast - Charlton, Maurice - 01313 - Chris Burke - In Progress - reviews still in progressCast - Dorsey, Steve - 02455 - Chris Burke - In Progress - reviews still in progressCMP - NULL - NULL - Fred Grove - NULL - reviews are completeCNS - Bennett, Mark - 09911 - Kahle Rummer - In Progress - reviews still in progressCNS - Buckingham, Mark - 00964 - Kahle Rummer - In Progress - reviews still in progressCRW - Eubanks, Kellie - 07599 - Rick Cramer - In Progress - reviews still in progressCRW - Luikart, Tyler- 09908 - Rick Cramer - In Progress - reviews still in progressMicroMll - NULL - NULL - Tim Cross - NULL - reviews are completeI hope this makes sense to someone -- if you have any questions just ask me. Another note I need to bring this back in a DS --for Crystal Reports. So the outline looks like this:
Dept ID- Supervisor - Reviews still in Progress Any unfinished Reviews for that dept
Dept ID- Supervisor- Reviews Complete No data shown since COMPELTEDept ID- Supervisor - Reviews still in Progress Any unfinished Reviews for that dept
View 3 Replies
View Related
Nov 15, 2004
I have a table in a database that has very old and not very relational and I want to create a quick view to show the information in a better way. Let's say that the table has 4 fields : id , child1, child2, child3. I want to create a view from this table that will show two fields : id and child. So, my table currently looks like this:
id child1 child2 child3
1 sam bob chris
and i would like it like this......
id child
1 sam
1 bob
1 chris
Can anybody help me? Thanks in advance,
Bob
View 3 Replies
View Related
Apr 9, 2008
Hi,
I have an accounting table with 5 year of data for an account, the table headers look as following
Acctno___Year___db_1,db_2,db,db_3,db4,….db_12,Cr_1,cr_2,cr_3…,cr-12
I want to with select statement to convert the data to query the header must be the following:-
Years1___Year2___year3___year4____year5
Db_1
Db_2
Db_3
.
.
.
Thanks for any help
View 3 Replies
View Related
Jan 23, 2015
I getting an error when trying to convert the following into a view. I can't take the credit for the code as I copied it from with a program.
Create view vwMAW_KnowledgeDB AS
Select * from don0001
left join (select donclass.donor from donclass where classification = 9 and code in ('KB')) inc0 on inc0.donor = don0001.donor where inc0.donor is not null
quote:Error; Msg 4506, Level 16, State 1, Procedure vwMAW_KnowledgeDB, Line 2
Column names in each view or function must be unique. Column name 'donor' in view or function 'vwMAW_KnowledgeDB' is specified more than once.
View 1 Replies
View Related
May 29, 2007
Dear Friends
I have 3 form with these names :
Clients <----- Projects <------- Files
I am using this query in my [ web ] page :
select clients.id ,
(select count(*) from projects where projects.clients_id = clients.id) as TotalProjects
from clients
I want to compute SUM for all relevant files for each client's projects.
how i can add it to select select statement ??
thank you.
m.o
View 5 Replies
View Related
Oct 8, 2007
Table 1
-------
NAME GROUPID
AAA 1
BBB 2
Table 2
-------
NAME GROUPID
CCC 1
DDD 1
EEE 2
Name & GroupID are column names.
I want to query these tables and get a result like that:
AAA CCC
DDD
BBB EEE
-----------
NOT
-----------
AAA CCC
AAA DDD
BBB EEE
Is it possible? If so, how?
Thanks in advance.
View 1 Replies
View Related
Jul 23, 2005
Hi,I have a view(A) and I am trying to do a join on another table (B) toinclude only rows where date values in view A is greater than in tableB. I also want the view to pick up rows in viewA based on date values.Here is what I have so far:SELECT *FROM viewA vwleft JOIN tableB tb ONvw.id = tb.id and(vw.date1 > tb.date1 orvw.date2 > tb.date2 orvw.date3 > tb.date3)WHERE vw.date4 > getdate()-1Not matter what kind of join I use I can get both the rows from theview where dateA > getdate()-1 AND where date1-3 are greate than intableB. Dates 1 - 4 seperate date fields. Could someone please tellme what I am doing wrong.Thanks.
View 3 Replies
View Related
Mar 28, 2006
I have a SQL 2000 table containing 2 million rows of Trade data. Hereare some of the columns:[TradeId] INT IDENTITY(1,1) -- PK, non-clustered[LoadDate] DATETIME -- clustered index[TradeDate] DATETIME -- non-clustered index[Symbol] VARCHAR(10)[Account] VARCHAR(10)[Position] INTetc..I have a view which performs a join against a security master table (togather more security data). The purpose of the view is to return allthe rows where [TradeDate] is within the last trading days.The query against the view takes over around 30 minutes. When I viewthe query plan, it is not using the index on the [TradeDate] column butis instead using the clustered index on the [LoadDate] column... Theodd thing is, the [LoadDate] column is not used anywhere in the view!For testing purposes, I decided to do a straight SELECT against thetable (minus the joins) and that one ALSO uses the clustered index scanagainst a column not referenced anywhere in the query.There is a reason why I have not posted my WHERE clause until now. Thereason is that I am doing what I think is a very inefficient clause:WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())The function calculates the proper trade date based on the specifieddate (in this case, the current date). It is my understanding that thefunction will be called for all rows. (Which COULD explain theperformance issue...)However, this view has been around for ages and never before caused anysort of problems. The issue actually started the day after I had torecreate the table. (I had to recreate the table because some columnswhere added and others where renamed.)On a side note, if I replace the WHERE clause with a hard-coded date(as in 'WHERE [TradeDate] >= '20060324'), the query performs fine butSTILL uses the clustered index on the [LoadDate] column.
View 4 Replies
View Related
Jul 31, 2007
Hi All,
I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?
Does anyone have idea how to join table that can reflect both left and right join?
Table A Table B
Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5
A Jan 5 xxx A Jan 1 yyy
B Feb 3 C Mar 4
B Mar 4 C Apr 3
C Apr 2 D May 2
E Mar 1
Result Table
Column1 Column2 Column3 Column4 Column 5
A Jan 6 (= 5+1) xxx yyyy
B Feb 3
B Mar 4
C Mar 4
C Apr 5 (= 2+3)
D May 2
E Mar 1
So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).
Thanks for the help.
View 3 Replies
View Related
Mar 23, 2006
I'm have made a series of tables and views to create some reports. I have included boiled-down versions of them below. The report query that I want to run (at the bottom) takes a long time to execute and sometimes times out. Creating a rank within the query is doing the most damage, but it can still take a long time to execute without it. The usage table has almost 70k records and there are over 10k users. Does anyone have suggestions for making these views/queries more efficient?
==========================
TABLE TGroupAudit
AuditID, intOldGroupID, intNewGroupID, strUserID, dtJoinDate
==========================
TABLE people
strUserID, name
==========================
TABLE usage
UsageID, strUserID, cost, seconds, gmttime, accesstype
==========================
CREATE VIEW VUsageSubtotals AS
SELECT TOP 100 PERCENT VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, usage.accesstype, SUM(usage.cost) AS subtotal, MONTH(usage.gmttime) AS [month], VUsageTotals.total
FROM VUserGroupDates INNER JOIN
people ON VUserGroupDates.strUserID = people.strUserID INNER JOIN
usage ON people.strUserID = usage.strUserID INNER JOIN
VUsageTotals ON VUserGroupDates.strUserID = VUsageTotals.strUserID AND
VUserGroupDates.intGroupID = VUsageTotals.intGroupID AND MONTH(usage.gmttime) = VUsageTotals.[month]
WHERE (usage.gmttime BETWEEN VUserGroupDates.dtJoinDate AND VUserGroupDates.dtLeaveDate)
GROUP BY VUserGroupDates.strUserID, VUserGroupDates.intGroupID, usage.accesstype, MONTH(usage.gmttime), VUsageTotals.total
==========================
CREATE VIEW VUsageTotals AS
SELECT VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, SUM(usage.seconds) AS totaltime, SUM(usage.cost) AS totalcost, MONTH(usage.gmttime) AS [month]
FROM VUserGroupDates INNER JOIN
people ON VUserGroupDates.strUserID = people.strUserID INNER JOIN
usage ON people.strUserID = usage.userid
WHERE (usage.gmttime BETWEEN VUserGroupDates.dtJoinDate AND VUserGroupDates.dtLeaveDate)
GROUP BY VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, MONTH(usage.gmttime)
==========================
CREATE VIEW VUserGroupDates AS
SELECT strUserID, intNewGroupID AS intGroupID, dtJoinDate,
ISNULL ((SELECT MAX(TGA2.dtJoinDate) FROM TGroupAudit TGA2
WHERE TGA2.dtJoinDate < TGA1.dtJoinDate AND TGA2.strUserID = TGA1.strUserID), GETDATE()) AS dtLeaveDate
FROM TGroupAudit TGA1
==========================
Here's a sample query that gets run for a report:
SELECT TOP 30 PERCENT WITH TIES V1.*,
(SELECT COUNT(DISTINCT V2.subtotal)
FROM VUsageSubtotals V2 WHERE V1.subtotal <= V2.subtotal
AND V1.intGroupID = V2.intGroupID
AND V2.month = 3) AS rank
FROM VUsageSubtotals V1
WHERE strUserID IN ('a1, a2, b5, b7, b9, d12')
AND V1.month = 3
AND accesstype = 'STANDARD'
GROUP BY strUserID, intGroupID, subtotal, month, total, accesstype
The purpose of the report is an accounting one. It is desired to know how much each user used each accesstype during a month. Usage is further split up by the user's groups (which may never change or could several times during a month) for billing purposes.
View 5 Replies
View Related
Apr 18, 2006
I am trying to create a view that returns data from three tables and can't seem to get it to return the data that I want. I am no SQL expert, so hopefully someone can give me some insight into what I need to do.
The tables are basically set up like this:
TABLE 1
PrimaryKey
Textfield1
Textfield2
Textfield3
TABLE 2
PrimaryKey
Table1ForeignKey
Table3ForeignKey
Textfield1
TABLE 3
PrimaryKey
Textfield1
Textfield2
Textfield3
Table 1 and Table 3 are each joined to Table 2 on their respective Primary/Foreign Key fields.
I want the view to return all of the records from Table 1, even if there are no matching records in Table 2.
From Table 2 I only want the latest record for each record in Table 1.
I want the view to look something like this:
Table 1
PrimaryKey
Table1
Textfield1
Table2
Textfield
Table3
Textfield
In other words, I want to return one record in the view for each record in table 1, and I want the data from table 2 in each of those records to represent the last record added to table 2.
Can anyone enlighten me on the query necessary to get this view?
View 5 Replies
View Related
Jan 10, 2005
I have the following SQL query that I like to view the out put in horizontal format:
Select ID, First_name, Last_name from ABC
Instead of getting out-put like
Id First_name Last_name
1Jim Smith
2Tom Jones
I like to see the out-put like:
Id1 2
First_nameJimSmith
Last_nameTomJones
Please advice.
Thanks in advance,
Abrahim
View 1 Replies
View Related
Apr 27, 2012
I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.
we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points.
ABSENCES
AWAY = Start of an absence from the building
RETURN = End of an absence from the building
A person during their reisdency will have multiple Absences
An absence could start on the same day a previous absence ends
Absences vary in length
CS
CS0-CS10 (each is a different TYPE) of communicatio
[code]....
Query in DESIGN View
SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER()
OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM CRM_CSLDB.dbo.vCommunication
[code]....
Results from QUERY in DESIGN VIEW
TYPE DATECOMPIDCOMMIDSEQ
AWAY2011-02-24 00:00:00.00051747531
RETURN2011-03-31 00:00:00.00051747542
AWAY2011-03-28 00:00:00.00064740681
RETURN2011-04-30 00:00:00.00064752972
[code]....
RESULTS FROM QUERY in SELECT TOP 11
TypeDateCompIDCommIDSeqAwayDaysReturnDate
AWAY2011-06-20 00:00:00.00016977564182011-06-28 00:00:00.000
AWAY2011-12-23 00:00:00.00015059050441122012-04-13 00:00:00.000
AWAY2011-10-09 00:00:00.0003534839461162011-10-25 00:00:00.000
AWAY2012-01-27 00:00:00.0003983890363942012-04-30 00:00:00.000
AWAY2012-03-09 00:00:00.0004064900615202012-03-29 00:00:00.000
[code]....
QUERY in DESIGN VIEW
SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate,
dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name,
Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM dbo.vCommunication INNER JOIN
[Code] .....
QUERY RESULTS IN SELECT
csTranscsTranDateCompanyIDComm_IDNameCommIDSeq
CS12009-07-27 00:00:00.0001176147Harrison Bailey 761471
CS32007-08-27 00:00:00.0002673777Dorothy Wheeler 737771
CS42011-12-02 00:00:00.0002685087Dorothy Wheeler 850872
CS52012-01-01 00:00:00.0002685446Dorothy Wheeler 854463
[Code] ....
View 12 Replies
View Related
Feb 4, 2004
Hey All...
Got a View question.
Have 2 tables:
#1 Currencies
|CCY_Name|CCY_Code|
#2 Rates
|CCY1|CCY2|CCY3|...etc|Active|
-> where the Columns CCY# = the Records in #1
How do I build a View to Select the ONE record in #2 where Active=Y, having the CCY_Name from #1 based on #2.CCY1 (Column NAME) = #1.CCY_Code (Record).
Thanks
robbied111
View 2 Replies
View Related
Feb 10, 2004
I need to see the sql for a view in query analyzer.
how can i do that ??
View 2 Replies
View Related
