|
There is a lot to think about. Way more involved than I first thought. All good though. Creating something so complex (provided it all works ok) must give real job satisfaction. I've only done basic database stuff in the past, queries, basic stored procs. I've not really thought about automated auditing, and I didn't realise you could use triggers. This is good stuff.
Sounds like you have plenty of experiance in this game. I have to say, I can see database design and administration playing quite a major part in my career.
oooo, the Jedi's will feel this one....
|
|
|
|
|
Good Day all
i have a two Tables MTM_ACTV_STAFF_CYCLE and MTM_ACTV_STAFF
MTM_ACTV_STAFF
ID | ACTV | STAFF | CYCLETEMPLATE
==================================
1 | 1 | 3 | 3
2 | 4 | 2 | Null
and
MTM_ACTV_STAFF_CYCLE
ID | IDL | CYCLE
=========================
1 | 1 | 3
2 | 4 | 2
i have the Following SP
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[sp_Get_Staff_Cycles]
(
@Subject_Descr varchar(30)
)
AS
--Check if the table Exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FINAL]'))
drop table [FINAL]
--Get the Cycle template description
Declare @CycletemplateTerm varchar(60)
set @CycletemplateTerm = ( select top 1 t.descr from MTM_ACTV_STAFF mtn
inner join tbl_actv a on a.id = MTN.actv
inner join tbl_cntc cc on a.cntcID = cc.id
inner join tbl_modl m on m.id = cc.modlid
inner join tbl_term t on a.term = t.id
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
WHERE M.DESCR = @Subject_Descr
)
SELECT [ID] = IDENTITY (int,1,1),A.DESCR AS [ACTIVITY], S.DESCR,
CASE MTN.CYCLETEMPLATE WHEN NULL THEN C.CYCLES
ELSE @CycletemplateTerm
END AS CYCLETEMPLATE
INTO FINAL FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
inner join tbl_actv a on a.id = MTN.actv
inner join tbl_cntc cc on a.cntcID = cc.id
inner join tbl_modl m on m.id = cc.modlid
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
WHERE M.DESCR = @Subject_Descr
now in the above Sp i have the Following Problem. In this Line of code
CASE MTN.CYCLETEMPLATE WHEN NULL THEN C.CYCLES
ELSE @CycletemplateTerm
END AS CYCLETEMPLATE
If the field CYCLETEMPLATE in MTM_ACTV_STAFF table is null i want to use the Values found in table MTM_ACTV_STAFF_CYCLE and Field CYCLES
and another condition again, if there is a value in the MTM_ACTV_STAFF and on the CYCLETEMPLATE then
use the Value in the Variable @CycletemplateTerm
Now my Problem is that instead of bringing 3 its Brings a Sting "Year". there is Prbably something Wrong in my Condiution. Can you please help me in that Regard
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
When you're selecting into @cycletemplateterm you're setting it to t.descr, which I'd assume is where the text "Year" is coming from. Have another look and check which table and column should be populating the variable.
|
|
|
|
|
Good Morning
i have changed it to this and it works
ALTER PROC [dbo].[sp_Get_Staff_Cycles]
(
@Subject_Descr varchar(30)
)
AS
--Check if the table Exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FINAL]'))
drop table [FINAL]
--Get the Cycle template description
Declare @CycletemplateTerm varchar(60)
set @CycletemplateTerm = ( select top 1 t.descr from MTM_ACTV_STAFF mtn
inner join tbl_actv a on a.id = MTN.actv
inner join tbl_cntc cc on a.cntcID = cc.id
inner join tbl_modl m on m.id = cc.modlid
inner join tbl_term t on a.term = t.id
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
WHERE M.DESCR = @Subject_Descr
)
SELECT [ID] = IDENTITY (int,1,1),A.DESCR AS [ACTIVITY], S.DESCR,
CASE
WHEN MTN.CYCLETEMPLATE IS NULL THEN C.CYCLES
WHEN MTN.CYCLETEMPLATE IS NOT NULL THEN @CycletemplateTerm
END AS CYCLETEMPLATE
INTO FINAL FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
inner join tbl_actv a on a.id = MTN.actv
inner join tbl_cntc cc on a.cntcID = cc.id
inner join tbl_modl m on m.id = cc.modlid
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
WHERE M.DESCR = @Subject_Descr
Thank you
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Hi,
How to select a row which contain null where i have a condition also.
eg:
field NUMBER have -1,null,1,2,3,4 values
how can i select the value excluding -1
i give a condition
select * from table where NUMBER <> -1,
its returning only 1,2,3,4
I want null row also
Regards,
YPKI
|
|
|
|
|
SELECT *
FROM [table]
WHERE [number] <> -1
OR [number] IS NULL
I are troll
|
|
|
|
|
SELECT *
FROM dbo.tblDisplayPositive
WHERE FieldNumber >= 0
OR FieldNumber IS NULL
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi,
I need to submit data from a form to an SQL2005 database via a stored procedure. The difficult part is that i also need to get 5 random records from a secondary table and insert these as part of the inserted record in table 1.
My structure is akin to this:
Tbl_Campaigns (table to be inserted to)
Key | field_1 | field_2 | field_3 | field_4 | field_5 etc
Tbl_Organisations (table to get the 5 random records from)
Key | field_1
I need to get 5 unique / random records from 'tbl_Organisations', catch them and insert them into a single record along with the data input to the stored procedure. I understand arrays arent an option in SQL2005(?).
So how do i catch these records and then insert them as a single record along with the SP Inputs?
Any help would be greatly appreciated as i am trying my best to get to grips with the complexities of SQL
Thanks.
modified on Tuesday, July 7, 2009 6:04 AM
|
|
|
|
|
This may be useful....
select top 5 key, field_1 from tbl_organisations order by 100 * rand()
However, I haven't tested it cos I don't have SQL Server here so it may not work at all!
|
|
|
|
|
Hi,
I would get a random record using the following:
SELECT TOP 5 (*) from tbl_organisations AS randomOrgs
However my issue is with how i would then catch these 5 records and insert them into the other table as a single record with the SP inputs.
|
|
|
|
|
SELECT TOP 5 (*) from tbl_organisations order by newid()
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Blue_boy,
Please read the original post in this thread. Thanks for your help but you appear to have misinterpretated my question.
|
|
|
|
|
i have two tables the first has foreign to another like this
table1:
table1Id table1Name table2Id
1 mahm
2 kha 1
table2:
table2Id table2Name
1 cc
2 dd
i need the following result
table1Id table1Name table2Name
1 mahm
2 kha cc
|
|
|
|
|
select table1Id, table1Name, table2Name
from table1
left join table2 on table2.table2Id = table1.table2Id
|
|
|
|
|
Dear Friend,
I am working on database which is having huge data and having complicated stored Procedure, some time i face problem because of this, want to clear all this....
To overtook this all problem i start working on Procedure which is making issue and fine tune some extend, i want to change them just like blood vain in my body so data can be flow just like the blood but this can only happen if i know the imp things....
You got idea what i want, i want to know the exact process for all this.
Me using following characters and syntex in my queries
1) IN and NOT IN
2) <>
3) CASE
4) GROUP BY.
5) Using Date Converting it to nvarchar datatype (for view only not in where clause).
6) UNION and UNION ALL
7) SUB Query
8) INNER JOINS (other joints are use but lesser amount)
Read some where that it is not good to use following character in where clause
1) IN and NOT IN
2) <>
3) CASE
I am using all this which is not possible to not use all this, if you have any other idea please give it to me.
Give me some link some article which help for all this
thanks and regards in advance,
Sasmi
|
|
|
|
|
Check this.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Actually i just want to output the difference of two Timespans with respect
to its dates e.g.
07/03/2009 05:00:00
07/04/2009 10:00:00
and the difference i get is 05:00:00 which is not correct i am using the following code:
DECLARE @EnterTime datetime, @DischargeTime datetime
Select @EnterTime = convert(varchar,EnterTime,8) from visit
Select @DischargeTime = convert(varchar,NextTime,8) from visit
Select convert(varchar,@DischargeTime - @EnterTime,8)
the above code is working fine if the duration is between 24 hours. Can you please suggest me a solution or i have to apply the logic on programmatic side i.e. .NET?
AliAmjad(MCP)
First make it Run THEN make it Run Fast!
|
|
|
|
|
Do you want to get days or hours or minutes between two dates?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I want to get time in HH:mm:ss format e.g. 13:15:02
AliAmjad(MCP)
First make it Run THEN make it Run Fast!
|
|
|
|
|
This will do your trick.
DECLARE @d1 DATETIME, @d2 DATETIME, @sd INT
SET @d1 = '20090706 00:00:00'
SET @d2 = '20090707 01:30:01'
SET @sd = DATEDIFF(SECOND, @d1, @d2)
SELECT
CASE WHEN @sd/3600<10 THEN '0' ELSE '' END
+ RTRIM(@sd/3600)
+ ':' + RIGHT('0'+RTRIM((@sd % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((@sd % 3600) % 60),2) AS Hours
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks Bro...
AliAmjad(MCP)
First make it Run THEN make it Run Fast!
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hey everyone,
In Oracle, can we store files as attachments in a table field "Files like MS. Word, Excel, PDFs" so it contains all the files embedded in a table and when exporting the database, you dont need to also backup a folder of files from your drive as well.
Please advise.
Many thanks!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
modified on Sunday, July 5, 2009 6:46 AM
|
|
|
|
|
|
Thanks mate but I'm sorry I forgot to mention that I'm using Oracle and apparently whatever the case is, I still need to store external files on the drive which's mainly my question.. Thanks anyways mate.
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|