I have a table that has attendance for programs. What I need to get is the number of days each month that any program had an entry for. What I'm trying to do is ensure that an active program had all entries for that particular month.
I can do this query and subquery in Access but not in sql. Here's the Access queries:
1)SELECT Cen_DailyReport.PgmID, DatePart("m",[date]) AS Mo, DatePart("d",[date]) AS Dy
FROM Cen_DailyReport
WHERE (((DatePart("yyyy",[Date]))=2004))
GROUP BY Cen_DailyReport.PgmID, DatePart("m",[date]), DatePart("d",[date])
HAVING (((Cen_DailyReport.PgmID)="dyad"));
Then to get the actual day count per month, the next query is:
2) SELECT Query11a.PgmID, Query11a.Mo, Count(Query11a.Dy) AS CountOfDy
FROM Query11a
GROUP BY Query11a.PgmID, Query11a.Mo;
and this query aggregates the data from the 1st query. I'm wanting to do this in SQL.
In the long run, I want to take that count in the query and compare it with the actual number of days in a month to see if any entries are missing (like a program not being entered at all on any given day that they should have had entries for)
I have 2 tables, Jobs and Categories.Each job belongs to a category. At present, I am returning all categories as follows:SELECT categoryID, categoryName FROM TCCI_CategoriesWhat I'm trying to do, is also return the number of jobs assigned to each category, so in my web page display, it would show something like this:Engineering(5)Mechanical(10) etc.My db currently has 5 categories, with only one job assigned to a category. I tried the following sub-query, but instead of returning all the categories with their job counts, it just returns the category that has a job assigned to it:SELECT c.categoryID, c.categoryName, COUNT(j.jobID)FROM TCCI_Categories c, (SELECT jobID, categoryID FROM TCCI_Jobs) jWHERE j.categoryID = c.categoryIDGROUP BY c.categoryID, c.categoryName, j.jobIDThis is the output when I run the query:categoryID categoryName Column1 ---------------- ---------------------- ------------------------------32 Engineering 1 How would I fix this?
I'm trying to return an integer from the following table that returnsthe number of unique cities:tblEmployeesName CityJohn BostonFrank New YorkJim OmahaBetty New YorkThe answer should be 3.DECLARE @EmployeeCities intSELECT @EmployeeCities = SELECT ... ???How go I return one row/column into @EmployeeCities from a Count and aGroupBy?Headache already... Maybe it's too early...
updating the # of Payer from below query to match with the # of rows for each payer record. See the Current and desired results below. The query is currently counting the # of rows for all payers together and updating 3 as # of payers. I need it to count # of rows for each payer like shown inDesired result below. It should be showing 1 for first payer and 2 for 2nd & 3rd based on # of times each payer is repeated..
SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN, (Select Count(*) From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c where a.CLAIM_ICN_NU = c.CLAIM_ICN and b.TPL_TS = c.TPL_TS and a.CLAIM_TYPE_CD = 'X'
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar, RDR1.U_Variety, (SELECT OITM.U_Variety FROM OWOR INNER JOIN WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN OITM INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity, OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet, OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode FROM OWOR AS t0 INNER JOIN ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode WHERE (t0.Status <> 'L')
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone) FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
The low down: Local Web Server on Windows 2003 Local SQL Server on Windows 2003 Hosting dynamic website tied to inhouse Access Application
Ok, basically, how it is set up, people can login to our website and enter data (insert record), on our end, we have an Access application where we can play with the data that was entered via the website. Currently, we do not have either server set up as a Mail server.
What we need to be able to do: When a customer enters data on our website, their supervisor, and about 2-3 other people related to the transaction need to be emailed to be notified that an order was submitted. So how do I code that? On the page with the Insert Record? OR after Insert Record redirect them to another page that sends the mail out?
Which server do I enable the mail?
I was reading about SQL Mail etc, which would be good since we do mass emails to clients weekly, but I have no idea how to set that up and I look crossed eyed at any tutorial.
Do I want to set up theSQL server to also be a Mail Server that way we can use the SQL database to email as well as data entered from the website? But then again, the website points to the Web Server which pulls data off the SQL server (so unless the Web Server is a mail server, nothing will be sent, am I right?)
*sigh*
I know very little about SQL and I'm being asked to impliment this and I am 100% confused. I'm a graphics artist not a programmer! LOL
All I am trying to do is use a SqlDataSource, Read that Data from the Session Variable, Do a RowFilter off the Data and then return the Data (Currently a DataSet but doesnt matter). But Since I dont use SqlDataSource that much not sure all the code I have is neccasry: protected DataSet GetSearchAppData(string strWhere) { DataSet dsSearchAppTable = new DataSet("SearchAppTable"); SqlDataSource ss = new SqlDataSource(); DataView dv = new DataView(); DataSourceSelectArguments dsArgg = new DataSourceSelectArguments();
dsArgg = DataSourceSelectArguments.Empty; ss = ((SqlDataSource)Session["SearchAppTable"]); dv = (DataView)ss.Select(dsArgg); dv.RowFilter = strWhere; dsSearchAppTable.Tables.Add(dv.Table.Clone());
return dsSearchAppTable; } And to mention it, dsSearchAppTable returns a Table with 0 rows. Not sure what the heck I'm doing wrong.
I have a table with 4 columns, ID, Key, Value and SiteID.
SiteID can be Null or an int.
As an example I could have these four rows, ID=1, key=colour, value=red, siteID=Null ID=2, key=colour, value=green, siteID=2 ID=3, key=font, value=arial, siteID=2 ID=4, key=size, value=6,siteID=Null
My query will pass in a siteID and I need to bring back all rows with that site ID plus any rows whos key I haven't already got who's siteID =Null.
The Null site ID is default, so it always needs to bring back the null if a key doesn't exist with a siteID.
Does anyone know if that is even possible with just a straight SQL query?
Hi I have been tearing my hair out trying to resolve this problem. A form is submitted with a number in to checkthe database for the items with that number of items in the quantity field(s) and display them.
There are 2 relevant Tables in the 2005 server SQL Database, One called "Products" the other called "ProductDetails"
In both tables is a field called Quantity, this hold a numerical value for the quantity of the product.
Some products have additional attributes such as sizes or colours and these are stored in the ProductDetails table.
Every product is stored in the "Products" table and has a unique ProductID, where a product has additional attributes, these along with the ProductID and the quantity for that particular attribute are stored in the ProductDetails table.
I am trying to loop through the database and produce a list of all the items and if they have any additional size or colour attributes then display these too, and if they dont have additional attributes then just display the product details and quantity stored in the Products table.
What I have actually done instead is produce a list of every item and every attribute in the database, but for example Where product 1 doesnt have any attributes, and neither does product 2, but product 3 and product 3 do, when the details are displayed on the page (Using ASP),
Rather than displaying product 1 details and the quantity in the "Products" table, product 2 and the details & quantity in the Products table, then product 3 and each of the attributes & Quanity pertaining to that item in the "ProductDetails" table, and so on, it shows every item as having every combination of attributes,
so although products 1 and 2 have no attributes, it will show product 1 with the first attribute of the other products and then show another product 1 with the next attribute from another product and keep going untill all the attributes in the whole of the Product details table is shown , then do the same thing with product 2 and then product 3 and so on.....
Also the products that have no attributes are not only shown with the attributes that they dont have, they are not shown without attribues and with the correct details in the list.
Here is my string, which has changed a million times, I am just learning as I go along so if it is obvious please be pateient with me and help me out...
rsResults.Source = "SELECT DISTINCT Products.ProductID, Products.ProductName, Products.ProductPict, Products.Quantity, ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_Color, ProductDetails.Quantity FROM Products, ProductDetails WHERE Products.ProductID = Products.ProductID AND Products.Quantity <= "& +rsResults__var1 & " OR Products.ProductID = ProductDetails.ProductID AND ProductDetails.Quantity <= "& +rsResults__var1 & " Order by Products.ProductID asc "
Can anyone see why it is doing this instead of only showing the attributes next to the items that have attribues?
I have two tables: persons (id,age,roleid) roles (roleid,description)
I want to build a sql query to produce the following rows (example): range(age) role1 role2 role3 .... rolen 0 to 4 11 24 5 7 5 to 9 42 7 1 0 10 to 14 14 21 9 8 15 to 20 7 0 7 19
I was reading an information concerning to ROLLUP and CUBE but I have no idea how to do a query like this.
Hello guys, I am askng for any help ...am trying to get this SQL language.
I want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100. -------------------------------------------------------------------- There are three tables in the database: Customer, Product and Orders.
The Customer table has three column: Customer_id (PK), priority, Address.
The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)
The Product table has three columns as well: Product_id(PK), Product_name, Importance.
So the order table is connected to both the product and the customers table by respective foreign key.
I am trying to use ssis (sql query or .net-script task) to transfer data from one table to another. I have difficulties to make the query:
i have one table tbl_games which fields are : type ,startDateTime, EndDateTime, playerId. I need to check every 1/2 hour according to the startDateTime, How many times a person is playing and to show it in a new table tbl_collectData like this: TimeplayerId Games_0-30_secondsGames_30-50_seconds
8-8:30 A 3 2 8:30-9B 2 10 9-:30C 20 7
The length of the game is measured by the StartDateTime -EndDateTime, and only games type # 2 is being collected. The number 3 example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.
I tryed to start by doing something like this , but it is not a complete query: INSERT INTO tbl_collectData (Time, playerId, Games_0-30_seconds) SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS numberOfCalls_0_30 FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND (StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM') GROUP BY playerId
Our office uses a program to keep demographic and private customer information. This program is installed in a Dell PowerEdge Windows 2000 Server with 8 desktops spread throughout the office for our employees to use. We back the SQL database up every night on 2 WD external HD that we rotate and the server does a nightly backup too. Our software vendor is changing to a different software program that we don't care for since our current software works so well. As a result, we want to keep using the original software. Here's my question:
If we choose to stay with the original software, do we need this software to read and print out the SQL database for individual customers forever? In other words, if this software fails, is there a way to read and print the existing records without using the original software? Our software vendor tells us that our version will be discontinued and no support will ever be available again. But since we are required to keep our records for many years down the road, we need a way to print this information out if the customer wants to have a copy say 5 years from now. Is there any way to do this without the original software? Thank you very much for your help and opinions.
Hello,can anyone help me on this: I have a table with 3 columns:id,time,description let's say that I have this set of records: ID TIME DESC 1 8:04 aa 1 8:05 aa 1 8:06 aa 2 8:07 ab 2 8:08 -- 1 8:09 bb
I need a query that return 1 8:04 aa 2 8:07 ab 1 8:09 bb means that from every set of same ID a need the distinct one,coz the first set of "1" refere to same person,the 4th "1" refere to different person.
Hi,I have two tables Trade table and Cons table. Records are inserted inboth the tables independent of each other. There are fields likeExc_Ref, Qty, Date in both the tables.I need to write a query which should give me records :1. Where there is missing Exc_Ref value in either of the table. i.e.If Trade table has a Exc_Ref value but missing in Cons table then thatrecord should be displayed. Similarly if Cons has a Exc_Ref valuewhich is not found in Trade table then that too should be displayed.2. In case where both the tables have matching Exc_Ref data then itshould display the record only when the remaining column does notmatch like Qty or Date.Please help me to resolve this complicated query.ThanksNick
The best way to explain this is by example.I have a source table with many columns.SourceSYMBOLEXCHANGE_NAMECUSIPTYPEISSUE_NAMEand so onThen I have 3 other destination tables.ExchangesEXCHANGE_ID IDENTITYEXCHANGE_NAME UNIQUESecurityMasterSECURITY_MASTER_ID IDENTITYSYMBOL UNIQUECUSIPTYPEISSUE_NAMEand so onExchange_mm_SecurityMasterEXCHANGE_IDSECURITY_MASTER_ID-- The Source table has multiple rows of the same symbol.-- The Exchanges table is already populated with all the exchanges.-- A single security (in the SecurityMaster table) can belong to manyExchanges, hence the Exchange_mm_SecurityMaster table.Now. If I just wanted to insert into the SecurityMaster table withouttouching the Exchange_mm_SecurityMaster table I could just execute:INSERT INTO SecurityMaster ([SYMBOL], [CUSIP], [TYPE], [ISSUE_NAME])SELECT DISTINCT[SYMBOL], [CUSIP], [TYPE], [ISSUE_NAME]FROM SourceWHERE NOT EXISTS (SELECT * FROM SecurityMaster SM WHERE SM.SYMBOL =Source.SYMBOL)Now to the Exchange_mm_SecurityMaster. I need the individual identityvalues for each row inserted into SecurityMaster so I can then turnaround and insert into Exchange_mm_SecurityMaster. Here are theissues/possibilities as I see it.- @@IDENTITY will not work since I am not inserting a single row at atime- I guess I could INSERT INTO SecurityMaster first, THEN do anotherINSERT INTO Exchange_mm_SecurityMaster with different where clause.- I could create a stored procedure that does a single insert intoSecurityMaster and Exchange_mm_SecurityMaster. Then call thatprocedure for each row in the SELECT DISTRICT from the Source table.My main worry is the number of arguments passed in. My example onlyshows a few but a regular SecurityMster table could have 30-50columns.- Maybe do something with a trigger but I am not sure if I can passthe EXCHANGE_NAME value to the SecurityMaster trigger when that tabledoes not need it.Hope I explained it clearly. Any help would be appreciated.
I Have not been able to solve this problem from quiete a while now.
I am using sql server 2005.
I have got a table which contains these columns - start date, end date and volumes if the month in the start date is same as that of end date, the volume remains same, else if the months in the two dates are different, then i have to distribute the volume in such a way that some part will go in the first month and the rest in the other month.. i have to somehow calculate (or prorate) the volume according to the no of days in each month
I have to perform a query on this table so that I can group the volumes for different months and different years.
Here is the sample data...
Service Start Service End FMIS Code No of Units Year Month Volumes
01-Oct-00 15-Aug-01 6440 32 ? ? ?
01-Oct-00 30-Sep-02 6441 40 ? ? ?
01-Oct-02 22-May-03 6440 78 ? ? ?
01-Oct-02 23-May-03 6990 87 ? ? ?
06-Mar-03 31-Jul-03 6997 102 ? ? ?
07-Mar-03 31-Jul-03 6744 3 ? ? ?
01-May-03 31-May-03 6440 789 ? ? ?
23-Jun-03 31-Aug-03 6447 1000 ? ? ?
29-Jun-03 30-Jun-03 6440 981 ? ? ?
30-Jun-03 31-Jul-03 6000 50 ? ? ?
01-Jul-03 08-Jul-03 6002 54 ? ? ?
01-Jul-03 13-Jul-03 6000 562 ? ? ?
I have to calculate the columns coloured in blue..
I am trying to use ssis (sql query or .net-script task) to transfer data from one table to another. I have difficulties to make the query:
i have one table tbl_games which fields are : type ,startDateTime, EndDateTime, playerId. I need to check every 1/2 hour according to the startDateTime, How many times a person is playing and to show it in a new table tbl_collectData like this: Time playerId Games_0-30_seconds Games_30-50_seconds
8-8:30 A 3 5 8:30-9 B 2 10 9-:30 C 20 7
The length of the game is measured by the StartDateTime -EndDateTime, and only games type # 2 is being collected. The number 3 in the example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.
I tryed to start by doing something like this , but it is not a complete query: INSERT INTO tbl_collectData (Time, playerId, Games_0-30_seconds) SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS Games_0-30_seconds FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND (StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM') GROUP BY playerId
Hi, My users table contains a field called researchInterestId which looks like this: 1, 5, 10 This is because users where allows to select multiple options when choosing their research interests. I have another table which contains the names of those research interests, which looks like this: researchInterestId researchInterestName 1 Biology 2 Cancer
My question is, when selecting my list of users, i wish to also display the names of their research interests. I know how to inner join but im not sure in this case as there are multiple values (1, 5, 10) Hope that makes sense and that someone can point me in the right direction or let me know what this type of query is called? Thanks Sam
Hi!I have tables like:States: Club persons----------- --------- --------------*id *id *id*Name *Name *FirstName *StateID *LastName *ClubIDNow I want to select the name of all clubs in a particular state (given the state-id) and also the number of persons in that club, for example:California:--------------------------------San Francisco Bridge Club (4 members)Los Angeles Bridge Club (9 members) How can I realise this with MS-SQL 2000? :-) Thank you for help!
I'll start by giving the basic idea of the data structure I'm looking at in the database (pardon my MS Paint skills!):
And this all starts with a root plan. So (taking projects out of the picture for the minute), it could be a structure going any number of levels down like so: plan -> goal -> strategy -> plan -> goal -> strategy -> plan -> goal -> strategy, etc.And not forgetting, that a strategy can also have any number of projects, and all these relationships are one to many, so it ends up being like a large tree structure. What I am trying to accomplish is user security related, in that I need to enforce rules set to say whether a single user is allowed to view a project. You can define the rules at any level in the structure above. So if for instance, a user is given a rule that they can access anything from a goal, they can see any projects that exist in any strategies underneath that goal in the tree structure. I hope this is making sense so far... The rules can also just be defined at a specific project level. These rules (at the moment, I'm more than happy to change if this is a silly way of doing it) are set in a table that has the columns:user_id intplan_id intgoal_id intstrategy_id intproject_id intallow bitEvery row will have a user_id value, and one of either a plan_id, goal_id, strategy_id or project id (which I thought would be a simple way to pull out what kind of rule if needed), and the allow column just defines if it's an allow or deny rule (a specific deny rule on any object lower in the tree structure will override any cascaded allow rule). The way my application needs to grab this information, is to simply have some way of returning a list project_id values for which the current user is allowed to access. I'm basically stumped. The closest I have gotten (which still didn't quite work) was going to end up using quite a large number of cursors... Which is bad. It can be inefficient to an extent, as once these values are gathered once, I can store them in a Session variable, so it's not grabbing them every time the user tries to load a project. Thanks for any help!
so for the accord the required attributes are a 5X114.3 lug pattern the offset needs to be 40-55 max diameter is 19" max width = 8"
My question is this, how many databases do i need to make? and what catagories do I use in each database? Also is it possible to do less than, greater than, etc, in databases? I think thats everything, thanks!
I have the following table and data:tblDepartments: (each department can only have a maximum of 3 sections attached to it)Columns: DepartmentName , SectionName Row1: dep1, sec1.0Row2: dep1, sec1.1Row3: dep1, sec1.2Row 4: dep2, sec2.0Row 5: dep3, sec3.0Row 6: dep3, sec3.1I need to derive the following table from tblDepartments :Columns: DepartmentName, SectionName1, SectionName2, SectionName3Row1: dep1, sec1.0, sec1.1, sec1.2Row2: dep2, sec2.0, '', ''Row3: dep3, sec3.0, sec3.1, ''Any ideas?
I have the following stored procedure that I use for an update. The table now has just over two million rows and the stored procedure takes days to run. I am looking for any help that would produce the same results faster. This runs on SQL Server 6.5, on an NT machine with 4 pentium pro 200 processors and 512 MB of RAM so I am relatively sure that the performance issue is in the below statement.
UPDATE PAY_CHECK_DETAILS SET JobID = j.JobID ,HROrganizationID = j.HROrganizationID ,JobDetailID = j.JobDetailID ,GLAccountNumber = j.GLAccountNumber FROM JOBS j, PAY_CHECK_DETAILS p WHERE j.EmployeeID = p.EmployeeID AND j.HRActionEffectiveDate = (SELECT max(HRActionEffectiveDate) FROM JOBS jj WHERE p.EmployeeID = jj.EmployeeID AND jj.HRActionEffectiveDate <= p.PayDate)
AND j.HRActionSequence = (SELECT max(HRActionSequence) FROM JOBS jjj WHERE p.EmployeeID = jjj.EmployeeID AND jjj.HRActionEffectiveDate = (SELECT max(HRActionEffectiveDate) FROM JOBS jjjj WHERE p.EmployeeID = jjjj.EmployeeID AND jjjj.HRActionEffectiveDate <= p.PayDate)) AND NOT (ISNULL(p.JobID,1) = ISNULL(j.JobID,1) AND ISNULL(p.HROrganizationID,'A') = ISNULL(j.HROrganizationID,'A') AND ISNULL(p.JobDetailID,1) = ISNULL(j.JobDetailID,1) AND ISNULL(p.GLAccountNumber,'A') = ISNULL(j.GLAccountNumber,'A'))
Thanks for any help or suggestions you have. Keith
Hi, I am positive some SQL Gurus can help me with this complicated query. I am not sure if it is possible with a single query or needs multiple queries.
I have 3 objects. First, PaintOrder; Second, Combination; and Third, Material. Each Paint Order can have multiple Combinations and each Combination can have multiple (from 0 to 5) Materials. All the data is stored in single table called paintMatCom.
I have a Search Screen where user can select multiple Materials (upto 5) to search for the appropriate PaintOrders. Also, use has the option to select AND/OR between materials. Please look at the attached image for clear understanding.
Some of the sample searches are as follows (Combination IDs are not needed in the Output; just PaintOrder IDs). 1. Find PaintOrders with MaterialIDs 1 AND 2 OR 4 2. Find PaintOrders with MaterialIDs 2 OR 3 OR 4 3. Find PaintOrders with MaterialIDs 3 AND 4 OR 5 OR 1
Hope you got the idea. I think it would require some Joins and GROUP BY which I am not expert at. I would appreciate any help.
[IMG]C:Documents and SettingsOwnerDesktopmaterials.JPG[/IMG]
