Transact SQL :: Query To Get Web Page Hits On Website
Sep 24, 2015I'm trying to piece together some code on how to find members of our organization who have visited our website.
View 2 Replies
ADVERTISEMENT
Transact SQL :: Output Data To Confluence Page
Jul 8, 2015We are using Confluence 5.6.5; essentially I want to output some data from a SQL Server 2012 Database onto a Confluence page; how to go about achieving this?
View 3 Replies View RelatedRetrieve Even If No Hits?
Apr 29, 2005Hello!
is it possible to have the count(*) to display 0 when there is no matching hits for that n_id?
my query looks like this but only displays the n_id and it's respective count(*) when count(*) is more than 0...
select n_id, count(*) from tblTable
where nSomething > nSomethingElse AND nSomething IS NOT NULL
group by n_id
any idaes?
When DTS Hits A Bad Date
Aug 24, 2006This routine works in most cases, but fails when a bad date is enteredsuch as:19910631 -- there is no June 31st.Instead of ignoring the bad date the entire DTS job fails. Obviouslythis is something that should be validated at data entry, butunfortunately the only control I have is when appending to the tablewith these data quirks. Any suggestions appreciated!!!'************************************************* *********************' Visual Basic Transformation Script' Copy each source column to the' destination column'************************************************* ***********************Function Main()'DTSDestination("Col002") = DTSSource("Col002")if DTSSource("Col002") = "99999999" or DTSSource("Col002") =Null thenMain = DTSTransforStat_SkipRowelseDTSDestination("Col002") = mid(DTSSource("Col002"),1,4) & "/"&mid(DTSSource("Col002"),5,2) & "/" & mid(DTSSource("Col002"),7,2)End ifMain = DTSTransformStat_OKEnd FunctionRBollinger
View 3 Replies View RelatedUpdating Hits Without Looping
Sep 6, 2007Hi.
This is my first attempt at a using stored procedures and I'm a bit confused.
I'm trying to follow as many best practices as I can to improve speed, security and scalability. However, I can't find a solution to what I think should be a simple problem.
I have a search page where users enter the criteria of properties they are interested in (bedrooms, price etc...). That takes them to a results page where the properties are displayed.
The problem is that I want the amount of times each property has been shown on the results page to be tracked so the property owner gets statistics.
The property details are all held in a single table along with the amount of times each property has be shown:
Table Name: zk_Property_USA
ID
INT
User_ID
INT
Property_Type
TINYINT
Market_Status
TINYINT
Price
INT
Bedrooms
TINYINT
Address_State
VARCHAR
Address_Location
VARCHAR
Property_Description
VARCHAR
Searched
INT
0
Contacted
INT
0
I'm trying to find a way to SELECT all the property details to be returned to my results page and UPDATE the "Searched" field by 1 without re-scanning the table for the UPDATE.
Is there a way to update "Searched" at the time when the record is chosen to be a result? I am using SQL Server 2005 and Visual Basic ASP.NET 2.0.
Many Thanks
Monitoring Database Hits
Nov 8, 2007In any asp.net application, whats the simplest way to monitor how many times a page hits the database (opens and closes a connection)?
View 1 Replies View RelatedLookup With Multiple Hits
Sep 27, 2007In a Lookup component I've defined a SQL query which returns a sorted resultset. For each Lookup component input row I want to have a single output row. Problem is that for each input row there is possibility of multiple matches in SQL query resultset. From all of the possible multiple hits I want only the first one to be returned, and if no match is found then no output row. How to implement this?
View 10 Replies View RelatedCan I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net
Mar 1, 2007Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
Hits Table And Counter Reports
Jan 22, 2008Using MSSQL (pre 2005):
I have a Link table:
int id (primary key/identity)
varchar(50) linkName
varchar(255) linkHref
//some other stuff
and my Hits table:
int id (primary key/identity)
int linkId (foreign key to Link)
datetime dateCreated
//some other stuff
Hits gets an insert whenever a link is clicked. (All this works just fine)
I'm trying to create a report that shows each link by its name and href, a counter and the last date each link was visited. What I currently have is an accurate listing for those that have been clicked on, but it does not show anything for links that haven't been clicked on. Any suggestions as to how I can modify the following SQL to return "0" and "never" (or DBNULL) if no entries are found in Hits that have the same id? Or do I have to do this in a couple queries?
SELECT COUNT(h.id) as counter, MAX(h.dateCreated) as lastVisited,
l.id as id, l.linkName as linkName, l.linkHref as linkHref
FROM Link as l INNER JOIN Hits as h ON l.id=h.moduleId
GROUP BY l.linkName, l.id, l.linkHref
Getting Estimated Total Hits Of Fulltextsearch
Apr 20, 2006Hello,
i am making a Fulltextsearch on MS SQL Server 2005 (indexed, with "Contains").
Because of performance reasons i am only showing the first 200 rows mssql finds ("select top 200...:").
Is there any possibility to get the estimated totalnumber of all rows? i have heard something that is possible to get this in mssql-server. The server then estimates how many rows with that searchword could be in the whole database. google i.e. makes the same thing....
is that true? what do i have to do to get this?
greetings and thx
cpt.oneeye
Capturing Client Logins And Hits
Apr 1, 2002I'd like to capture the avg. # of user logins and # db hits per a 5 interval for a weeks time. I'm guessing there are sys tables containing this info. and by using temporary tables and/or creating/modifying SPROCS this info. can be retrieved. If I'm on the right track, a little direction would be very appreciated. If I'm not on track, please assist this rookie dba.
Thank you,
Eoin
Tracking/Reporting Record Hits.
Feb 7, 2008I am serving ad-units. In each ad-unit I show somewhere between 3 and 10 article headline. I track the headline impressions to get an idea of the headline click through rate. I save the output from the stats process in another table. I am currently evaluating the stats every hour, and then truncating the table every night at midnight. The problem is that I get lots of impressions and the database gets bogged down evaluating the data such as...
SELECT COUNT FROM articleimpressions WHERE articleid = x
But the issue isn't the reporting of the data...so much as it's the capture. I had to add caching on the ad-server because the database couldn't handle the number of inserts.
I thought about parsing the web server log files the next day, but the file sizes seem to be too large, and I can't process them all in one day. (At least not on the hardware that I am using.)
I've thought about splitting log files by hour, but was wondering if there may be a more "native" solution within SQL Server? Maybe a trigger, and/or multi-threaded SP that fires and forgets an insert statement to a linked server. But performance is the key here.
Thoughts?
Sort Results By Number Of Hits
Jul 23, 2005In MSSQL, is there a way to count the number of instances of asubstring within a string, so that I can sort by that?For example:table tst contains one column: tst_dataif tst_data = "the man with the plan"I'd want a function that counts the occurances of "the"count_substring(tst_data,'the') = 2Basically, I'm making a search engine and I'd like to put the mostrelavent hits at the top of the page.
View 2 Replies View RelatedNeed Clever Way To Count Outside Hits In Sql Table
Dec 19, 2007hi . would greatly appreciate a clever way to make an internal "counter" on an sql table that contains 6000 articles.(one in each row). these are being accessed from a web page and i would like to know which are the most read ones. so i thought of adding a column that would in some way count each time that the row is being accessed. is there a way for this to be done?? please be detailed since i am quite new to all this.
thanks beforehands.
How To Handle Users Hits To Single Table
Feb 4, 2008Hello friends
I am new to SQL database design.I am having a table name customerletter_master in which all details of customer letters are saved.At a time i.e in single second around 200- 400 entry are made in customerletter_master table. Hits to customerletter_master table are more.
Following fields are used in customerletter_master – CID (autogen number), letterno(primary key),consignee, consignor, letterstatus1, letterstatus2, letterstatus3, POD, and some more fields. When letter passes to different stages letterstatus1 is filled to yes and then letterstatus2 and letterstatus3 are filled according to passage of letter.
And at same time many user can accesses the customerletter_master table to search any letter according to letterno. Therefore customerletter_master is used to enter data and to search data at same time and there can be more than 200-400 users doing add and search records to and from customerletter_master
how should i design this table. What should i use to enter and search record and minimize the table hits made by the user at same time. Should i use store procedures or any other method to add and search record. Plz help me out by giving some example.
Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!
Mar 28, 2007Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
Multiple Database Hits Vs Bulk Data Parameters
Apr 11, 2006I was curious to know if it the amount of data sent to the sql server mattered.
I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).
So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.
Thanks
Reporting Services :: All Record Are Displaying On One Page - How To Display Page By Page
Nov 11, 2015I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
View 3 Replies View RelatedNested CASE WHEN - Create A Field To Show If Measure Hits Threshold Or Not To Be Later Picked Up In SSRS
Oct 28, 2015I am trying to script a case when to achieve the following.
I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?
CASE WHEN M.[Threshold Direction] = '>' THEN
CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN'
CASE WHEN A.[Value] < M.[Threshold] THEN 'RED'
ELSE ''
END END END AS 'Condition'Is this at all possible?
Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns
Jul 30, 2015For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
How To Run An Asp Page From Sql Query
May 28, 2008Hi,
I want to run an asp file from a query written in sql 2005.
How can I achieve this in sql?
Please let me know its very urgent.
SQL Query From Web Page?
Sep 9, 2004Hi, I have an SQl database that i want to build a simple search query for that would be accessible from an HTML page, what would be the best way of going about this? and what would be the best language to use?
Thanks in advance
Page 2 - HELP With SQL Query - (complicated)
Oct 10, 2006You can display both by referring to their position in the recordset or by aliasing them in the query and referring to the alias in your code.
Note that in order to put Products.Quantity or ProductDetails.Quantity in just one column you'll have to add some logic in the query, using a coalesce, like:
sql Code:
Original
- sql Code
SELECT
Products.ProductID, Products.ProductName, Products.ProductPict,
COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity,
ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_Color
FROM
Products
LEFT OUTER JOIN
ProductDetails
ON Products.ProductID = ProductDetails.ProductID
WHERE
Products.Quantity <= "& +rsResults__var1 & "
OR
ProductDetails.Quantity <= "& +rsResults__var1 & "
Order by Products.ProductID asc;
SELECT Products.ProductID, Products.ProductName, Products.ProductPict, COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity,ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_ColorFROM Products LEFT OUTER JOIN ProductDetails ON Products.ProductID = ProductDetails.ProductID WHERE Products.Quantity <= "& +rsResults__var1 & " OR ProductDetails.Quantity <= "& +rsResults__var1 & " ORDER BY Products.ProductID ASC;
Untested, beware of syntax errors!
In this case you'll have just one Quantity colum and you won't have to change anything in your asp code.
Query For Page Views
Apr 28, 2008I have a table which records the amount of times a user views a webpage
tblpageview
id
pagename
view_date
userid
I would like to have a query that returns the unique pagename and the amount of times that page has been viewed. I've had a go with count etc. but cant seem to get it. Anyone can help?
Page 2 - Query Problem
Sep 6, 2007Hmm... the query should return the number of athletes with scores >= to your own.
So if you have the highest score in the world, for example, then the only score >= to your own is... yours. The count of that is 1, giving you rank 1.
If the order seems backwards to you just reverse the <= operator in the WHERE clause. Either way, as a responsible developer, never never never put a SQL query you don't understand into production.
Fixed the ServerID thing, too. It was there because I don't have a table that matches yours and wrote the query against one with a compatible structure, but different names.
Transact SQL :: Use Query Results As Select Criteria For Another Query
Jul 10, 2015I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
Transact SQL :: SELECT On Column Name From Query Result Set In Same Query?
May 9, 2015I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB Â Â Â Â 123
colA Â Â Â Â XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
Transact SQL :: Query Plan Shows Table Not Even In Query?
Jul 22, 2015I am trying to optimize a stored procedure in SQL 2008. Â When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.
I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? Â I can't just change the query script because the table in question isn't there.
I Need To Pass Data Entered /created On The First Page To The Next Page And Populate The Next Page With Some Data From The Fir
Nov 28, 2006Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.
When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.
I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)
I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.
Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦
Urgent Converting A Query To A .asp Page
Aug 24, 2004I am extremely new with .asp. We have the .net framework 1.1 installed on our server and the following query works in Crystal Reports (older version). We would like to have this converted to a .asp page with two inputs. One for the date range and one for the state. Can someone please tell me how to go about this. I do have downloaded as test. Visual Studio 2003.net and Web Matrix. I really am looking for (if there is any) a way to pretty quickly convert query to web pages so users can use them without a huge expense. IF someone already has something like this done. That would be great. The following query is for Visual Enterprise (Manufacturing software). Thanks
SELECT
SHIPPER."CUST_ORDER_ID", SHIPPER."SHIPPED_DATE", SHIPPER."INVOICE_ID",
SHIPPER_LINE."USER_SHIPPED_QTY", SHIPPER_LINE."SHIPPED_QTY", SHIPPER_LINE."UNIT_PRICE",
RECEIVABLE_CURR."CURRENCY_ID", RECEIVABLE_CURR."SELL_RATE",
CUST_ORDER_LINE."PART_ID", CUST_ORDER_LINE."ORDER_QTY", CUST_ORDER_LINE."USER_ORDER_QTY",
INVENTORY_TRANS."TYPE", INVENTORY_TRANS."ACT_MATERIAL_COST", INVENTORY_TRANS."ACT_LABOR_COST", INVENTORY_TRANS."ACT_BURDEN_COST", INVENTORY_TRANS."ACT_SERVICE_COST",
CUSTOMER_ORDER."CUSTOMER_ID", CUSTOMER_ORDER."SHIP_TO_ADDR_NO", CUSTOMER_ORDER."CURRENCY_ID",
PART."DESCRIPTION", PART."STOCK_UM",
CUSTOMER."STATE",
CUST_ADDRESS."ADDR_NO", CUST_ADDRESS."NAME", CUST_ADDRESS."STATE"
FROM
{ oj ((((((("VMFG"."dbo"."SHIPPER" SHIPPER INNER JOIN "VMFG"."dbo"."RECEIVABLE_CURR" RECEIVABLE_CURR ON SHIPPER."INVOICE_ID" = RECEIVABLE_CURR."INVOICE_ID") INNER JOIN "VMFG"."dbo"."SHIPPER_LINE" SHIPPER_LINE ON SHIPPER."PACKLIST_ID" = SHIPPER_LINE."PACKLIST_ID" AND SHIPPER."CUST_ORDER_ID" = SHIPPER_LINE."CUST_ORDER_ID") INNER JOIN "VMFG"."dbo"."INVENTORY_TRANS" INVENTORY_TRANS ON SHIPPER_LINE."TRANSACTION_ID" = INVENTORY_TRANS."TRANSACTION_ID") INNER JOIN "VMFG"."dbo"."CUSTOMER_ORDER" CUSTOMER_ORDER ON SHIPPER_LINE."CUST_ORDER_ID" = CUSTOMER_ORDER."ID") INNER JOIN "VMFG"."dbo"."CUST_ORDER_LINE" CUST_ORDER_LINE ON SHIPPER_LINE."CUST_ORDER_ID" = CUST_ORDER_LINE."CUST_ORDER_ID" AND SHIPPER_LINE."CUST_ORDER_LINE_NO" = CUST_ORDER_LINE."LINE_NO") INNER JOIN "VMFG"."dbo"."CUSTOMER" CUSTOMER ON CUSTOMER_ORDER."CUSTOMER_ID" = CUSTOMER."ID") INNER JOIN "VMFG"."dbo"."PART" PART ON CUST_ORDER_LINE."PART_ID" = PART."ID") LEFT OUTER JOIN "VMFG"."dbo"."CUST_ADDRESS" CUST_ADDRESS ON CUSTOMER_ORDER."CUSTOMER_ID" = CUST_ADDRESS."CUSTOMER_ID" AND CUSTOMER_ORDER."SHIP_TO_ADDR_NO" = CUST_ADDRESS."ADDR_NO"}
WHERE
SHIPPER."SHIPPED_DATE" >= {ts '2002-08-01 00:00:00.00'} AND
SHIPPER."SHIPPED_DATE" < {ts '2004-08-25 00:00:00.00'}
ORDER BY
SHIPPER."SHIPPED_DATE" ASC
Page 2 - Monsterous SQL Query... Suggestions?
Jan 18, 2008Here you go:
The telephone numbers are just strings of the same integer,
1's: Calls made every day
2's: Calls made every day
3's: Two, non consecutive days without calls
4's: No calls made from 2nd till 6th
5's: Only one day with calls
6's: No calls made from 2nd to 6th, but many made on the 1st
7's: No calls made from 2nd till 13th
If you'd like a bigger sample set then I will create one.
Code:
CREATE TABLE tblCallSummary(
[CallID] [int] IDENTITY(1,1) NOT NULL,
[TelephoneNumber] [varchar](11) NOT NULL,
[StartTime] [smalldatetime] NOT NULL,
[Duration] [int] NOT NULL,
CONSTRAINT [PK_tblCallSummary] PRIMARY KEY CLUSTERED
(
[CallID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-02'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-03'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-04'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-05'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-06'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-31'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-02'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-03'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-04'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-05'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-06'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-31'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-02'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-03'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-05'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-31'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-31'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('55555555555',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-31'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-31'),1)
Our requirements have just changed and we only need customers which were active in the last five days, a very easy query. So please don't spend too much of your time on this now, however, I am still keen to find an elegant way of solving this problem, if one exists so any input from you would be appreciated.
Thanks for you help
Dave
Page 2 - Some Assistance With Query Needed -_-
Oct 26, 2004Code:
select *
from linkboxes l
left outer
join subjects s
on l.sub_link_id
= s.sub_id
left outer
join external_links e
on l.ext_link_id
= e.ext_id
where l.sub_id = subid
Front Page 2002 And SQL Query
Jul 23, 2005I am using a very basic query in Front Page 2002 against a SQL table inSQL 2000SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)and f_docnumber is a search form field.When I run I getDescription: The precision is invalid.Number: -2147467259 (0x80004005)The field is defined in the table as numberic 9(10,0)Any ideas?
View 1 Replies View Related