SQL Server 2012 :: Query To Find The Difference In Values From Previous Month?
Dec 12, 2013
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 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----
View 3 Replies
ADVERTISEMENT
Mar 25, 2014
I 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]....
View 2 Replies
View Related
Jun 17, 2008
--find day,month,year
--for day select datediff(d,'01 may 2008',getdate())
-- --for month select datediff(m,'01 jun 2006',getdate())
-- --for year select datediff(year,'01 jun 2006',getdate())
above working fine but suppose difference is 1 year 4 month and 2 month 15 days then It's giving 1 year and 2 month respectively.
but I want completely so I can use this in case of expired user in my project.
User can be expired in 1 month,3 month and 1 year. So I'm not able to recognize.
thanks
View 3 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
Sep 23, 2014
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?
View 9 Replies
View Related
Jan 7, 2014
CREATE TABLE #MyTable
(
Teams VARCHAR(10),
StartDate DATETIME,
Count INT
)
INSERT INTO #MyTable (Teams,StartDate,Count)
SELECT 'Team A', '01/01/2014',10
[Code] ....
'Team A' Has No records for '01/02/2014' and '01/03/2014' so I need to insert values of '01/01/2014' StartDate for Team A for the missing dates
So 'Team A' will now have 3 records
Team A2014-01-01 00:00:00.00010
Team A2014-01-02 00:00:00.00010
Team A2014-01-03 00:00:00.00010
'Team B' Has No records for '01/03/2014' so I need to insert values of '01/02/2014' StartDate for Team B for the missing date
So 'Team B' will now have 3 records
Team B2014-01-01 00:00:00.00030
Team B2014-01-02 00:00:00.00040
Team B2014-01-03 00:00:00.00040
As for 'Team C' we have values for all 3 dates, no inserts needed.
View 3 Replies
View Related
Jul 7, 2015
I have a data that with month values ranging from jan 2012 till july 2013 with some values associated with it.
I want to generate months automatically after july 2013 till december 2013 in sql something like the below one:
Is there a way in sql to do this?
View 2 Replies
View Related
Jun 7, 2015
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.
View 3 Replies
View Related
Nov 25, 2014
SQL 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)
View 9 Replies
View Related
May 23, 2008
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
View 1 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
Oct 18, 2007
Please refer to this table in this discussion:
Charges
Date
1
1/1/07
2
2/1/07
3
3/1/07
4
4/1/07
5
5/1/07
6
6/1/07
7
7/1/07
8
8/1/07
9
9/1/07
10
10/1/07
11
11/1/07
12
12/1/07
What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:
If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4).
If my date parameter is 7/10/07, the charges would be 18 (7+6+5)
I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:
Code Block
SELECT a.DayCount, a.Sales, SUM(b.Sales)
FROM Sales a CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Here is the result set i'm looking for:
Charges
Date
1
1/1/07
2
2/1/07
6
3/1/07
9
4/1/07
12
5/1/07
15
6/1/07
18
7/1/07
21
8/1/07
24
9/1/07
27
10/1/07
30
11/1/07
33
12/1/07
Each date's charges are a culmination of 3 months worth of charges.
Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!
Thanks in advance!
View 6 Replies
View Related
Dec 22, 2007
how can i do this
search between 2 rows
day before Last day of the Previous Month + day Last day of the Previous Month"
Code BlockSELECT empid, basedate, unit_date, shift, na
FROM dbo.empbase
WHERE (basedate = DATEADD(d, - 2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 5)
AND
(basedate = DATEADD(d, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))) AND (shift = 1)
TNX
View 5 Replies
View Related
Sep 10, 2013
Need getting a query which I will get previous year, previous month first day everytime i run the query.
Ex: If i run the script on 9/10/2013 then result should be 8/1/2012. (MM/DD/YYYY)
View 4 Replies
View Related
Oct 15, 2007
Query1: SELECT TOP 5 A,B,C from Table1 where A > 10 or B < 10
Query2: SELECT @@rowcount
After 'Query1' if 'Query2' is executed will return 5.
But how to get the total resultset number of rows for 'Query1' (without TOP) by executing the 'Query2' or others.
The above option is available in mysql.
View 4 Replies
View Related
Jan 21, 2015
Got following query:
SELECT
event_data.value('(event/data/value)[4]', 'bigint') AS cpu_time,
--database name
event_data.value('(event/data/value)[5]', 'bigint') AS duration,
--estimated cost
--estimated rows
--nest level
[code]...
Basically, is a simple T-SQL query that reads the local file for my already setup extended event sessions. But I can't find the way to retrieve the following attributes as part as the T-SQL query:
--database name
--estimated cost
--estimated rows
--nest level
--object name
I am trying to find a BOL or some MS link with the full list of possible values for event_data.value but can't find one.
View 2 Replies
View Related
Mar 18, 2015
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 Related
Sep 13, 2015
I 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
View 6 Replies
View Related
Mar 20, 2015
I have a query which provides the below result set:
1165 6
1,173.0013
9740 6
9820 13
2271 6
2287 13
10,952.006
11,029.0013
4,074.006
4,103.0013
I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.
1165 6 -8.00
1,173.0013-8.00
9740 6 -80
9820 13 -80
[code]...
View 5 Replies
View Related
May 30, 2014
I am working with SP. How can we find out values of parameters when the SP is executed with the default values?
View 9 Replies
View Related
Jan 18, 2007
I need to write a t-sql query that will take the value of the previousrecord into consideration before calculating the current row's newcolumn value...Here's the situation...I have a query which return the following table structure...Full_Name Points----------------- ------------Name1 855Name2 805Name3 800Name4 775Name5 775Name6 741etc.... etc...I need to create a calculated column that tells me where the personranks in point position. The problem i run into is that in thesituation where two or more people have the same point value i need thecalculated rank column to display the same rank number (i.e. 4th orjust "4") I'm not sure how to to take into consideration the previousrow's point value to determine if it is the same as the current onebeing evaluated. If i new they were the same i could assign the samerank value (i.e. 4th or just "4").If any one has any insight that would be great.ThanksJeremy
View 2 Replies
View Related
Nov 7, 2014
I have the following Games table:
CREATE TABLE [dbo].[Games](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Lge] [nvarchar](255) NULL,
[GameDate] [date] NULL,
[HomeTeam] [nvarchar](255) NULL,
[Home_Score] [float] NULL,
[AwayTeam] [nvarchar](255) NULL,
[Away_Score] [float] NULL)
with the following data:
INSERT INTO [dbo].[Games2]
([Lge]
,[GameDate]
,[HomeTeam]
,[Home_Score]
,[AwayTeam]
[Code] ....
This gives the standings as:
Team B4 - 1 -
Team C1 - 1 1.5
Team A2 - 5 3
How can I query the data to find the "games behind" at any date?
View 9 Replies
View Related
Oct 26, 2007
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.
View 2 Replies
View Related
Jul 2, 2014
I'm trying to use a recursive query to find path between assembly and parts.
The BOM is similar to(I've limited the number of rows and lines):
BOM NumberMat Number
20000222001770
20000222003496
20000222001527
20000222003495
20002462002005
20005062000246
[code]....
How should I modify it so that is returns the 4 path?
View 1 Replies
View Related
Apr 2, 2015
So I have a query that need to find the most recent datetime record each day for a customer. So I have a query that looks like this:
SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)
[Code] ....
View 4 Replies
View Related
Apr 30, 2015
One of my varchar columns in a table has multiple key words enclosed in a pattern of special characters.
Eg: William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world's pre-eminent [##dramatist##]. He is often called England's national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain. His plays have been translated into every major living language and are performed more often than those of any other [##playwright##].
I need to write to query to find all distinct key words that are enclosed within [## and ##]. My query should yield the following results from the string in the example above
[##actor##]
[##dramatist##]
[##playwright##] -- 2 occurrances, but I need it only once in my result set
[##poems##]
[##poet##] -- 2 occurrances, but I need it only once in my result set
[##sonnets##]
[##verses##]
[##writer##]
I need to run this on a large table, so I am looking for the best possible way to minimize any performance issues.
Just give you sample code, I have provided below 2 separate snippets, one with table variable and another with temp table.
DECLARE @MyTable TABLE (MyString VARCHAR (8000))
INSERT @MyTable VALUES ('William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world''s pre-eminent [##dramatist##]. He is often called England''s national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain.
[code].....
View 7 Replies
View Related
Nov 22, 2013
I have two tables I am working with, they are "Institutions" and "InstitutionOversights". The relationship is one-to-many.
The sample data is below.
Table one:
InstitutionID, InstName
------------------------
1 School Alpha
2 School Beta
3 School Charlie
4 School Delta
Table two:
InstitutionOversightID, InstitutionID, Type
------------------------------------------------
1 1 Accreditation
2 1 Verifcation
3 1 Old System
I would like a query to return the results in the following format:
InstitutionID, InstName, TypeList
-----------------------------------------------
1 School Alpha Accreditation, Verification, Old System
2 School Beta null
3 School Charlie null
4 School Delta null
View 3 Replies
View Related
Jul 16, 2015
I have a nested query that retrieves a min value. I would like to know how do i retrieve the corresponding time with that.
my data looks like this
valuevaluetime
1212/31/14 14:51
12.42/19/15 23:30
[highlight=#ffff11]10.92/21/15 6:40[/highlight]
13.21/25/15 20:47
My min value is 10.9 and i need the date 02/21/15
my nested query is as follows
( select min(cast(f.valuestr as float))
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time)
the above returns me the 10.9
i modified the query
select min(cast(f.valuestr as float)),
f.valuetime
from bvfindings f
where f.ObjectName = 'Hb'
but i get all values then.
View 6 Replies
View Related
Jun 7, 2000
Hi Friends
do you have any soluyion or function for last day of Previous Month
thanks
Nilesh
View 1 Replies
View Related
Jul 20, 2005
How can i calculate the last day of the previous month?Help me,please
View 3 Replies
View Related
Aug 29, 2007
Newbie question. I am using a query that pulls month-to-date data that has the following where clause:
WHERE (MONTH(datefield) = MONTH(GETDATE())) AND (YEAR(datefield) = YEAR(GETDATE()))
this works just fine but what I would like for it to do is give me the previous month of data if the if it's
the first day of the month and then any other day give me month to date. Is this possible?
Thanks in advance,
Marco
View 12 Replies
View Related
Dec 12, 2014
I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.
SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME)
,d.fmc_prodlnk, d. fmc_cost
INTO #TestTable
FROM (
SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL
[Code] ....
Expected outcome
fmc_terminalfmc_prodlnkfmc_cost StartDateEndDate
1A2.25 NULL2014-12-03 00:04:00.000
1A2.26 2014-12-03 00:04:00.0002014-12-03 11:33:00.000
And so on….
View 4 Replies
View Related
Jul 10, 2013
I can get the name of the month by doing this...
SELECT
Datename(month,getdate()) as monthName
But I am not able to get the previous Month? How can I do that I thought by putting -1 it would do that for me, but it doesn't.
View 3 Replies
View Related