Get The Row Counts For Each Day Going Back To 6 Months (was Query Help)
Jun 15, 2006
I have a proc to get the rowcounts for the given date range.
I have to get the row counts for each day going back to 6 months on the table.
With this proc i can get one day's row couts.. i need to loop through for all dates.
Please can someone get me the code for this.
create proc p_rowcounts
@Date1 datetime,
@Date2 datetime
SELECT
count (*) as 'Number of Rows', @Date1 as Date
FROM
Table1 (nolock)
WHERE ModifyTime >= @Date1 and ModifyTime < @Date2
thanks for the help.
View 3 Replies
ADVERTISEMENT
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
Jan 18, 2005
SELECT K.TRANS_TYPE,K.COMPANY,K.DIVISION,K.SALES_FORCE,K.SALES_LINE,K.SALES_REGION,K.SALES_ZONE,K.FORECAST_ DATE,C.STATISTICS_PERIOD,K.PRODUCT_NUMBER,
K.CUSTOMER_CODE,K.WAREHOUSE,0,0,0,K.FORECAST_QTY
FROM KPI_SIGNOFF AS K inner join ROUNDED_CALENDAR as C on K.FORECAST_DATE
between C.START_DATE and C.END_DATE
Hi this is the code that I am using to populate a table let us call new_table.
However what I would like to do is get the max forecast date and get the statistics_period for that max forecast date.
Once I get that I would set that as my current statistics period.
Then I want to create a new column in the same new_table call stat_period-1, which would be the one period back.
Please note that the statistics period is like the months 1 – 12.
Does not go higher that 12 and does not go lower than 1.
So for stat_period-1 when statistics period is equal to 1 it would have to be set to 12.
This is what I want but do not know how to place this into the code, please help me with this.
THanks
View 10 Replies
View Related
Feb 18, 2008
I´m creating a report in SSRS 2005 where you can filter after month and year. The result is the total sale for that particular month. What I want to do, is adding an extra column to compare with the result in the same month last year. F.eks. say that I choose january 2008 I would like to see the results for january 2007.
I thought of writing an expression to do that, but as that isn't my stronges side I would appreciate some help
View 3 Replies
View Related
May 20, 2008
I have a table that is linked to other tables in one to many relationship.I have a query using LEFT OUTER JOINs to join the tables together.There are multiple counts in the query and count numbers are messed up.(if there are 4 records from one table and 3 from the other - it shows 12 as the count) Your help is appreciated.
View 2 Replies
View Related
Dec 8, 1999
I need to build a table to combine data into a single table but I need it to include a count on more than 1 column. For example, I have a table containing a store number, an isbn, an on hand quantity, and an on order quantity. It's probably easier to explain with an example. I have a source table containing this:
store isbn OnHand OnOrder
===== ========== ====== =======
104 0394572368 2 0
108 0394572368 0 1
109 0394572368 2 0
104 3321695545 2 1
108 3321695545 1 0
109 3321695545 3 1
And I need a table that will combine the isbns and give me sums
and counts like this:
Isbn OnHandLocations OnHandQty OnOrderLocations OnOrderQty
========== =============== ========= ================ ==========
0394572368 2 4 1 1
3321695545 3 6 2 2
Does anyone know of a query that can accomplish this? Thanks in advance.
View 1 Replies
View Related
Jun 11, 2014
How to get task counts in a sales report.
The data is located in three tables: Projects, ProjectTasks, Sales.
I need to group the data by month and project. It needs to include sales per month as well as the number of each project task completed that month.
Projects:
ProjectIDName
1Project1
ProjectTasks:
ProTaskIDProjectIDTaskCodeBeginDateEndDate
11 Task11/1/20141/15/2014
21Task21/15/20141/20/2014
31Task3 1/21/20141/29/2014
Sales:
SalesIDProjectIDClosingAmount
11 1/31/2014$5000
Query Output:
ClosingDateProject TotalSalesTask1sTask2sTask3s
1/2014Project1$500011 1
My query so far is:
SELECT right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)) as ClosingDate,
p.name as Project, SUM(s.amount) as TotalSales
FROM Sales s
JOIN Project p ON p.projectID = s.projectID
WHEREs.closing >= DATEADD(mm, -12, GETDATE())
GROUP BY right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)), p.name
This will give me the grouping by month/year and project.
View 4 Replies
View Related
May 12, 2008
I'd appreciate some help with the issue below - my SQL is a bit rusty and was never that hot to be frank. I'm using SQL Server 2000 (although have a test box with 2005 Express also). I've trawled MSDN and a few forums but can't find the solution (maybe I don't know what I'mm looking for!), so any help would be marvellous...
I have a table with a field called 'IRV' containing a string of comma-separated values. I want to be able to query a point in that string and count the number of times a given value appears. So...as an example, I want to count how many times '1' appears at position 7 in the IRV. I can create SQL to do this as follows:
SELECT COUNT(X) AS is1
FROM myIRVtable
WHERE (SUBSTRING(IRV, 7, 1) = '1')
So far so good. However, it is also possible that the value at position 7 in this string could be '2' (or '3', or '4', etc) - and rather than re-running the query again and again to get these values, I'd like to do it in one hit.
How can I combine all this together - anyone have any brilliant solutions?
View 4 Replies
View Related
May 18, 2007
On my company site, I have created a database that is for the purpose of tracking google adwords, as well as pages that the user visits. For instance, if you were to search for "guitars" and then click our ad, an entry is created in the database like thisKeyword: SessionGUID: PageVisited: VisitedDateTime:Guitars lkjfeilfjskdlfjsije ~ViewCategory.aspx?Cat=Guitars,KW=Guitars 12/01/2000 12:00amGuitars lkjfeilfjskdlfjsije ~ViewProduct.aspx?ProductID=1253&SubProductID=3 12/01/2000 12:03amGuitars lkjfeilfjskdlfjsije ~Search.aspx?Q=BC%20%Rich 12/01/2000 12:05am Pretty much, in our administrative area, I want to be able to have a table that would generate these results: Keyword Total HitsGuitars 3So im guessing that obviously I would need to do a select distinct for the Keyword column, but how do I also have a column showing a count of the records?
View 1 Replies
View Related
Jul 23, 2005
The row counts I am getting from SQL Enterprise Manager and QueryAnalyzer are different. When I double click a table in EnterpriseManager it gives me a row count of say 1000. However, when I select *from that same table in Query Analyzer it returns 1028 rows. Anyoneknow why or better yet, how to fix it? Thanks!
View 2 Replies
View Related
May 13, 2008
Hi All,
I need to display the rent not paid months in the year. How can I form the query?
Help me.
Kamal.
View 7 Replies
View Related
Jan 31, 2008
Hi,
I need to count last 3 months records. The query I'm trying to use was based on last 8 days. How can I use this query in months and not days? Or should I use a new query?
Code Snippet
SELECT COUNT(Column1) AS [Nº de Cartões], Column2 AS [Tipo de Cartão], Column4 AS Periodicidade
FROM main_client_file
WHERE (Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 3), 0)) AND (Column9 <= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 1), 0))
GROUP BY Column2, Column4
Thx.
View 4 Replies
View Related
Jul 18, 2006
Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:
cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";
cmd.Add(new SqlCeParameter("@IntColumn", DBNull.Value));
cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = DBNull.Value;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32));
cmd.Parameters["@IntColumn"].Value = DBNull.Value;
cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));
cmd.Parameters["@IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";
md.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));
cmd.Parameters["@IntColumn"].Value = DBNull.Value;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Variant));
cmd.Parameters["@IntColumn"].Value = DBNull.Value;
cmd.CommandText = "select count(*) from Meter where not IntColumn = @IntColumn";
Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.
View 5 Replies
View Related
Mar 27, 2008
I have the following table
FeedBack Type Date
test2 positive 03/15/08
tes3 negative 03/01/08
.. ....
in my page i need to select the number of negative/positive comments within the last
1 month, 6 months, 12 months
How can I accomplish that?
thanks
View 5 Replies
View Related
Jun 23, 2008
Hi,
I'm writing a query that will be calculating a number of business performance measures including the following...
Average Stock:
This is calculated by taking the actual stock value of the product category per month and dividing it by 13 if we have over 12 month's history, or if a new product, then by the number of months we have had it in stock for.
Cost of Goods Sold Annualised:
This is an average figure for the year of the sold landed costs for the product category over a 13 month period if we have over 12 months history. If it's a new product, then it's for the number of months we have had stock.
My question is, how do I tell the query to go back 13 months from the current month?
View 1 Replies
View Related
Feb 5, 2008
I have a table like FK_ID, Value, Date (here FK_ID is foreign key)this table getting updated frequently by daily bases that means one record per one day(For example in January month it has maximum 31 records or minimum 0 records, in February it has maximum 28 or 29 or minimum 0 records, based on calender year)I need to query this table to get missing dates in between particular monthsfor example for one FK_ID has only 25 records in Jan 2008 month and in Feb 2008 it has 10 records , so i need to get those missing 6 dates from JAN month and 18 dates from FEB monthhow can i query this
View 2 Replies
View Related
Sep 16, 2004
Hi,
I need to write a select query to get last six months data from a table , on monthly wise.Can anyone help me in writing this query
I am using sql server 2000.
eg: Jan...data
feb...data
TIA,
Ravi
View 1 Replies
View Related
Oct 18, 2007
I have a data set that returns the following
Customer
pncount
pn%
monthval
ABC
5
30
01/01/2007
DEF
9
20
01/01/2007
GHI
4
40
01/01/2007
ABC
DEF
4
90
02/01/2007
GHI
7
10
02/01/2007
ABC
4
5
03/01/2007
DEF
GHI
6
100
03/01/2007
but I need this
01/01/2007
01/01/2007
02/01/2007
02/01/2007
03/01/2007
03/01/2007
abc
5
30
4
5
def
9
20
4
90
ghi
4
40
7
10
6
100
Any Idea Help
View 13 Replies
View Related
Jul 27, 2015
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)
But in result i want the table as -
Month Value
Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull
View 9 Replies
View Related
May 1, 2015
I'm written a query to pull adjustments made on customer accounts, and I am attempting to pull in the payment associated with the adjustments. I'm using a single account to test my query and somewhere in my final output select statement I'm doing something that is bringing me back more records than I want. The code I have written is:
IF OBJECT_ID('TEMPDB..#TMP1O') IS NOT NULL DROP TABLE #TMP1O
select a.batch
,a.acctcorp
,a.house
,a.cust
--,RIGHT (REPLICATE ('0',5) + CONVERT(VARCHAR(5),A.acctcorp),5) +
[Code] ....
I've tried making changes within my where statement and parameters, but so far I've come up emtpy.
View 9 Replies
View Related
Nov 15, 2007
I Have read a few of the postings and I hate to beat a dead horse. But I am desperatly looking for your Help...
I am converting all of my Access Databases over to SQL server 2005. but the catch is that I need to keep Access as my front-end. One of the problems that I have faced (and there maybe more) but when it comes to running queries (especially Delete), I cannot seem to get things to work.
I would like to just store all of my data in SS2K5 and have all of my queries and forms run in Access.
Why is it that I am getting errors that do not seem to make sense (Access Error 3086) when I have opened up all of the permissions associated to my database?
Please let me know if anyone has any type of solution, or some place that I can go to that will help me understand this a little more.
my query is pretty simple.
DELETE Opsec_View.*
FROM Opsec_View;
View 1 Replies
View Related
Apr 24, 2007
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies
View Related
May 14, 2015
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
View 2 Replies
View Related
Jun 9, 2015
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
View 4 Replies
View Related
Jun 16, 2007
Hi ,
i am working with .net and as backhand using mySql.
i have done all configration to use the database drive, my problem is i want to insert a path of my xxx selected file from open dialog box. we all know very well that paht has back salesh like "c:data estfilename" that is cause of my problem when ever i insert a path into my table of mysql it accesses all put in different way like "catatestfilename".
that is what i hope u understood what i want to know.
any one who has some idea please mail me.
i will thankfull for your's concern.
bye
keep thinking.
View 3 Replies
View Related
Oct 28, 2015
I have a person table
CREATE TABLE Person
(
PersonID INT
Name varchar(50),
HireDate datetime,
HireOrder int,
AltOrder int
)
Assume I have data like this
INSERT INTO Person VALUES(1, 'Rob', '06/02/1988', 0, 0)
INSERT INTO Person VALUES(2, 'Tom', '05/07/2016', 0, 0)
INSERT INTO Person VALUES(3, 'Phil', '01/04/2011', 1, 0)
INSERT INTO Person VALUES(4, 'Cris', '01/04/2011', 2, 0)
INSERT INTO Person VALUES(5, 'Jen', '01/04/2011', 3, 0)
INSERT INTO Person VALUES(6, 'Bill', '01/05/2011', 0, 0)
INSERT INTO Person VALUES(7, 'Ray', '01/23/2012', 0, 0)
I'm trying to simplify my requirement... providing the input of HireDate, HireOrder, and AltOrder, I need to be able to pick up the next person
For ex:, if I provide input, HireDate: 06/02/1988, HireOrder:0, AltOrder:0, the return value expected is "Tom" because he is the next person after the provided input.
For ex:, if I provide input, HireDate: 05/07/2016, HireOrder:0, AltOrder:0, the return value expected is "Phil" because he is the next person after the provided input. Though Phil and Cris have same dates, their HireOrder takes precedence in this case. If they also have same HireOrder, AltOrder would be coming in picture to determine next person
Another ex: if I provide input, HireDate: 01/04/2011, HireOrder:1, AltOrder:0, the return value expected is "Cris" because she is the next person after the provided input. Here hireorder determines.
If I provide, HireDate: 01/23/2012, HireOrder:0, AltOrder:0, as there is no person after this, I should be able to pick the first person on the list - in this case Rob.
I can write some business logic in front-end, but I thought it would be good, if I can move this to a stored procedure which can return me the PersonID for optimal performance.
I have tried writing various conditions but couldn't achieve a query that meets all my requirements here.
I'm even fine if my last condition is not met (returning the first person in the list, in case no one is available after the provided input).
View 4 Replies
View Related
Aug 18, 2014
Currently I have a standard query with a join to several tables. There are two additional tables MAS_CTB and MAS_STB. I would like to do a union between those two tables to get FIELDVALUE which will exist in either the CTB table or the STB table and then have that value be returned with the results of the original query.
I can of course write a UNION from the main query to the CTB and then to the STB table, however it's about 80 dummy fields I would have to replicate in the union which is why I was wondering if there was a more simple way.
Main Query:
SELECT Field1, Field2...Field80
From Table1
Join Table2
Union Portion:
Select FieldValue <------
From MAS_CTB
UNION
Select FieldValue <-----Return to main query above
From MAS_STB
View 3 Replies
View Related
Jul 20, 2005
Hello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
View 2 Replies
View Related
Sep 22, 2006
Does anybody know of a way to rollback SQL Server 2005
databases back to SQL Server 2000? Is there a way of doing it without
resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database
to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I
want to rollback to SS 2000, which was a lot more stable. I am having
several major issues that are affecting my whole company's day-to-day
operations and the managers are not happy. Some of the issues include
night time batch running very sluggish for no apparent reason. This is a
biggest problem because it only occurs once or so a week and causes a disturbance
with the daily activities when the night time processing isn€™t completed on
time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I
don't believe it is a matter of my application needing to be retuned because if
that was the case, then why isn't it running sluggish every night? Also,
it's never the same day that the sluggish behavior occurs. If it was occurring
on the same night, then I would have something to investigate within our
application, but it doesn't. Another issue that I am having involves a
night time job that restores a copy of the production database to the Data
Warehouse server to be used for updating the data warehouse. Again, most
of the time it runs great (~2 1/2 hours), but once or twice a week, it goes
stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening
the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the
database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended
state. What's a suspended state and how can I get it out of suspended
state? I can find no information about suspended state in BOL. I
did a Google and nothing came up. If this suspended state was put
in for security reasons, great, but then tell me how I can remove the suspended
state. I am also not happy with the
fact that I can't get accurate information about the queries that are actively
running at that particular moment. In SS 2000, when I noticed high CPU
usage on the server, I would run the sp_who2 active stored proc and it would
show me all the active thread and how much CPU it was consuming. I would
then find the running threads with the highest CPU numbers and investigate the
query and see if we could improve it. Now in SS 2005, I get in the same
situation and run the sp_who2 stored proc, and there is no smoking gun.
All of the active threads are showing very little CPU usage, which I am very
suspect of. What the heck happen to sp_who2? I looked at some of
the other ways of looking at running processes (i.e... sys.sysprocesses) and
they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of
years before I upgrade to SS 2005.
Dave Brown
View 1 Replies
View Related
Feb 5, 2007
If I right click and browse the properties for the table I can get the value of rows. But for the same table if I do select count(*) from table the value does not match the table properties rows. Please can some one tell me why this is so?
SQL Newbie
View 2 Replies
View Related
Jan 8, 2007
I expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.1 select Count(*) as cnt--, TeamID
2 from EvalAnswers
3 where CoID=@CoID
4 and EvaluatorID=@EvaluatorID
5 and (Scr0=0 and Sugg0 is NULL)
6 --group by TeamID
7
View 4 Replies
View Related
Sep 6, 2007
I need to create a view that shows the number of times that clients made payments, how many clients, and how much they paid over a period of time. I'm not sure if I can use a case for this. How can I put something like this together?
I have a tblClients with a clientid field
I have a tblPayments with the clientid, pmtdate, and pmtamount
For example:
1 Payment ----- 23 Clients ----- $16000
2 Payments ----- 12 Clients ----- $32000
3 Payments ----- 4 Clients ----- $13000
etc...
View 3 Replies
View Related
Nov 15, 2000
I need a procedure that will go to a database and give me all the row counts for the user tables.
Does anyone know how I can get this?
Thanks,
Dianne
View 3 Replies
View Related