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 was working on what I was told was SQL 2012 and it turns out it is SQL 2005. I wrote two procs that I need to convert to 2005. Here is the code:
SELECT era_provider_name AS Provider, RIGHT([era_upi], 5) AS 'ERA Upi', [era_fy] AS 'ERA FY', ProcGrp, COUNT(DISTINCT UCI) AS 'Client Count',
[Code]....
I'm not finding an efficient way to do this. I cannot use GROUPING SETS with 2005. Here is the code for the second proc:
SELECT CASE WHEN GROUPING(era_provider_name) = 1 THEN 'TOTALS' ELSE era_provider_name END AS era_provider_name, CASE WHEN GROUPING(era_fy) = 1 THEN 'TOTALFY'
[Code] ...
The results as in SQL 2012 are exactly as I would like them. I want to mimic those results in 2005.
Hi folks. Hope all the gurus including Brett,Pat Phalen, RdjBarov, r937 are fine. ;) been so long to ask stupid question. Here's the question and i really need help on this.
i have data that tracks patterns of bus stops from one point to another. like point a, to point b, point b to point c forms one pattern. point a to point c , point c to point b should be a different pattern.
create table #journeypatterns (patternid int ,points varchar(100)) go insert #journeypatterns select 1 ,'a' union all select 1 ,'b' union all select 1,'c' union all select 2,'a' union all select 2,'c' union all select 2,'b' union all select 3 ,'a' union all select 3 ,'b' union all select 3,'c'
select * from #journeypatterns
patternid points 1 a 1 b 1 c 2 a 2 c 2 b 3 a 3 b 3 c
what i want is to get unique pattern value of sequence of points by grouping on patternid. if the sequence of points change, i need a unique value against that pattern. like for patternid 1, sequence of points a,bc for example should be abc. for patternid 2, sequence of points a,c,b for example should be acb. again patternid 3, sequence of points a,bc for example should be abc.
i tried CHECKSUM_AGG which brutally failed in production because the checksum values for each single point when summed produce SAME result for different patterns.
select checksum_agg(binary_checksum(points)) ,patternid from #journeypatterns group by patternid
961 962 963
here patternid 2 should be different because sequence is acb. i know checksum is not the right approach for what i need.
I NEED A GENERIC FUNCTION, that marks the pattern differences, my ultimate goal was to create a procedure, whom a patternid should be passed, and it would result the NEXT patternid in the table which has the SAME ORDER OF point sequences.
now folks, i can do this holding all data into a temp table and write a cursor to traverse through each patternid and concatenate the sequence of points. BUT, using this approach is the ugliest, as it has slow down the process badly and boss is not happy with the performance. the table holds a lot of data. I NEED a query rather than a cursor on the fly to resolve this. Here's the query i am using to get the current sequence of a pattern and then i have to search all sequences similarly against it.
declare @patternid int set @patternid =1 declare @sequence [varchar] (100) declare @id varchar(10) declare cr_sequence cursor fast_forward for select points from #journeypatterns where patternid=@patternid open cr_sequence fetch next from cr_sequence into @id while @@fetch_status = 0 begin select @sequence = isnull(@sequence,'')+@id fetch next from cr_sequence into @id end print @sequence -- next i have code to find the similar sequence for another patternid.... which is not mentioned here but is similar
How to writing this query using rollup or grouping sets in two way?
data shop time date sku discount% sales qty amount A 13:00pm, 2014-feb-11 apple 30% 5 #20 A 13:00pm, 2014-feb-11 apple 30% 5 #20 A Shop Qty :10 Amt:40 B 23:00pm, 2014-feb-11 apple 30% 5 #20 B 23:00pm, 2014-feb-11 apple 30% 5 #20 B shop Qty :10 Amt $40 Grand total qty:20 , Amt $80
How do you incorporate a case statement so that you can add "sub total" and grand total" to each grouping set section? Trying to see how to incorporate case.
[URL] ....
SELECT CustomerID, SalesPersonID, YEAR(OrderDate) AS 'OrderYear', SUM(TotalDue) AS 'TotalDue' FROM Sales.SalesOrderHeader
i have these 2 queries with the included results sets...What commands could I use to take the TotalBlueCircle Column from the 2nd Results Set and have it included next to the TotalRugby column in the 1st results set??Do i need to do a UNION or use Sub Queries or something else?
Code: SELECT DISTINCT LEFT([REPORTING_MONTH], 4)+'-'+SUBSTRING([REPORTING_MONTH],5,6) as REPORTING_MONTH, t.EMPLOYEE, '' as COUNT_FTP, CASE WHEN [MEDIUM_RCVD] = 'EMAIL' THEN COUNT(MEDIUM_RCVD) ELSE '' END AS COUNT_EMAIL FROM [GPO].[dbo].[DW_SUBMISSION] as s JOIN #TEMPActivity as t
[Code] ....
I'm trying to get the set to come out all on one line. REPORTING_MONTH, EMPLOYEE, COUNT_FTP, COUNT_EMAIL
But when I try null or '' it creates a second record and doesn't merge the two results.
We are trying to limit are query that returns items from our database. The query currently returns 32,000 records. We are trying to figure out an effecient way so we can request the 1st 50, or the 3rd 50, or the 5th 50 to display to the screen. We dont want to return the entire 32,000 then limit whats displayed to the screen in ADO. We want the select statment to only return 50 at a time. Any suggestions?
Dear All,This is a query surrounding a problem I encounteredyesterday.In SQL Server, it is possible to write a procedure thathas one or more select statements in it.The results from these select statements will all beindividually returned to SQL Query Analyser where theycan be viewed in "grid" views. Also, these individualresults sets can be consumed by eg ADO.NET by steppingthrough each results set in turn and processing therespective results.My question is, can you do the same in a SQL Serverprocedure? ie:Create Procedure Proc1ASbeginselect Col1, COl2from Table1select Col1, Col2, Col3from Table2endCreate Procedure Proc2ASbeginexec Proc1endCan both/either of the results sets from Proc1 beconsumed by the calling procedure Proc2?I can see that you could design the procedures up-front to do almost anything without consuming theresult sets in this way, but if the proceduresreturning the results sets are already built andin use in other places (for instance in client code),can they be re-used on server-side SQL procedures?Thanks in anticipation!Paul.
I have two SQL queries that we would like to automate. Ideally we want them to both be scheduled to run and dump their results to a single Excel spreadsheet with two workbooks, one for each query
Is it possible to do this? If not, sending each query to a seperate XLS or CSV file would be OK
Here are the queries:
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, p21_view_unvouched_po_currency_report.po_line_number, po_line.created_by, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home FROM P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report, P21.dbo.po_line po_line WHERE po_line.po_no = p21_view_unvouched_po_currency_report.po_no AND po_line.line_no = p21_view_unvouched_po_currency_report.po_line_number
I have a report based on our product names that consists of two parts.Both insert data into a temporary table.1. A single grouped set of results based on all products2. Multiple tables based on individual product names.I am getting data by calling the same stored procedure multipletimes... for the single set of data I use "product like '%'"To get the data for individual products, I am using a cursor to parsethe product list.It's working great except that I have no idea how to identify theresults short of including a column with the product name. While thatis fine, I'm wondering if there is something that is like a header ortitle that I could insert prior to generating the data that would looka little tighter.Thanks in advance-DanielleJoin Bytes!
I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18 2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I have attached the scripts also, the line i described in step1 is
Hello,I have a very simple problem which I will illustrate with an example:I have the following records in my table:A 1 CA 2 CA 3 CB 8 KB 9 KI now want to group them and the result has to be:A 1,2,3 CB 8,9 KSo the results in the second row have to be concatenated. I guessthere is no function to do this... What is the simplest solution?Kind regards,Bart Warnez
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_]
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100 SELECT * FROM Customer WHERE CountyId IN ( SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId) THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist. My current SQL statement is as follows. SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))" So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated. Thanks, James.
Hi All, I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
I'm new to MDX and was hoping someone could answer this question. I'm working on an MDX query, a simplified version is below.
What I'm trying to do is to pull 2 sets, based on a date range. Based on those, I'd like to see the total "cost" measure for each set, and divide one by another.
What I'm finding is that when I run the query below, the cost is broken out by month. So, my select statement gives me the cost for January 2008 and February 2008 in the CurrentPeriod set, and for January, February, etc. 2007 in the PriorPeriodData set. What I want instead is to get the total "cost" measure for all the months in each set. I believe this is what I'd get if I put the date range in a subcube or a where clause - but in that case, I wouldn't be working with 2 distinct sets.
Is there some other way to write this, or to combine all the months in the set before the measures?
PLEASE SEE my update (post #3) for a better simpler explanation!!!!!
Hi, I need a query which is basically
(query 1) Except (query 2)
SQL server supports (query 1)Union (query 2) but I can't get the Except to work.
Alternatively, I also tried to implement it by using the "NOT IN" but didnt work. Background: 3 tables, accnts, Opty, Opty_postn I want the accounts who have 1 or more opty's at 100% (sold) before 1/1/2002 but zero opty's at 100% after 12/31/2001. accnts has fields ID & name, ROW_ID opty has fields acct_id, prob, ROW_ID opty_postn has fields opty_id, close_dt
My query: SELECT accnt.name FROM accnt WHERE accnt.ROW_ID IN ( SELECT acct_id FROM opty where prob = 100 AND opty.ROW_ID IN (SELECT opty_id FROM opty_postn WHERE close_dt<1/1/2002) ) AND accnt.ROW_ID NOT IN ( SELECT acct_id FROM opty where prob = 100 AND opty.ROW_ID IN (SELECT opty_id FROM opty_postn WHERE close_dt >12/31/2001) ) AND accnt.ROW_ID = opty.accnt_ID
my intent was select account.names where id is in set(prob = 100 & sale before 1/1/02) but not in ( prob = 100 & sale after 21/31/01) SO ,the accounts which have sales both before and after get counted. But I want only those accoutns which have sales only before 1/1/02. I hope i explained this right. Thanks in advance for your help.
Hi,I've tried without success to create a function where one of theparameters determines the final line of a select statement.The final line includes a 'where referencenumber in (@set)' line.. and@set is the parameter I wish to use which is a string.If the set is just one value, it works fine, however with anything morethan that I just get no records returned.. If I output the parameter,and copy and paste that into query analyser I do get the correct result,so it's nothing to do with string delimiters etc..Any ideas to fix this problem? Or am I going to have to dynamic sql thewhole thing?Cheers,Chris
I need to manipulate some sets, doing joins based on intersection, and looking for subsets. Is there any way to store an array in a single data field? And then do operations based on those array lists? e.g. -
{1,2,3} intersect {2,3,4} ==> {2,4} (would like to then be able to do joins based on intersection, so that I could say, return all cases where two sets have all but one member from each in common...)
Hi, I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number(). I would like to give my users to option of removing individual people from this list but cannot find a way to do this. I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message. Is there any way to do this? I am still new to stored procedures so any advice would be helpful. Thanks
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
Hi, I'm currently trying to retrieve results from a large dataset, there are over 45000 records and I need to use them all to peform counts etc. I have set up views, but my page is still being returned slowly, is there anything I can do to speed this up? Thanks Gemma
I am trying to query one table and get two different timeperiods of data, I am summing monthly totals to provide a running year total, but I also need last month's total in a seperate column. This is what I have so far but the subquery makes me group it which provides duplicate grouping.DECLARE @LASTPD AS INT SET @LASTPD = (SELECT MAX(LASTPERIOD) FROM TABLE) SELECT NAME, POST_PD AS [MONTH],SUM(CHARGE_AMOUNT) AS MONTHLY_$, LASTMONTH.LAST_MONTH,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH FROM TABLE INNER JOIN TABLE2 ON TABLE2.NAME = TABLE.NAME WHERE POST_PD = @LASTPD AND TABLE2.NUM= 539 GROUP BY NAME) AS LASTMONTH INTO #TEMP_SAFROM TABLE INNER JOIN TABLE2 ON TABLE2.NAME = TABLE.NAME,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH FROM TABLEWHERE TABLE2.NUM = 539 GROUP BY NAME, POST_PDORDER BY NAME, POST_PD SELECT NAME, LAST_MONTH, CAST(SUM(MONTHLY_$)AS DECIMAL(20,2)) AS YEARLY_$ FROM #TEMP_SA GROUP BY NAME ORDER BY NAME
