Here is the table A:
Week Year Name
1 2007 x
2 2007 b
3 2007 d
4 2007 d
5 2007 d
6 2007 d
7 2007 d
1 2008 h
This table goes all the way to week 52. I have only 2007 and 2008. so when the user pass start week :1 end week:4 start year 2007 end year 2007
The report will come out something like this
Week Year Name
1 07
2 07
3 07
4 07
Here is the select statement for that
Select week,year,name from dbo.A
Where Week between @startWeek and @endWeek in this case 1 and 4
The real challenge is if the user enters the following input
start week :49 end week:1 start year 2007 end year 2008
in the case the report should look something like this
Week Year Name
49 07
50 07
51 07
52 07
1 08
How can I accomplish such thing in my select statement? I did create a function to handle such scenario but it did not work out.
Any thoughts!
Thanks
proj_name res_name yr SumOfHours x Mike 2007 1 x Mike 2008 2 x Mike 2008 3
Here is table dbo._LastWeekTotalHours
proj_name res_name yr td_hours Week x Mike 2007 1 1 x Mike 2008 2 8 x Mike 2008 3 9
now here is what the output should look like
Lets assume the user well enter 8 for week number
proj_name res_name totalHours LastWeekHours x Mike 6 2 Y AJ .. ¦.
I am trying to write a select statement for that:
Here is what I have:
SELECT a.proj_name, a.res_name,a.TotalHours,g.TotalHours as LastWeekhours FROM
Line 1( SELECT r.proj_name,r.res_name, SUM(r.td_hours) AS TotalHours FROM line 2 dbo._LastWeekTotalHours as r, _vw_Employee as e Line 3 WHERE e.RES_NAME = r.res_name and r.Week = 5 and r.yr = 2008 Line 4 GROUP BY r.proj_name, r.res_name ) as g
Line 5(SELECT r.proj_name, r.res_name, r.TotalHours Line 6 FROM Line 7 (SELECT proj_name, res_name, SUM(td_hours) AS TotalHours FROM line 8 dbo._LastWeekTotalHours line 9 GROUP BY proj_name, res_name ) r , line 10 (SELECT proj_name, res_name, SUM(SumOfHours) AS TotalHours FROM dbo.vw_TimebyProjectAll line13 GROUP BY proj_name, res_name )w )a
if i excute line 1 - 4 i will get the last week hour correctly if i excute line 5 - 13 i will get the totalhours correctly
my problem i can't put these two pieces work togather. feel free to re-write the select statment thanks
Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem. I'm relatively new to SQL, so this may not be so tough for some of you. Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him. The list is presented to him via a datalist. Each item of the datalist contains a table of 2 columns and 1 row as pictured below. The first box contains the user photo and user name of the person who SENT him the message (not the logged in user). The second box contains the subject title of the message. FROM | SUBJECT | User Photo | || | Subject || User Name | | Here is the list of the relevant 4 tables of my database and the relevant fields..... aspnet_Users tableUserId (used to link to Member table)UserName Member tablememberId (int - primary key)UserId (guid - used to link to aspnet_Users table)primaryPhotoId (int - used to link to Photo table) Photo tablephotoId (int - primary key)photoUrl (string - path to file on local drive) Message tablemessageId (int - primary key)fromMember (int - connects with memberId from Member table)toMember (int - connects with memberId from Member table)subject (varchar(max)) So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user. The senders will be determined by the Member.fromMember fields. Intuitive enough so far, I guess. This is the SQL statement I have so far..... SELECT aspnet_Users.UserName, Message.subjectFROM aspnet_Users, Member, MessageWHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember Note that I'm grabbing the logged in user info from Profile.UserName. So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user. HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)? Do you see the core of my dilemna here? I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER). Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender. And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ?? I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe. The SQL statement so far asks to retrieve information based on the logged in user. But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained..... and still hang on to the results I have up until now? And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement. This is indeed very very complicated for me lol. This problem has been giving me migraines this whole weekend. Has anyone been through such a problem before? Any help would be greatly appreciated - thanks in advance.
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are:
Field02
----------------------
22810 8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760 22810
Is there any good suggestion? Thanks in advance for any help, Aldo.
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E
can any one help me in this? please give me a sample query.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
Hi,I need help to generate following query....------------------------------------------------------------------------------------------------------------- Tables and their columns:Table name: Employee Columns for Employee Table : (1) EmpID (2) EmpName (3) EcatID (4) ProjectIDSecond Table Name: EmpskillColumns for Empskill Table: (1) EmpID (2) SelfRating (3) EvalRatingInformation: (1) If EcatID = 1 means he is Project Manager [if not then he is a software Engineer] (2) Both Project Manager and his subordinates have the same ProjectID------------------------------------------------------------------------------------------------------------- Result Needed:-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PM Name Software Enggs Nme under that PM No of Self Rated Projects (for SEs) No of Evaluator rated Projects (for SEs) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Rajesh 10 8 Suguna Mahesh 5 5 Roopesh 3 3--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------In Short: List the Project Mangers name, in the next column list their subordinates (for one PM there are many sub ords), in the next column list the no of self rated projects of the sub ordinates, in the last column, list the No fo evaluator rated projects Can Some One Help me ??Thanks in AdvanceHave a nice day
I am a bit into this one and it is kind of bugging me as i have not figured it out yet after 1 hour. I have a web task in a Sql Server 2K that generates ASP code and ftps it to a remote location. This is nothing fancy: selects a few fields from a few tables and creates an HTML page into a file with a *.asp extension. It is much like a report that gets ship from the intranet to the Internet side of our business. You can do something similar by using the Tools>>Wizards>>Management>>Web Assistant Wizard... inside Enterprise Manager. The page looks fine but the problem (my) is that the rows all have the same color - white the default. I am using a template [*.tpl] and the alternating color for the rows <tr> has to come from the data in the query. Now what I would like to do is something like this to get alternating colors in my table rows: declare @bgcolor nvarchar(12) set @bgcolor = 'white' select @bgcolor = CASE @bgcolor WHEN 'white' THEN 'gray' WHEN 'gray' THEN 'white' ELSE 'white' END, , field1 , field2 , field3 from table1 inner join table2.... where .... And, maybe get results like these: 'white', 'field1res', 2, 3 'gray', 'field1res, 3, 3 'white', ....etc. The above DOES NOT WORK. Maybe because T-SQL is a set based language trying to do something like this is not appropriate - aka procedural. It does, however, still bugs the hell out of me. :) Maybe I should investigate using Reporting Services to handle this requirement. If anyone has any suggestions, please let me know.
Dear all, i have a challenging problem in writing a trigger here,,
here is the database script
-----------------------SCRIPT------------------------ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Categories] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Categories_Properties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Categories_Properties] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Properties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Properties] GO
CREATE TABLE [dbo].[Categories] ( [CategoryID] [int] IDENTITY (1, 1) NOT NULL , [CategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ParentID] [int] NULL , [CategoryDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MainCategory] [bit] NOT NULL , [HavePhoto] [bit] NOT NULL , [MaxPhotos] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
CREATE TABLE [dbo].[Categories_Properties] ( [Categories_PropertiesID] [int] IDENTITY (1, 1) NOT NULL , [CategoryID] [int] NOT NULL , [PropertyID] [int] NOT NULL ) ON [PRIMARY] GO
CREATE TABLE [dbo].[Properties] ( [PropertyID] [int] IDENTITY (1, 1) NOT NULL , [PropertyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO ------------------------END SCRIPT--------------------------
Categories and Categories_Properties are joined by CategoryID Properties and Categories_Properties are joined by PropertyID
there is a relation between the table Categories and itself, the PrimaryKey is CategoryID, the ForiegnKey is ParentID
the problem is i need a trigger to fire when adding a record in Categories_Properties to add this property to all the children for this Category, for eexample if the categories tree
Autos >> Cars >> FIAT
when adding the Property BRAND to to Autos, this Property had to be inserted to Cars and FIAT too, i wrote some trigger but it adds this property to just the direct children, just to cars, How to update it to include all the children in all levels till the leaf ones??
here is what i wrote -----------------------Trigger------------------------- CREATE TRIGGER [inherete_property] ON [dbo].[Categories_Properties] FOR INSERT AS declare @CatID integer,@PropID integer --set @CatID=select CategoryID from inserted set @PropID=(select top 1(PropertyID) from inserted) DECLARE cat_cursor CURSOR FOR select CategoryID from Categories where ParentID in (select CategoryID from inserted) open cat_cursor fetch next from cat_cursor into @CatID WHILE @@FETCH_STATUS = 0 BEGIN insert into categories_Properties(CategoryID,PropertyID) values (@CatID,@PropID)
FETCH NEXT FROM cat_cursor INTO @CatID END CLOSE cat_cursor DEALLOCATE cat_cursor -----------------------End Trigger---------------------
This problem occurs in a Visual Basic 5 application which accesses the SQL Server 6.5 database through Jet Engine(DAO).
If a user starts a transaction and then his application terminates abnormally (before rolling back or committing), say due to power failure or if the user kills the application with Ctrl+Alt+Delete, then the transaction started by him is not terminated (rolled back) by SQL Server. Even the process cannot be killed using the "Kill" command and the user keeps holding all the locks that he has already acquired. Also nobody can use Enterprise Manager. This happens when you have lock on more than 8 tables including tables in tempdb. The only way to solve this problem is to restart the server which is never expected in a multiuser system.
One can know the threadID of the process that needs to be killed using "Sysprocesses" system table. Is it possible to kill the thread in Windows NT?
Is there any setting due to which SQL SERVER will automatically kill a process or rollback a transaction started by a disconnected client ?
Is it possible that a process or transaction will have to release lock after the specified time ?
How does one figure out, I mean compare the results after some changes have been done to the SQL Server, I mean the method in which the comparision is done, lets say I have a query I ran it it gave me the results in 40 seconds , I ran it again this time it took 30 seconds, then I again ran it it took 35 seconds, I then created an index , this time the query ran in 30 seconds ... how does one compare such things , I mean i need to give stats as too what kind of performace has takern place ... please helpo , I need to knw as a DBa how would you convince your maanger that becasue of some changes the performance has improved, cause when you ask the users they say its ok , we dont see the differnece & stuff , please help.
Table DDL:create table test(inId int primary key identity,inFK int not null,inSeq int not null,dtDate datetime)Data Insert:insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 1,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 2,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 2,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 3,1, getdate()WAITFOR DELAY '000:00:01'insert into testselect 4,1, getdate()If we select on this table:inId inFK inSeq dtDate----------- ----------- -----------------------------------------------------------------1 1 1 2005-02-01 12:54:40.9672 1 1 2005-02-01 12:54:41.9673 1 1 2005-02-01 12:54:42.9674 2 1 2005-02-01 12:54:43.9675 2 1 2005-02-01 12:54:44.9676 3 1 2005-02-01 12:54:45.9837 4 1 2005-02-01 12:54:47.077(7 row(s) affected)Problem:What I would like to do (using SQL and not a cursor) is to update thevalue of inSeq to its ordinal position, this will be based on the # ofoccurences of inFK. For Example, I would like to run a sql statementthat would transform the data to:update testset inSEQ = (some sql)select * from test - This would then produce:inId inFK inSeq dtDate----------- ----------- -----------------------------------------------------------------1 1 1 2005-02-01 12:54:40.9672 1 2 2005-02-01 12:54:41.9673 1 3 2005-02-01 12:54:42.9674 2 1 2005-02-01 12:54:43.9675 2 2 2005-02-01 12:54:44.9676 3 1 2005-02-01 12:54:45.9837 4 1 2005-02-01 12:54:47.077(7 row(s) affected)Any help would be greatly appreciated.TFD
here is a structure of tables in a databaseCUSTOMERS (CustID*, CompanyName, ContactName, ContactTitle, Address,City, Region, PostalCode, Country, Phone, Fax)EMPLOYEES (EmployeeID*, Lastname, Firstname, Title, TitleofCourtesy,Birthdate, Hiredate, Address, City, Region, Postalcode, Country,Homephone, Extension, Reportsto)ORDERS (OrderID*, CustID, EmployeeID, OrderDate, RequiredDate,ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,Shipregion, ShipPostalCode, ShipCountry)ORDER_DETAILS (OrderID*, ProductID*, UnitPrice, Quantity, Discount)PRODUCTS (ProductID*, ProductName, SupplierID, PL_ID, QuantityPerUnit,UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)PRODUCT_LINES (PL_Id*, PL_Name, Description)SHIPPERS (ShipperID*, CompanyName, Phone)SUPPLIERS (Supplierid*, Companyname, Contactname, Contacttitle,Address, City, Region, Postalcode, Country,Phone,Fax)* - Primary keysqueries (these are problem questions given in a competition!!!)1.) In which month of 1997 was the most number of orders placed? Listthe month, number of orders placed during that month , number ofcustomers who placed orders in that month , gross (i.e. disregarddiscount) total value of orders placed .2.)Determine the products for which there has been at least oneorder during each month of 1997. Display for these products: theProduct ID, Product name, Product Line Name, number of orders placedduring 1997 , number of customers who have ordered the product in 1997, and gross (i.e. disregarding discount) total value of orders placedfor the product in 1997 .3.) Find the customers who have placed orders during 1996 & 1997 forproducts in every product line AND who have had their orders handledby either all employees or all but one employee. Display the customerID, company name, address, city, country and number of orders placed4.) For each product line, find the top three customers (based onmaximum total purchase value; ignore discounts). Display the ProductLine name, the Customer ID & Company name, and the total purchasevalue for that customer in the product line . Sort the output byProduct Line and within that, the line revenue figure (highest first).i think rank() shud b usedany clues/ideas ???
First, please reference the following document which shows the layout of the database and tables: http://www.eastproject.org/cody/table_layout.pdf Here's the deal-- I have two databases, SiteData and CommunityServer2. Both run on the same MS SQL 2000 Server. We're concerned about two tables on each database: SiteData.dbo.Users, SiteData.dbo.UM_User_Access_Type, CommunityServer2.dbo.aspnet_Users and CommunityServer2.dbo.aspnet_UsersInRoles. Whever a user is inserted into the CommunityServer2.dbo.aspnet_Users table, I need to give the user appropriate rules (e.g., moderator, student, etc.) by inserting the correct RoleID in the SiteData.dbo.aspnet_UsersInRoles table. Have I lost you yet? Good. Please reference the following trigger: ALTER TRIGGER trig_UpdateForumRoles ON CommunityServer2.dbo.aspnet_Users AFTER INSERTAS DECLARE @Username VARCHAR(100) --variable to hold the username from CommunityServer2.aspnet_UsersDECLARE @UserType VARCHAR(50) --variable to hold the username from SiteData.dbo.UM_User_Access_TypeDECLARE @UserID uniqueidentifier --variable to hold the username from CommunityServer2.aspnet_UsersBEGIN SELECT @Username=Username, @UserID=UserID FROM Inserted --should only return one record, meaning we'd only insert one record in aspnet_Users at a time. SELECT @UserType=UserTypeFROM SiteData.dbo.UM_User_Access_Type UM, SiteData.dbo.Users U, aspnet_Users AU, aspnet_Roles AR, aspnet_UsersInRoles AUIRWHERE U.Username=@UserName and U.ID = UM.Student_ID and U.Username = AU.UserName and AU.UserID = AUIR.UserID and AUIR.RoleID = AR.RoleID -- if usertype is a SuperAdmin as determined from SiteData.dbo.UM_User_Access_Type, we insert the appropriate RoleID in aspnet_UsersInRoles IF (@UserType = 'SuperAdmin') BEGIN UPDATE aspnet_UsersInRole SET RoleID = 'EDA73944-1B67-4DAA-B6CB-792847B6C694' WHERE UserID = @UserID END IF (@UserType = 'StaffAdmin' or @UserType='Partner') BEGIN UPDATE aspnet_UsersInRole SET RoleID = '7B317FF8-7C3C-4D95-AC44-E27E849D4520' WHERE UserID = @UserID END IF (@UserType = 'Facilitator') BEGIN UPDATE aspnet_UsersInRole SET RoleID = 'D04FEA9F-35E9-41A5-B062-B9C1524D4266' WHERE UserID = @UserID END IF (@UserType = 'Student') BEGIN UPDATE aspnet_UsersInRole SET RoleID = '4F94ABB9-1371-4834-95D6-E8364824F484' WHERE UserID = @UserID END ENDGO However, it's not yet working. I can create the trigger fine (so no syntax problems); however, it's not updating the aspnet_UsersInRoles table.
I have about 20 remote databases that I need a query/scheduled task to run that generates a .rpt file. That part I can get.
However, I need to get this file to another company (via email or ftp, etc) and make it automated.
For example:
Scheduled task runs twice a month and generates a file that then needs to be emailed to an individual or ftp'd to a ftpsite. Is there a way to do this in SQL 7.0 (or 6.5)?
I am using SQL Server7 to store many thousands of distinct attributes for a product. Because SQL Server7 has a columns-per-table limitation of 1024, I had to decide between spreading the attributes among multiple tables or try a "link table" approach which would only use two tables. I chose the "link table" approach because I thought it would be a more elegant solution. I have had success using this approach up to the point of doing complex filtering of the records. This is where I need help.
Here is this "link table" approach database schema:
Instead of having tables with many columns, I use a single table to list the thousands of attributes. I then use another table to associate an attribute with a product and a attribute value.
The problem i am having is building a filter query that will get products that have multiple attribute values. Example, I want to find all products that have a attribute value of 'AAA' and an attribute value of 'BBB'. Because the attribute values are not in separate columns, a simple AND in a WHERE clause does not work- i get zero records returned. Here is sample SQL:
SELECT DISTINCT ProductItemID FROM tblProductItemAdditional WHERE ((AttributeValue = 'AAA') AND (AttributeValue = 'BBB'));
My question to you is- How do I filter the records to get the results I want?
I have started looking in cascading select statements. I am worried about cascading select statements because of performance problems that are associated with many nested subqueries. SQL Server7 also has a nested subquery limitation of 32 (which limits how large my search filter can be- I would like to make a filter using around 100 different values).
Is it possible to do what I want in practical way, or did I come to a "dead-end" in my database design? What should I do?
I tried to create a view on following 4 tables with two different syntaxes. One with ANSI syntax and other with conventional(using WHERE clause). Both SELECT work fine and return same result sets. Views are created in both cases. SELECT from Conventional_View works fine But SELECT from the ANSI_View returns error message without Error Number as :
I am new to Reporting Services. I built a tabular report using report build wizard, comprise 10 columns, including a year column from year1 to year10.
Now my client requests 3 of the columns need to display different value accordingly:
From year 1- 5, display the field value from data source, From year 6 onward, display zero value, which there is value exist in data source.
The subtotal calculated column 10 applies different formula as well:
From year 1-5, sum (column 3, 4, 6) From year 6 onward column 10 less sum (column 3, 4) Could anyone help to shed some light for how to overcome this challenge? Many thanks in advance,
I need to search the field containing the word I enter, but this word is bracketed by "{{" and "}}". For example, I would like to search the keyword apple, I need to search by the following sql statement.
select name from table where name likes '%{{apple}}%'
But the case is that that column may contain some space between the bracket and the keyword. i.e. {{ apple }}. How can I write a sql statement to search name from the keyword?
Assume that there are no overlapping enrollments. We are able to enforce this at the time the records are inserted (e.g., through the front-end application).
Members can have multiple enrollments. These enrollments can be adjacent (e.g, member 222-22-2222 has one enrollment which terminates on 06/30/2000 and another that begins on 07/01/2000) or there can be gaps (e.g., member 111-11-1111 has one enrollment which terminates on 04/30/2000, the subsequent enrollment begins 06/01/2000).
Our task is to write a SELECT statement that will coalesce all adjacent enrollments where the policy is the same. E.g., member 333-33-3333 has two records in the source table, these records would be combined into a single enrollment with start date = 01/01/2000 and end date = 12/31/2001.
SELECT e.ssn, e.policy, MIN(e.startdate) AS startdate, MAX(e.enddate) AS enddate FROM enrollment e GROUP BY e.ssn, e.policy WHERE EXISTS (SELECT * FROM enrollment e2 WHERE e.ssn = e2.ssn AND e.policy = e2.policy AND e.startdate = DATEADD(y,1,e2.enddate)) OR EXISTS (SELECT * FROM enrollment e3 WHERE e.ssn = e3.ssn AND e.policy = e3.policy AND e.enddate = DATEADD(y,-1,e3.startdate))
UNION
SELECT e4.ssn, e4.policy, e4.startdate AS startdate, e4.enddate FROM enrollment e4 WHERE NOT EXISTS (SELECT * FROM enrollment e5 WHERE e4.ssn = e5.ssn AND e4.policy = e5.policy AND e4.startdate = DATEADD(y,1,e5.enddate)) AND NOT EXISTS (SELECT * FROM enrollment e6 WHERE e4.ssn = e6.ssn AND e4.policy = e6.policy AND e4.enddate = DATEADD(y,-1,e6.startdate))
This works for member 333-33-3333 who has only one set of adjacent enrollments.
However it fails for member 444-44-4444 who has two sets of adjacent enrollments. Can anyone suggest an approach that would return two rows for 444-44-4444, one with start date 01/01/2000 and end date 11/30/2000 and another with start date 03/01/2001 and end date 12/31/2001?
I have data coming in from our OLTP's view which has the following sales related information:
1) Date of Sale 2) Product Sold 3) Customer to which the Product was sold 4) QTy. sold 5) Total Sale Amount
I am bringing in this information into my "staging area" and from there I am building my Customer, Product, and Time Dimension and obviously my FACT table. The measures in my fact table were TotalSalesand Total QtySold and everything was working fine and I was able to analyze, slice, dice my cube in many ways...
Now the problem:
I also have planning related data available from another OLTP based view which contains the following information:
1) Plan Year 2) Plan Month (using Plan Year and Plan month,I "derived" a date which for any month was the 1st of that month) 3) Planned Product 4) Planned Sale (Amount) 5) Planned Qty (for sale)
You would have noticed that planning is NOT done at "Customer Level" and the planning data is only for product and time level (and that too only upto month level).
After populating my FAct table from the Sales data, I appended the planning related data into my fact table hoping that as long as I do not select any thing in the Customer dimesion and only go as low as the month level on my Time dimension, I will be able to see all of the following (measures) for any/all products:
Planned Sale, Actual Sale, Planned Qty., Actual Qty,
However I am unable to get anything displayed in the Planned Sale and Planned Qty despite applying all of the little knowledge that Ihave so far in the world of BI and making data marts ....
I really hope that my questions and requirement is clear.Can someone please help me get to
Hi, I have a table with three group headers. I want the header row in the third section to be toggled by a textbox in the second header. This works as expected. However, there are times when there is no data in the third section. When this happens, the third section header row shows even though there is no data. I can also create an expression for the third section header's hidden property that looks for the existence of data, but then the toggle is deactivated. I need some way to say, "here's the toggle item. Show the header if there is data for it."
