Is my query below the most efficient way to query the first day of each month?
SELECT base_rate, doc_pt, chgdate
FROM producthistory
WHERE chgdate IN
(
SELECT MIN(chgdate)
FROM ProductHistory
WHERE prod_id =1199812
and base_rate > 0
GROUP BY MONTH(chgdate), YEAR(chgdate)
)
AND prod_id = 1199812
ORDER BY chgdate DESC
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
Looking advice on how to query for just a single day of the month. Example is for billing sent on the same day of each month, need to get all accounts that started on that day of the month no matter which month. If a account started January 7th, and it is July 7th, I would like it to list all accounts that started on the 7th day of the month. The database has a start date field in short date format.
View 1 Replies View RelatedHi alli want result as month name from my queryi tried with the following query but it give result as month numberlike (8)select (month(getdate())) as expri want result as month name (Augest)..give me the proper query...from Sachin
View 2 Replies View RelatedI would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
Hello,
I have made a query that does a cross-tab of data. The date data is normal like 02/04/2004, 03/04/2004 etc.
I am using Month Name to group the dates by Month name.
My problem is the query result sorts the month names in alphabetical order, not month order.
I am using the query to generate a graph and the months are not in order.
Is there a way to sort the months by month order not alphabetical order.
Thanks
Goong
Can someone Please help me with what seems to me a "complex" query??
I have the following columns/data - See Original Data.jpg
This is what I need the SQL query(s) to do. I need it to
1) Select the distinct names AND months
2) Count the number of times that a name AND a month appeared together
3) Sum totalsold for each name/month
4) sum employeescore for each name/month
So it would look like - After Query.jpg
elo!
actually im using interbase but id like to know the equivalent SQL code so that i can explore how to do it in interbase.
i have a table with a date field. i would like to select all entries which belong to a certain month. this is how i do it:
"select * from table WHERE (cast(datefield as varchar) like :MNT)"
:MNT is a parameter equivalent to "1/%/2007" for example for january
can you please help me convert this statement to a valid query?
"select * from table where month of datefield in [month]"
i hope you can help me.
thanks in advance
Hi,I have a simple table called events, which lists the start and end dates of events. I'm using a calendar control that queries the db for events, but at the moment it does a check for every day by passing in the day. (Very inefficient) What I'd like to do is pass in the current month, and get a set of rows that have an event which is in that month. It sounds easy, but I'm a little confused about what to do if the event starts current month -1 and ends currentmonth +1 I obviously need to return results like these also.
View 4 Replies View RelatedI am writing a usage query, I need to determine what the current month and year is. If the current month is 1, then I need Year-1. I keep getting this error Line 5: Incorrect syntax near '='.
If I comment out the case statement just evaluate the (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))or (YEAR(User_Date) = YEAR(GETDATE())) by itself, the query works. What I am missing? Anybody?1 SELECT CONVERT(char(10), User_Date, 101) AS userdate, COUNT(*) AS CNT, User_Name2 FROM Users3 WHERE (User_Name = 'Joe Bob') AND (MONTH(User_Date) = MONTH(DATEADD(mm, - 1, GETDATE()))) AND 4 case MONTH(User_Date) when 1 then 5 (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))6 else (YEAR(User_Date) = YEAR(GETDATE()))7 END8 GROUP BY CONVERT(char(10), User_Date, 101), User_Name9 ORDER BY CONVERT(char(10), User_Date, 101), User_Name
Hello everybody,I want to ask how to create query use the solve the problem of this..Example : I want to display the month january between february... Sorry if my english is not good...Thank you...
View 3 Replies View RelatedI need SQL to determine what the date range for the previous month was, ie
Start date 11/01/01 for 11/30/01.
The results will populate a drop down box for user queries.
Need query to expand the count from a month to the entire year in 2013 .Below is my query where I got the values for a month in 2013 ,How do I expand on this query so that it generates for the entire 2013
Query
SELECT RD.RPTDESC,Count(SR.RPTDESC) AS ReportCount,sum(SR.Hrs) as ProdHours,Sum(SR.Mins) as ProdMins, (sum(SR.Hrs)*60+ Sum(SR.Mins)) as TotalProdTime,
(sum(SR.Hrs)*60+ Sum(SR.Mins)/Count(SR.RPTDESC)) as AverageProdTime,sum(SR.TriageHrs) as TriageHours,Sum(SR.TriageMins) as TriageMins,
(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)) as TotalTriageTime,(sum(SR.TriageHrs)*60+ Sum(SR.TriageMins)/Count(SR.RPTDESC)) as AverageTriageTime
[Code] ....
Results
Jan-13Feb-13
RPTDESCReportCountProdHoursProdMinsTotalProdTimeAverageProdTimeTriageHoursTriageMinsTotalTriageTimeAverageTriage
TimeReportCountProdHoursProdMinsTotalProdTimeAverageProdTimeTriageHoursTriageMinsTotalTriageTimeAverageTriageTime
20506060120252558014014017039394
[Code] ...
Also when I am running for months individually there are certain months where for certain reportids there is no data returned,I would like to populate all zeroes for that row for example report id 306 in February and how to get decimal values in average fields.
I have a few million rows of a data in a table that store indexing information for scanned customer documents. Fields like last name, first name, document description (descript), document type and scan date (scandate).
I want to be able to query the document description column where it'll show how many times each document description was scanned/used over the course of the last 6 months or year.
SELECT CRDATE, RCTNCCRD
FROM creditcardtable
WHERE (RCTNCCRD <> '') AND (NOT (ISNULL(RCTNCCRD, '999') = '999')) AND (RCTNCCRD NOT LIKE '%x%')
ORDER BY CRDATE DESC
i want to convert any credit card numbers in creditcardtable to an obfuscated format such as:
4332 3423 5423 5428
And convert it to
XXXX XXXX XXXX 5428
HOWEVER, it will only do it if the order is older than 3 months old. Only 90 days/3 months max
how can i do that?
when i use where crcdate<90...it gives me error..
can anyone help me?
hello, how can I query dates using "between" function but grouped by months? for example:
QUERY:
FROM: 15/DIC/2007 TO: 15/FEB/2008
RESULT:
DECEMBER-2007 --- $49,535
JANUARY-2008 --- $45,352
FEBRUARY-2008 --- $52.345
Thanks in advance-!
I have a table with the following fields;
sub_id (unique), insertdate, buy (bit), buydate
I need to query to show count of sub_id for all fields inserted by month
from this I need to then count all fields with buy = 1 by month with each subsequent month only showing values that were contained in the previous months dataset.
The only way I can think to do this is to write a seperate query for each month that references the previous months query, but I'd like to make this as easy to maintain as possible, one query would be nice, not having to hard code the months even nicer.
Sounds a bit confusing, but hopfully you follow thanks in advance for the help.
Hello,
Can any one write a query to give the last date of each month
thanks alot PROS
Hi, I'm dabbling in trying to retrieve some info from an SQL database and publish it in Sharepoint data view. I'm trying to create a View in the SQL database that retrieves all records for the current month. So that in July, it automatically shows me July records, and when August ticks over, it automatically displays August's records. I can hard code the dates in to the query no probs (e.g. a statement like ENTRYDATE >= '1/07/2007') but was hoping there is a way to specify current month. So maybe the statement looks like ENTRYDATE >= "CURRENTMONTH".
Thanks in advance!
Background is that I am recreating charts in SSRS that were being created in Excel. The source data is residing in a SQL Server database. I'm having a hard time coming up with a SQL query to provide a 6 month forecast. I can get my data into a pivot (within a stored procedure) in the following format:
Period---------Date--------------------------------Percent
1--------------2013-11-01 00:00:00.000---------0.3762
2--------------2013-12-01 00:00:00.000---------0.3584
3--------------2014-01-01 00:00:00.000---------0.3604
4--------------2014-02-01 00:00:00.000---------0.3292
5--------------2014-03-01 00:00:00.000---------0.3519
[Code] ....
I need to forecast the next 6 dates 12/1/2014 thru 5/1/2015 using the last 6 months in the data set (periods 8 thru 13)
Period---------Date--------------------------------Percent-------Forecast
1--------------2013-11-01 00:00:00.000---------0.3762
2--------------2013-12-01 00:00:00.000---------0.3584
3--------------2014-01-01 00:00:00.000---------0.3604
4--------------2014-02-01 00:00:00.000---------0.3292
5--------------2014-03-01 00:00:00.000---------0.3519
6--------------2014-04-01 00:00:00.000---------0.4064
[code].....
how to use the first table to generate the forecast values in the second table.
I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these
MonthYear CCG AttendedCity CCG DNACity CCG Cancelled
Oct2014010
Jan2015000
Jan2015000
Jan2015100
Feb2015000
Mar2015210
May2015010
SQL QUERY
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
[Code] ....
Hi,
I want to display records from Jun-2006 To Mar-2008.Month and Year are two different fields.I got the expected result using Union as follows.But i want it within one qury only.Is it possible? if yes please guide me.
(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month >= 6 AND R.Year = 2006)
Group By R.Year , R.Month )
Union
(Select SUM(Amt),
R.Month AS Month ,R.Year AS Year FROM Receipt R
WHERE (R.Month <=3 AND R.Year = 2008)
Group By R.Year , R.Month )
I'm working on project for school that involves building a query in a video store database. The query is suppose to pull the total number of movies rented the previous month. I can get it to work if I physically put in the dates. However, part of the requirements is to set it up so the date range is auto calculated. The following is the code I have
SELECT COUNT(RecordNumber) AS TotalRentalsForMonth FROM RentalHistory
WHERE TransactionDate BETWEEN (YEAR(getdate()), MONTH(getdate()), 1)
AND (YEAR(getdate()), MONTH(getdate())+1, 0)
I get the following error message when I try to run it:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Anyone have an idea where my mistake is within the date range
Hi,
I have the following query which is working fine, but its returning a row for each day.
I'd like to modify it somehow so that we had the same data returned, but 1 row for each month. Can anyone offer some insight ?
Any help is much appreciated !!
thanks once again,
mike123
CREATE PROCEDURE [dbo].[select_Stats_LoginHistory]
(
@numDays int
)
AS SET NOCOUNT ON
SELECT CONVERT(varchar(10),LL.loginDate,112) as loginDate,
COUNT(LL.userID) AS TotalLogins,
COUNT(DISTINCT LL.userID) AS TotalLogins_Unique
FROM tblLogins_Log LL WITH (NOLOCK)
WHERE DateDiff(dd, LL.loginDate, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),LL.loginDate,112)
ORDER BY loginDate DESC
table structure below
CREATE TABLE [dbo].[tblLogins_Log](
[loginID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NULL,
[IP] [varchar](15) NOT NULL,
[loginDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
I am in seach of a query where in I can provide month, year and client name and fetch last available comments from the table.
Client,Month,Year and Comments are columns in that table.
For Ex: If i pass client as A, month as 7 and year as 2015, I should get comments for client A, month July and year 2015 if available.
If data not available, it must go to June month and so on until it finds comments.Also when month is Jan, if query is going back, year also should get changed.
following table global_usage
ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01
I've tried a few thing like group,having even row_number() but I keep getting wrong results
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
[Code] ....
This is my table and data
CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484
I need to convert into this result
CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months
Most of the data is in one table.
Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264
I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.
SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',
I need several stored procedures to fire on the 1st of every month. I was told my another developer I can possibly use SQL sever agent.
Any ideas????
Hello,
I am having trouble in getting the expected results. I have two tables as below. I need Idno,transdt,cycdt,amt from joining two tables.
The criteria is that if the transdt greater than same month of cycdt then we need get the next month cycdt and corresponding amount for that,
if it is less than or equal to same months cycdt then get the same months cycdt and amt.
table1
idno,trandt
12345,04/15/2005
12345,04/22/2005
12345,07/03/2005
12345,09/10/2005
3421,03/05/2005
3421,05/06/2005
3421,07/04/2005
3421,07/15/2005
idno,cycdt,amt
12345,02/10/2005,15.43
12345,03/13/2005,40.84
12345,04/18/2005,10.10
12345,05/24/2005,13.00
12345,06/16/2005,20.89
12345,07/18/2005,12.12
12345,08/17/2005,10.89
12345,09/17/2005,12.87
12345,10/16/2005,13.89
3421,05/10/2005,15.00
3421,06/11/2005,20.00
3421,07/11/2005,14.15
3421,08/12/2005,15.54
Expected result.
12345,04/15/2005,12345,04/18/2005,10.10
12345,04/22/2005,12345,05/24/2005,13.00
12345,07/03/2005,12345,07/18/2005,12.12
12345,09/10/2005,12345,09/17/2005,12.87
3421,05/06/2005,3421,05/10/2005,15.00
3421,07/04/2005,3421,07/11/2005,14.15
3421,07/15/2005,3421,08/12/2005,15.54
I really appreciate if someone can give solution for this using a query (SQL server,Access,Foxpro) is fine.
Thanks
This is what I have for a query but i would like to take the year off. is there a way i can just have the month and day without the year attached?
View 3 Replies View RelatedI have the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201406 xxxxxx 27 1
In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201403 xxxxxx 28 0
201404 xxxxxx 28 0
201405 xxxxxx 28 0
201406 xxxxxx 27 1