Transact SQL :: Number Of Distinct Users In Last 3 / 6 / 9 / 12 Months
Aug 26, 2015
I have table A(year int, month int, user varchar(20)), and I am trying to write a view that would show number of distinct users in the last 3 months, last 6 months, last 9 months and last 12 months(all from the most recent year/month) in following format:
3 months 6 months 9 months 12 months
number of distinct users x y z w
View 20 Replies
ADVERTISEMENT
Aug 27, 2015
I need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth)
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'
For the above table, the answer should be:
ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01
1) don't include an ID column,
2) don't use the start date/end dates in the data or
3) use cursors, which are forbidden in my environment.
View 9 Replies
View Related
Oct 15, 2014
I have a simple query which returns a purchase date plus the number of months warranty an item has. What I need to do is add these months to the purchase date to get a warranty expiry date. What is the best way to do this? My query and results are below, I need an extra column in the results with that calculated warranty date:
select item, purchase_date, warranty_period
from inventory where item like '05MC%'
item purchase_date warranty_period
-------------------- ----------------------- ---------------
05MC0001 2014-07-22 00:00:00 36
05MC0002 2014-07-11 00:00:00 36
View 3 Replies
View Related
Jul 12, 2007
if
(datalength
(cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD])
) = 1)
then
(0 ||
cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD]))
else
(cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD]))
Goal:
To be able to tell the Period number ([PD]) to change from an integer to a var_char so that it can be concatenated with the Fiscal Year. I.e. Fiscal Year || Period number to produce the following results.
2006 01
2006 02
2006 03
Etc……
Status of Problem:
The issue is that I can only get it done to the point to where the Fiscal Year || Period number yields the following results:
20061
200610
200611
200612
20062
Etc….
This is as issue since I am trying to get all the information from the past to a certain year and period. Using a filter that says
Fiscal Year + Period Number <= ?Prompt_Fiscal_Year? + ?Prompt_Period_Number?
So if I enter Year: 2007 Period 3
I should get:
2006 01
2006 02
2006 03
Etc…….
2006 11
2006 12
2007 01
2007 02
2007 03
Can you please let me know if my thinking is off or if my SQL is wrong, Thank you for your assistance in this matter.
View 1 Replies
View Related
Aug 5, 2013
I have a report that has 41 fields one of these fields is the Admit Date. I need to create a field that shows a count of Admits in the past 12 months .
View 6 Replies
View Related
Sep 2, 2015
I have a table with sample data as shown in the table below. for each month end date, I need to aggregate (sum) all the quantities for each product and customer and include the current month plus the past 12 months (including current month).
For example, for customer C123, P123 and for 7/31/2015, the query should add 7/31/2015 quantity and the quantity for 1/31/2015.
DECLARE @TEMP TABLE (Customer VARCHAR (10), Product VARCHAR(10), Month_end_date DATE, Quantity INT)
INSERT INTO @TEMP VALUES ('C123','P123','1/31/2015',10)
INSERT INTO @TEMP VALUES ('C124','P124','2/28/2015',20)
INSERT INTO @TEMP VALUES ('C125','P125','3/31/2015',30)
INSERT INTO @TEMP VALUES ('C126','P126','4/30/2015',40)
[Code] ....
the output for the above example should be
C123 P123 7/31/2015
70+10=80
is this possible?
View 10 Replies
View Related
Oct 14, 2015
I have a date into format YYYYMM. Data type is int. I need to make query where every time it will return me last 3 months, but without current one. For example I have data for months below
201510
201509
201508
201507
Query should return all records for 201509, 201508, 201507.
I was trying lot of solutions founded in internet like this one:
Date_Column >= DATEADD(MONTH, -3, GETDATE())
or
DATEDIFF(MONTH, my_date_column, GETDATE()) <= 3
but it doesn't work.
View 4 Replies
View Related
Jun 15, 2015
I am looking to calculate no of months between two dates which are in YYYYMM format.
Like no of months between 201505 and 201305
View 7 Replies
View Related
Oct 5, 2007
I have the following Table:DataIDuserIDTimeStampI need to create a query that selects the "newest" records for all distinct users. I'm hoping to get this done in one query. I know I could write multiple queries to find all distinct users, and then the newest records based on that... and so on... but I'm not sure that's the most efficient. I have the following so far, but is it the most efficient (I have indexing on most of my columns)? SELECT DATA.DataID, DATA.UserID, UN.vchName, UN.vchImage, U.vchFirstName + ' ' + U.vchLastName AS 'FullName' FROM [tblData] DATA INNER JOIN [tblUnits] UN ON DATA.UserID = Un.UserID INNER JOIN [tblusers] U ON U.UserID = UN.UserID WHERE GPS.intGPSDataID IN (SELECT MAX([tbLData].DataID) FROM [tbLData] GROUP BY [tbLData].UserID)
View 4 Replies
View Related
Oct 7, 2015
I have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012
What I want to know is the average number of months between the purchases. And do this for each customer.
View 6 Replies
View Related
May 26, 2015
I am trying to calculate how much revenue we may get, based on potential new business opportunities. The core fields we have are
Total Contract Value ($ or £)Duration of contract (months)Revenue start dateVarious information about the new business - ID, Title, Customer etc.
We can easily calculate the revenue per month with "Total Contract value divide by duration".
However what I would really like to do is be able to know how much revenue we will be getting each month.
To do this I was thinking we should probably create a new row for each month entry, with the mm-yyyy being the only difference for each row. But how to create the appropriate months and the correct amount of rows.
View 3 Replies
View Related
Jul 1, 2015
My goal is to show the most recent 12 months of data including the current month based on a patients discharge date (relative to the query execution time). There could be many years of data so I want to be sure I am pulling the most recent 12 monthsThis seems to work (on the surface anyway … not quite sure if this is the best logic to use).
DATEDIFF(month,PAT_ENC_HSP_discharged.HOSP_DISCH_TIME,
GEtDate())
< 13
View 5 Replies
View Related
Aug 20, 2015
I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.
MC refers to Maintenance.
MCLog data:
carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15
[Code] .....
Here is my Table:
CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));
[code]....
View 6 Replies
View Related
Oct 13, 2015
I get a column in hours in a table and when I try to convert it to months and days its giving me incorrect results.
My timehours column in my table has hours recorded timehours value is 5832 and I get results as 11 months and 27 days.
This query is giving me wrong result -select months=(timehours %365)/30,days= (timehours % 365)%30
View 13 Replies
View Related
May 28, 2015
I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'
How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from 05/16 and 05/09 and so on...
View 13 Replies
View Related
Aug 5, 2015
I have hours which can be like 32.5 and would like to have them in 1 month 2 Days format.
View 2 Replies
View Related
Jun 23, 2015
Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
View 4 Replies
View Related
Mar 19, 2008
Wondering if there is a way to do this...
I want to select the DISTINCT user name from each record in a table, and then have another field tell me how many records it found..
is there any way to do this with SQL?
SELECT DISTINCT user_Name FROM table ORDER BY user_Name
is what I have now....
need something that will return:
user_Name numRecs
bob 3
fred 6
sam 1
linda 2
View 1 Replies
View Related
Mar 29, 2007
suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
View 1 Replies
View Related
Nov 17, 2013
I have a table name farmer data and it has attributes like farmer name, father name, pesticides used, town etc (these attributes would be required for query). also the data type of columns is var char.
I have to write a query to calculate the average no of farmers in each town.
How can I write this query? I have tried this query but its not giving me the count of all the farmers in each town. its not giving me the count of distinct farmers
select town, count (*)
from farmer_data
group by town
View 7 Replies
View Related
May 3, 2006
Hi, I have a table that for ease has this data in:R1, R2, R....z---------------------A | 12A | 22A | 30B | 0B | -1B | -3C | 100I want to generate a table for each distinct row in R1, gives a countof all the rows with data correspondingFor the above table I would getA | 3B | 3C | 1Im probably being stupid but cannot see this at the moment... pleasehelp.Thanks
View 3 Replies
View Related
Sep 16, 2004
Quick question, is there a limitation on the number of users that can get into SQL server at one time? Say 1500 or 2000 users? If not, I wonder how many users can actually cause the performance of the server to have a significant drop.
Thanks
SHK
View 2 Replies
View Related
Feb 12, 2015
select SERIALNO, Max(TIME)
from dbo.TASK A
join dbo.Status B
on A.TID=B.TID
where A.ID in ('1111',2222')
and A.TYPE='Pen'
group by B.SERIAL_NO, B.TIME
order by BM.TIME
For this query I may get serial no duplicates but times are unique
For that serial no, I have to find the recent time. But if I use group by, I am getting the wrong no
View 2 Replies
View Related
Apr 9, 2007
when i see the number of users on my local connection. It says 4 and another DB says 12. But nobody is connected to these DB but me. Why is it showing 4 and 12?
View 1 Replies
View Related
Dec 19, 2006
How many users can access to SQL server at the same time with web aplicationwritten in C# 2005? What if my database is 1GB and 1000 people access at 1second - some simple query. My web hosting acc will behttp://www.webhost4life.com or something simmilar!
View 2 Replies
View Related
Apr 13, 2006
Hello,
There was a 5 concurrent user limitation in MSDE (the version prior to SQL Express). Does that same limitation exist with SQL Express 2005?
Thanks.
View 3 Replies
View Related
Oct 1, 2005
Hi,
Can somebody let me know how many (maxi) no of users can be created to deal with MSDERelA ?
With kind regards,
Ashfaque
View 4 Replies
View Related
Jul 20, 2005
SQL Server 2000 - MSDE 2000Is there a way to get the number of current users logged into a SQL 2000 Server (also MSDE)? Cant be distinct users as most users are logged into the database using the same login.-- Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.http://www.vehiclewebstudio.com
View 4 Replies
View Related
Oct 28, 2007
I need a small SQL server for up to 10 users. If I understand the EULAs and FAQs correctly, there is no limit on the number of users imposed by the SQL Server 2005 Express license. If so, then the number of users is only limited by the OS's license so all I need is SQL Server 2005 Express (free), Windows XP Pro (10 users limit) and no CALs for either Windows or SQL Server.
I'd be thankful if someone could verify this.
View 1 Replies
View Related
Jun 23, 2015
I created a view which gave me the TOP1000 Companies we do consulting work for.
It includes their name, rank, year, companyID. I wanted to then link this view to the address table. I was able to do this, but because one company might have multiple addresses my result set was large when I started out by just adding city to my view. How can I take my result set and just choose the first DISTINCT value in the result set? Here's an example of the result set:
Name Rank Year CompanyID City
Bill's Fish 22 2015 779 Sunrise
Bill's Fish 22 2015 779 Billings
I just want the Sunrise as city record.
View 20 Replies
View Related
May 21, 2014
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
how can I update Table 1 to get desired results?
View 2 Replies
View Related
Jul 14, 2014
How can i get the total number of each distinct value in the column and write it to another table e.g.
MyTable
Id Fruit
1 Apple
2 Banana
3 Apple
4 Watermelon
5 Banana
6 Watermelon
7 Apple
Result
Fruit Count
Apple 3
Banana 2
Watermelon 2
View 9 Replies
View Related
Mar 30, 2001
Hi,
could somebody please let me know what is the maximum number of simultaneous users/connection per single SQL Server 7.0 box? Licenses are not the issue here, rather the number of physical connections supported before another server needs to be purchased.
Another variation of this question would be what's the highest number of databases that 7can be created on SQL 7.0 server and what is the highest number of physical connections supported per database? Again, licenses are not the issue. Thanks a lot in advance.
View 2 Replies
View Related
