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.
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.
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 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:
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
Periodically throughout the day a report is manually pulled from a SQL Server database. Is their a way w/o me adding a field to the database to have the result set return the "new" results? For example, lets say this is our DDL
Create Table OneTwoThree ( id int ,date11 datetime ,firefly varchar(10) )
Prior I could use this syntax, but that was only with needing to generate 1 result set.
Select id, convert(varchar(10), date11, 101) As [Date], firefly from onetwothree where CONVERT(varchar(10), date11, 101) < CONVERT(varchar(10), GetDate(), 101)
Looking at my datetime values, let's say the 1st was generated at 18:45, obviously the 1st two records in the table would be returned. And let's say a 2nd time I need to generate I want to exclude the 1st two entries as they have already been verified. How can I do such w/o adding a field to the table?
INSERT INTO @GroupRelation_Test ( CustomerNumber, AmountBilled,MinAmounttBilled) SELECT'12','15243','' UNION ALL SELECT'1231234','15243','' UNION ALL SELECT'463','15243','' UNION ALL SELECT'442','15243','' UNION ALL
I have a table with duration values for different machine states. I 'm trying have a sum of the duration value of each state ( the duration sum , was an earlier question).
In the below query, I can get the individual/single group by columns as well as multiple but I cannot control the order in which I would like to group by the data.
So lets say I want to group the data by OS->browser->browser_version(just one example) then I cannot achieve that as the order of OS column comes later in the query.
I know one option would be to write a dynamic SQL but i dont want to do that because of performance reasons. Any other way this can be achieved?
select case when @include_browser = 1 then browser_name end as browser_name, case when @include_browser_version = 1 then browser_version end as browser_version,
TeamStatus T 1 Complete or Escalate T 2 Pick Up T 2 Resolve Case T 1 Pick Up T 1 Complete or Escalate T 1 Pick Up T 1 Complete or Escalate
I want to get he group based of Resolve Case value in Status Column. Anything before Resolve case will be considered as Group 1 and after Resolve Case status should be considered as Group 2. Below is desired new Group column,
Group TeamStatus Group 1 T 1Complete or Escalate T 2 Pick Up T 2 Resolve Case
Group 2 T 1Pick Up T 1Complete or Escalate T 1 Pick Up T 1 Complete or Escalate
SELECT DISTINCT CASE WHEN t2.idNum IS NULL THEN t1.idNum ELSE t2.idNum END, CASE WHEN t2.FreqDt IS NULL THEN T1.FreqDt else t2.FreqDt END, CASE WHEN t2.freq is null then t1.freq else t2.freq end FROM @tmptbl as t1 LEFT JOIN @tmptbl as t2 ON t1.idNum = T2.idNum AND t1.FreqDt = t2.FreqDt AND t1.rn = (t2.rn-1)
After all this, I'm supposed to condense the result set to only include sequential frequency dates with unique frequencies.should look like below (this is where I'm stuck)
I tried to ask a similar question yesterday and got shot down, so I'll try again in a different way. I have been looking online at the gaps and islands approach, and it seems to always be referencing a singular field, so i can't find anything which is clear to get my head around it.In the context of a hotel (people checking in and out) I would like to identify how long someone has been staying at the hotel (The Island?) regardless if they checked out and back in the following day.
Data example: DECLARE @LengthOfStay TABLE ( PersonVARCHAR(8) NOT NULL, CheckInDATE NOT NULL, CheckOutDATE NULL
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'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'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
I need to upgrade a 6.5 box to 7.0 but also change the character sets. Let me know how this sounds.
-Have a 2nd machine w/ 6.5 on it and same char sets. -backup/restore data from prod box to 2nd machine. -upgrade 2nd machine to 7.0 using the same char sets. -uninstall prod machine and install 7.0 w/ new char set. -use dts to move data from 2nd machine to new machine. (changing char sets from old to new)
We're constructing a three-tier application. We want the middle tier to extract a recordset from the database, disconnect from the database, then pass the recordset to the front tier. After changes have been made by the front tier, it will pass the recordset back to the middle tier, which will reconnect, and update the database. The problem: Using stored procedures, the recordset is no longer available once the connection has been closed. Is there any way, using stored procedures, to be able to keep the recordset available after the connection has been closed, either by preserving it, copying it, or otherwise?
they are not necessary mutual exclusive. thus, union is not good. However, when I use derived table method, I will have 'dups' on column of 'Region' and 'county', 2 total are fine.
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?
