I have been tasked with creating a report that shows sales of our products grouped into buckets of 5 each, DESC. I have a table that has the itemNo and revenue. The final report would be something like:
Top 5 Spreads$695,066 Next 5 Spreads$467,845 Next 5 Spreads$416,946 Next 5 Spreads$361,946 Next 5 Spreads$305,607 Next 5 Spreads$270,567 Bottom Spreads$15,954
My initial thinking was to use row_number() and partition to label the rows in groups of 5 like:
ALTER PROCEDURE [dbo].[ADAMHsp_BSS_GetNonMedicaidReportTotals] @pstrProviderNameVARCHAR(100) AS BEGIN
[Code] ....
See my result set in the picture below. The rows with NULL in the 'ProcGrp' column have the totals of the groupings by FY that I am looking for - that's great. What I want to do now is have another row that contains the sums of the values from any row where 'ProcGrp' is null so that I have a totals row.
I have a query where I have customers, date they ordered a swatch, date they ordered an item, and eh date diff between the two. I want to show the MIN date diff for each customer, and also show the swatch date and item date as well. But to use the MIN aggregate, it forces me to group everything, where I just want to group by customer, and have the 2 dates tag along, because i only want one record per customer. What is the easiest way for me to accomplish this?
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 am trying to return rows from a query and I want to display the information from 2 columns into one. Here is my query:
SELECT TOP (100) PERCENT dbo.CASES.CaseID, dbo.CASENUMBERTYPES.CaseNumTypeDesc, dbo.CASENUMBERS.CaseNumber, dbo.SCANNEDDOCUMENTS.TopicID, dbo.EVENTS.EventTypeID, dbo.EVENTS.ev_StartDate, dbo.SCANNEDDOCUMENTS.Doc FROM dbo.SCANNEDDOCUMENTS INNER JOIN dbo.CASES ON dbo.SCANNEDDOCUMENTS.CaseID = dbo.CASES.CaseID INNER JOIN dbo.CASENUMBERS ON dbo.CASES.CaseID = dbo.CASENUMBERS.CaseID INNER JOIN dbo.CASENUMBERTYPES ON dbo.CASENUMBERS.CaseNumTypeID = dbo.CASENUMBERTYPES.CaseNumTypeID INNER JOIN dbo.EVENTS ON dbo.CASES.CaseID = dbo.EVENTS.CaseID WHERE (dbo.CASENUMBERTYPES.CaseNumTypeDesc IN ('cc', 'dc')) AND (dbo.EVENTS.EventTypeID IN (7048, 6467)) AND (dbo.SCANNEDDOCUMENTS.TopicID IN (2, 3, 4)) AND (dbo.EVENTS.ev_StartDate > CONVERT(DATETIME, '2008-01-13 00:00:00', 102)) AND (dbo.EVENTS.ev_StartDate < CONVERT(DATETIME, '2008-01-19 00:00:00', 102)) ORDER BY dbo.CASENUMBERTYPES.CaseNumTypeDesc, dbo.CASENUMBERS.CaseNumber
I get this for my return
1526959 CC 228762 2 7048 1/16/2008 12:00:00 AM <Binary data> CC is my CASENUMBERTYPES and the next column (228762) is CASENUMBERS
How can I get it to return it to where the CASENUMBERTYPES and CASENUMBERS show together in one column?
Hi there, I'm trying to concat the results of a select statement into a single row separated by commas without duplicates. The query and output is something like this:SELECT Specification FROM Cars WHERE Model = 112Which would return something like:Specification------------------Model 1Model 2Model 2Model 4 What I'd like is these to be combined into a single row such as:Specification ------------------Model 1, Model 2, Model 4
I want to use a NEWID() to generate order numbers, but i dont want to give customers the long uniqueID. so im wondering if i concatinate it to 8 characters, if that would be safe or not...
I am trying to concat an int with an nvarchar in my select:
select distinct(id + name) from load where name not like '%test%'
but I am getting an error saying: Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the nvarchar value '灪愭楮敭' to a column of data type int.
My question is: how do you concat int and string in MS SQL Server. The equivalent in Oracle would be ||, I think.
I have a string let's say: "123". I want to convert it to: -- Start SqlServer BEGIN {CALL DBA.applsp_ChangeComments( 'ArtPurchPriceUpdate', 'TRIGGER', 'nl', 'ArtPurchPriceUpdate', '1' + CHAR(13)+ CHAR(10) + '2' + CHAR(13) + CHAR(10) + 3' )} END; -- End SqlServer
This works well for oracle and sybase, for sqlserver I can't concatenate in a parameter of a stored procedure. I really want to do this. To create a variable is a lot of extra programming. Is there anyone who knows how to cancat these strings within the stored procedure call?
Hi there, I'm trying to concat the results of a select statement into a single row separated by commas without duplicates. The query and output is something like this:
SELECT Specification FROM Cars WHERE Model = 112
Which would return something like:
Specification ------------------
Model 1 Model 2 Model 2 Model 4
What I'd like is these to be combined into a single row such as:
SELECT ROW_NUMBER() OVER (ORDER BY BM.BILL_NUMBER_V) AS [SL_NO],PP.KID_ID_NO_V AS [KID_NO],(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME], CONVERT(VARCHAR(10),PP.UPDATEDDATE_D,101)AS [VISIT_DATE],BM.BILL_NUMBER_V AS [BILL_NUMBER],CONVERT(VARCHAR(10),BM.BILL_DATE_D,101) AS [BILL_DATE], ROUND(BM.BILL_AMOUNT_M,2) AS [BILL_AMOUNT],ROUND(BM.CONCESSION_AMOUNT_M,2) AS [CONCESSION_AMOUNT],ROUND(BM.TOTAL_AMOUNT_M,2) AS [TOTAL_AMOUNT],
[Code] .....
In the above query i want to concat symbol '%' for output of percentage column. How to do that?
If I have table1 and table2 with table2 having multiple rows tied to asingle row in table 1.What I am trying to do is set up a view that has one row that showsthe followingtable1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,table2.row3.detailI'd like to be able to do a select on the view and only come back withone row per widget. If possible, I'd actually like to be able toconcat all the rows from table 2 into one column if that's possible.table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail- table2.row3.detail), table1.dateCreatedthxM@
I want to concat strings such that if one string is null, it is treated as an empty string. For example 'abc' + NULL = 'abc'.
SQL Server has the CONCAT_NULL_YIELDS_NULL defaulted to ON, and it needs to be on in most cases when dealing with indices. (INSERT, DELETE, UPDATE, etc.) However, when I am running a select, and one field has a null value, I still want to see the other fields in the resulting string.
I can do this in a function, but I need a view, and the view doesn't seem to let me save a command with 'SET' in it. (However, it does let me run the command! And it works!) It's just when I try to save it, I get a syntax error, so the view is unavailable to me as I want it.
The command that works, but won't save is:
PHP Code:
SET CONCAT_NULL_YIELDS_NULL OFF SELECT t1.field1+'-'+'t2.field2 AS viewField FROM table1 t1 INNER JOIN table2 t2 ON t1.fk1=t2.pk2
I'm wondering how it's possible to have a select statement resultant rows concatenated into one row and column. For example: select letter from alphabet_table a b c d e ... 26 rows returned.
Other than a cursor, how would I write a query to return the following: row1: abcdefghijkl...
SELECT dbo.T1.t1_id, dbo.CONCAT(dbo.T2.product) AS product FROM dbo.T1 INNER JOIN dbo.T2 ON dbo.T1.t1_id = dbo.T2.t1_id GROUP BY dbo.T1.t1_id
but following error is be shown. why? Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CONCAT", or the name is ambiguous.
I have the following table called "tests" : id WeekNbrnametest hours -------------------------------------------------------------------------- 1 2007/26John "testA"5 2 2007/26John "testB"6 3 2007/26David "testA"3 4 2007/28David "testC"2 5 2007/30Victor "testD"1
I want to write a query so that I have as a result one row per person and per week, as followed
This means that I need to concatenate the values of the test column if in the same week and same person. For now, I have only managed to do the job without the test concatenation the following way:
SELECT DISTINCT WeekNbr, name, SUM(hours) as [Total of hours] FROM tests GROUP BY WeekNbr, name
and I get the following: WeekNbrnamehours ------------------------------------------------------- 2007/26John 11 2007/26David 3 2007/28David 2 2007/30Victor 1
I have a procedure (a) where i call another procedure (b) passing @message as a parameter to procedure B.
Ex: Create procedure a as Begin declare @prevyear exec b @message = 'avvasdva' + cast(@prevyear as varchar) End
When i execute above procedure, i get error at + sign i.e. @message parameter. how can i concatenate string with another parameter when passing parameters
Hi All,Here's a challenge.If there is a 'one word' answer to this, i.e. the name of a built inSQL Server function I can use, that would be great! However...I have a standard 1:m relationship, e.g. tblHeader and tblDetail.I want to create a view of records from 'tblHeader' and within thatview have a column (called e.g. DetailTypes) that provides a singlecomma separated varchar of values from a varchar field (called e.g.DetailType) that appears in related records in the 'tblDetail' table.(hope you understood that :)e.g. the contents of my 'tblDetail' table could be....Detail ID | Header ID | DetailType-----------------------------------1 | 1 | A2 | 1 | A3 | 1 | B4 | 2 | A5 | 2 | C6 | 3 | BTherefore, the view I want to create should return:Header ID | DetailTypes-------------------------------1 | 2A, 1B2 | 1A, 1C3 | 1Bi.e. the first row can be read as "Header 1 has 2 'A' detail recordsand 1 'B' detail record."I have created a view e.g. 'vqryHeaders' which calls a user definedfunction that takes the HeaderID and opens a cursor on another view,e.g. 'vqryDetailGrouped'. The other view groups the records intblDetail so that I can get a count of each DetailType for each HeaderID. The cursor then loops through the returned records concatenatingthe count and detail type into a comma separated string (as shownabove).However, when I run this across 20k records it is soooo sloooooww. Ihave indexes on the relationship fields and I am using realisticallysized varchars - neither made any difference in speed. It isdefinately the function that I wrote that slows it down as the view islightning fast when I remove my function call.I can supply source code if necessary, but I think that this is akind-of generic problem so I don't see the point - yet.I really hope you can help.Regards,Jezz
I'm new to SQL and I've been trying this for a while now.
I have let's say a loop of numbers from one to ten. It appears like this:
1 2 3 4 5
What I want to do is to have it appear on one column like this: 12345, the problem is I can't seem to figure out if I need to only have one variable or I'm missing something else, I figured you need to convert them into a string character but I'm still unable to do it.
Hi all. I have been going round and round for the past 2 days on this and would appreciate any help. In a view select statement, I need to concat 2 varchar fields with a text field. If I CONVERT the TEXT field to VARCHAR, only the first 30 characters of the field appear in the result set. If I convert the VARCHAR fields to TEXT, I get an error that I cannot CONCAT TEXT fields. I'm not sure what to do. Can someone please offer some assistance? Thanks in advance! Steve