Count Of Occurrence In Given Period Of Time - Query Has Unexpected Results
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 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.
Hello, I would like to exclude the time period after 5.30pm and before 8.30am in my results. The time is in a 13 digit timestamp format which is the same as a standard unix timestamp with 3 digits which are microseconds.
I used:
dataadd(ss, TTIME/1000, '1970-01-01')AS time
to create a column with a readable time in it.
Here is a screenshot: http://www.abtecnet.com/timescreenshot.jpg
Can anyone help me with this. Thanks very much. Andrew
I'm trying to run what I thought was a relatively straightforward query to find all entries from one table that don't appear in another table:
select * from Search_Suggestion where Suggestion not in (select distinct C106 from Search_Log)
The Search_Suggestion table contains 4060 entries and the Search_Log table contains 142,000+ distinct entries.
From running a similar query using 'in' instead of 'not in' I find that there are 3778 matches between the two tables would logic which suggest leave 282 that don't exist in the Search_Log table.
However....when I run the above query it returns no records.
I've tried changing it around and have also tried using temp tables but each time I still get no records.
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!! -- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on go if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1 if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and
'17:00:00' order by [DateTime]
However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.
select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and
'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and
'09:00:00' order by [DateTime]
I need a way to indicate that the start and end times span two days. Can anybody help with this?
I want to count number of occurrence in column c3. i.e., count 1 occurred value 9 times, count 2 occurred value 3 times, count 3 occurred value 3 times.
See sample data below. I'm trying to count the number of occurrences of strings stored in table @word without a while loop.
DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))
INSERT INTO @t SELECT 'There are a lot of Multidimensional Expressions (MDX) resources available' AS String UNION ALL SELECT 'but most teaching aids out there are geared towards professionals with cube development experience' UNION ALL
Hello, I use the full-text search utility in SQL Server 2005 to find word in PDFs document. This is my 'Documents' table:
id (PK), data (VarBinary(max)), extension (nvarchar(4))
My full-text catalog on 'data' column works fine because when I search 'Microsoft', my document containing this word is returned as result.
SELECT * FROM Documents WHERE freetext([data], 'Microsoft'); 1 , 0x255044...., .pdf
But I need to know how many times 'Microsoft' word appears in this document. Do you have any idea how can I retrieve this information? Thanks in advance!
Hello, I use the full-text search utility in SQL Server 2005 to find word in PDFs document. This is my 'Documents' table: id (PK), data (VarBinary(max)), extension (nvarchar(4)) My full-text catalog on 'data' column works fine because when I search 'Microsoft', my document containing this word is returned as result.
SELECT * FROM Documents WHERE freetext([data], 'Microsoft'); 1 | 0x255044.... | .pdf
But I need to know how many times 'Microsoft' word appears in this document. Do you have any idea how can I retrieve this information? Thanks in advance!
I have a table that seems to have a bad index. When I do the followingquery I get inconsistant and needless to say incorrect results.select count(*) from mytable where mycolumn = 1If I remove the index from "mycolumn" the query works correctly. If Iadd the index back (even with a new name etc...) it doesn't workright.Has anyone ran into this? or does anyone know how I can fix thisproblem?It seems that removing the index is not really removing everythingbecause when I add a new one I get this same problem... btw, this isisolated to this column on this table. all other indexes within thedatabase are fine.Any help would be appreciated.Thanks,dharper
I have lost the reference but I read somewhere that when running DBCC DBREINDEX against a clustered index, all the secondary indexes on the table are automatically re-indexed as well. I did a test of this on a small table and it seemed to confirm this. However, now I've put this into practice, I am finding that it doesn't seem to work this way. I noticed that having run DBCC DBREINDEX against a table's clustered index (DBCC DBREINDEX ('tablename', 'clusteredIndexName', fill_factor)), the secondary indexes were not automatically re-indexed - as born out by the fact that they remained badly fragmented.
First of all, do the dba's who read this beleive it is correct that DBCC DBREINDEX run against a clustered index will automatically rebuild the secondary indexes too? If so, why wouldn't it work in all cases?
Normally, after I use DBCC DBREINDEX, I can be sure that Scan Density on a clustered or non-clustered index is very good - eg. 99% or 100%. However, I have one database where there are a number of indexes that are not showing any improvement in Scan Density after running DBCC DBREINDEX. In on case, a clustered index, I run it on two days in succession and Scan Density actually go worse! Can anyone give me a reason for this? Can anyone suggest how to fix it?
SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357 SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
But I want to add the COUNT statements, so this is what I did:
select COUNT(vws.id)+COUNT(lnks.id)*2 AS totalcount FROM location_views vws,extlinks lnks WHERE (vws.createdate>DATEADD(dd,-30,getdate()) AND vws.objectid=357) OR (lnks.createdate>DATEADD(dd,-30,getdate()) AND lnks.objectid=357)
Turns out the query becomes immensely slow. There must be something I'm doing wrong here which results in such bad performance, but what is it?
I'm having difficulty coming up with the right syntax for a query. Suppose I have a database containing a Stores table, an ProductInventory table, and a Customers table. The Stores table has an ID field that serves as a foreign key in both the ProductInventory table and in the Customers table. I'm trying to write a query that, for each Store record, will return the total number of records in the ProductInventory table and the total number of records in the Customers table.
The following query returns, for each store, the total number of records in the ProductInventory table:
SELECT Stores.Name, COUNT(ProductInventory.ID) AS ProductInventoryItemCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID GROUP BY Stores.Name
The following query returns, for each store, the total number of records in the Customers table:
SELECT Stores.Name, COUNT(Customers.ID) AS CustomerCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = Customers.StoreID GROUP BY Stores.Name
I combined the two queries:
SELECT Stores.Name, COUNT(ProductInventory.ID) AS ProductInventoryItemCount, COUNT(Customers.ID) AS CustomerCount FROM Stores LEFT JOIN ProductInventory ON Stores.ID = ProductInventory.StoreID LEFT JOIN Customers ON Stores.ID = Customers.StoreID GROUP BY Stores.Name
When I run this last query, however, I get an "Arithmetic overflow error converting expression to data type int" error. Using COUNT_BIG instead of COUNT eliminates the error, but the numbers that are generated are astronomical in size. This indicates to me that there is a *lot* more table joining going on than I expected
What is the correct syntax to produce the desired results? I have a few other tables similar to ProductInventory and Customers; I'm hoping to extend the correct syntax so as to be able to get a comprehensive record count list for each store. Thanks for your help!
I am using snapshot replication to copy data from one database to another. I truncate the destination table first. The publisher and the subscriber are different databases on the same server. The data turns into 1 unrecognizable row.
Here is what the source table looks like: displayTypeID, displayType 1, Title List Formats 2, Title Details 3, Title List Export 4, Pricing Notes 5, clone Mask 001 6, clone Mask 002 7, clone Mask 004 8, clone Mask 008 9, hide From Admin 10, not Common Attr 11, Insert Title 12, title Types
The resulting data looks like this: DisplayType, displayTypeId , 1660953600 I see no errors in replication and nothing in the source table looks like bad data that could cause such a malady. I see the problem on two different servers now and identified the source of the problem, the column order is reversed in the source and destination tables. The data types do not even match so I would at the very least expect an error from bcp.
I have a simple update statement (see example below) that when runs, I expect to see the number of records updated in the Results tab. This information shows up in the Messages tab; however, what is displayed in the Results tab is (No column name) 40. From where the 40 is being generated. I have tried restarting SSMS 2012, restarting my computer, turning NOCOUNT on and off.
"UPDATE TableA SET Supervisor = 'A123' WHERE PersonnelNumber = 'B456'"
Hi I have a strange issue here. Both the input datasets of the merge join are sorted . Everything works fine if the dataset doesn't contain speacial characters like - (hipen). This is the sorted dataset.ASI311_3ASI311_3ASI312ASI311ASIKit1ASIKit1
Merge Joiner has to look for ASI311 and finds it in the list and everything works as expected. But if the dataset is changed to
ASI311_3ASI311_3ASI-312ASI311ASIKit1ASIKit1
Then it doesn't find the ASI311 in the above dataset ? Only change between the two datasets is the - (hipen) which is added in one of the field. Does SSIS has a differenent precedence of the special characters than that of the Database. Both the datasets are on SQLServer.
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
We are getting unexpected results from the following update statement when it is executed on SQL Server 2005.
The strange thing is that we get duplicated values for QM_UID (although when run under SQL Server 2000 we don't get duplicated values)
Can anyone explain why this happens or suggest another way of populating this column without using a cursor or adding a temporary autoincrement column and copying the values over?
declare @NextID int;
set @NextID = 1;
update tmp set QM_UID=@NextID, @NextID = @NextID + 1;
select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID
-- NB: The number of rows that must be added to tmp before this problem will occur is machine dependant
-- 100000 rows is sufficient on one of our servers but another (faster) server doesn't show the error
-- at 100000 rows but does at 1000000 rows.
-- Create a table
CREATE TABLE tmp ( [QM_ADD_OP] [char](6) DEFAULT '' NOT NULL, [QM_ADD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_ADD_TIME] [int] DEFAULT -1 NOT NULL, [QM_EDIT_OP] [char](6) DEFAULT '' NOT NULL, [QM_EDIT_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_EDIT_TIME] [int] DEFAULT -1 NOT NULL, [QM_LOCK_OP] [char](6) DEFAULT '' NOT NULL, [QM_QUOTE_JOB] [smallint] DEFAULT 0 NOT NULL, [QM_QUOTE_NUM] [char](12) DEFAULT '' NOT NULL, [QM_JOB_NUM] [char](12) DEFAULT '' NOT NULL, [QM_PRJ_NUM] [char](12) DEFAULT '' NOT NULL, [QM_NUMBER] [char](12) DEFAULT '' NOT NULL, [QM_REV_NUM] [char](6) DEFAULT '' NOT NULL, [QM_REV_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REV_TIME] [int] DEFAULT -1 NOT NULL, [QM_REV_OPR] [char](6) DEFAULT '' NOT NULL, [QM_STYLE_CODE] [char](4) DEFAULT '' NOT NULL, [QM_REP_JOB_NUM] [char](12) DEFAULT '' NOT NULL, [QM_REP_COLUMN] [smallint] DEFAULT 0 NOT NULL, [QM_REP_PART] [char](6) DEFAULT '' NOT NULL, [QM_REP_MODEL] [smallint] DEFAULT 0 NOT NULL, [QM_REP_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_MODEL_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_RUN_NUM] [int] DEFAULT 0 NOT NULL, [QM_SOURCE_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_SOURCE_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_SOURCE_QTY] [char](12) DEFAULT '' NOT NULL, [QM_SOURCE_PART] [char](6) DEFAULT '' NOT NULL, [QM_SOURCE_MODEL] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_ORIG_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_QTY] [char](12) DEFAULT '' NOT NULL, [QM_ORIG_PART] [char](6) DEFAULT '' NOT NULL, [QM_COPY_JOB] [char](12) DEFAULT '' NOT NULL, [QM_COPY_COLUMN] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_J_PART] [char](6) DEFAULT '' NOT NULL, [QM_COPY_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_COPY_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_QTY] [char](12) DEFAULT '' NOT NULL, [QM_COPY_Q_PART] [char](6) DEFAULT '' NOT NULL, [QM_JOINT_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_QUOTE_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_JOB_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_LIVE_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_USER_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_DEL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_IS_CONVERTED] [smallint] DEFAULT 0 NOT NULL, [QM_PRINTED] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_RATES] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_UPDATE] [smallint] DEFAULT 0 NOT NULL, [QM_CRED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CRED_TIME] [int] DEFAULT -1 NOT NULL, [QM_CRED_AMT] numeric(26,8) DEFAULT 0 NOT NULL, [QM_CRED_OP] [char](6) DEFAULT '' NOT NULL, [QM_HELD] [smallint] DEFAULT 0 NOT NULL, [QM_PROOF] [char](12) DEFAULT '' NOT NULL, [QM_DELIV_METHOD] [char](12) DEFAULT '' NOT NULL, [QM_ART_METHOD] [char](12) DEFAULT '' NOT NULL, [QM_DES_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_REC_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REC_TIME] [int] DEFAULT -1 NOT NULL, [QM_OWN_OP] [char](6) DEFAULT '' NOT NULL, [QM_RESP_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_RESP_TIME] [int] DEFAULT -1 NOT NULL, [QM_RESP_OP] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_1] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_2] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_3] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_4] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_5] [char](6) DEFAULT '' NOT NULL, [QM_RECONTACT] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REQ_FLAG] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_ORIG_TIME] [int] DEFAULT -1 NOT NULL, [QM_PREF_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_PREF_TIME] [int] DEFAULT -1 NOT NULL, [QM_LATE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_LATE_TIME] [int] DEFAULT -1 NOT NULL, [QM_TITLE] [char](72) DEFAULT '' NOT NULL, [QM_DELIV_CODE] [char](12) DEFAULT '' NOT NULL, [QM_CLT_SPEC] [char](12) DEFAULT '' NOT NULL, [QM_TAX_REF] [char](22) DEFAULT '' NOT NULL, [QM_CONTACT] [char](36) DEFAULT '' NOT NULL, [QM_PHONE] [char](22) DEFAULT '' NOT NULL, [QM_FAX] [char](22) DEFAULT '' NOT NULL, [QM_ORDER] [char](20) DEFAULT '' NOT NULL, [QM_ORDER_CFM] [smallint] DEFAULT 0 NOT NULL, [QM_ORDER_REL] [char](6) DEFAULT '' NOT NULL, [QM_REP] [char](12) DEFAULT '' NOT NULL, [QM_REP_1] [char](12) DEFAULT '' NOT NULL, [QM_REP_2] [char](12) DEFAULT '' NOT NULL, [QM_REP_3] [char](12) DEFAULT '' NOT NULL, [QM_REP_4] [char](12) DEFAULT '' NOT NULL, [QM_REP_5] [char](12) DEFAULT '' NOT NULL, [QM_COORDINATOR] [char](12) DEFAULT '' NOT NULL, [QM_PRIORITY] [smallint] DEFAULT 0 NOT NULL, [QM_TYPE_CODE] [char](12) DEFAULT '' NOT NULL, [QM_GRADE] [smallint] DEFAULT 0 NOT NULL, [QM_FIN_SIZE_CODE] [char](12) DEFAULT '' NOT NULL, [QM_FIN_WID] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_LEN] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_DEP] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_GUSS] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_GSM] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_ORIENT] [smallint] DEFAULT 0 NOT NULL, [QM_PROD_CODE] [char](22) DEFAULT '' NOT NULL, [QM_FIN_GOOD] [char](22) DEFAULT '' NOT NULL, [QM_CUST_CODE] [char](12) DEFAULT '' NOT NULL, [QM_CUST_CODE_1] [char](12) DEFAULT '' NOT NULL, [QM_CUST_CODE_2] [char](12) DEFAULT '' NOT NULL, [QM_CUST_PROS] [smallint] DEFAULT 0 NOT NULL, [QM_REQD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REQD_TIME] [int] DEFAULT -1 NOT NULL, [QM_FOLIO] [char](12) DEFAULT '' NOT NULL, [QM_FOLIO_1] [char](12) DEFAULT '' NOT NULL, [QM_FOLIO_2] [char](12) DEFAULT '' NOT NULL, [QM_PACK_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_USAGE] numeric(26,8) DEFAULT 0 NOT NULL, [QM_REORDER] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_EACH_WGT] numeric(26,8) DEFAULT 0 NOT NULL, [QM_WGT_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_RFQ_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_RFQ_TIME] [int] DEFAULT -1 NOT NULL, [QM_RFQ_OPR] [char](6) DEFAULT '' NOT NULL, [QM_SALES_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_SALES_SRC] [char](12) DEFAULT '' NOT NULL, [QM_SALES_RSN] [char](12) DEFAULT '' NOT NULL, [QM_PROFILE] [char](12) DEFAULT '' NOT NULL, [QM_JOB_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_PREV_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_JOB_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_PO_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_PO_TIME] [int] DEFAULT -1 NOT NULL, [QM_PO_OP] [char](6) DEFAULT '' NOT NULL, [QM_DLY_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_DLY_TIME] [int] DEFAULT -1 NOT NULL, [QM_QTY_DESP] numeric(26,8) DEFAULT 0 NOT NULL, [QM_TOTAL_DLY] [int] DEFAULT 0 NOT NULL, [QM_SCHED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_SCHED_TIME] [int] DEFAULT -1 NOT NULL, [QM_CLOSE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CLOSE_TIME] [int] DEFAULT -1 NOT NULL, [QM_INV_NUM] [char](12) DEFAULT '' NOT NULL, [QM_PACK_NUM] [char](12) DEFAULT '' NOT NULL, [QM_DOWN_LOAD] [smallint] DEFAULT 0 NOT NULL, [QM_TRACK_CODE] [char](4) DEFAULT '' NOT NULL, [QM_TAX_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_TAX_CODE] [char](6) DEFAULT '' NOT NULL, [QM_CURR] [char](6) DEFAULT '' NOT NULL, [QM_EXCH_RATE] numeric(18,8) DEFAULT 0 NOT NULL, [QM_UNIT_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_UNIT_FLAG] [smallint] DEFAULT 0 NOT NULL, [QM_RUNON_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_SPEC_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_CHARGEABLE] [smallint] DEFAULT 0 NOT NULL, [QM_NC_REASON] [char](22) DEFAULT '' NOT NULL, [QM_CUST_MKUP] numeric(18,8) DEFAULT 0 NOT NULL, [QM_JOB_MKUP] numeric(18,8) DEFAULT 0 NOT NULL, [QM_BROKERAGE] numeric(18,8) DEFAULT 0 NOT NULL, [QM_CUST_DISC] numeric(18,8) DEFAULT 0 NOT NULL, [QM_INVOKED_BTNS] [int] DEFAULT 0 NOT NULL, [QM_IMPORTED] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_RECALC] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_CONVERT] [smallint] DEFAULT 0 NOT NULL, [QM_BRANCH] [char](6) DEFAULT '' NOT NULL, [QM_CODE] [char](36) DEFAULT '' NOT NULL, [QM_TEMPLATE] [smallint] DEFAULT 0 NOT NULL, [QM_REPEAT_PERIOD] [int] DEFAULT 0 NOT NULL, [QM_REOPEN_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CAT_OPTION] [char](16) DEFAULT '' NOT NULL, [QM_UNIT_ID] [char](10) DEFAULT '' NOT NULL, [QM_PROD_BRANCH] [char](6) DEFAULT '' NOT NULL, [QM_UID] [int] DEFAULT 0 NOT NULL, [QM_AVAIL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_AVAIL_TIME] [int] DEFAULT -1 NOT NULL ) ON [PRIMARY]
GO
-- Create an index on the table
CREATE unique INDEX [QM_NUMBER_ORDER] ON tmp([QM_QUOTE_JOB], [QM_NUMBER]) ON [PRIMARY]
GO
-- Populate the table
declare @Counter as int
SET NOCOUNT ON
set @Counter = 1
while @Counter < 100000
begin
insert into tmp (QM_ADD_TIME, QM_NUMBER) values (1,@Counter);
set @Counter = @Counter + 1
end
GO
-- Update QM_UID to a sequential value
declare @NextID int;
set @NextID = 1;
update tmp set QM_UID=@NextID, @NextID = @NextID + 1;
-- Find rows with a duplicate QM_UID (there should be no duplicate)
select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID
--drop table tmp
-- output from select @@VERSION
-- Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I have a query which returns the movements to and from our warehouse stock, as well as the current stock for each depot and how much is on order. What I need is a kind of pivot so that each item is shown just once, and then summarises the movements in 4 extra columns: Last 30 days, 30-60 days, 60-90 days and 90-120 days. How can I achieve this with my query below? A sample of some of the results is also shown.
I want to save every query executed from a given software, let's say Multi Script for example, and save in a table query text, execution time and rows count among other possible useful information. Right now I've created a sp and a job that runs every 1 milliseconds but I can't figure out how to get execution time and rows count. Another problem with this is that if the query takes too long I end up with several rows in my table.
Which works fine for all my other calculations except this, where I need a distinct count. CNT is a calculated measure. The browser would look like this:
Category Year1 Year2 ..... MDX what I have now MDX what I need
How do you build a query to calculate the time of employement or any space of time for each person in a table? I'm thinking I would want to use a "Current Date" and "Select Datediff" calculations of some sort and reference the hiredate against the current date but I have been unable to find much on this type of query.. Please help..
HiI am hoping someone might be able to help me out with this.I am writing a helpdesk system which records agents logging in and outof the system.I need to write a stored procedure which will show the peak number ofagents logged in concurrently during a specified time period. Withinthe time period, the person viewing the report should be able tospecify intervals at which to see the data.Eg. There is already a table in the system which holds loggedin/logged out data like22/11/2004 14:02 - 22/11/2004 17:3022/11/2004 09:00 - 22/11/2004 17:1222/11/2004 10:25 - 22/11/2004 16:3022/11/2004 11:02 - 22/11/2004 12:3022/11/2004 16:00 - 22/11/2004 17:30The report user can then say for example they want to view databetween 10th November and 12th November broken down into 15 minutesintervals which would produce a table like this:10/11/2004 00:00 - 10/11/2004 00:1510/11/2004 00:15 - 10/11/2004 00:3010/11/2004 00:30 - 10/11/2004 00:4510/11/2004 00:45 - 10/11/2004 01:00 etc etcAgainst each of these time slots, I need to work out the peak numberof concurrent agents logged in using the first table.Can anyone make any suggestions? The time period the report user canchoose are either 15 mins, 30 mins, 45 mins, 1 hour and 1 day.Thanks in advance
There are a lot of questions posted on SQLTEAM asking how to find the beginning of various time periods. The script will create and demo 14 functions that return a datetime for the beginning of a time period relative to the datetime value passed in parameter @DAY.
I put together this script to create these functions for several reasons: 1. To allow people to find them on their own without having to post a question. 2. To allow posted questions to be answered with a reference to this script. 3. To document algorithms that work for the widest possible range of datetime values. All except for the Century and Decade functions work for any datetime value from 1753/01/01 00:00:00.000 through 9999/12/31 23:59:59.997. The Century is limited to datetimes from 1800/01/01 forward, because 1700/01/01 is not valid in SQL Server. The Decade function is limited to datetimes from 1760/01/01 forward, because 1750/01/01 is not valid in SQL Server. 4. And last, you can actually use them on your application.
There is a separate post for function dbo.F_START_OF_WEEK to find the first day of the week at this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
This script was tested with SQL Server 2000 only.
I posted a script for End Date of Time Period Functions here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
Other Date/Time Info and Script Links: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
Edit 2006-11-01: Added dbo.F_START_OF_X_MIN( @DAY ) at the suggestion of Peter.
Edit 2007-02-24: Modified the following functions to use a simpler algorithm, that is shorter, runs faster, and is more suited for use with in-line code: dbo.F_START_OF_30_MIN( @DAY ) dbo.F_START_OF_20_MIN( @DAY ) dbo.F_START_OF_15_MIN( @DAY ) dbo.F_START_OF_10_MIN( @DAY ) dbo.F_START_OF_05_MIN( @DAY )
*/ go if objectproperty(object_id('dbo.F_START_OF_CENTURY'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_CENTURY end go create function dbo.F_START_OF_CENTURY ( @DAY datetime ) returns datetime as /* Function: F_START_OF_CENTURY Finds start of first day of century at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes >= 1800-01-01 00:00:00.000 Returns null if @DAY < 1800-01-01 00:00:00.000 */ begin
end go if objectproperty(object_id('dbo.F_START_OF_DECADE'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_DECADE end go create function dbo.F_START_OF_DECADE ( @DAY datetime ) returns datetime as /* Function: F_START_OF_DECADE Finds start of first day of decade at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes >= 1760-01-01 00:00:00.000 Returns null if @DAY < 1760-01-01 00:00:00.000 */ begin
end go if objectproperty(object_id('dbo.F_START_OF_YEAR'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_YEAR end go create function dbo.F_START_OF_YEAR ( @DAY datetime ) returns datetime as /* Function: F_START_OF_YEAR Finds start of first day of year at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(yy,datediff(yy,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_QUARTER'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_QUARTER end go create function dbo.F_START_OF_QUARTER ( @DAY datetime ) returns datetime as /* Function: F_START_OF_QUARTER Finds start of first day of quarter at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(qq,datediff(qq,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_MONTH'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_MONTH end go create function dbo.F_START_OF_MONTH ( @DAY datetime ) returns datetime as /* Function: F_START_OF_MONTH Finds start of first day of month at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(mm,datediff(mm,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_DAY'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_DAY end go create function dbo.F_START_OF_DAY ( @DAY datetime ) returns datetime as /* Function: F_START_OF_DAY Finds start of day at 00:00:00.000 for input datetime, @DAY. Valid for all SQL Server datetimes */ begin
return dateadd(dd,datediff(dd,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_HOUR'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_HOUR end go create function dbo.F_START_OF_HOUR ( @DAY datetime ) returns datetime as /* Function: F_START_OF_HOUR Finds beginning of hour for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
return dateadd(hh,datediff(hh,0,@DAY),0)
end go if objectproperty(object_id('dbo.F_START_OF_30_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_30_MIN end go create function dbo.F_START_OF_30_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_30_MIN Finds beginning of 30 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_20_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_20_MIN end go create function dbo.F_START_OF_20_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_20_MIN Finds beginning of 20 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_15_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_15_MIN end go create function dbo.F_START_OF_15_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_15_MIN Finds beginning of 15 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_10_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_10_MIN end go create function dbo.F_START_OF_10_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_10_MIN Finds beginning of 10 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_05_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_05_MIN end go create function dbo.F_START_OF_05_MIN ( @DAY datetime ) returns datetime as /* Function: F_START_OF_05_MIN Finds beginning of 5 minute period for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_X_MIN'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_X_MIN end go create function dbo.F_START_OF_X_MIN ( @DAY datetime, @INTERVAL int ) returns datetime as /* Function: F_START_OF_X_MIN Finds beginning of @INTERVAL minute period for input datetime, @DAY. If @INTERVAL = zero, returns @DAY. Valid for all SQL Server datetimes. */ begin
-- Prevent divide by zero error if @INTERVAL = 0 return @DAY
declare @BASE_DAY datetime set @BASE_DAY = dateadd(dd,datediff(dd,0,@Day),0)
end go if objectproperty(object_id('dbo.F_START_OF_MINUTE'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_MINUTE end go create function dbo.F_START_OF_MINUTE ( @DAY datetime ) returns datetime as /* Function: F_START_OF_MINUTE Finds beginning of minute for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
end go if objectproperty(object_id('dbo.F_START_OF_SECOND'),'IsScalarFunction') = 1 begin drop function dbo.F_START_OF_SECOND end go create function dbo.F_START_OF_SECOND ( @DAY datetime ) returns datetime as /* Function: F_START_OF_SECOND Finds beginning of second for input datetime, @DAY. Valid for all SQL Server datetimes. */ begin
insert into @test_dates (DT) select DT = getdate()union all select '17530101 00:00:00.000'union all -- Test start of Decade cutoff select '17591231 23:59:59.997'union all select '17600101 23:04:59.997'union all -- Test start of Century cutoff select '17991231 23:59:59.997'union all select '18000101 00:00:00.000'union all -- Test start of Decade and Century select '19000101 00:00:00.000'union all select '19001231 23:59:59.997'union all select '19400101 00:00:00.000'union all select '19491231 23:59:59.997'union all select '19900101 00:00:00.000'union all select '19991231 23:59:59.997'union all -- For start of Hour testing select @lyear+' 00:00:00.000'union all select @lyear+' 00:59:59.997'union all select @lyear+' 01:00:00.000'union all select @lyear+' 01:59:59.997'union all select @lyear+' 12:00:00.000'union all select @lyear+' 12:59:59.997'union all select @lyear+' 17:00:00.000'union all select @lyear+' 17:59:59.997'union all select @lyear+' 23:00:00.000'union all select @lyear+' 23:59:59.997'union all -- For start of Month, Quarter, and Year testing select @year+'0101 00:00:00.000'union all select @year+'0131 23:59:59.997'union all select @year+'0201 00:00:00.000'union all select @year+'0228 23:59:59.997'union all select @year+'0301 00:00:00.000'union all select @year+'0331 23:59:59.997'union all select @year+'0401 00:00:00.000'union all select @year+'0430 23:59:59.997'union all select @year+'0501 00:00:00.000'union all select @year+'0531 23:59:59.997'union all select @year+'0601 00:00:00.000'union all select @year+'0630 23:59:59.997'union all select @year+'0701 00:00:00.000'union all select @year+'0731 23:59:59.997'union all select @year+'0801 00:00:00.000'union all select @year+'0831 23:59:59.997'union all select @year+'0901 00:00:00.000'union all select @year+'0930 23:59:59.997'union all select @year+'1001 00:00:00.000'union all select @year+'1031 23:59:59.997'union all select @year+'1101 00:00:00.000'union all select @year+'1130 23:59:59.997'union all select @year+'1201 00:00:00.000'union all select @year+'1231 23:59:59.997'union all -- Test start of 5, 10, 15, 20, and 30 min testing select @today+' 23:04:59.997'union all select @today+' 23:09:59.997'union all select @today+' 23:14:59.997'union all select @today+' 23:19:59.997'union all select @today+' 23:24:59.997'union all select @today+' 23:29:59.997'union all select @today+' 23:34:59.997'union all select @today+' 23:39:59.997'union all select @today+' 23:44:59.997'union all select @today+' 23:49:59.997'union all select @today+' 23:54:59.997'union all select @today+' 23:59:59.997'union all select '99991231 23:59:59.997' order by 1
-- Convert dates in @test_dates table to test F_START_OF functions
select TYPE = 'CENTURY' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_CENTURY( DT ),121) from @test_dates order by DT
select TYPE = 'DECADE' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_DECADE( DT ),121) from @test_dates order by DT
select TYPE = 'YEAR' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_YEAR( DT ),121) from @test_dates order by DT
select TYPE = 'QUARTER' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_QUARTER( DT ),121) from @test_dates order by DT
select TYPE = 'MONTH' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_MONTH( DT ),121) from @test_dates order by DT
select TYPE = 'DAY' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_DAY( DT ),121) from @test_dates order by DT
select TYPE = 'HOUR' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_HOUR( DT ),121) from @test_dates order by DT
select TYPE = '30_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_30_MIN( DT ),121) from @test_dates order by DT
select TYPE = '20_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_20_MIN( DT ),121) from @test_dates order by DT
select TYPE = '15_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_15_MIN( DT ),121) from @test_dates order by DT
select TYPE = '10_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_10_MIN( DT ),121) from @test_dates order by DT
select TYPE = '05_MIN' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_05_MIN( DT ),121) from @test_dates order by DT
select TYPE = 'MINUTE' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_MINUTE( DT ),121) from @test_dates order by DT
select TYPE = 'SECOND' , DT = convert(varchar(23),DT,121), FUNCTION_RESULT = convert(varchar(23),dbo.F_START_OF_SECOND( DT ),121) from @test_dates order by DT /* End of test script */
Can anyone offer any advice. I was wondering whether MS SQL server offeredany facilities to trigger an event after a period of time had lapsed? e.g.once a week check data and if it is out of date email an related address.many thanks
I am using SQL Server 2000. I need to query my database for all thecontracts that came in during a certain time frame (user is promptedfor reportingperiodid).Table - PeriodsFields - Reporting Period id intReporting Period desc varchar(30)Reporting Period Begin Date datetimeReporting Period End Date datetimeIf the user selects a 3 then the begin date is Jan. 1, 2004 and theend date is June 30, 2004.Now I need to calculate did any money come in for each week in thattime frame. I need to create a weekly list of all the weeks in thattime frame. Each time frame begins on a Monday. So my list wouldlook like1/5/20041/12/20041/19/20041/26/2004All the way to the end of that time period.How do I create this weekly list from a given time period using T-SQL?I would appreciate any and all help on this.Thanks,Tony
We are having 8 databases mirrored with witness server, by default automatic failover occure in 10 secs if witness does not get a ping from a principal server. If there any way to increase the failover time perioed.
