I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.
At the moment I'm using this:
DECLARE @Date DATE = '20131217'
SELECT CONVERT(DATE, CAST(DATEPART(YEAR,
IIF(
--If we're at the start of a year
--we'll need to go back a year
DATEPART(MONTH, @Date) IN (1,2,3),
DATEADD(YEAR, - 1, @Date),
@Date
)) AS VARCHAR(4)) + '0401')
I have [income]- (REVENUE) table of accounts, each account can have multiple instances for one month. how can I find the last occurrence of all the accounts in a month (and then summarize all of these latest occurrences)?
account revenue revenue_date
1 100 2010-01-01
1 200 2010-02-03
1 300 2010-02-04
1 1000 2010-06-04
2 100 2010-02-01
2 200 2010-03-03
2 1300 2010-07-04
2 11000 2010-08-04
3 2100 2010-01-01
3 2200 2011-02-03
3 300 2011-02-04
3 221000 2011-06-04
4 222 2010-02-01
4 2222 2012-03-03
generate output as specified below. i need to get 1st value when record is duplicated in hierarchyval column
IF OBJECT_ID('tempdb..#test') IS NOT NULL
drop table #test
create table #test
(
hierarchyid int
,hierarchyval int
[code]...
I am using MS SQL 2012 and have a pretty simple table dbo. Migration Breakdown with sample data as follows.
DepartDateTime ZoneMovement
2015-06-26 14:00:00.000 6 to 4
2015-06-26 14:00:00.000 11 to 7
2015-06-26 15:30:00.000 9 to 6
2015-06-26 21:00:00.000 7 to 3
2015-06-27 08:01:00.000 7 to 4
[code]....
What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business.
I have a select query that results all the dbs and their several backup dates×. Howover I only need the latest backup dates for each of the db.How can I write that in tsql?
View 5 Replies View RelatedI have table in which month & year are stored, Like this
Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015
I wanted a query in which it should return the value in a status field which has latest year & month.
In our Microsoft Dynamics Nav instance we have a Sales Header Archive table - into which copies of the Sales Header are placed, with 3 items forming the compound key:
Document Number
Version
Occurrence number
so if doc 1 is archived, then the records would be
Doc# | Version | Occurrence #
1 | 1 | 1
When a second copy is archived a new record is added:
Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2
and then when maybe a 3rd version is archived a 3rd entry added
Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2
1 | 2 | 2
This is for EACH document and I now need to retrieve the dataset which is the latest version of each document... but I'm drawing a blank!
If I
select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#]
then I get the distinct list of docs, but I now need to use this list to select the records which match this criteria, from this table.
How do I select just these?
I thought (wrongly) that I could simply say:
Select * from Invoice Table where
Invoice.[Doc#], Invoice.[V], Invoice.[Occurrence #] in
(select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#])
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
I would like to get the second last work day of the month.
View 8 Replies View RelatedI am trying to add month to a date. Here is my code
declare @CollectionDate date='10-28-2014'
select @CollectionDate
;WITH CTemp AS (
SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1
UNION all
[Code] ....
It is working fine. But when I am giving date '10-30-2014' it shows me the error
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
I can understand the problem that it is for the month of February. But How do I overcome the situation?
I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.
View 9 Replies View RelatedWhat's the best way to calculate a customers age and value by month and year?
I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:
select fh.contact_number
, concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year
, cast(fh.transaction_date as date) as transaction_date
, sum(fh.amount) as ttl_amount_in_month
[Code] .....
It seems to work, but any better way to achieve this?
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
I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.
Year Month Productname
2013 11 ACB
2013 11 CDE
I would now like to add another column called date and store these Year Month as a date to my existing table
Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01
Is there a way I can do it for all the columns of the existing table ??
I need to build a report that compares a count on a certain day of the week by month by year by stacks. That is,for first Monday in October 2012 against first Monday in October 2013 for stack DM1 against first Monday in October 2014 stack DM1, same for second Monday, first Tuesday, second Tuesday, ect. Attached is a sample dataset and what I want to achieve.
View 9 Replies View RelatedSQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.
On a daily basis i store the hourly values of the meter in a SQL table.
On-Peak
Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)
off-Peak
Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak
Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak
Here is the DB Table Structure:
Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)
I have my sql tables and query as shown below :
CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
[code]....
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year],
T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
Something like this :
+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----
I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will describe output required
For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.
Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.
As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month
Period No of Void Days Rent Loss
06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57
01/10/2014 - 15/10/2014 15 349/7*15 = 747.85
I have uploaded a screenshot of how the result on this link: [URL] ....
Declare @void Table
(
PropCode VARCHAR(10)
,VoidStartDate date
,LetDate date
,Market_Rent Money
[Code].....
How do I find sales trend of an employee via comparing current month and previous month sales?
I got so far query upto following,
;WITH SalesOrderHeader As
(
SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,
[Code]....
I am getting following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below
Amount Month Year
25,000 July 2014
25,000 August 2014
25,000 September 2014
25,000 October 2014
25,000 November 2014
25,000 December 2014
25,000 January 2015
25,000 February 2015
.
.
.
.
25,000 June 2015
I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.
Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:
I need my query to output in the following format:
How to write this query?
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
I am working with one of the task automating the db refresh in test server from production backup.The production backup sits on the production server local drive.Moving the production server latest backup from its local drive to Test server local drive
View 9 Replies View RelatedMy 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
I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.
This is for a data cleanup.
CREATE TABLE #Names ( FIRSTNAME VARCHAR (20), LASTNAME VARCHAR (20))
INSERT INTO #Names VALUES
('Thomas', 'Hohner')
,('Mike', 'SSmith')
,('TtTony', 'Hawk')
,('Jeff', 'Smith')
,('ZZSara', 'Jones')
,('Luke', 'HendersonZZ')
,('Lily', 'PPadZZZ')
SELECT * FROM #Names
DROP TABLE #Names
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.
Got a job here that fails on the first Wednesday of every month. It's a complex job (66 steps at last count) that takes roughly 7 hours a night. It starts at 1am, and should be finished by 8am.
The first step normally takes under 5 minutes and is one of the quicker steps However, on the first Wednesday of every month, the first step never finishes; it'll still be running when I come in at 8.20 (ish...). Kill the job, start it again, and no matter what it will not get past step 1. The only solution is to restart the service (not the server!), and then restart the job. When we do this it then goes through in the usual less than 5 minutes.
According to all the monitoring I'm doing, the actual step is not doing anything; there doesn't appear to be any processing, no CPU usage, no reads, no writes etc. It just will not process it
I've tried clearing the cache, both ad-hoc and proc (the step runs a SP). Although I have to confess I've not tried running the SP in step 1, seeing if that works, and then run the job from step 2. Part of the issue is we need to get the process running as quickly as possible, and I don't generally have time to experiment.
Ops and Tech Support say there is nothing running on the server (servers - as it's a cluster) - no AV downloads or anything. The fact I only have to restart the service, rather than the server would imply it's a SQL thing.
Hello,
I see the following error on the sql server log of one of our sql servers running sql 2000 with sp4.
SQL Server has encountered 1964 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [h: empdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000534. The offset of the latest long IO is: 0x0000002b09e000
Any idea as to what might be causing this error. Appreciate any comments.
Thanks!
I need to automate the db users to get the data for every month on all servers ....
View 9 Replies View RelatedI have 12 month report and I need show volume and difference between current and prev month volume, what is the smart way to do this, do I need to put prev month value onto same row horizontally? I think should be some other smart way, I heard about LEAD function?
This what I think for now, It should be listed per ClientID also, in my example I have single ClientID for simplicity.
I tried to do LEAD but with not success..
/*
IF OBJECT_ID('tempdb..#t') is not null drop table #T;
WITH R(N) AS
(SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N <= 12 )
SELECT N as Rn,
10001 ClientID,
DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())) AS [Month],
[code]....
Based on the description below on average how many hours a month would it take to monitor and maintain the MSSQL Server databases?
Description of IT infrastructure.All Windows Servers and MSSQL Servers are up to date on patches and best practices.
Corporate site with 3 remote sites.
All remote sites have one DC and one MSSQL Server.
The corporate site has one MSSQL Server.
Replication is performed between the remote MSSQL databases and the corporate office MSSQL database.
There is no in-house DBA. All DBA services will have to be outsourced. I am trying to determine what is reasonable in budgeting for time involved for this service.
There is one project written in MS Access using Visual Basic for Applications (VBA) with the backend residing on these database.
The question is on average approximately how many hours a month would it take to monitor and maintain the health of the MSSQL Servers database by a MSSQL DBA. The DBA will not have to create any user reporting, queries, etc. Just maintain the existing MSSQL Servers database.
I need to load the latest csv files from file server , The files are placed in a folder called -
Posted 02022015- --> csv files .
I am able to copy the csv files from filserver using bulk insert (manually) , giving the file location
I am having difficulty picking up the latest folder which is posted on the server and import it into database using a stored proc .