|
I used profiler to create a trace of all the queries from SSMS to the database and came up with this:
<br />
SELECT ISNULL(smv.definition, ssmv.definition) AS [Definition]<br />
FROM sys.all_views AS v<br />
LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id<br />
LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id<br />
WHERE (v.type = 'V')and(v.name=N'{my_view_name}' and SCHEMA_NAME(v.schema_id)=N'dbo')<br />
Just replace {my_view_name} with the name of the view you're interested in. Also, if the view is using a schema other than 'dbo' make sure to change that as well.
|
|
|
|
|
Hello everybody,
I have a Attendance table, which has columns Date. ShiftInTime, ShiftOutTime, EventTime, Login_Type.
Now for every EventCode= "I" or "O" there are two records. That means for a day say 07/10/2006 as Workdate there are two records like,
EmpID Date EventTime EventCode
1 07/10/2006 9:00 I
1 07/10/2006 17:15 O
NOW.. I want a report in Table or Crystal report which will have a single record for a single Workdate..as folllows
EmpID WorkDate ShiftInTime ShiftOutTime InTime OutTime
1 07/10/2006 09:00 17:00 9:00 17:15
Can you please help me out..I'm usng a table in ASp
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
select * from table t1 inner join table t2
on t1.date = t2.date
Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com
|
|
|
|
|
Sir,
U'r suggestion is not enough...because i am using onlu only table ,,,my quear gave me theattendance report like i shown above...but Can i simply make the inner join with the same table?
i need the out put as follows
----------------------------------
ID |Name |InTime |OutTime|
1 |xxxxxxxxxxx| 09:05 | 06:01
2 |sdsssdddddd| 06:15 | 06:15
-----------------------------------
Like that..But at present they are displaying as i explainned in my first question.
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
Try this:
SELECT main.EmpId, main.Date,
(SELECT i.EventTime FROM Attendance i
WHERE i.EmpID = main.EmpId AND
i.Date = main.Date AND i.Login_Type = 'I') AS ShiftInTime,
(SELECT o.EventTime FROM Attendance o
WHERE o.EmpID = main.EmpID AND
o.Date = main.Date AND o.Login_Type = 'O') AS ShiftOutTime
FROM Attendance main
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thanks for u'r advide,
But I am using only one table namedv History with following tables
EmpId,EmpName,ShiftIn,ShiftOut,EventTime,LoginType From Event Time I am extracting the values based on the LoginType,,Sir u got me...That is only one table is there....(History)....One doubt regarding that u specified the main,i,o as defrent tables or not?
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
i, main and o are not different tables. They are all table aliases. If you look at the syntax carefully, you will see that this is a way to use items from the same table in sub-items. Change the references from Attendance to History, but leave the table aliases alone.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sir,
I tried but i am getting the error message as "At most one record can be returned by this sub query" ?
This is the code thai i tried
SELECT main.UserName, <br />
(SELECT i.EventTime FROM History i <br />
WHERE i.FuncCode="0") AS InTime, <br />
(SELECT o.EventTime FROM History o <br />
WHERE o.FuncCode="10") AS OutTime <br />
FROM History main
Any chance of mistake in this code?
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
That'll teach me to do it away from SQL Server. Here you go:
SELECT
h.EmpID,
h.[Date],
h.EventTime AS ShiftInTime,
h2.EventTime AS ShiftOutTime
FROM
History h
LEFT OUTER JOIN
History h2
ON
h.EmpID = h2.EmpID AND
h.[Date] = h2.[Date] AND
h2.EventCode = 'O'
WHERE
h.EventCode = 'I'
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sir,
Some changes i made from u'r query ..any way u'r concept worked here,,,Thanks for u'r assistance sir
This is the code i used.....
SELECT h.UserName, h.EventDate, h.EventTime AS InTime, h2.EventTime AS OutTime, h.Dept<br />
FROM History AS h LEFT JOIN History AS h2 ON (h.UserName = h2.UserName) AND (h.EventDate = h2.EventDate)<br />
WHERE (((h.EventDate)=#2/24/2006#) AND ([h.FuncCode]="0") AND ([h2.FuncCode]="10") AND ((h.Dept)="Engg"));
Thanks and Regards
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
i have 3 columns in my table the room,timeStart,timeEnd.
For example i have inserted this data
room: room1
timeStart: 2007-03-02 8:00 AM
timeEnd: 2007-03-02 3:00 PM
Here's the data that will not be accepted if try to insert
room: room1
timeStart: 2007-03-02 9:00 AM
timeEnd: 2007-03-02 4:00 PM
because the timestart is between the timeStart and the timeEnd. Room1 will only be occupied after 3:00 PM.
Can you help me with this?.
Thank you very Much
Bernie
|
|
|
|
|
Hmm. You and this guy Clickety[^] seem to be trying to solve the same problem. Coincidence? Homework?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
please help me getting a portable data access layer code which independent of language
thanks in advance
Tony
|
|
|
|
|
tonyalex wrote: please help me getting a portable data access layer code which independent of language
Have you looked at NHibernate? It will help you build a language independent DAL.
|
|
|
|
|
Tony
DevExpress Persistent Objects is pretty good. I've used it quite a bit now and I have to say that I like it.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sorry,
What i meant is the DAL Source Code that is given by the microsoft for all its .net language.........
|
|
|
|
|
tonyalex wrote: What i meant is the DAL Source Code that is given by the microsoft for all its .net language.........
What DAL source code? Microsoft does not supply DAL source code.
|
|
|
|
|
yes, microsoft is providing code for the data access layer for the our custom application,think some thing like SQLHELPER
|
|
|
|
|
tonyalex wrote: yes, microsoft is providing code for the data access layer for the our custom application,think some thing like SQLHELPER
That is part of the Enterprise Library. It is not a DAL, it is designed to help you create a DAL more easily.
|
|
|
|
|
Can anybody please tell me in SQL Server how should I get the records with their row numbers in a table. Like we use Rownum in Oracle. Thank you.
Regards,
Mohd. Abdul Aleem,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
indian143 wrote: Can anybody please tell me in SQL Server how should I get the records with their row numbers in a table.
You need SQL Server 2005. However, you should realise that row numbers are a fiction. Oracle and SQL Server 2005 supply a number based on the order that the rows are returned and have little (only incidental) or no bearing on how the data is actually stored.
Anyway, the answer is ROW_NUMBER[^]
|
|
|
|
|
Could you please tell me that dont I have any other option to get it SQL Server 2000. Please. Thank you.
Regarsd,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
I do not recommend this but you could create a insert/delete trigger that loops through the recordset and inserts the new value. This is a performance hit. A sample trigger is below. You must have a primary key on the table to do this. I am confused of why you need to reference the rownumber. It has no significance. If you dont mind a few holes here and there after you have deleted a record, use an Identity column.
<br />
CREATE TRIGGER dbo.trg_Count <br />
ON dbo._TEST<br />
AFTER INSERT, DELETE<br />
AS <br />
BEGIN<br />
<br />
SET NOCOUNT ON;<br />
DECLARE @PKEY UNIQUEIDENTIFIER<br />
DECLARE @I BIGINT<br />
<br />
IF ((SELECT trigger_nestlevel()) = 1)<br />
BEGIN<br />
DECLARE TRG_TEST_CUR CURSOR FOR<br />
SELECT [GUID] FROM _TEST<br />
<br />
OPEN TRG_TEST_CUR<br />
FETCH NEXT FROM TRG_TEST_CUR<br />
INTO @PKEY<br />
<br />
SET @I = 1<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
UPDATE _TEST SET ID = @I WHERE [GUID] = @PKEY<br />
SET @I = @I + 1<br />
FETCH NEXT FROM TRG_TEST_CUR<br />
INTO @PKEY<br />
END<br />
CLOSE TRG_TEST_CUR<br />
DEALLOCATE TRG_TEST_CUR<br />
END<br />
END<br />
GO<br />
|
|
|
|
|
I need to make a a recursive table.coz i have Main Category & this Main Category have sub category & these Sub Categoris Have Another sub category.my be alot it depend in the main Category.& in the last there are some items to the last sub category.so i need to design the tables.IF Anyone can help me.
J.M.M.G
|
|
|
|
|
You made me confused about the term of "recursive tables". There is no such thing actually because tables cannot be recursive. Regarding your message I suggest you to layout all items such as (Main Category, Sub Category, Child Category and etc.) and each item should have an index for instance lets say that "Fruits" is the main category and it has an index of 01. The banana blongs to Fruits and its index should be 0101. If bana would have a sub category you should index it as 010101... goes on like that...
I sugges you not to use a field as Number but text. That would make it easier to call back the main index.
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|