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

SQL 2012 :: Using LEAD Function To Show Previous Month Difference?

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

Find Difference In Month And Year In Sql Server 2005

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

SQL Server 2012 :: Select Query - Get Result As Month And Values For All Months Whether Or Not Data Exists

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

SQL Server 2012 :: Split Difference (number Of Nights) Between 2 Dates Into Respective Month Column

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

SQL Server 2012 :: Insert Row With Values From Previous Row If Does Not Exist?

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

SQL Server 2012 :: Generate Months Based On Previous Values

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

SQL Server 2012 :: Find Trend How Employee Makes Sales Every Month?

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

SQL Server 2012 :: Calculate Total On-peak And Off-values For A Month

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

Problem Query For Records For Previous Month

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

SQL 2012 :: Generating Date Range Values (start / End Dates) From Month Columns With Boolean Values

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

Pls Help W/ A Query To Return Running Balances From The Previous Rolling 3 Month Period.

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

Search Day Before Last Day Of The Previous Month + Day Last Day Of The Previous Month

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

Date Of Previous Year Previous Month

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

How Find Rowcount Of The Previous Query

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

SQL Server 2012 :: Where To Find List Of Values For Event Data Files

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

SQL Server 2012 :: Just Get Month And Date Only In A Query Without Year?

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

SQL Server 2012 :: Query To Get The Month Wise Data?

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

SQL Server 2012 :: How To Subtract A Column From A Query Result And Add Difference To Another

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

SQL 2012 :: Find Out Values Of Parameters When SP Is Executed With Default Values?

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

Query Which Calculates A Field Value Based On Previous Row's Values

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

SQL Server 2012 :: Query To Find Games Behind In Sports Table

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

Query Values Progressivly By Month

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

SQL Server 2012 :: Using Recursive Query To Find Path Between Assembly And Parts?

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

SQL Server 2012 :: Query Design - Find Most Recent Datetime Record Each Day For A Customer

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

SQL Server 2012 :: Query To Find Distinct Multiple Instances Of A Pattern In A String?

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

SQL Server 2012 :: Return Query With Concatenating Values

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

SQL Server 2012 :: Nested Query Retrieving Min And Max Values?

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

How To Get Last Day Of Previous Month

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

Last Day Of Previous Month

Jul 20, 2005

How can i calculate the last day of the previous month?Help me,please

View 3 Replies View Related

Previous Month On First Day

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

SQL Server 2012 :: Query That Generates Values By Date Range

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

Unable To Get Previous Month

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







Copyrights 2005-15 www.BigResource.com, All rights reserved