Grouping By Unrelated Field- SQL Masters, Try This!
Jul 20, 2005
I would like to retrieve 10(dynamic) records of table x (proucts) for
each user in table y (users). Can this be done?
I would like the end result to be something like this: (would this be
a union?)
__________________________
y.name | x.pid | x.pname
Bob | 1 | fork
Bob | 2 | spoon
.... | |
Bob | 10 | potato
Jeff | 11 | pen
etc.....
__________________________
But also with the number to return based off of a query, ex-
select @pcount = count(products)
select @ucount = count(users)
select @pcount / @ucount
10
And lump all this in an Stored procedure
ex-
get number of total records in x, divide by total y = z
select z records for each user in y.
You would be a master in my book if you can give me hints on this one!
Thanks,
Jeff
View 1 Replies
ADVERTISEMENT
Apr 2, 2008
Hi all experters,
Please suggest how to build the report in below case:
Raw data:
ID
Member
Functions
1
Alan
A
1
Alan
B
2
Tom
A
2
Tom
B
2
Tom
C
3
Mary
D
3
Mary
E
Report Shows:
ID
Member
Functions
1
Alan
A,B
2
Tom
A,B,C
3
Mary
D,E
I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.
Any good suggestion?
Thanks in advance,
Steve Wang
2008/4/2
View 6 Replies
View Related
Jun 4, 2004
In a recent article <<Don' Get Left Behind>> written by Brian Knig(http://www.sqlservercentral.com/columnists/bknight/dontgetleftbehind.asp
), he presented the following 3 key tips to keep up with the SQL server's evolution.
"Here are a few tips (in order of importance) to becoming a hybrid:
Learn XML - If there is one technology that a DBA must know in the next few years it is XML. SQL Server and other products of all kinds are integrating XML into their system. Not being able to speak this language would be equivalent of not understanding SQL in the next few years.
Learn CLR - A hybrid DBA will be expected to know at least one other programming language. These languages will be tightly woven into stored procedures in Yukon and much of SQL Server will go untapped if you're not up on these.
Learn DTS - The minute you cut a 2-week project into 2 hours by creating a DTS package, your stock rises. Developers will think of you as a peer, not a road block."
What are you SQL knowledgeBase people think about it? Personally, I have not touched XML, is learning CLR and only the simplest DTS without global vaiables. Most of my daily jobs are t/sql developement, though the part I enjoying the most is the data modeling.
------------------------------
XML: still don't see it as important as SQL. And where I am going to use it in a 100% windows environment?
CLR: I begin to learning VB.NET and ASP.NET so that to have some front-end tools and WEB presentation on 100 servers and 1500 databases size, activity, job running result, index-defrag... But how could I handle the coming C#/C++ code in the coming SQL2005 as whole or part of the stored proc if I don't know nothing about C#/C++? Probably I have no way to learn them too....
DTS: have to dig in deeper to dynamic level. Right now, use dynamic t/sql to cross 100 servers to get all neccessary info.
Am I in the right direction for the coming wave?
thanks
-D
View 3 Replies
View Related
Jan 17, 2002
I'm adding up quantities of an item that are entered in one after another, so the date is the same, but the time differs.
I used the "first" function in access which works the way I want, but sql7 doesn't use the same function. The "convert" function does not work in access but Here is the gist of the query:
SELECT first(CONVERT(varchar,transactions.tran_date,101)) AS [trandate], transactions.tran_type,
SUM(transactions.qty) AS totqty,...etc.
thanx,
Herb
View 1 Replies
View Related
Jul 20, 2005
All,Given multiple records with identical values in all fields except asingle varchar field, is there an efficient query that will group therecords into a single record and concatenate the aforementionedvarchar field into a single field with each of the source records'values separated by commas?Example:Record 1 'Doug' , '1'Record 2 'Doug' , '2'Output record 'Doug' , '1,2'Thanks in advance,Doug
View 2 Replies
View Related
Jun 15, 2007
Hello,
I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?
Code Snippet
left(Fields!TestName.Value,len(Fields!TestName.Value)-11))
View 2 Replies
View Related
Jun 16, 2007
In the "tblEmailGroupLink" table...
I need to find all records with the "UnSubscribed" field having a "True" value. All these records will have a corresponding "Emailid" field.
In the "tblEmailAddress" table...
The same "Emailid" field has a corresponding "EmailAddress" field.
What needed is all the email addresses found in the "EmailAdddress" field of these records.
I'm very new at this so I hope I explained this right. I'd really appreciate any help I can get.
Thanks,
Bill
View 2 Replies
View Related
Mar 26, 2008
I want to group by a fields value in a table. I have a purchase table where contracts and orders are stored in together. Now what I need is, I have to group only by the rows that contain an identical contract_no and order_no as those records represent the actual contract. the other rows contain order data-sets which need to be subtracted from the contracts values.
datasource:
contract_no order_no name amount
12 12 contract1 1,000,000
12 215 order215 50,000
12 460 order460 75,000
280 280 contract2 500,000
280 340 order340 10,000
280 410 order410 9,000
I want to display each order that took place via drilldown/hide function in the table, and calculate the amount from the contract that is left.
desired result report:
12 contract1 1,000,000
order 215 50,000
order 460 75,000
amount left 875,000
View 1 Replies
View Related
Jun 15, 2015
I am creating a report that shows orders created by our employees but I am having trouble with one employee. She changed her last name this year and so in the report, it shows a line her with her maiden name and one with her new name.
Is there a way to combine all the data from both names into one name so it appears on one line and looks like a single person instead of two.
View 4 Replies
View Related
Jan 9, 2008
Is there a way in Reporting Services layout mode to get the report to display more than one response for one field in the same cell?
More specifically, I have a list of classes that lists section, title, and instructors. If the class has more than one instructor, I want the instructor cell to list both instructors in one cell, separated by a semi-colon, rather than listing all the class information - along with instructor - in two separate rows. Any suggestions?
something like this:
Section
Title
Instructors
15
Class 1
Jones
16
Class 2
Jones; Smith
View 5 Replies
View Related
Mar 7, 2006
We're getting duplicate records b/c we're joining two tables that really don't have a unique common field. Does anyone know of a way to join two tables that aren't completely related?? I know this is contradictory to the entire concept of a relational db :).
View 4 Replies
View Related
Oct 26, 2004
Hi
I have similar problem and i explained in detail here http://www.balainfo.com/forums/viewtopic.php?t=39
Pls help me to fix this sql
Regds
Bala
View 1 Replies
View Related
Sep 8, 2006
Hi,
I have two tbls (trade & retail) - at the minute I have a query (in just one tbl) which looks like this
SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek, YEAR(OrderDate) AS SalesYear
FROM dbo.Orders_Retail
WHERE (account = @Account) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND (OrderStatus <> 'Refunded') AND (DATEDIFF(d, OrderDate,
@StartDate) <= 0) AND (DATEDIFF(d, OrderDate, @EndDate) >= 0)
GROUP BY YEAR(OrderDate), DATEPART(wk, OrderDate)
ORDER BY YEAR(OrderDate), DATEPART(wk, OrderDate)
I then have the same query (bar a couple of differences) for the trade tbl which looks like this:
SELECT SUM(total) AS WeekTradeTotal, COUNT(*) AS MonthTradeOrderNo, DATEPART(wk, order_date) AS SalesWeek, YEAR(order_date) AS SalesYear
FROM dbo.Orders_Trade
WHERE (order_status <> 'Deleted') AND (account = @Account) AND (order_status <> 'Removed') AND (order_status <> 'Refunded') AND
(TradeCustomerID <> 'Cha928') AND (DATEDIFF(d, order_date, @StartDate) <= 0) AND (DATEDIFF(d, order_date, @EndDate) >= 0)
GROUP BY YEAR(order_date), DATEPART(wk, order_date)
ORDER BY YEAR(order_date), DATEPART(wk, order_date)
Now what I want to do is make one query which has the same WHERE expression(s) but for both tbls at once.
Because the two tbls have a couple of different names in their fields (I.e the retail tbl identifier is called CustomerID and the Trade tbl identifier is called TradeCustomerID). I thought the best bet would be to first dump the data into a temp tbl giving new fields names so that they all match, and then run the query.
But I haven't got a clue how to write a temp tbl SP, nor how to clean it up afterwards as we are talking a whole lot of data...
Any help anyone?
View 6 Replies
View Related
Apr 23, 2008
Hello,
I'm struggling with a query that needs to use Not Exists between two unrelated tables. I'm not sure of the best way to handle this with SQL Query Analyzer.
I have three tables, employees, courses and training_records. There is no link between employees and courses other than through the training records table. My problem is that I am unsure of how to pull all employees and an associated course id and course title when there is not a training record for that employee/course?
I had a query that would work when I specified the course id, but I wanted to do this for a list of about 25 courses.
SELECT e.empnumber, e.Nickname + ' ' + e.lastname as employeeName
FROM empdata e
WHERE NOT EXISTS (SELECT * FROM #TrainingRecords tr
LEFT JOIN #TrainingCourses tc
ON tc.ClassID = tr.classid
WHERE tc.ClassID = 1377
AND tr.employee = e.empnumber)
I then modified it to get the classid and name
SELECT e.empnumber, e.Nickname + ' ' + e.lastname as employeeName, tc.ClassID, tc.ClassName
FROM empdata e
LEFT JOIN #TrainingCourses tc
ON tc.ClassID = 1377
WHERE NOT EXISTS (SELECT * FROM #TrainingRecords tr
WHERE tr.ClassID = tc.ClassID
AND tr.employee = e.empnumber)
I then got it to work using the full list
SELECT e.empnumber, e.Nickname + ' ' + e.lastname as employeeName, tc.ClassID, tc.ClassName
FROM empdata e
LEFT JOIN #TrainingCourses tc
ON tc.ClassID in (1032, 1054, 1059, 1060, 1062, 1063, 1069, 1072, 1074, 1075, 1122, 1189, 1190, 1191, 1192, 1193, 1210, 1218, 1219, 1220, 1310, 1377, 1411)
WHERE NOT EXISTS (SELECT * FROM #TrainingRecords tr
WHERE tr.ClassID = tc.ClassID
AND tr.employee = e.empnumber)
My results seem to work like I want them to, but I have never joined a table without actually joining on a value from each table. In this case I join to a list of values and that just looks odd to me. Is this good or crappy development? Is there a better way this should be done?
View 6 Replies
View Related
Sep 6, 2005
Hi all,I'm regularly getting the "deadlocked..you're the victim" message when twothreads work on a table at the same time via JDBC. The two threads don'tupdate the same records. I suspect the cause is related to index or pagelocks, and/or the fact that the UPDATE statements are doing table scansbecause there's no index on the primary key (no, I'm not the DBA!)1. Thread A selects some records to play with:select top 50I.*,M.this,F.thatfrom APInvoice Iinner join M ...inner join F ...where I.CmStatus = 'O'(There is an index on CmStatus + another column called CmTime, so I expectthis index to be used. It's also the only index on the table.)2. Gradually, within the same transaction that did the SELECT, thread Aupdates all 50 selected records:update APInvoiceset CmStatus = 'S',CmTime = getdate()where itemid = (an ID number from the select in step#1).(There is no index on itemid, which is the unique primary key column)(There is an index on CmStatus + CmTime).3. While step#2 is running, thread B starts and does a single update.That's all that thread B does:update APInvoiceset CmStatus = 'C',CmTime = getdate()where itemid = (an ID number that is NOT in the list of records being usedby thread A)I consistently get deadlocks whenever thread A & B run at the same time.To resolve ths problem, I'm looking at the following actions:1. Create a unique primary index on ITEMID.2. Add an "(UPDLOCK)" or "FOR UPDATE" to the SELECT statement.Are these reasonable things to try? Is there something else I should bedoing?
View 1 Replies
View Related
Mar 12, 2015
I've built a robust looking Calendar dimension but that's easy because its all so well structured. Every date belongs to a particular week and every week belongs to a particular month and every month belongs to a particular quarter etc. January 1st is always going to be a member of week 1, month 1, quarter 1.But here's the rub, real life isn't as predictable and stable as a calendar.
Lets say you have an table containing personal information. You might have gender and marital status in there and even though they both relate to one person, they're unrelated to each other so how do you put those in a hierarchy? Which one goes first and which second because you can get combinations of either.
View 1 Replies
View Related
Nov 4, 2015
I tried using the page below but wasn't able to quite properly adapt it to my scenario
[URL]
I have two UNRELATED (can't be related for reasons outside the scope of this, other relationships already exist) tables.
Each table does, however, contain a WorkerID:
WorkerTimesheets:
WorkerID, HoursLogged, Date
3,10,2015-05-05
3,6,2015-05-10
4,8,2015-05-01
Then the unrelated table "ConstructionSites"
SiteID, SiteManagerID, SiteOpen,SiteClosed
A5, 3,2015-01-01,BLANK
What I want to do is be able to show a measure that SUMS the number of hours logged by anyone who is a SiteManager from the ConstructionSites table.
I wanted to do a SUMX of WorkerTimesheets against HoursLogged, but FILTER against WorkerTimesheets[WorkerID] = ConstructionSites[SiteManagerID] so only workers who are also SiteManager would be counted.However, I can't seem to get that to resolve it always throws an error along the lines that it can't determine context.
View 3 Replies
View Related
Jun 25, 2007
Hi,I have a question regarding best practices in database design. In arelational database, is it wise/necessary to sometimes create tablesthat are not related to other tables through a foreign Keyrelationship or does this always indicate some sort of underlyingdesign flaw. Something that requires a re evaluation of the problemdomain?The reason I ask is because in our application, the user can perform xnumber of high level operations (creating/updating projects, creating/answering surveys etc. etc.). Different users can perform differentoperations and each operation can manipulate one or more table. Thispart of the system is done and working. Now there is a requirement tohave some sort of audit logging inside the database (separate from thetext based log file that the application generates anyway). This"audit logging" table will contain high level events that occur insidethe application (which may or may not relate to a particularoperation). This table is in some sense related to every other tablein the database, as well as data that is not in the database itself(exceptions, external events etc.). For example : it might haveentries that specify that at time x user created project y, at time Auser filled out survey B, at time C LDAP server was down, At time D anunauthorized login attempt occurred etc.As I said, these seems to suggest a stand alone, floating table with afew fields that store entries regarding whats going on the systemwithout any direct relationship to other tables in the database. But Ijust feel uneasy about creating such an isolated table. Another optionis to store the "logging" information in another schema/database, butthat doubles the maintainance work load. Not really looking forward tomaintaining/designing two different schemas.I had a look at the microsoft adventureworks database schema diagramand they seem to have 3 standalong tables - AWBuildVersion, ErrorLogand DatabaseLog (unless i am reading it wrong!)Any advice, Information or resources are much appreciated.
View 12 Replies
View Related
Jul 11, 2013
how to set 'Ignore Unrelated Dimensions' property in Tabular Model.
View 3 Replies
View Related
Feb 11, 2007
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.
Any suggestions would help.
Thanks
View 1 Replies
View Related
Nov 26, 2007
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:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
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_]
View 1 Replies
View Related
Aug 25, 2015
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
View 8 Replies
View Related
May 4, 2006
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
View 3 Replies
View Related
Jul 11, 2005
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies
View Related
Dec 30, 2003
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
View 6 Replies
View Related
May 17, 2015
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
View 3 Replies
View Related
Oct 17, 2007
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
View 1 Replies
View Related
Jul 23, 2005
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
View 2 Replies
View Related
Nov 17, 2012
DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect
View 2 Replies
View Related
Feb 20, 2007
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
View 1 Replies
View Related
Aug 28, 2015
I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
Inserted Value -> Result in table
123456-8 -> 123456-8 *Correct
1234567-9 -> 1234567- *Wrong
123456789 -> 123456789 *Correct
1234567#9 -> 1234567# *Wrong
1234567a9 -> 1234567a9 *Correct
Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?
I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.
View 3 Replies
View Related
Mar 25, 2008
How can I format the background color of a data-region field when I just want it to be "silver" If it is a subtotal value???
View 3 Replies
View Related
Jan 19, 2005
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
View 12 Replies
View Related
