I have an interesting challenge. Imagine I own a pizza restaurant and I have delivery drivers (4). I also have a fixed number of delivery Routes for my restaurant and these routes have varying number of households on the them. I want to sort and assign the routes as evenly as I can to the drivers.
Drivers:
-------------------------
1 - Bob
2 - John
3 - Ricky Bobby
4 - Batman
What I'm TRYING to is write a Stored Proc to loop though the records (routes) and assign them to the drivers as evenly as possible based on households (staying with whole integers).
I've tried a couple of different concepts but keep hitting 'writers block'.
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
Is there a way to conditionally turn off a group? where would that be? I dont want it to group and sort the data in anyway? is there a way to force the grouping order (not as ascending/descending but your own stated order without doing that in the proc it calls? Thanks!
I've got a report built and I'm trying to figure out how sorting and grouping works. I can group the report by Patient, Albumin and it groups as I would expect.
Patient Date Albumin Adams, John 01/28/2007 4.1 Adams, John 12/30/2007 3.9 Adams, John 01/15/2007 3.2 Barker, Mark 01/18/2007 4.3 Barker, Mark 01/22/2007 4.1 Barker, Mark 01/05/2007 3.9
However, when I try to group by Albumin, Patient, it just sorts by Albumin. Patient Date Albumin Barker, Mark 01/18/2007 4.3 Adams, John 01/28/2007 4.1 Barker, Mark 01/22/2007 4.1 Adams, John 12/30/2007 3.9 Barker, Mark 01/05/2007 3.9 Adams, John 01/15/2007 3.2
What I'm looking for is this: Patient Date Albumin Barker, Mark 01/18/2007 4.3 Barker, Mark 01/22/2007 4.1 Barker, Mark 01/05/2007 3.9 Adams, John 01/28/2007 4.1 Adams, John 12/30/2007 3.9 Adams, John 01/15/2007 3.2
Is this something that can be done with grouping and sorting?
i am using powershell to get the output..I am planning implement sorting to my output.below is the format I am writing my objects to html file.I want sort the object of lastrunoutocome field.
I have an execution plan that is huge, the pdf it generates if I print it is over 1000 pages. Is there a way to change the graphical plan into a table, so I can sort the %, and find the items that are taking the longest?
INSERT INTO #Activities VALUES('Field phase S14-04932-01') INSERT INTO #Activities VALUES('Phase reporting') INSERT INTO #Activities VALUES('Phase running') INSERT INTO #Activities VALUES('RD1')
They need to be grouped by range specific to the alpha numeric part, which can vary within the same despatch. I was thinking of using a row over partition after splitting the numeric and alpha part and to check if they are consecutive and build the range. But I am thinking that this approach is an overkill and there may be a better way to achieve this in SQL 2012.
I have included the create table scripts and example data below:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SAMPLE_TABLE]( [DESPATCH_ID] [nvarchar](30) NOT NULL, [SAMPLE_ID] [nvarchar](30) NOT NULL
I have a dataset for report builder 3 that has 8 fields. The data for the report needs to be grouped by the first field. The 2nd field is a string that describes the group field and this too needs to be on the same row as the group field. This 2nd field need nearly the full page width. The remaining 6 fields need to end up rendering below the first row. I can't figure out how to get report builder to put the first 2 fields on the first row and use the full page width.
I have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75
The Table:
/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/ IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL DROP TABLE [dbo].[#TestProject2] GO CREATE TABLE [dbo].[#TestProject2]( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Code] ....
The data:
--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT #TestProject2 ON INSERT INTO #TestProject2 ("ID","PersonID", "PercentLoad","MonthID") SELECT 1,123456,0,1 UNION ALL
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. Â When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. Â The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. Â The sorting is implemented using the Category Group sorting option.
We are facing problem in doing page break with column grouping. Our column group contains years e.g 2011, 2013 . We want to show a complete page for a year.Â
Suppose 2011 has 10 records(horizontal) and 2013 has 12 records(horizontal) in column. The output should be 10 records of 2011 in first page, 12 records of 2013 in second page.
We cannot change the report layout to make column to row and vice versa.
USE [Testing] GO /****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Detail #etc WO# -WO Line # --(Details) --Work Order Line Detail #1 --Work Order Line Detail #2 --Work Order Line Detail #3 --Work Order Line Detail #etc --(Parts) --Work Order Line Parts #1 --Work Order Line Parts #2 --Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header Work Order Line Work Order Line Details Work Order Line Requisitions
The Header has a unique PK. The Line uses the Header and a Line # as foreign keys that together are unique. The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No, [Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No FROM [Work Order Header] LEFT OUTER JOIN [Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN [Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN [Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND [Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData ( Id BIGINT, --is actually an identity column Manifest_Id BIGINT, Doc_Num BIGINT, Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X) FROM #InvLogData GROUP BY X HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
I just don't have the skills to work this out...maybe there is an SQL guru out there that can solve this:I can't work out how to do a recursive query so I'm using this function:Public Shared Function FindFriendsFriends(ByVal userID As Integer)Dim iUserID As Integer = userIDDim friendAdapter As New TableAdapters.FriendsTableAdapterDim oFriends As New FrontEnd.FriendsFriendsDataTableDim oTemp As New Data.DataTableDim oContainer As New Data.DataTableoFriends = friendAdapter.GetFriendsByUserID(iUserID)For InLoopCounter As Integer = 0 To oFriends.Count - 1oTemp = friendAdapter.GetFriendsByUserID(oFriends.Rows(0)("UserID"))oContainer.Merge(oTemp)NextReturn oContainerEnd FunctionWith this SQL statement in the table adapter is:SELECT tblFriends.FriendHashID, tblFriends.cCreated, tblFriends.UserOwnerID, tblUsers.UserID, tblUsers.Displayname, tblUsers.EmailAddress, tblFriends.RequestStatus, tblFriends.FriendUserID, tblUsers.ProfilePhoto FROM tblFriends INNER JOIN tblUsers ON tblFriends.FriendUserID = tblUsers.UserID WHERE (tblFriends.UserOwnerID = @UserID) AND (tblFriends.RequestStatus = 2) UNION SELECT tblFriends_1.FriendHashID, tblFriends_1.cCreated, tblFriends_1.UserOwnerID, tblUsers_1.UserID, tblUsers_1.Displayname, tblUsers_1.EmailAddress, tblFriends_1.RequestStatus, tblFriends_1.FriendUserID, tblUsers_1.ProfilePhoto FROM tblFriends AS tblFriends_1 INNER JOIN tblUsers AS tblUsers_1 ON tblFriends_1.UserOwnerID = tblUsers_1.UserID WHERE (tblFriends_1.RequestStatus = 2) AND (tblFriends_1.FriendUserID = @UserID)I want to replace the SQL statement with a recursive query that I simply pass the UserID to and get rid of the function which is very inefficient!
Ok, here's the problem. I have CA's Unicenter ServiceIT Enterprise Edition 5.0 running on a production box that has NT4 (SP6) and SQL server 2000 running on it.
Just before we continue, this is about backing up the database that ServiceIT connects to.
I know that you can schedule EM to make backup's of a specific database over a certain time, but this is part of the problem. What i have since discovered is that ServiceIT will not run on a database backed up and restored by EM.
It will however run on the restored backup created through the dos command pdm_backup (comes with ServiceIT).
What i am trying to figure out is to try a find a way that i can run a dos prompt command to do the following tasks at a specific time of day:
Stop the Paradigm Server Daemons (service) Run a DBCC CHECKDB on the database AHDTEST50. If there are no errors, create a verbose backup using the dos command pdm_backup -v -fC:ackup[todaysdate] If there are errors run a script to correct them, then run a backup Restart the Paradigm Server Daemons
I wrote a stored procedure that searches for user on my table depending on the search option chosen by the person doing the search. But my challenge is displaying users without a picture. On my members table, i used -1 to indicate a user that doesn't have or set his/her picture, this makes it difficult for me to track that person when the person searching chooses to display members with or without pictures. What happen is that my inner join of the photos table dat reference the user primarypictureid does not exist in the photos table. How do i overcome this challenge. My script
IF (@withpicture = 'True') SET @withpicture = '%[0-9][^-]'
IF(@countryId = '-1') SET @countryid = '%'
BEGIN SELECT m.UserName, m.MemberId, m.Gender, m.PrimaryPictureId, DATEDIFF(year,m.BirthDate,GETDATE()) AS 'Age', c.CountryName, p.PicFileName FROM Members AS m INNER JOIN Photos AS p ON m.PrimaryPictureId = p.PictureId INNER JOIN Countries AS c ON m.CountryId = c.CountryId WHERE (m.Gender = @searchfor) AND (DATEDIFF(year,m.BirthDate,GETDATE()) BETWEEN @firstage AND @secondage) AND (m.CountryId LIKE @countryid) AND (p.PictureId LIKE @withpicture) AND (m.UserName <> @username) ORDER BY m.BirthDate ASC END
I have a challenge for all DBAs. I am getting INCONSISTENT TIMINGS for Querry Results from a SQL Server 2000 Standard Edition on a HP Proliant 2 CPU Server with 4 GB RAM and SCSI DrivAFAe. Our Database is 72 million records and have 8 columns. Most of them are indexed, which are used in the “Where…� clause. In addition to independent indexes, we also have a covering index for 3 most frequently searched fields. My challenge is that out of the 7000 odd queries that hit the server with various search conditions in a Week, 5% of the queries return result in less than a minute. The same query at a different Time or with a different Value, returns results inconsistently. For e.g. searching FirstName = “Anna�; LastName = “Williams� returns result in 0.01 sec. Searching for FirstName “Benjamin�; LastName = “Watson� returns in 5 minutes. Any kind of help is welcome and will be highly appreciated. - Santy san.rely@gmail.comNote: Edited to fix white on white font.
I have a query that I am trying to optimize. It works on some 9000 records and runs too slow. What the query does is takes the multiple assignment of a single contact record to multiple attributes (a.k.a many-to-many). For example:
Membership table. Contact_ID 1 2 3
Relate table 1 1 1 3 1 4
Relate Item 1 item1 2 item2 3 item3 4 item4
The query will take all ocurrences of the related items and place them in a single field while delimiting by comma "item1" , "item3", "item4"
Here is the query as it exists now:
select CONTACT_ID, UNION_NAME into #tmp from MEM_UN MU inner join MEM_UN_REL MUR on MU.UNION_ID = MUR.UNION_ID order by CONTACT_ID, UNION_NAME
insert into #unionlist (CONTACT_ID, UNIONS) select distinct CONTACT_ID, UNIONS = '' from MEMBERSHIP
while exists(select CONTACT_ID from #tmp) BEGIN update #unionlist set UNIONS = UNIONS + '"' + ( select min(UNION_NAME) from #tmp where #unionlist.CONTACT_ID = #tmp.CONTACT_ID ) + '",' where CONTACT_ID in (select CONTACT_ID from #tmp)
update #unionlist set UNIONS = UNIONS + '"",' where CONTACT_ID not in (select CONTACT_ID FROM #tmp)
delete FROM #tmp where UNION_NAME in ( select min(UNION_NAME) from #tmp tmp2 where #tmp.CONTACT_ID = tmp2.CONTACT_ID ) END
I believe that the slow down is in the process of deleting from #tmp every time it loops through the recordset.
On march 23, Micorosoft was supposed to benchmarch their SQL server 7.0 versus Oracle 8i. I didn't watch the benchmark... I figured that I would be able to read about it in the news. But it seems like there is some moritorium on the subject. I haven't found any result information whatsoever.
Does anybody know who won the challenge? Did microsoft win $1 million from oracle?
Tom Mack, MCSE Database Administrator Advancia Corporation
We're having to work with some legacy data. The tables in the so-called database seem to have way more nulls than actual data. One table appears to have around 100 or more columns in it. It has close to 40,000 rows.
This Db has pretty much 0 normalization present.
IOW, your worst nightmare.
Is there a way we could run a query that would return the total number cells inthat contain NULL and another that could return the total number of data-bearing cells so we could come up with a % or a ratio.
Can anyone tell how I can parse the WHERE clause of an SQL statement to check for special characters such as ''' (single quotes) in fields of type varchar?
Well i wanted to prove to some guys that cursors are not really that important:shocked: . :D So this code is suppose to remove duplicate tuples from a table without temporary tables or cursors:D. Except it needs some optimization(and alot of system down time, not sure about that:confused: ). I would like it, if some one could find an instance of the table when the below code fails or some way to optimize the code or anything;) .
--trashtable for real data create table abc (col1 tinyint, col2 tinyint, col3 tinyint)
--trash values for trash table insert into abc values (1,1,1) insert into abc values (1,1,1) insert into abc values (1,1,1) insert into abc values (1,1,1) insert into abc values (2,2,2) insert into abc values (2,2,2) insert into abc values (2,2,2) insert into abc values (3,2,1) insert into abc values (2,2,3) insert into abc values (3,2,4)
--check that there are ten rows select * from abc --check that there are only five distinct rows select distinct * from abc
--run code : next 15 line as a batch declare @lp tinyint declare @col1 tinyint,@col2 tinyint,@col3 tinyint set @lp=1 while @lp>0 begin if not exists (select top 1 * from abc group by col1,col2,col3 having count(col1)>1) set @lp=0 else begin select top 1 @col1 = col1,@col2 = col2,@col3 = col3 from abc group by col1,col2,col3 having count(col1)>1 delete from abc where col1=@col1 and col2=@col2 and col3=@col3 insert into abc values(@col1,@col2,@col3) end end
--only distinct values left in trash table select * from abc
--think code can be optimized --just wanted to prove: can be done without cursors or temporary tables
I have a database with three tables: Employees, Assets, and Recovery.
I have the following SELECT statement:
SELECT Employees.EmployeeID, SUM(Assets.Amount) AS [Case Value:], SUM(Recovery.Recovery) AS [Recovery:] FROM Assets INNER JOIN Employees ON Assets.EmployeeID = Employees.EmployeeID INNER JOIN Recovery ON Employees.EmployeeID = Recovery.EmployeeID GROUP BY Employees.EmployeeID
Here is the challenge:
You will always have data in the Employee table, but not neccesarily in the Assets or Recovery table.
If there are no Assets or Recovery I would still like the query to show 0.00 for the Assets or Recovery for each Employees.EmployeesID
With the above SELECT statement it will only return values that have data created in each of the three tables.
How do you modify the SELECT statement so a row is returned for every Employees.EmployeeID?
Here is the table:CREATE TABLE [child]([pk_child_id] [int] NOT NULL ,[fk_parent_id] [int] NOT NULL ,[code] [char] (2)NOT NULL ,[dt] [datetime] NOT NULL ,[newcode] [int] NULL)There is a situation where there will be more than one record with thesame [fk_parent_id] value, but different values for the [code]field.If one of those records has a [code]= 5, but the [dt] is AFTER asimilar record where [code]= 6 or [code]= 7 (but same [fk_parent_id]value), I need to set [newcode] = 10. How can I pull this off? Again,the group of records can have different [code] values, different [dt]values, but a common [fk_parent_id].Help!
This code is attempting to find records that have a RegJrnID that doesnot occur more than one time in the table.The reason that I want to find records with non-duplicated RegJrnIDvalues is to create "reversal" records for these such that the reversalrecord has identical values for every column except the TaxableAmountwhich will contain a negative amount. (see: example data below)./* Set up */CREATE TABLE t1(RegJrnID INTEGER, InvoiceDate VARCHAR(8), InvoiceNumberVARCHAR(20), TaxableAmount DECIMAL(32,8))/* Example data */INSERT INTO t1 VALUES (1, '20060101', '2321323', 100.00)INSERT INTO t1 VALUES (9, '20060213', '2130009', 40.01)INSERT INTO t1 VALUES (3, '20060101', '9402293', 512.44)INSERT INTO t1 VALUES (1, '20060104', '2321323', -100.00)INSERT INTO t1 VALUES (4, '20060105', '9302221', 612.12)INSERT INTO t1 VALUES (5, '20060105', '0003235', 18.11)INSERT INTO t1 VALUES (6, '20060111', '5953432', 2101.21)INSERT INTO t1 VALUES (3, '20060111', '9402293', -512.44)INSERT INTO t1 VALUES (7, '20060115', '4234444', 44.52)INSERT INTO t1 VALUES (8, '20060115', '0342222', 95.21)INSERT INTO t1 VALUES (6, '20060119', '5953432', -2101.21)INSERT INTO t1 VALUES (2, '20060101', '5440033', 231.01)/* Show what's in the table - just because */SELECT * FROM t1 ORDER BY RegJrnID, InvoiceDate/* Query for records to reverse */SELECT *FROM t1 a/* Ignore records that have already been reversed */WHERE a.RegJrnID != ALL/* This subselect finds reversed records (i.e. those that have aduplicate RegJrnID) */(SELECT b.RegJrnIDFROM t1 bGROUP BY b.RegJrnIDHAVING COUNT(*) > 1)/* User selection criteria are appended here *//* AND InvoiceNumber >= '5000000' AND InvoiceNumber <= '7500000' *//* Make the results look pretty (optional) */ORDER BY RegJrnID/* Housekeeping */DROP TABLE t1
