Transact SQL :: Select With Value For Specific Month
Nov 13, 2015
I have table with below scheme
Year    Month    Day    RELH   Â
------ Â Â -------- Â Â Â ------ Â Â -------
1973 Â Â Â 4 Â Â Â Â Â Â 1 Â Â Â Â Â Â 50
...... etc
1973 Â Â Â 4 Â Â Â Â Â Â 30 Â Â Â Â Â 20Â
.....etc
1974 Â Â Â 5 Â Â Â Â Â Â 1 Â Â Â Â Â Â Â 50
....etc
1974 Â Â Â Â 5 Â Â Â Â Â Â 30 Â Â Â Â Â Â 99
I need to get years where average of RELH  of  months within year meetÂ
Month 4 Average(RELH) > 33 and
Month 5 Average(RELH) > 60 andÂ
Month 6 Average(RELH) < 33Â
How can I achieve this?
View 4 Replies
ADVERTISEMENT
May 23, 2015
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
View 4 Replies
View Related
Jun 29, 2015
I have 3 month of record in my table. if i pass 2 and 10, i need to select the record of between 2 and 10 days of record of every month. if i pass 10 and 20, it should select the record between 10 and 20 of every month. How to query for that?
View 8 Replies
View Related
Jul 31, 2015
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
View 3 Replies
View Related
Aug 1, 2015
i have table that has rows as below
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
[code]...
i want to select the data priority wise. the o/p should look likeÂ
e.g first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'Â
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
View 6 Replies
View Related
Nov 9, 2015
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] ....
View 6 Replies
View Related
Aug 11, 2015
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',Â
View 2 Replies
View Related
Mar 27, 2007
Hi there !
Thanks for taking the time to read this thread.
I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.
My situation is this...
I have a dataset that has values to fill cells to multiple tables in a report.
However, I only want to select specific data from the dataset to fill textboxes and others.
I cannot change the stored procedure, but the sample of the data is shown below:-
Row Stat Val
0 dtRpt1 02/01/2005
1 Value1 1
2 Value2 2000
3 dtMailSent 02/28/2005
4 Value3 0
5 Value4 5
6 Value5 658
I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.
so my new report would have multiple tables to denote different categories.
In my first table, I tried putting the cells as follows:-
(expressions are highlighted in italics and bold)
TextBox1 =IIF(Fields!Row.Value =0, Fields!Val.Value,"")
Table1
Column1
DetailRow1 =IIF(Fields!Row.Value =1, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =2, Fields!Val.Value,"")
Table2
Column1
DetailRow1 =IIF(Fields!Row.Value =3, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =4, Fields!Val.Value,"")
DetailRow3 =IIF(Fields!Row.Value =5, Fields!Val.Value,"")
DetailRow4 =IIF(Fields!Row.Value =6, Fields!Val.Value,"")
I only expect this report to print out one page holding the previous values.
However, it ended up printing like this
----------------------------------------------------------
Table1
Column1
DetailRow1 1
DetailRow2
Column1
DetailRow1
DetailRow2 2000
Table2
Column1
DetailRow1 02/28/2005
DetailRow2
DetailRow3
DetailRow4
Table2
Column1
DetailRow1
DetailRow2 0
DetailRow3
DetailRow4
Table2
Column1
DetailRow1
DetailRow2
DetailRow3 5
DetailRow4
Table2
Column1
DetailRow1
DetailRow2
DetailRow3
DetailRow4 658
------------------------------------------------------
I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value.
Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.
Is this the correct expression ?
=IIF((Fields!Row.Value,"Dataset") =1, (Fields!Val.value, "Dataset"), "")
and it give me an error
The value expression for the textbox €˜textbox5€™ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Appreciate any advice or suggestion for this scenario !
Thanks!
Bernard
View 3 Replies
View Related
Aug 6, 2015
At the following MDX code , I want to get the aggregate of measure only for  members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.
WITH MEMBER A AS
(b)+(C)
MEMBER [Measures].[Aggregate] AS
Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00],
([Measures].[D])
[Code] ....
View 13 Replies
View Related
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Apr 21, 2015
I am using below query i want to order it by month so that data is displayed like
Select (count(I.Colour)) as Litres,
DATEName(MONTH,ShipDate) as [Loaded Date] from InkData I
group by DATEName(MONTH,ShipDate)
order by DATEName(MONTH,ShipDate)
Right now it is not ordering by month, it is ordering alphabetically.I want this to be ordered like
Jan
Feb
March
View 6 Replies
View Related
Nov 10, 2015
Is it possible to return the name of a month in a certain language?
I know I can use SELECT DATENAME(month ,GETDATE())to get the name of the month, but it will appear in the language of the OS regional settings (?)Is it possible for instance to make it appear for instance in English or Portuguese, adding any SET command?
View 3 Replies
View Related
Oct 14, 2015
How to set ddl triggers for specific tables, instead of on entire database.
View 2 Replies
View Related
May 4, 2015
Is there a way to capture any firing for a SP such as (datetime , loginname,..) and not using SQL profiler ?
View 3 Replies
View Related
Feb 13, 2001
I want to set up stored procedures that let me group data into months. I use the data to produce charts so I need to be able to group into month and year like '01, 2000', '02, 2000'. What is the best way to produce data grouped into month and year from a date field? Using the Month and Year functions I get data like 1,2000 and 11,2000 which don't stand up to a text sort.
View 1 Replies
View Related
Nov 12, 2015
I have the following requirement I need to extract all records for min or max date between the 1 and 10th of every month, the following query works but I find some dates that are not in the range specified.
Select
[EventType],ItemName,FORMAT([TimeLastModified],'MM')asFromMonthValue,
FORMAT([TimeLastModified],'MM')asToMonthValue,
FORMAT([TimeLastModified],'MMM')asFromMonthLabel,
FORMAT([TimeLastModified],'MMM')asToMonthLabel, Â
min([TimeLastModified])asMinDate,max([TimeLastModified])asMaxDate,ModifiedBy,Casewhen[EventLog].EventType=1
ThenÂ
'Item Added'
[Code] .....
View 5 Replies
View Related
Dec 10, 2010
How to get First day of previous month and last day of previous month(From getdate()) using SQL..?
View 13 Replies
View Related
Jun 9, 2015
I have a filed named 'date'. I convert this into 101 format. Now I want to sort that date by year not by month.
View 12 Replies
View Related
Sep 23, 2015
I am storing duration of a lot of jobs in a column in a table per job. This duration is in seconds and an integer datatype.
Sample data:
Job     Duration   date
Job1 Â Â Â 25 Â Â Â Â Â 2015/9/23
Job2 Â Â Â 30 Â Â Â Â Â 2015/9/23
Job3 Â Â Â 45 Â Â Â Â Â 2015/9/23
Job4 Â Â Â 1 Â Â Â Â Â Â 2015/9/22
Now I need to get average duration per day for every month. Is this possible? I have a calendar table that has every single day month year microsecond millisecond  second minute and hour.
View 5 Replies
View Related
Jun 3, 2015
I have a query for which in the where clause i use where Year(openDate) = Year(GETDATE()) and Month(OpenDate) = Month(GETDATE())-1 which would give me the data i needed for this year last month. However if i run this query on Jan 2015 or Jan 2016, this query would fail.
how to modify my where clause so that it runs regardless of even if its Jan ?
View 6 Replies
View Related
Aug 10, 2015
I am still learning T-SQL .Lets consider the table below, ID 1-3 shows our purchase transactions from various Vendors and ID 4-6 shows our payments to them
Table 1 - VendorTransactions
ID Â Â PARTY Â Â AMOUNT Â VOUCHER
---------------------------------------
1 Â Â Â A Â Â Â Â Â Â Â 5000 Â Â Â Â Â Â Â Purchase
2 Â Â Â B Â Â Â Â Â Â Â 3000 Â Â Â Â Â Â Â Purchase
3 Â Â Â C Â Â Â Â Â Â Â 2000 Â Â Â Â Â Â Â Purchase
4 Â Â Â A Â Â Â Â Â Â Â 3000 Â Â Â Â Â Â Â Payment
5 Â Â Â B Â Â Â Â Â Â Â 1000 Â Â Â Â Â Â Â Payment
6 Â Â Â C Â Â Â Â Â Â Â 2000 Â Â Â Â Â Â Â Payment
7 Â Â Â A Â Â Â Â Â Â Â 1000 Â Â Â Â Â Â Â Payment
Now we have a blank table Table 2 - Liabilities
ID Â Â PARTY Â Â AMOUNTÂ
I want that SQL should look for each individual party from Table 1 and Calculate TOTAL PURCHASE and TOTAL PAYMENTS and then deduct TOTAL PAYMENTS from TOTAL PURCHASE so we get the remaining balance due for each party and then add the DIFFERENCE AMOUNT alongwith PARTY to the TABLE 2 so I can get the desired result like below
ID Â Â PARTY Â Â AMOUNT
-------------------------
1 Â Â Â A Â Â Â Â Â Â Â 1000 Â Â Â Â Â Â Â
2 Â Â Â B Â Â Â Â Â Â Â 2000 Â Â Â Â Â Â Â
3 Â Â Â C Â Â Â Â Â Â Â 0 Â Â Â Â
View 3 Replies
View Related
Jul 6, 2015
I have a column that has data in column user4 such as :
CALBRIGÂ Â ALBIONÂ COMPANY LTDÂ Â
1900 No. 8 Road, RIchmond, BC, V6V 1W3
I can do a LEFT(user4, 7) That returns me the first 7 charcters but I need to retrieve the company information but the number of characters may vary with each field. Is there anyway I can do this without returning the entire line?
View 3 Replies
View Related
Nov 19, 2015
I am trying to get a query that subtracts a month and a day from current date in SQL Server.
Currently I have SELECT Â DATEADD("MM", -1,GETDATE()) which subtracts one month from the date but I want to subtract a month and a day from date.
View 9 Replies
View Related
Jul 21, 2015
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.
View 10 Replies
View Related
Oct 7, 2015
I am trying to get count of  records by month wise when they select year .It was showing the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...
SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)
Output
Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139
View 4 Replies
View Related
Oct 13, 2015
I have a table that records a GL period as a string. I want to convert this value to the last date in the month.Â
GlPeriodID
APR 2015
I want to convert toÂ
4/30/2015
View 7 Replies
View Related
Nov 6, 2015
Is there a way to figure out if a table is partitioned by month/day in SQL.Â
View 2 Replies
View Related
Oct 17, 2015
I need a simple query to display all the days of a given month and year
View 2 Replies
View Related
Sep 23, 2015
I have a column which stores a set of dates. I want to tell how many days left of a date till it’s month end. It should be noted that month ends are taken from the date series, not a calendar month end.
Â
Something like below,
Â
DateTDÂ Â Â Â Â Â Days left
2009-01-05Â 14
2009-01-06Â Â Â Â Â Â 13
2009-01-07Â Â Â Â Â Â 12
2009-01-08Â Â Â Â Â Â 11
2009-01-09Â Â Â Â Â Â 10
2009-01-12Â Â Â Â Â Â 9
2009-01-13Â Â Â Â Â Â 8
2009-01-14Â Â Â Â Â Â 7
2009-01-15Â Â Â Â Â Â 6
2009-01-16Â Â Â Â Â Â 5
2009-01-19Â Â Â Â Â Â 4
2009-01-20Â Â Â Â Â Â 3
2009-01-21Â Â Â Â Â Â 2
2009-01-22Â Â Â Â Â Â 1
2009-01-23Â Â Â Â Â Â 0
2009-02-02Â Â Â Â Â Â /
2009-02-03Â Â Â Â Â Â /
View 28 Replies
View Related
May 13, 2015
I want to find the first weekend day, second weekend day, third weekend day and fourth weekend day per month using sql query. This is getting from recurring appointment. If weekdays = 65 means it accepts only saturday and sunday. So I want first, second, third and fourth weekend days for a month using query in sql server 2008...
View 17 Replies
View Related
Jun 17, 2015
I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.
AdjusterNameSpecialtyDatestartDateEnd
Test Inside Property2009-08-29 2010-07-31
Test Management2010-08-012012-07-31
If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .
View 2 Replies
View Related
Jul 23, 2015
I am trying to query a code where i need to loop a month in a specified date range. Inside the loop I need to return a result of data each month and need to update the table of the returned data. How do I do the update a field inside the loop? Here's my query:
declare @table1 table (
YEAR_EFF int,
MONTH_EFF int,
IDNumber (8),
SUBS_CNT smallint,
MEM_CNT smallint)
declare @StartDate datetime,
[code]....
Others says I need to use exec sp_executesql N'' but how do I use it using my code above?
View 7 Replies
View Related
Nov 19, 2015
I want to find out count of same UserID used within 7 days for that month.
For example, in below scenario, UserID 458's 1st TTo is 8/26/2015 and it used again on 8/28/2015 which is less than 7 so result should be 1. (DateDiff between 1st TTo and 2nd Tfrom should be less than 7) This is for month of Aug.
ID TFrom TTo
9876 8/1/2015 8/7/2015
4140 8/21/2015 9/4/2015
458 8/23/2015 8/26/2015
458 8/28/2015 9/8/2015
Scenario 2,for UserId 458, TTo is 9/20 and it used again Tfrom on 9/20 so result should be 1Â
user ID TFrom T To
592 9/1/2015 9/24/2015
526 9/3/2015 9/11/2015
292 9/11/2015 9/25/2015
352 9/12/2015 9/24/2015
458 9/14/2015 9/20/2015
458 9/20/2015 10/2/2015
706 9/22/2015 10/6/2015
View 5 Replies
View Related
