I've got a bar chart which shows has a month category, incident data value, and a OccuranceDescription series.
So in january I might have three bars, feb one bar, three in march...seems if theres no series data to show then the place where the bar would have been is just blank...so if I have 12 occurances the bars are REALLY thin seperated by nothing but blank space on either side.
Is there a way for it to auto-scale if theres no other series data in that month?
Hello, I have a Chart with one series as values. The chart is good and I can see the series label in the corner. I can hide it by uncheck the "show legend.
Now I added another value to my chart and got another series label.
Is there a way to hide only one label? If I use =" " in the "series label" of that data then the label its self is hidden but the label mark is still visible.
I have a Multivalue parameter with 4 available values and have 4 columns in the report that correspond to each of these values.I apply column visibility to each of the 4 columns with the following expression (the number changes for each column
What I want to do is that if I select a KPI and the Column value is NULL then to hide the row.Obviously if you select multiple KPIs and only 1 of the columns has null value then I wouldn't want the Row hidden.
I encountered a very strange problem again. Why the time series displayed on the chart are so strange? The Key time column I chose for my time series algorithm is cal_month(e.g 199001...), but why the date displayed on the time series chart is like :05/06/2448? (it should be like 199001..?) What is that data? And where exactly did it come from? What is the exact cause of this?
Hope it is clear for your help.
I am really confused on this and thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode 1A 1B 24 28 26 310 312 410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10 - 2 and 11 - 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). Â In some cases, items can be components of another item (called "Kits"). Â In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". Â In our Items table, we have a field called "Parent_Item_Id". Â Records for Child Items contain the Item Id of the parent. Â So a sample of my database would be the following:
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. Â So an example of the report would be the following:
Name | Available Qty -------------------------- Kit A | 15 Kit B | 25 Item 4 | 100
Hi, I have setup a chart with series on right hand side. The series is from one of the fields. How is it possible to remove series and instead place each series name on a graph line? Or at the very least, how can I place a text on each graph line? Thanks
I m using the Time Series Algorithm to forecast sales across regions for various products. Assume the model is built with last 3 years data with the periodicity being monthly.
Is it possible that sometimes I can make predictions based on just 1 yr or 2 yrs data for certain products alone or certain regions alone? Can this be done without having to retrain the already built model?
Also, is it possible that using the model, i can predict week-wise / month-wise / quarterly sales as well?
Hello Forum, I am have posted questions regarding selecting a series of dates and am still having trouble doing so. I have been using vwd to build a website, and now would like to retrieve data from access and display a weeks worth of information at one time from selecting a column of information from the data base. I have used this section of code to do the required function. 'SELECT OrderId, CustomerId, Price FROM Orders WHERE OrderedDate BETWEEN DATEADD(d, -7, GETDATE()) AND GETDATE(), I have replaced these variables with my own, and keep getting an error message 'Undefined function 'GETDATE' in expression', I have tried many different functions to get it to work. Any ideas? Thanks Forum, Tim p.s. thank you todd for replying to my first question!
Hi All, I have a table Test1: ID date Value AAUGVAL 2/27/198760.848 AAUGVAL 3/2/1987 64.288 AAUGVAL 3/3/1987 63.77 AAUGVAL 3/4/1987 62.495 AAUGVAL 3/5/1987 62.65 AAUGVAL 3/6/1987 62.548 AAUGVAL 3/9/1987 62.292 AAUGVAL 3/10/198763.045 AAUGVAL 3/11/198763.021 .... I am trying to see the value % changes day by day and here is is the code I wrote: select starttime=cast(v.date as char(8)), endtime=cast(a.date as char(8)), startval=v.Value, endval=a.Value, change=substring('- +', sign((a.Value-v.Value)+2,1)+ cast(abs(a.Value-v.Value) as varchar) from (select date,Value, ranking =(select count(distinct date) from Test1 T where T.Value<=S.Value) from Test1 S) v left outer join (select date,Value, ranking=(select count(distinct date) from Test1 T where T.Value<=S.Value) from Test1 S) a on( a.ranking=v.ranking+1)
I got the following error message: Server: Msg 174, Level 15, State 1, Line 4 The sign function requires 1 arguments. Server: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near 'v'. Server: Msg 170, Level 15, State 1, Line 9 Line 9: Incorrect syntax near 'a'.
Could someone please help with this? Thank you in advance! shiparsons
I have a large number of relatively simple sql queries that each perform tests on different tables returning different columns and rows for each test.
I would like to be able to run all of these at once and get the output in a usable format.
Would it be possible to run a single query or store procedure which could perform each test then output the results from each test to a new sheet in excel?
Could it output the results of each test to individual csvs?
To be able to tell the Period number ([PD]) to change from an integer to a var_char so that it can be concatenated with the Fiscal Year. I.e. Fiscal Year || Period number to produce the following results.
2006 01
2006 02
2006 03
Etc……
Status of Problem:
The issue is that I can only get it done to the point to where the Fiscal Year || Period number yields the following results:
20061
200610
200611
200612
20062
Etc….
This is as issue since I am trying to get all the information from the past to a certain year and period. Using a filter that says
Fiscal Year + Period Number <= ?Prompt_Fiscal_Year? + ?Prompt_Period_Number?
So if I enter Year: 2007 Period 3
I should get:
2006 01
2006 02
2006 03
Etc…….
2006 11
2006 12
2007 01
2007 02
2007 03
Can you please let me know if my thinking is off or if my SQL is wrong, Thank you for your assistance in this matter.
I am trying to write a stored proc the calculates a moving average overthree periods. In the following example, I need to stratify the data bypersonID and RecordID in the #Temp table, but I am not sure how to doit. Right now I am restricting the data I use to build my time series bypersonID and I get the results I want *by PersonID*. If I can figure outhow stratify by personID so I don't have to use this restriction, I'msure I can extend it to the RecordID.Create Table #Temp(tmpID int identity,DetailID int,RecordID int,AdminDate Datetime,AdminTime datetime,Status tinyint,--decimal(9,2),Location varchar(100),PersonID char(9),PatientName varchar(100),DOB Datetime,Drug varchar(100),Sort varchar(10))--populate with data by personIDinsert into#Temp(DetailID,RecordID,AdminDate,AdminTime,Status ,Location,PersonID,PatientName,DOB,Drug,Sort)Select MD.PatMedOrderDetailID, MD.PatMedOrderID, M.Date as AdminDate,Case M.Time When 'A' then '8:00:00 AM' When 'N' then '12:00:00 AM' When'P' then '4:00:00 AM'When 'H' then '8:00:00 PM' else M.Time End as Admintime,100*M.Status, P.Location,P.PersonID, P.Name as PatientName, P.DOB,D.GenericName + ' (' + D.TradeName + ') ' +D.Strength,Left(P.Location,3)From PatMedOrderDetail MD Inner Join PatMedOrder MO on MD.PatMedOrderID= MO.PatMedOrderIDinner Join PatMedPass M on MD.PatMedOrderDetailID =M.PatMedOrderDetailIDinner join Patient P on M.PersonID = P.PersonIDinner join Drugs D on MO.DrugID = D.DrugIDWhere P.PersonID = '000126230'Order by P.PersonID,MD.patMedorderID, M.Date, M.TimeSelect * from #Temp -- to view entire set--returns relevant rowsSelect Derived.RefusalRate,T.* from #Temp T inner join(select t1.tmpID, avg(t2.Status) as RefusalRatefrom #Temp t1 cross join #Temp t2WHERE t1.tmpID>=3 AND t1.tmpID BETWEEN t2.tmpID AND t2.tmpID+2group by T1.tmpIDhaving avg(t2.Status)< 100) as Derived on T.tmpID = Derived.tmpIDDrop Table #Temp*** Sent via Developersdex http://www.developersdex.com ***
I am new to SQL Server and learning lots very quickly! I am experienced at building databases in Access and using VBA in Access and Excel.
I have a time series of 1440 records that may have some gaps in it. I need to check the time series for gaps and then fill these or reject the time series.
The criteria for accepting and rejecting is a user defined number of time steps from 1 to 10. For example, if the user sets the maximum gap as 5 time steps and a gap has 5 or less then I simply want to lineraly interpolate betwen the two timesteps bounding the gap. If the gap is 6 time steps then I will reject the timeseries.
I have searched the BOL and MSDN for SQL Server and think there must be a solution using the PredictTimeSeries in DMX, but not quite sure if I can do this. I may be better off simply passing through the time series as a recordset and processing as I would have done in Access...(I am reluctant to do this as I have of the order 100 * 5 * 365 time series and growng by 100 each day and fear it will take quite some time...)
Can anyone help me by pointing me in the right direction please?
Unless there is a way of using PredictTimeSeries on its own, I think the solution is:
Identify if a record is the a valid one or part of a gap (ie missing values). Identify the longest gap and reject or process data on this value. Identify if a record preceedes or succeeds a gap. For each gap fill it using a linear interpolation.
First question: How many months of data do you need to make this algorithm to work in Excel 2007? And is there an issue about data types in Excel for this algorithm?
I have found some odd behaviours regarding this. If I use the DM sample Excel 2007 with time series data everything works fine. If I copy and paste data into Excel 2007, from another data source, I can get a forecast of repeating values, that is one value, that will be repeated for each month that I am trying to do a forecast.
Should I avoid having time members for forecast dates in a column? Sometimes my forecast values will be placed below my dates that do not have values. If I am forecasting months in 2008, with month values from 2007 and 2006 the forecast values will be placed below my 2008 empty months.
I'm trying to learn about time series algorithm but I can't set the time periodicity right. I have information stored 2 times a year (semester) so I'll should set up a PERIODICITY_HINT = {2}, right? but it does not change anything.
Here is a screenshot that might help understand the problem:
I'm going to create an analysis report based on time range. The data is grouped by the hourly range. There're two problems that I'm facing.
1. How can I generate such result set so that it will give me 0 count instead of missing that column?
2. How can I vary the start and end time which depends on another table?
I believe this is quite hard to be complete within a single SQL. However, I would still want to try. The SQL server is the Express version. No analysis service is available.
Obviosly for Person1 and 200501 I expect to see on MS Time Series Viewer $3000, correct? Instead I see REVENUE(actual) - 200501 VALUE =XXX, Where XXX is absolutly different number.
Also there are negative numbers in forecast area which is not correct form business point Person1 who is tough guy tryed to shoot me. What I am doing wrong. Could you please give me an idea how to extract correct historical and predict information?
I have few chart based reports where I am showing different costs in series. I want to fix the colour of each type of cost so that I can maintain the same colour for each cost in different reports.
I am building data mining models to predict the amount of data storage in GB we will need in the future based on what we have used in the past. I have a table for each device with the amount of storage on that device for each day going back one year. I am using the Time Series algorithm to build these mining models. In many cases, where the storage size does not change abruptly, the model is able to predict several periods forward. However, when there are abrupt changes in storage size (due to factors such as truncating transaction logs on the database ), the mining model will not predict more than two periods. Is there something I can change in terms of the parameters the Time Series Algorithm uses so that it can predict farther forward in time or is this the wrong Algorithm to deal with data patterns that have a saw tooth pattern with a negative linear component.
I have the following store procedure which list a certain amount of product
SELECT P.Name, P.ProductCode, P.Quantity, P.ItemUnitWeight, P.Weight, P.WeightUnitMeasureID, P.IsDeleted, UNIT.UnitMeasureCode, P.StoreID, dbo.v_ProductItemWeight.TotalWeight, dbo.v_ProductItemWeight.UsedWeight, Production.ProductCategory.Name AS CategoryName, Production.ProductCategory.ProductCategoryID, Production.ProductItem.Quantity AS UnitQuantity, Production.ProductItem.UsedQuantity FROM Production.Product AS P INNER JOIN
[Code] ....
This store procedure produce the following result set :
Result Set
From the result set above, in fact what I need to get is a single records for each ProductCode and adding 2 fiels which are the Sum(UNitQuantity) and the Sum(UsedQuantity)
How can I add those 2 field in my querry and group it by ProductCode ?
l've a series of day which record the date of an event. l would like to count the # of continuous days for the event. In this case, it would be 14/5, 15/5, 16/5, 17/5, 18/5, 19/5 and 20/5. Any idea to do this in SQL?
I inherited an application that creates reports and each report pulls data from a SQL database. The reports are for each School district in the state and pull the number of students that have Asthma conditions, simple enough. However what happens is this... Each initial report is saved with a code of 2 for initial. As long as there aren't and revisions I am fine. If a school makes a revision (Code of 4) I now have two records in the database and I need the one from the revision (Code 4). Not every record will have a revision, and sometimes there may be multiple revisions. How can I code my stored procedure to go through the database and check to see if there is a code of 4 or multiple codes of 4 and if so grab that data, if not grab the initial data? Below is the code currently being used. Frank
CREATE PROCEDURE dbo.usp_Rpt_Asthma (@WhereClause VARCHAR(400)) AS SET NOCOUNT ON DECLARE @WhereClause_STR VARCHAR(400) SELECT @WhereClause_STR = CONVERT(VARCHAR(400),@WhereClause) Exec ('Select D.Code, C.Code, InstCtgyFK, C.[Description] as County, D.DistrictName, A.InstName, Sum(K4+K+G1+G2+G3+G4+G5+G6+G7+G8+G9+G10+G11+G12+UnGrSpEd+Other) as TotalADM, Asthma, Asthmatics FROM dbo.tblAnnualReports AR Inner Join dbo.tblAddresses A on A.InstitutionFK = AR.InstitutionFK Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblInstitution] I on A.InstitutionFK = InstitutionPK Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblActiveInstCtgy] AIC on A.InstitutionFK = AIC.InstitutionFK Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCounties (PA Standard)] C on C.Code = I.DOHCountyCode Inner Join [DHHBGSQLPROD1].[Shared Common Data].[dbo].[tblCommunityHealthDistricts] D on D.Code = C.CommunityHealthDistrictCode Inner Join dbo.tblAverageDailyMemberships ADM on AR.[ID] = ADM.AnnualReportID Inner Join dbo.tblChronicConditionsInjuries CC on AR.[ID] = CC.AnnualReportID Inner Join dbo.tblMedicationAdministration MA on AR.[ID] = MA.AnnualReportID Where (A.StartDate <= AR.DOHDateProcessed and (A.EndDate >= AR.DOHDateProcessed or A.EndDate is Null or A.EndDate = ''1/1/1900'')) and ReportTypeCode = 2 and IndOrdStandOrd = ''I'' ' + @WhereClause_STR + ' Group By D.Code, C.Code, InstCtgyFK, C.[Description], D.DistrictName, A.InstName, Asthma, Asthmatics ') GO
Hi Running on SQL Server2005 I have an tabel Match, TeamInGroup, Team, Group and Series.
Want following resutlt:
Tabell Lag S V O F TOTAL P Grupp A BK Örnen 2 2 0 0 30-10 4 Wåxnäs BC 3 2 0 1 35-25 4 BK Kaskad 2 1 0 1 20-20 2 BK Bågen 3 1 0 2 29-31 2 BK Glam 3 1 0 2 24-36 2 IFK Norrköping BF 3 1 0 2 22-38 2
Grupp B Uppsala BC 90 3 2 1 0 35-25 5 Ludvika BK 2 2 0 0 25-14 4 Sundbybergs IK F 3 1 1 1 34-26 3 LBK Hudik 3 1 0 2 23-36 2 Domnarvets BS 2 0 1 1 19-21 1 Örta IF 3 0 1 2 23-37 1
Grupp C Stureby BK 3 2 0 1 39-21 4 Tureberg IF 2 2 0 0 28-12 4 BK Stallis 3 2 0 1 28-31 4 BK Amiki 3 1 0 2 28-31 2 Djurgårdens IF 2 1 0 1 15-25 2 BK Brio 3 0 0 3 21-39 0
Tabel Match:
USE [Bowlingserier] GO /****** Object: Table [dbo].[Match] Script Date: 09/19/2007 17:25:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Match]( [MatchId] [int] NOT NULL, [Matchstart] [datetime] NULL, [LagIdHemma] [int] NOT NULL, [LagIdBorta] [int] NOT NULL, [Resultathemma] [int] NULL, [ResultatBorta] [int] NULL, CONSTRAINT [PK_Match_1] PRIMARY KEY CLUSTERED ( [MatchId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT [FK_Match_Team] FOREIGN KEY([LagIdHemma]) REFERENCES [dbo].[Team] ([TeamId]) GO ALTER TABLE [dbo].[Match] CHECK CONSTRAINT [FK_Match_Team] GO ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT [FK_Match_TeamInGroup] FOREIGN KEY([LagIdHemma]) REFERENCES [dbo].[TeamInGroup] ([TeamId]) GO ALTER TABLE [dbo].[Match] CHECK CONSTRAINT [FK_Match_TeamInGroup]
Tabel Team:
USE [Bowlingserier] GO /****** Object: Table [dbo].[Team] Script Date: 09/19/2007 17:28:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Team]( [TeamId] [int] NOT NULL, [Name] [varchar](20) NOT NULL, [GroupId] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED ( [TeamId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
Tabel TeamInGroup:
USE [Bowlingserier] GO /****** Object: Table [dbo].[TeamInGroup] Script Date: 09/19/2007 17:27:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TeamInGroup]( [TeamId] [int] NOT NULL, [GroupID] [int] NOT NULL, CONSTRAINT [PK_TeamInGroup_1] PRIMARY KEY CLUSTERED ( [TeamId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO ALTER TABLE [dbo].[TeamInGroup] WITH CHECK ADD CONSTRAINT [FK_TeamInGroup_Grupp] FOREIGN KEY([GroupID]) REFERENCES [dbo].[Grupp] ([GroupId]) GO ALTER TABLE [dbo].[TeamInGroup] CHECK CONSTRAINT [FK_TeamInGroup_Grupp]
Tabel Group:
USE [Bowlingserier] GO /****** Object: Table [dbo].[Grupp] Script Date: 09/19/2007 17:29:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Grupp]( [GroupId] [int] NOT NULL, [SeriesId] [int] NOT NULL, [Namn] [varchar](50) NULL, CONSTRAINT [PK_Grupp_1] PRIMARY KEY CLUSTERED ( [GroupId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Grupp] WITH CHECK ADD CONSTRAINT [FK_Grupp_Serier] FOREIGN KEY([SeriesId]) REFERENCES [dbo].[Serier] ([SeriesId]) GO ALTER TABLE [dbo].[Grupp] CHECK CONSTRAINT [FK_Grupp_Serier]
Tabel Series
USE [Bowlingserier] GO /****** Object: Table [dbo].[Serier] Script Date: 09/19/2007 17:30:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Serier]( [SeriesId] [int] NOT NULL, CONSTRAINT [PK_Serier] PRIMARY KEY CLUSTERED ( [SeriesId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
This is what i dot so far:
SELECT tg.GroupID, t.Name, COUNT(m.ResultatHemma) AS S, CASE WHEN m.ResultatHemma - m.ResultatBorta > 0 THEN COUNT(m.ResultatHemma) ELSE 0 END AS V, CASE WHEN m.ResultatHemma - m.ResultatBorta = 0 THEN COUNT(m.ResultatHemma) ELSE 0 END AS O, CASE WHEN m.ResultatHemma - m.ResultatBorta < 0 THEN COUNT(m.ResultatHemma) ELSE 0 END AS F, SUM(m.ResultatHemma) AS Hemma, SUM(m.ResultatBorta) AS Borta, SUM(m.ResultatHemma - m.ResultatBorta) AS Diff,
CASE WHEN m.ResultatHemma - m.ResultatBorta = 0 THEN 1 ELSE CASE WHEN m.ResultatHemma - m.ResultatBorta > 0 THEN 2 ELSE 0 END END AS P FROM Match AS m INNER JOIN Team AS t ON m.LagIdHemma = t.TeamId INNER JOIN TeamInGroup AS tg ON m.LagIdHemma = tg.TeamId WHERE (m.MatchId = m.MatchId) AND (tg.GroupID = 2 OR tg.GroupID = 1 OR tg.GroupID = 3) GROUP BY t.Name, tg.GroupID, m.Resultathemma, m.Resultatborta ORDER BY P DESC, Diff DESC, S DESC
I can't figure out howto group by t.Name and team in Group A,B, and C
Team: TeamId Name GroupId ----------- -------------------- ----------- 30896 Sundbybergs IK F 2 31346 Turebergs IF 3 32186 Örta IF 2 33286 Domnarvets BS 2 33290 Ludvika BK 2 33407 BK Glam 1 33628 BK Amiki 3 33684 Stureby BK 3 33705 BK Brio 3 33722 BK Stallis 3 33737 Uppsala BC90 2 33744 BK Bågen 1 33768 Wåxnäs BC 1 33874 BK Kaskad 1 33894 BK Örnen 1 42031 LBK Hudik 2 43635 Djurgårdens IF 3 159120 IFK Norrköping 1
Group: GroupId SeriesId Namn ----------- ----------- -------------------------------------------------- 1 48967 Grupp A 2 48967 Grupp B 3 48967 Grupp C
(3 row(s) affected) And Serier: SeriesId ----------- 48967
To be able to tell the Period number ([PD]) to change from an integer to a var_char so that it can be concatenated with the Fiscal Year. I.e. Fiscal Year || Period number to produce the following results.
2006 01
2006 02
2006 03
Etc……
Status of Problem:
The issue is that I can only get it done to the point to where the Fiscal Year || Period number yields the following results:
20061
200610
200611
200612
20062
Etc….
This is as issue since I am trying to get all the information from the past to a certain year and period. Using a filter that says
Fiscal Year + Period Number <= ?Prompt_Fiscal_Year? + ?Prompt_Period_Number?
So if I enter Year: 2007 Period 3
I should get:
2006 01
2006 02
2006 03
Etc…….
2006 11
2006 12
2007 01
2007 02
2007 03
Can you please let me know if my thinking is off or if my SQL is wrong, Thank you for your assistance in this matter.
I want to generate a resultset that is just a series of numbers inascending order or perhaps a series of dates.. What I mean is, isthere a way to generate a temporary table of dates given an input of astart date and an end date.. This table would then contain an entryfor each date in ascending order from the start date to and includingthe end date..Or perhaps with numbers, given a start of 5 and and end of 7the resulting table would be567Would appreciate any help with this one.. ThanksChris
