I have an assignment to make a library. Right now I'm at the point of implementing business rules. One is that I need to create a trigger that won't allow a member to exceed a certain number of loans at a time, based on their category (student = 5, Teacher =10 and Researcher = 20).
View 1 Repliescan i make a trigger to fire based on given date and time?
View 2 Replies View RelatedI have to build a database model, create tables and use triggers or/and procedures on it. It's about a library. Books are borrowed by students. They can make a reservation of a book.
I built the database model so far. My problem is how to represent following tasks:
- Students can borrow a maximum of 5 books at the same time.
- bookings/book reservations should be later processed in the order they occurred. That is, who first flagged the book also gets the book/books first
I have following entities:
- Book
- Book exemplar
- Borrowed by (a table between the entities Book exemplar and Student)
- Reserved for (a table between the entities Book exemplar and Student)
- Student
How would you represent the tasks I mentioned above in terms of triggers or procedures?...
I have this following query,
select [Parent Name],[ID],[Year],[Sales Name],
sum([Total VtM]) as 'Total Sales'
from RegData
group by [Parent Name],[ID],[Year],[Sales Name]
order by [Total Sales] desc
I need to modify this query to get the top 5 of each category based on Total Sales amount.
Hi all,
I have a large dataset (currently 131,000 rows) that looks similar to the following:
ID NewPer NewAmt OldPer OldAmt
334 1/07/08 200 22/01/08 200
2396 1/07/08 4000 10/12/07 3600
7650 1/07/08 1100 07/07/06 1200
.
.
.
and I need to create a session temp table (eg ##output) that translates the calculation (NewAmt - OldAmt) into categories such as
"decrease -201 to -500"
"decrease -1 to -200"
"no change"
"increase 1 to 200"
"increase 201 to 500"
so that my final output would look like this:
ID NewPer NewAmt OldPer OldAmt Change ChangeCategory
334 1/07/08 200 22/01/08 200 0 no change
2396 1/07/08 4000 10/12/07 3600 400 increase 201 to 500
7650 1/07/08 1100 07/07/06 1200 -100 decrease -1 to -200
.
.
.
I understand how to add the "Change" column to my temp output table, but am struggling with the ChangeCategory column - can someone point me in the right direction?
Thanks in advance
Jamie
selecting number of products that comes under a parent categorysqlserver2005CREATE TABLE [ProductCategoryAssociation]([CategoryID] [bigint] , --this is primary key of table Category[ProductID] [bigint] ,--this is primary key of table product) the above table binds a product to its category CEATE TABLE [Category]([CategoryID] [bigint] IDENTITY(1,1) -- this is primary key of table category[Name] [nvarchar](255)[ParentCategory] [bigint] NULL,) I have a Category"A" as a supper parentcategoryI have 1product in Category"A"Category"A" have 2 child category A11,A12. A11,A12 both have 2 products in the category (totally 4)Now I can get the count of all product belong Category"A" (ie is 5= 1+2*2) fivelet 1 be the id of category "A" Now the following querry give me the expected resultselect count(ProductId) from ProductCategoryAssociation where categoryid=1 or categoryid in (select categoryid from Category where Parentcategory=1) BUT if i have child for A11 named A111 and if there is ONE product belong to A111 this querry wont take that in to cosiderationso the following querry will do itselect count(ProductId) from ProductCategoryAssociation where categoryid=1 or categoryid in (select categoryid from Category where Parentcategory=1 or Parentcategory in (select categoryid from Category where Parentcategory=1))ie i nested one more selectbuthis has limitationsSo kindly give me a general solution to check up to the las level of category
View 8 Replies View Relatedhi all,
I was wondering if it is possible in SSAS 2005 that a calculated member is based off of an (integer) dimension attribute and another (integer) measure (let's say a multiplication operation) ?
If there a trick on doing so? other than stuffing the (integer) dimension attribute back in the fact table, as an measure?
thx much,
Cos
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
If I wanted to make a report that shows different fields, one being cost, but I only wanted certain people to see cost based on their AD groups, is there a way to hide this information?
Basically, domain/user1 belongs to AD purchasing group purchasing, he can see item, description and cost, where domain/user2 belongs to sales so he can only see item and description?
I saw a previous request for help, but I did not see a solution (other than deleting and re-initializing the dump.
I'm copying a database from one server to another and I repetedly get this error when I try to restore from the .bak file I create on backup. The host machine for the database is a workstation (no RAID). The Destination does have RAID, but is funtioning properly (according to the SysOps and in testing file copies). I've tried copying the .bak file to several different drives several different times and I get the same error. I also tried to back-up a database on the Destination server and restore the .bak file to a newly created database and I get the same error.
Anyone solve this problem?
getting Correct Measure based on Member Present in Other Attribute .
I am working on SSAS 2012 and have cube build and ready ..
I have Two Measure in Cube
[MEASURES].[Actual] and [MEASURES].[Target] and I need to create One more Calculate Measure
I have dimension DimProduct
I want to Display [MEASURES].[Actual] if Color "Purple" is present for PRODUCT1 Else Display [MEASURES].[Target].
MDX to create Calculate Measure for this logic?
Hi,
I have a simple table:
Categories
----------
CategoryID
ParentID
Name
I want to associate my Products to a category so that I can search by category....ok. If I just have a simlpe table:
CategoryProducts
-------------------
CategoryId
ProductId
I can link a product to a category. Now, if I just link a product to a single category, such as the bottom leaf category:
Self Help / Personal Development / Spiritual / Meditation
I would link a product to the Meditation category. However if I
click on Self Help while browsing, I want to see all items underneath
Personal Development, Spiritual and Meditiation. So my question
is is this a good way to store the product-category relationships, or
should I put many entries into CategoryProducts to keep the queries
simlpe and faster? Are they faster doing it this way? In this way there
would be 4 entries for a product in meditation. My personal idea
is that adding all entries up a tree arm of a category path will be
cumbersome to manage, but it does solve the problem of clicking on Self
Help and seeing all products that exist within sub-categories. I
am sure an SQL query would be able to work this out, but I dont know if
performance would be something to consider on an ecommerce site? Are
there any patterns fo rthis stuff - seems a reasonably repeatable
pattern for business sites?
Thanks,
jr.
I have to make server trigger to monitor the actitites if sysadmin members. i need to get the login name,hostname and query which they are running.
View 3 Replies View Related<SQL Server 2008 R2>
I created a Time table using BIS. I found that the default naming of time members is too long and redundant.
For example, the wizard generated "Fiscal Calendar 2015", "Fiscal Quarter 1, 2015", etc. However, shorter expression like "FY2015", "FQ1 2015", etc would be enough for me.
Is it possible to change the default naming rule, or does SSAS works correctly if I update the Time table values using SQL?
Hey,
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Thanks.
I have this table below with customers having loans. Nou I want to write a query which retrieves all customers with exactly the same set of Loans.
Rules:
If I give C1 then I should get C2 (same set of loans)
If I give C2 then I should get C1 (same set of loans)
If I give C3 then I should get C4 (same set of loans)
If I give C4 then I should get C3 (same set of loans)
If I give C5 then I should NOT get any row because there is no other Customer with the same set of loans.
If I give C6 then I should NOT get any row because there is no other Customer with the same set of loans.
If I give C7 then I should NOT get any row because there is no other Customer with the same set of loans.
Table LoanCustomer:
[KEY] [LOAN] [Customer]
1L1C5
2L2C1
3L2C2
4L3C1
5L3C2
6L4C3
7L4C4
8L5C6
9L5C7
10L6C7
I tried this query below but it doesn't give me always the same right results.
SELECT t1.LOAN, t1.CUSTOMER
FROM LoanCustomer t1
WHERE EXISTS (
SELECT t2.LOAN, t2.CUSTOMER
FROM LoanCustomer t2
WHERE CUSTOMER= 'C1'
and t1.CUSTOMER != t2.CUSTOMER and t1.LOAN = t2.LOAN
)
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
Hi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
Hi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
I need to create a member that one of its Attributes (maybe my term is wrong) is a reference to another entity member named group
the code
createRequest.Members.MemberType = MemberType.Leaf;
createRequest.Members.Members = new System.Collections.ObjectModel.Collection<Member> { };
Member aNewMember = new Member();
aNewMember.MemberId = new MemberIdentifier() { Name = uag.groupName, MemberType = MemberType.Leaf };
[code]....
"The attribute data type is not valid".Is it wrong to add the reference as attribute? How can I embed the reference in the new member?
I have a table where I need to delete rows if the member does not exists in the member table. Will the following syntax work ?
Delete T
FROM Event_Temp_Lead_Screen T
left join member M on ( M.MemberID = T.MemberID )
where
T.Gender is NULL
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
Hi All,
I am using varchar(8000) in my stored procedure. But the value passed to it get trimmed off as it exceeds max limit.
I can't use text datatype here. Please tell me alternative to exceed the varchar limit so that it can save more value.
Thanks in advance.
Geetanjali
Hello, everyone
I need to create a trigger to update a FLAG column by row modification. The table likes,
Col1FLAG
a0
b0
c0
d0
e0
If "a" in Col1 is changed, the "0" in same row (First row) should be changed to '1'. Other FLAG values should not be changed. The same rule for other row.
Anyhelp will be approciated.
Thanks a lot
ZYT
I was trying to build a string based on one field from one table in Stored Procedure. I know if I declare A VARCHAR variable for the string, it won�t allow to exceed 8000. If does, it will truncate the reminding parts of the fields. What might be the simple solution for that? Even though I know I might need to use several substrings but how?
Thanks!
J827
Declare @sql varchar 8000
Select @sql =@sql + convert(varchar(50),fieldName) from tableA where conditions �
Hi,
I did have SQL Server Express, but have had Visual Studio Pro installed, which comes with SQL Server 2005 Enterprise.
I'm getting this error about the 4Gb limit when trying to restore a database.
How do I get around this problem?
Gary
I'm trying to select records that occur more than once. I'm trying to base this on the email column. So basically I want the query to look something like this:
select * from table where emailaddress count > 1
Can someone provide me with the correct syntax? :)
Hi!
Need some help building a query that does the following :
I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)
In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]
Both Time Dimensions are Year -> Month -> Day and have the same Members.
I have every option available, using calculated Members and/or Measures to do this.
The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.
Appreciate all help!
Regards,
Stian Bakke
One of my table called as 'customertable' contains following fields
customername, emailid, subscriptionendperiod
Example records are:
david, david@john.com, 12/20/2005(mm/dd/yyyy format).
My question is that: Just one month before subscriptionendperiod that is on 11/20/2005(mm/dd/yyyy) an automatic email should go to david@john.com with the message 'Your subscription period ends on 12/20/2005'
How to write trigger for this.
Please Note : No ASP code is invloved in this.
Only MSSQL coding to be done.
Regards
Do triggers work on the field level or only for full table updates, etc...?
I want to have a trigger that will change a field on another table based on the update of a specific field on a table.
Could anyone provide a sample please, if this is possible?
Thank you!
Hi!
I have a table called DB1 that contains this:
MID
IIN
NUM_EVENTS
DATE
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations.
I have attached the look up table data for reference.
I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color.
If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed.
If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4.
The exact colorid of the combination must be returned on passing the values to p1 and p2.
To elaborate; I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like
colorid p1p2p3p4p5p6p7p8
1 redred
10 redredred
20 redredred
30 redredredred
40 redredredredred
50 redredredredredred
60 redredredredredredred
70 redredredredredredredred
So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result.
I need a function or procedure that will accept the arguments and provide me the result based on the values.
[URL]
I need a new field added 'Field1' which will add SEQUENCE number 1,2,.. based ON GROUP BY MasterID..AND another field TotalCount which will COUNT total masterID (here it will be 2)
CREATE TABLE #Temp1
( MasterID INT,
ClientName VARCHAR(10),
ProductName VARCHAR(50)
[code]...