SQL Server 2012 :: Retrieving Minimum Value Over Rolling Time Range?

Apr 28, 2015

I need to provide a minimum value over a 12 hour time range of data. I'm struggling with performance issues due to the amount of data. Currently I log about 100 devices reporting once per minute into a table. Also about once per minute I need to pull the minimum value reported for each device in the last 12 hours. Currently I'm maintaining a separate table with entries for just the last 12 hours and just performing a Select Min(Temp) Where DeviceID=x, but it already holds about 700,000 records at any given time. The number of devices will increase substantially and this will no longer be viable.

Sample Table
ID DeviceID Temp InsertDate
1 10 55 04-28-2015 8:00 AM
2 65 74 04-28-2015 8:00 AM
3 44 23 04-28-2015 8:00 AM
4 10 87 04-28-2015 8:01 AM
5 65 65 04-28-2015 8:01 AM

View 3 Replies


ADVERTISEMENT

Retrieving 1st Record In Time Range Else

Jan 5, 2014

I have a date/time value (dd/MM/yyyy HH:mm:ss) stored in a field as part of each record in a dataset.

I would like to achieve the following via a single T-SQL query if possible (efficiency is not an issue as the dataset is small)

On query run;

Check if any records in the dataset are within 10 minutes + or - of the current system time (using the date/time value referenced above)

if none

Load the first record

View 6 Replies View Related

Rolling Weeks Date Range Of Current Week

Apr 22, 2015

I require some scripting to return data which sits in a rolling weeks date range of the current week.

For example, on a Monday I want to return data from the previous day (Sunday to the previous Monday).
On a Tuesday I want to return data from the previous day (Monday to the up coming Sunday).
On a Wednesday I want to return data from the Monday (previous 2 days ago to the upcoming Sunday)
On a Thursday I want to return data from the Monday (previous 3 days ago to the upcoming Sunday)

And so forth.

So I have my SQL script which works with static, fixed date ranges. But I need this additional dynamic date range logic built in.

View 1 Replies View Related

How To Return Hourly Minimum Count Of Column For A Given Range Of Date

Nov 26, 2015

This is the code I have written and I am trying to retrieve minimum count of PQpageId for every hour for a given date of range.

WITH CTE AS (
SELECT PQIM.PQPageID
,PQIM.PageURL as PageDescription
,CONVERT(Date,NCPI.RequestDateTime) AS [Date]
,DATEPART(HOUR,NCPI.RequestDateTIme) AS [HOUR]
,ISNULL (COUNT(NCPI.PQPageID),0)AS HourlyPQPageIdCount
FROM dbo.NewCarPurchaseInquiries AS NCPI WITH (NOLOCK)

[Code] ....

This is the output I get :

PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 04-11-2015 8 2359
1 05-11-2015 8 2332
1 06-11-2015 8 2008
1 07-11-2015 8 1964
1 08-11-2015 8 2139
1 09-11-2015 8 54

[Code] ....

But I am expecting

PQPageId Date HOUR MINCOUNT
-------- ---------- ---- --------
1 09-11-2015 8 54
1 11-11-2015 9 10
1 11-11-2015 10 4
2 11-11-2015 8 10
2 11-11-2015 9 2
2 11-11-2015 10 1

For ex: Pqpageid 1, at 8am on date 4-11-2015 has a count of 2359 and on 9-11-2015 at 8 am it has count 54then it should return date 9-11-2015 and count 54 for hour 8 like wise for every pageid and hour from 8 to 23 it should return the min count from given date of range.

View 5 Replies View Related

SQL Server 2012 :: Updated Aggregate Column Where Value Is A Rolling Sum

Apr 29, 2014

I am trying to create an aggregate table where the value is a rolling sum. Type a on date 1 is the sum of the values in the main tbl. Type a on date 2 is the sum of values for type a on date 1 and date 2. Is this possible? I have been trying update t sql with sum(case where date <= date) statements but can't get it to run.

create table main_table (type nvarchar(10), date int, datavalues int);
insert into main_table
values('a', '1',3);
insert into main_table
values('b', '1',4)

[Code] .....

View 9 Replies View Related

Data Access :: How To Reduce Record On The Basis Of Time If Minimum 2 Minutes Duration In Server

Sep 8, 2015

I have a table with following data

(Id, date ,time)

11 2015/8/1
12:20:00

11
2015/8/1 12:21:00

11 2015/8/1
18:05:20

12 2015/8/1
11:20:00

12 2015/8/1
11:21:00

12 2015/8/1
18:10:20

I need the table with following record only

(Id, date ,time)

11 2015/8/1
12:20:00

11 2015/8/1
18:05:20

12 2015/8/1
11:20:00

12 2015/8/1
18:10:20

View 7 Replies View Related

SQL Server 2012 :: Query - Counting Item Occurrence Over A Rolling 72 Hour Period

Jun 18, 2015

I am using MS SQL 2012 and have a pretty simple table dbo. Migration Breakdown with sample data as follows.

DepartDateTime ZoneMovement
2015-06-26 14:00:00.000 6 to 4
2015-06-26 14:00:00.000 11 to 7
2015-06-26 15:30:00.000 9 to 6
2015-06-26 21:00:00.000 7 to 3
2015-06-27 08:01:00.000 7 to 4

[code]....

What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business.

View 9 Replies View Related

Query Info Between Time Range & Date Range

Aug 16, 2006

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

View 6 Replies View Related

Retrieving All Users In Db With Specified AGE Range

Jul 29, 2007

Hello, I have a table called Member in my database that I use to store information about users including the date of birth for each person.  I have a search function in my application that is supposed to look through the Member table and spit out a list of users with a user-inputted age range (min and max ages).  Now, I could have stored ages instead of dob in the table, but I would think that's bad practice since age changes and would need continuous recomputing (which is db intensive) as opposed to dob which stays the same.
So what I'm thinking is getting the min and max user inputted ages, convert them to dob values (of type DateTime) in the application.  And then, to query the db and return a list of all users in the Member whose dob falls in between those two dates (is a BETWEEN even possible with DateTime values?).
How is the best way to go about this?  There are many sites out there that return users with user specified age ranges.  Is there a best way to do this that's the least taxing on the db?
TIA.

View 6 Replies View Related

Retrieving Server Time Using Stored Procedure

Jul 21, 2000

Hello all,

We have a domain where all computers are on GMT(Greenwitch Mean Time). We have an access front end that timestamps certain fields according to the client time that the program is running on, but we will be moving our client workstations off of GMT time and keep our SQL Server on GMT time, and want to keep the timestamps GMT.

So, I wanted to know if it was possible to create a stored procedure that gets the Server's time and returns it to the Access frontend for entry into the timestamp fields?

Or, if anyone has a better idea of how to get the time from the server to use on the clients, I would greatly appreciate it!!!

Kevin Kraus

View 2 Replies View Related

Minimum Time Difference Between Tables

Sep 3, 2013

I receive the following result set from TableA (In Time)

7/9/2013 9:27:00.000 AM
7/9/2013 10:24:00.000 AM
7/9/2013 11:25:00.000 AM
7/9/2013 1:23:00.000 PM
7/10/2013 7:27:00.000 AM

Then we receive the following result from TableB (Out Time)

7/9/2013 9:30:00.000 AM
7/9/2013 10:29:00.000 AM
7/9/2013 1:37:00.000 PM
[NULL]
[NULL]

We may not always get Out Times in TableB so I want to merge these into one table to have the In Time and Out Time in separate columns in that one table. In this example with the red type those should be In Time and Out Time for mapped unique identifiers from each table and yet the purple color coded example would have an In Time of 11:25 AM and the Out Time would remain as NULL.

I am using this block of code but is not working the way I want it to because the 11:25am In Time is getting mapped to the 1:37pm Out Time.

and out_time = (select min (out_time)
FROM tableB WHERE
tableB.record# = tableA.record#
and tableB.loc_id = tableA.loc_id

GROUP BY tableB.record#, tableB.loc_id )

It seems I need to focus on the minimum datediff for each record line but can't figure that part out.

View 2 Replies View Related

Retrieving Records Within An Index Range, The Nth Record?

Mar 5, 2007

if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?Possible? How can I do it?When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?I'm using SQL 2005 Express, not SQL 2000.

View 14 Replies View Related

Rolling Back Sql 2000 To A Point In Time

Aug 9, 2005

I would appreciate a bit of advice here. There is a largish complaint here regarding a cutomer who has entered data in 1 of our online forms, but we suspect this was then overwritten by a cached form she also had open.
Anyway to cut a long story short i need to roll back the database to a point in time.

Not something i have ever had to do.

The row where the id field is equal to 3352, and this would have been written to the database at 14:58:36 on 08-aug-2005. This was over written by the data in row with id 3380 at about 11am this morning, now is this is a live database on a webserver so i cant compromise its uptime as it get written to about 3 times a minute, so how should i go about this?


Anyway, what do you think?


Cheers

View 2 Replies View Related

Mean, Minimum, Maximum, Standard Deviation Of Time

May 7, 2007

I have a report that calculates mean, min, max, stddev of somer exercises for a class (pushups, situps, trunk lifts, etc). I also have to calculate those for the 1-mile run time (ex: data -- 7:56, 6:35, 9:45 ( in minuteseconds). Obviously the standard Avg(), Min(), Max(), StdDev() functions won't work for time. So I put in some custom code to convert the time to seconds so I can use the standard functions on the seconds and also code to convert that answer back to minuteseconds. Max() works, but for example when I try to calculate the min(), it includes nulls from the dataset as zeros (not every student has to do the mile run). So the min is always 0. How can I exclude nulls from being calculated. The min() function excludes nulls so it returns the correct min() on integer data. What else can I do?

View 3 Replies View Related

SQL Server 2012 :: Retrieving Null Values

Sep 10, 2015

Question in review today is Creating a Report showing the FName, LName of all Employees not Specified in a region; I would assume "No Value to be Null" Correct?

Or is there another way for me to do this?

This is what I have so far...What am I missing that this is not showing me results?

Select Firstname, Lastname, Region
From Employees
WHERE Region LIKE 'null%';

View 3 Replies View Related

Power Pivot :: 28 Day Rolling Averages With Discrete Time Attributes

Jul 14, 2015

I've been able to find plenty of info online in relation to calculating moving averages for a given value. I am having a specific problem however.

So because I need to see the average on a daily basis, my forumla (below) works fine:

However, my Calendar dimension (TIME) also has a discreet, non-continuous attribute called DateMonth (which looks like 1-Jan, 2-Jan etc.) which has no reference to the year.

I was hoping this would allow me then to take my year attribute and compare the 28 Day Average for the 1st of Jan across multiple years.

So you could have a line chart where the X axis is 1-Jan to 31-Dec (with no reference to the year) and the series category is by year, so a line for each year.

But when I replace DATE with DateMonth in my pivot, the calc gives some strange numbers.

Rolling28DayAvg :=
CALCULATE (
    [Revenue],
    DATESINPERIOD (
        TIME[DATE],
        LASTDATE ( TIME[DATE] ),
        -28,
        DAY
    )
)
    / 28

View 15 Replies View Related

SQL Server 2012 :: Nested Query Retrieving Min And Max Values?

Jul 16, 2015

I have a nested query that retrieves a min value. I would like to know how do i retrieve the corresponding time with that.

my data looks like this

valuevaluetime
1212/31/14 14:51
12.42/19/15 23:30
[highlight=#ffff11]10.92/21/15 6:40[/highlight]
13.21/25/15 20:47

My min value is 10.9 and i need the date 02/21/15

my nested query is as follows

( select min(cast(f.valuestr as float))
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time)

the above returns me the 10.9

i modified the query

select min(cast(f.valuestr as float)),
f.valuetime
from bvfindings f
where f.ObjectName = 'Hb'

but i get all values then.

View 6 Replies View Related

SQL Server 2012 :: Retrieving Dataset That Only Includes Latest Versions Of Data

Mar 28, 2014

In our Microsoft Dynamics Nav instance we have a Sales Header Archive table - into which copies of the Sales Header are placed, with 3 items forming the compound key:

Document Number
Version
Occurrence number

so if doc 1 is archived, then the records would be

Doc# | Version | Occurrence #
1 | 1 | 1

When a second copy is archived a new record is added:

Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2

and then when maybe a 3rd version is archived a 3rd entry added

Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2
1 | 2 | 2

This is for EACH document and I now need to retrieve the dataset which is the latest version of each document... but I'm drawing a blank!

If I

select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#]

then I get the distinct list of docs, but I now need to use this list to select the records which match this criteria, from this table.

How do I select just these?

I thought (wrongly) that I could simply say:

Select * from Invoice Table where
Invoice.[Doc#], Invoice.[V], Invoice.[Occurrence #] in
(select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#])

View 2 Replies View Related

SQL Server 2012 :: Modifying Date Range

Jun 27, 2014

I would like changing the date range to be -1 year from today until today

This is the current WHERE, AND

WHERE [entry].[Posting Date] >= DATEADD(YEAR, YEAR(GETDATE()) - 1900, 0)
AND [entry].[Posting Date] < DATEADD(YEAR, YEAR(GETDATE()) - 1899, 0)

View 2 Replies View Related

SQL Server 2012 :: Date Range Calculation

Feb 3, 2015

I have some location assignment data that I need to convert. I need to know how long each account spent in a certain location for each month of it's overall startdate/enddate period.

E.g.
Account 1 stayed in USA for 31 days in January, and 15 days in February.
Account 1 stayed in UK for 13 days in February and 26 days in March.
Etc.

create table #temp(account int, loc varchar(10), startdate datetime, enddate datetime)
insert into #temp select 1,'USA','2014-01-01','2014-02-15'
insert into #temp select 1,'UK','2014-02-16','2014-03-26'
insert into #temp select 1,'AU','2014-03-27','2014-06-07'
insert into #temp select 2,'UK','2014-08-15','2014-09-01'
insert into #temp select 2,'AU','2014-09-02','2014-10-17'
select * from #temp
drop table #temp

View 6 Replies View Related

SQL Server 2012 :: Alphanumeric Range Grouping?

Feb 25, 2015

I am having some difficulty getting a query to output an alpha numeric range grouping.

I have this data set:

Despatch_id Sample_ID

MIR00831 MCR0005752
MIR00831 MCR0005753
MIR00831 MCR0005754
MIR00831 MCR0005755
MIR00831 MCR0005756
MIR00831 MCR0005757

[code]....

Output:

DESPATCH_ID SAMPLE_ID_FROM SAMPLE_ID_TO
MIR00831 MCR0005752 MCR0005762
MIR00831 MCR0005803 MCR0005806
MIR00831 MCR0005808 MCR0005813

They need to be grouped by range specific to the alpha numeric part, which can vary within the same despatch. I was thinking of using a row over partition after splitting the numeric and alpha part and to check if they are consecutive and build the range. But I am thinking that this approach is an overkill and there may be a better way to achieve this in SQL 2012.

I have included the create table scripts and example data below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SAMPLE_TABLE](
[DESPATCH_ID] [nvarchar](30) NOT NULL,
[SAMPLE_ID] [nvarchar](30) NOT NULL

[code]....

View 7 Replies View Related

SQL Server 2012 :: Range Group Summary Of Particular Column

Jun 3, 2014

I have column which stores People count based on department, Now I want to keep them in the batch of 1000, If the running summary of (No of people) from departments reached 1000 then it should start sum(no of people) from 0 to 1000

Is there any running summary kind of function which can start sum record with in range of 0-1000

For Ex. My Data stored like this
Dept People Count
CSE 200
IT 250
EEE 312
ECE 214
MEC 337

Batch Grouping
Dept People Count BatchSum
CSE 200 200
IT 250 450
EEE 312 762
ECE 214 976
MEC 337 337 (Note here since its crossing 1000, its resetting and starting summary)

I implemented this with While Loop & if condition, But its very slow, is there any other way to achieve it in better way.

View 8 Replies View Related

SQL Server 2012 :: Assigning Numbers For A Range During INSERT

Jun 25, 2014

Given a Table1 with two columns 'Name' with some N rows of data and another Table2 with one column 'SeqNo' with N rows, each of which contains a unique integer which can be ordered monotonically, I want to do an INSERT into some Table3 with two columns 'Name' and 'SeqNo' such that each INSERT'd row gets one of the unique integers.

E.g. -

Table1 contains 'Fred','Tom','Mary','Larry'
Table2 contains 6000978,6000979,6000980,6000981

INSERT INTO Table3
SELECT Table1.Name
Table2.SeqNo
FROM Table1

And I want to get Table3

'Fred',6000978
'Tom'.6000979
'Mary',6000980
'Larry',6000981

How can I reference Table2 so that Table2.SeqNo will 'line up' properly? Note that the ordering of the SeqNo values isn't mandatory as long as each SeqNo is assigned to one and only one row.

On edit: Table2 isn't required, it's just the way I started thinking about it. It would be nicer to just have two integer vars, @StartSeqNo = 6000978 and @EndSeqNo = 6000981 for he example above. Either way is fine.

View 3 Replies View Related

SQL Server 2012 :: Query Result For Sequential Range

Aug 9, 2014

I wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.

The data looks like:
ABCD-001869202
ABCD-001869203
ABCD-001869204
ABCD-001869205
ABCD-001869209
ABCD-0018692010
ABCD-0018692011
ABCD-001869309
ABCD-001869310
ABCD-001869311
ABCD-001869312
ABCD-001869313
ABCD-001869314

Desired out put:

From To
ABCD-001869202 ABCD-001869205
ABCD-001869209 ABCD-0018692011
ABCD-001869309 ABCD-001869314

I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:

SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher
INTO #tempVoucher
FROM LEDGERJOURNALTRANS
where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'

[Code] ...

View 6 Replies View Related

SQL Server 2012 :: Date Range Checking In Table

Mar 16, 2015

I have a table with EmployeeID, StartDate, and EndDate with a PK of EmployeeID, StartDate. How can I check to see that there's no overlap for StartDate and EndDate for a given employee? That is, on any given day there must only be 1 row for an employee where Getdate() is Between StartDate and EndDate. For an active employee their EndDate is set to 06/06/2079.

I've tried it using Row_Number() with Over() but am returning too many rows indicating overlap when none exists.

View 7 Replies View Related

SQL 2012 :: Rolling Up Multiple Row And Columns Into A Single Cell NULL Error

Jul 28, 2015

So I have a problem with combining cells: some of the cells are blank and some of the cells have NULL value.

The combined blank cells return perfect.

But the combined cells in which 1 or more contains NULL will return as NULL

0-4139-00-01OPTIONAL EQUIPMENT 139 - A -
0-4139-00-02NVG EQUIPMENT 139 - A -
0-4139-00-03NVG EQUIPMENT 139 - A -
0139-01-01BRAKE RESERVOIR NULL

A part of the script which i use is:

STUFF((SELECT ', ' + i.ac_type + ' - '
+ i.range_or_single+ ' ' + ' - ' + i.serialno_to
FROM msc_item_effectivity i
inner JOINmsc_item h
ONb.taskcard_verno_i = h.taskcard_verno_i
WHERE h.itemno_i = i.itemno_i
FOR XML PATH('')), 1, 1, '')

i.ac_type = 139
i.range_or_single = A
i.serialno_to = blank or NULL

I want the result of the blank value to be the same as the NULL value.

0139-01-01BRAKE RESERVOIR 139 - A -

View 2 Replies View Related

SQL Server 2012 :: Query That Generates Values By Date Range

Dec 12, 2014

I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.

SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME)
,d.fmc_prodlnk, d. fmc_cost
INTO #TestTable
FROM (
SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL

[Code] ....

Expected outcome
fmc_terminalfmc_prodlnkfmc_cost StartDateEndDate
1A2.25 NULL2014-12-03 00:04:00.000
1A2.26 2014-12-03 00:04:00.0002014-12-03 11:33:00.000

And so on….

View 4 Replies View Related

SQL Server 2012 :: Day Wise And Date Range Calculation With Looping Or Dynamic Data?

May 14, 2015

I am using Sql Server 2012.

This is how I calculate the ratio of failures in an order:

31 Days Table 1 query
sum(CASE
WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .

How do i loop and pass dates dynamically in the Datediff?

31 Failures Table 2 query
SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102))
AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2
((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.

I want a similar kind of calculation done for day wise and month wise.

2. what approach should I be using to achieve day wise and month wise calculation?

I do also have a table called Calender with the list of dates that i can use.

View 3 Replies View Related

How To Create Sql For Retrieving 10 Records Per Time In .NET

Aug 4, 2004

I want to create sql for retrieving 10 records per time but need to know the total result matches.
And I have previous and next button for retrieving previous or next 10 records.

Thanks

View 6 Replies View Related

Time Range

Apr 21, 2008

Hi there

I am using SQL2005 and would like to check the whether the time is within the valid range for instance:

Data to check: 2:15AM

Range is between: 01:00AM till 06:59AM, 07:00AM till 02:00 PM

I can use the datepart for hh but that is not what I want. I want to have a full time: HH:MM cause sometimes if you do datepart by hour and if you apply into the above excample will be:

2 BETWEEN 1 AND 6 (this is ok)
2 BETWEEN 7 AND 2 (this is not ok)

Any idea?

View 3 Replies View Related

Reducing Time On Retrieving Large Amount Of Data

May 5, 2003

Hi,
My application needs to retrieve data from a table which has more than 15 lakh records. The records keep increasing in thousands every 15 days.
Is there anyway i can reduce the time to retrieve? basically i have a select statement with a few conditions and a clause for the id's of these records.

View 2 Replies View Related

Retrieving A Piece Of Datalogged Equipment By Most Recent Time

Jun 22, 2006

The TSQL below all works except the bolded part at the end. I'm want to grab only the most recently logged piece of equipment not the most recent and all past ones as well which is what I've got doing minus the bolded part below. But I don't know how to say get this Equipment ID etc and only the most recently logged one to find its present location. The bolded part below is just there to show what I want it to do I know you can use an aggregate in a where clause. So in the first table listed tblRdrLog there is a column Time that I want to do this on so a.Time. I don't want to display a.Time just reference.

String dbsql = " SELECT a.EquipmentID " +
" , f.Subcategory " +
" , c.Area " +
" , d.Room " +
" FROM tblRdrLog a " +
" JOIN tblRdrInfo b ON a.ReaderID = b.ReaderID " +
" JOIN tblRdrArea c ON b.AreaID = c.AreaID " +
" JOIN tblRdrRm d ON b.RoomID = d.RoomID " +
" JOIN tblEquipInfo e ON a.EquipmentID = e.EquipmentID " +
" JOIN tblEquipSubcat f ON e.SubcategoryID = f.SubcategoryID " +
" WHERE a.EquipmentID IN (SELECT a.EquipmentID " +
" FROM tblEquipInfo a " +
" JOIN tblEquipCat b ON a.CategoryID = b.CategoryID " +
" JOIN tblEquipSubcat c ON a.SubcategoryID = c.SubcategoryID " +
" LEFT OUTER JOIN tblEquipMake d ON a.MakeID = d.MakeID " +
" LEFT OUTER JOIN tblEquipModel e ON a.ModelID = e.ModelID " +
" JOIN tblStatus f ON a.StatusID = f.StatusID " +
" WHERE b.CategoryID = '" + this.ddlCategory.SelectedValue.ToString() + "' ";

if (!"".Equals(this.ddlSubcategory.SelectedValue.ToString()))
dbsql += " AND c.SubcategoryID = '" + this.ddlSubcategory.SelectedValue.ToString() + "' ";

#region Advanced Search Criteria

// Check whether advanced search submitted
if (adv)
{
if (!"".Equals(this.tbSerialNo.Text.ToString()))
dbsql += " AND a.SerialNo = '" + this.tbSerialNo.Text.ToString() + "' ";
if (!"".Equals(this.ddlMake.SelectedValue.ToString()))
dbsql += " AND d.MakeID = '" + this.ddlMake.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlModel.SelectedValue.ToString()))
dbsql += " AND e.ModelID = '" + this.ddlModel.SelectedValue.ToString() + "' ";
if (!"".Equals(this.ddlStatus.SelectedValue.ToString()))
dbsql += " AND f.StatusID = '" + this.ddlStatus.SelectedValue.ToString() + "' ";
}

#endregion

dbsql += " ) " +
" AND a.Time = max(a.Time) " +
"";

View 11 Replies View Related

Retrieving Actual Sales Value From The Time Series Model

Jul 27, 2006

Hi,



I have built a Sales Forecast model to predict the sales
value.
Along with making historic predictions for previous time
periods I also want to retrieve the actual sales values for those periods.


How can I achieve this in a time series model?



I also would like to know how do mining models store the data.

Do they store the data in the same table/view format
as their respecive data source view or in the Model Content format.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved