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
ADVERTISEMENT
Sep 12, 2013
I need to query the count of an occurrence in a given period of time, so I created this query. But it does not give me any results.
SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
GROUP BY x.[StreeLine1], o.ID, o.TimeOfOrder
HAVING COUNT(x.[StreeLine1])>1
Then I change the query slightly and I ask it to show me the ones that are going to '599 Ships Landing Way' and it gives me 356 results! The Query doesn't crash, but it doesn't give me the results I need. What did I do incorrectly?
SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
AND x.[StreeLine1]='599 Ships Landing Way'
I use Microsoft SQL 2008
View 12 Replies
View Related
Oct 18, 2007
Please refer to this table in this discussion:
Charges
Date
1
1/1/07
2
2/1/07
3
3/1/07
4
4/1/07
5
5/1/07
6
6/1/07
7
7/1/07
8
8/1/07
9
9/1/07
10
10/1/07
11
11/1/07
12
12/1/07
What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:
If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4).
If my date parameter is 7/10/07, the charges would be 18 (7+6+5)
I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:
Code Block
SELECT a.DayCount, a.Sales, SUM(b.Sales)
FROM Sales a CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Here is the result set i'm looking for:
Charges
Date
1
1/1/07
2
2/1/07
6
3/1/07
9
4/1/07
12
5/1/07
15
6/1/07
18
7/1/07
21
8/1/07
24
9/1/07
27
10/1/07
30
11/1/07
33
12/1/07
Each date's charges are a culmination of 3 months worth of charges.
Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!
Thanks in advance!
View 6 Replies
View Related
Nov 14, 2001
I have a table that is recording hits to a website. Everytime someone views a page, the datetime of the hit is recorded in a field called hit_date_time. I would like to be able to come up with a query that will show how many hits occured on a given day or given days, broken down by hour.
The resulting table for two days would look something like:
Time Hits
1/1/01 12:00 1
1/1/01 1:00 23
1/1/01 2:00 54
1/2/01 1:00 15
1/2/01 2:00 14
I can't seem to figure out how to write the query so that I can take into consideration the date and hour of the event so that I can count it.
Thanks,
Eron
View 2 Replies
View Related
May 22, 2008
Thanks for taking the time to read my post. I greatly appreciate it!
What i'm trying to do is get a distinct count of account numbers within a rolling period. My actual take is rather large but i've created a smaller-like version below. Please reference this table.
Account
Date
1
1/1/08
2
1/2/08
3
1/2/08
2
2/8/08
4
2/9/08
1
2/15/08
1
3/5/08
5
3/6/08
4
3/9/08
3
3/10/08
1
4/1/08
5
4/9/08
2
4/15/08
3
4/26/08
1
5/3/08
2
5/15/08
3
5/29/08
6
5/30/08
Let's say i want to return distinct count of accounts within a 2-month rolling period meaning in February, i'd get a distinct count for accounts in January & February, then in March i'd get a distinct count for February & March, then in April i'd get it for March & April, and so on... my results table would like the table below:
Account
Month
3
1
4
2
5
3
5
4
5
5
I had asked this before but it was a summing equation and not a unique count. I've tried to play with the summing equation to kind of make it work, but i'm starting to get a headache. It's probably so simple!
Here's my previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2289509
View 8 Replies
View Related
Jan 7, 2008
Hi,
I need to count and display the number of records which have GradeTitle="SHO". I'm only starting to use BI development studio and all attempts at using the built in aggregate functions have failed.
Also, the report I wish to create has a fixed number of columns and a fixed number of rows as the info being displayed is really only counting values in the DB. I tried using Table but multiple rows were created.
I'd appreciate if anyone could point me in the right direction, as searching this forum turned out to be pretty fruitless for me.
Thanks in advance,
John
View 16 Replies
View Related
Nov 2, 2005
I am trying to get a count of how many times a badge is entered in each hour it appears. In other words, I would like to find out how many times badge 3333 comes up in each hour of the day, the same with badge 4532.
The task at hand is to find out productivity for items processed per hour per badge number. I know I will need to take the hour out of timedate using datepart, but the results do not look right. Would anyone have any ideas on this? Thanks!
the table I have looks like this:
IDBadgeOrdNumberTimeDate
6112133333781267941/10/05 9:19
6112084532781275591/10/05 9:21
6111973333781265611/10/05 9:29
6111903333781261671/10/05 9:30
6111774532781253021/10/05 9:38
6111693333781257811/10/05 9:40
View 4 Replies
View Related
Jun 17, 2015
calculating a rolling median over a period of 3 years.
I already calculate median and I've tried to calculate rolling median over a period of 3 years as below.
MEDIAN([Date].[Year].CurrentMember.Lag(3):[Date].[Year].CurrentMember,[Measures].[median])
What this does is, it calculates the median of the medians over the period of 3 years. But, what I'm looking for is the overall median of the underlying measure over a period of 3 years.
What I have now:
Year1 - 41,52,73; Median1 - 52
Year2 - 6,9,12; Median2- 9
Year3 - 24,68,89; Median3 - 68
Overall Median of 9,52,68 - 52
What I need:
Year1 - 41,52,73; Median1 - 52
Year2 - 6,9,12; Median2- 9
Year3 - 24,68,89; Median3 - 68
Overall Median of 41,52,73,6,9,12,24,68,89 is 41
View 4 Replies
View Related
Jan 22, 2015
I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.
TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime.
OpDatesessionStartTimesessionCloseTime
2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166
2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442
2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853
2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707
[code]....
can see how many sessions logged in per hour with the following statement:
SELECT
opDate,
FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour,
Count(*) AS Total
FROM #empSessionLog
Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00')
Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASCResults:
opDateopHourTotal
2015-01-20041
[code]....
Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.
View 5 Replies
View Related
Oct 16, 2015
generate output as specified below. i need to get 1st value when record is duplicated in hierarchyval column
IF OBJECT_ID('tempdb..#test') IS NOT NULL
drop table #test
create table #test
(
hierarchyid int
,hierarchyval int
[code]...
View 3 Replies
View Related
Jan 17, 2014
I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.
At the moment I'm using this:
DECLARE @Date DATE = '20131217'
SELECT CONVERT(DATE, CAST(DATEPART(YEAR,
IIF(
--If we're at the start of a year
--we'll need to go back a year
DATEPART(MONTH, @Date) IN (1,2,3),
DATEADD(YEAR, - 1, @Date),
@Date
)) AS VARCHAR(4)) + '0401')
View 8 Replies
View Related
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
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
View Related
Dec 6, 2013
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
View 5 Replies
View Related
Dec 31, 2013
I have a request where i would like to get the start date/time and end date/time and flag (with an int) which hours (24 hour clock) have values between the two dates. Example car comes into service on 2013-12-25 at 0800 and leaves 2013-12-25 at 1400 the difference is 6 hours and i need my table to show
Column: Hour_6 Value: 0
Column: Hour_7 Value: 0
Column: Hour_8 Value: 1
Column: Hour_9 Value: 1
Column: Hour_10 Value: 1
Column: Hour_11 Value: 1
Column: Hour_12 Value: 1
Column: Hour_13 Value: 1
Column: Hour_14 Value: 0
As i'm working away at it i'm trying to figure out how i could use a Time Dimension table for this but dont really see much. So far i have the difference between the two times in hours (hour_diff) and the start hour (min_hour) so i would like to do something where i update the first hour (min_hour) and update columns based on the numbers of hours (hour_diff)
View 9 Replies
View Related
Sep 18, 2014
Below is a script that count the amount of Void properties we had in a specific month.
SELECT COUNT(C.[Place Referance]) AS [April Total Voids]
FROM
(
SELECT DISTINCTTOP 100 PERCENT
HIST.[PLACE-REF] AS 'Place Referance'
[Code] ....
It tells me I have 53 total voids.
What I also want is a column next to this to say how many of those voids back in April are STILL Void.
So basically the WHERE clause would still be the same -
WHERE [Void Start Date YEAR] = '2014'
AND [Void Start Date MONTH] = '4'
but with the added -
AND HIST.[END-DATE] IS NULL
So ideally I'm after two columns with figures in them and then going forward I can then calculate other months as well.
View 5 Replies
View Related
Mar 26, 2015
I have a requirement in a payroll system to show the status of an employee on the first and last day of each pay period. The pay periods are pretty simple, 24 per year starting on the 1st and 16th of each month. The status effective date, however, can occur on any day, not just the first or last day of the pay period. Here's my DDL.
SET NOCOUNT ON;
declare @EmployeeStatus TABLE (
[EmployeeID] [int] NOT NULL,
[EmployeeStatus] [varchar](24) NOT NULL,
[EffectiveDate] [date] NOT NULL
[Code] .....
View 9 Replies
View Related
Jun 11, 2014
I am trying to count the characters in a sting before a space. Here is the example of what I am trying to accomplish.
"2073 9187463 2700' 4 7 4, the string character count is 4 before the space, 7 is the count before the next space and the last is the last in the string, if there was more characters within this string for example....'2073 9187463 2700 7023 6044567' it would return the number of characters in the string before the space and at the very end of it.
View 9 Replies
View Related
Sep 16, 2015
I have a table with a datetime and an Item column.
I want to retrieve only the rows where item didn't change for a given value.
In the example below, given the value of 5 I only want the rows starting at 19:14:50 to 19:26:06.
Dateteime Item
2015-06-05 19:05:03.0002
2015-06-05 19:08:31.0002
2015-06-05 19:14:50.0001
2015-06-05 19:19:33.0001
2015-06-05 19:20:46.0001
[Code] ....
View 9 Replies
View Related
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
Sep 4, 2015
I tried this:
SELECT Convert(smalldatetime,Round(Convert(float,(dbo.fnDateOnly(getDate())))+16.0000000/24.0000000,6))
Result: 2015-09-04 16:00:00
It works (FnDateOnly strips the time).
Is there a more efficient way ?
View 9 Replies
View Related
Jun 9, 2015
How to write a Query for multiple legal names that have the same CARE Number (same address) with difference of one Legal Name having a period in the name versus the other legal name that doesn't.
For example: Looking for cases of two of the same legal name one set off by period
All Season Equipment Ltd.
All Season Equipment Ltd
West End Housing, Inc.
West End Housing, Inc
Wellings, Norman L.
Wellings, Norman L
North Texas Boats, LLC
North Texas Boats, L.L.C.
Oktibbeha County Cooperative (A.A.L.)
Oktibbeha County Cooperative (AAL)
S & R Turf & Irrigation Equipment, L.L.C
S & R Turf & Irrigation Equipment, L.L.C.
Burke Equipment Company; Burke Equipment-Seaford, Inc.; Newark Kubota, Inc.
Burke Equipment Company
Burke Equipment-Seaford, Inc.
Pleasant Valley Outdoor Power, L.L.C.
Pleasant Valley Outdoor Power, LLC
J & D Lawn and Tractor Sales, Inc.
J&D Lawn & Tractor Sales, Inc"
View 2 Replies
View Related
Apr 25, 2014
I have a problem creating a SELECT query which involves getting the FIRST occurrence, the SUMS and the LAST occurrence of a selected row. I have two tables A and B which has the following data:
Table A
----------------------------------------------------------------
|Item ID | Item Name | Item Description | Current Quantity |
----------------------------------------------------------------
----1------------Bolts-------------35"----------------20
----2-----------Paint----------GOLD Paint------------30
----3-----------Screws------------30"----------------40
----------------------------------------------------------------
and Table B
--------------------------------------------------------------
|Log ID | Item ID | Beginning | Add | Less | Ending | Date |
--------------------------------------------------------------
---1---------1----------30-------0-----10------20------04-04
---2---------2----------40-------10----10------40------04-04
---3---------3----------50-------5-----10------45------04-04
---4---------1----------20-------0-----0-------20------04-05
---5---------2----------40-------0-----10------30------04-04
---6---------3----------45-------0-----5-------40------04-04
-------------------------------------------------------------
and I have two datetime pickers, one FROM date picker and a TO date picker If a user selects FROM 04-04 TO 04-05...I want a select statement which will select the Item Name and Desc, the BEGINNING QUANTITY (in reference to the FROM DATE), the SUM of Adds and Less and the ENDING QUANTITY (in reference to the To Date) and then group by their Item ID
In this case, it will generate a table like this
----------------------------------------------------------
Item Name | Item Desc | Beginning | Add | Less | Ending |
-----------------------------------------------------------
--Bolts---------35"---------30---------0-----10-----20
--Paint-----Gold Paint------40---------10----20-----30
--Screws-------30"---------50---------5-----15-----40
"SELECT A.Item Name, A,Item Desc, B.Beginning=(select B.Beginning from Table B where A.Item ID = B.Item ID AND Date = From Date), SUM(B.ADD), SUM(B.LESS), B.Ending=(select B.Ending from Table B where A.Item ID = B.Item ID and Date = To Date) WHERE A.Item ID = B.Item ID and Date(B.Date)>From Date AND Date(B.Date)< To Date group by B.Item ID order by A.Item Name, Item Desc"
My query returns the right Item Name, Desc, the Sum of Add and Less.My problem is that I can't get the value of my beginning and ending.I think I have a problem in A.Item ID = B.Item ID. As I tried a fixed value condition, (B.Item ID = 2), it will get the right result), I'm guessing the program is not getting A.Item ID.
View 1 Replies
View Related
Jun 23, 2008
Hi,
I'm writing a query that will be calculating a number of business performance measures including the following...
Average Stock:
This is calculated by taking the actual stock value of the product category per month and dividing it by 13 if we have over 12 month's history, or if a new product, then by the number of months we have had it in stock for.
Cost of Goods Sold Annualised:
This is an average figure for the year of the sold landed costs for the product category over a 13 month period if we have over 12 months history. If it's a new product, then it's for the number of months we have had stock.
My question is, how do I tell the query to go back 13 months from the current month?
View 1 Replies
View Related
Aug 26, 2015
My data look like this:
ITEMID DESC QTY PRICE
134 ITEM1 4 $200
134 ITEM1 1 $200
134 ITEM1 1 $200
how do I write SQL stmt to add the quantity if the itemid is the same?
The result should be:
ITEMID DESC QTY PRICE
134 ITEM1 6 $200
View 2 Replies
View Related
Dec 4, 2007
Hi
I need to write something in T-SQL which will return a record set from a table of IT hardware. The record set should comprise all items that are older than 3 years from the current date. Each hardware item has a purchase date against it so it should not be too difficult. I am considering the DATEDIFF function but I am not sure how best to write it.
Any suggestions ??
Thanks
View 2 Replies
View Related
Sep 11, 2014
I have created a report in visual studio 2013 that talks with my SQL server through data connections. I want to be able to query my report BY HOUR. Meaning, I want the report to gather values at 14:30, 22:30, and 06:30. Here is my nonworking code so far.
AND DATEPART(hh,[datetime]) = 0630 AND DATEPART(hh,[datetime]) = 1430 AND DATEPART(hh [datetime]) = 2230
View 13 Replies
View Related
Jul 17, 2007
Hello,I have been having a tough time writing the follow requirement for aquery.On a table that the primary key is a tagId and an hourly timestamp, Iwould like to find out for every hour which tags did not get enteredinto the database. Essentially I am looking for patterns of entriesthat are not making it into tableB.Examples of the tables:TableA TableBTagID and TagName TagId TimestampPK PK1 PK2approx 6000 rows approx 6000 rows per hourI am thinking that I will need to do something like:Select tableB1.time, count(*) from tableB1 group by tableB1.timehaving tableB1.time >= XXXX and tableB1.time <= XXXX and tableB1.tagIdnot in (select tagId from tableA where not exists (selecttableA.tagId, distinct.tableB2.time from tableB2)I have been trying to create an effecient query handle this but havenot had any luck. Any assistance would be more then appreciated.Thanks,Andy
View 1 Replies
View Related
Apr 17, 2008
Hi
I am looking to write a query that returns all records Inserted in the last hour.
The problem, as I see it, is that the column I need to refer to is a VARCHAR() datatype. Can I convert from varchar (example 14:04:31)to a time value and calculate from this ?
I would like to subtract 1 hour from current_timestamp or similar, so that the query dynamically changed.
Many thanks
View 16 Replies
View Related
Mar 21, 2013
What is this? How do I fix it? I get a ton of these and it slows down the server for 5 minutes.
An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.ExternalMailQueue' for its status. Error: 1222, State: 51. Lock request time out period exceeded. This is an informational message only. No user action is required.
View 1 Replies
View Related
Mar 27, 2012
I am trying to create a calculated member for parallel period function using ssas 2012. I have 10 measures for which i need to create parallelperiod.
I can successfully create for 1 measure but when i add multiple values to it it breaks. Below is the sample i tried for multiple measures:
sum(ParallelPeriod([Date].[Calendar].[year],1,[Date].[Calendar].currentmember) ,
([Measures].[Revenue],[Measures].[Expenses]))
View 10 Replies
View Related
Nov 6, 2007
Hi,
Does anyone know how to find out how many rows have been updated or deleted in a particular table for the last 1 hour?
Please reply.
Best Regards,
Ansaar
View 5 Replies
View Related
Mar 6, 2007
Hi,
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
THANKS
View 3 Replies
View Related
