October 21, 2008 at 5:26 am
Hi All,
can anybody help me in writing the query. I have Table like below:
Col1 Col2
JAN 1000
FEB 1200
MAR 1250
I want the result should look like this :
S_MONTH S_SUM
--------------- ----------
JAN 0
FEB 200(i..e...1200-1000)
Mar 50(i.e..1250-1200)
Please help me in this.
Thank You.
Regards,
Raghavender Chavva
October 21, 2008 at 9:36 am
I think I see what you are trying to do, find the difference between each month. Is your column1 an int, datetime, or is it actually storing the month name like in your sample? If you're actually storing the month name, it makes this task alot more difficult. If you're storing it in numerical format like 200801, 200802, 200803 etc or as an actual date value, you can use someting like this:
CREATE TABLE #bogus (Col1 datetime, Col2 int);
INSERT INTO #bogus
(Col1, Col2)
SELECT '2008-01-01', 1000
UNION ALL
SELECT '2008-02-01', 1200
UNION ALL
SELECT '2008-03-01', 1250;
WITH cte (Col1, Col2, Seq) AS
(SELECT Col1, Col2, ROW_NUMBER() OVER (ORDER BY Col1)
FROM #bogus)
SELECT c.Col1, ISNULL(c.Col2 - p.Col2, 0)
FROM cte c
LEFT OUTER JOIN cte p ON c.Seq = p.Seq + 1
DROP TABLE #bogus
October 21, 2008 at 9:48 am
I tried your suggestion just for learning and I'm getting errors running it both in 2000 and 2005. Here are my errors:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
Msg 195, Level 15, State 10, Line 2
'ROW_NUMBER' is not a recognized function name.
October 21, 2008 at 9:52 am
Thank you Chris Harshman.
It's worked for me, and need another small query on this situation only.
I have the table with the below information already:
Col1 Col2
JAN 1000
FEB 1200
MAR 1250
but I want a select quesry which gives the result like below :
S_MONTH S_SUM
--------------- ----------
JAN 0
FEB 200(i.e..1200-1000)
March 50(i.e..1250-1200)
Please help me in this.
Thank You.
Regards,
Raghavender Chavva
October 21, 2008 at 9:55 am
When you create your table you can actually give it the name you wish... i.e.
CREATE TABLE #bogus (S_MONTH datetime, S_SUM int);
October 21, 2008 at 10:00 am
OK, vnguyen, yes this solution won't work on 2000, (didn't realize you needed a 2000 solution) but it should work fine on 2005 unless you have compatibility level set below 90.
For 2000, if you have a datetime datatype for column1 and you know the data won't be skipping any months, try something like this:
SELECT c.Col1, ISNULL(c.Col2 - p.Col2, 0)
FROM #bogus c
LEFT OUTER JOIN #bogus p ON c.Col1 = DateAdd(month, 1, p.Col1)
I did the 2005 way to try and cover any chance of missing data so that's why I used the ROW_NUMBER function.
Raghavender,
If you are storing month names in column1 and can't avoid it, you'll either need a cross reference table that has 2 columns (CurrentMonthName, PreviousMonthName) and join your table twice to that, or have a really big ugly CASE in your join. I'd recommend storing as an int or datetime though.
In my original post, the statement that began:
WITH cte(...
is the query, you'd just change the line:
FROM #bogus
to whatever your existing tablename is, the rest should stay the same.
October 21, 2008 at 10:10 am
Thanks for the reply but I couldn't run it in 2005 either... I tried the new one you sent and it works like a charm...
Anyways to answer the last part of the OP question, here is how to get the month only to display as the result:
SELECT CASE MONTH(c.S_MONTH)
WHEN 1 then 'JAN'
WHEN 2 then 'FEB'
WHEN 3 then 'MAR'
END as S_MONTH, ISNULL(c.S_SUM - p.S_SUM, 0) as S_SUM
FROM #bogus c
LEFT OUTER JOIN #bogus p ON c.S_MONTH = DateAdd(month, 1, p.S_MONTH)
October 22, 2008 at 5:30 pm
Hi
The following creates a database and the table as given and inserts the data. Then it creates a view with sql to produce the required result and executes the view.
-- BEGIN SCRIPT
use master
go
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'testdb')
DROP DATABASE [testdb]
go
CREATE DATABASE [testdb] ON PRIMARY
( NAME = N'testdb', FILENAME = N'D:\DATA\xpress\testdb.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'testdb_log', FILENAME = N'D:\DATA\xpress\testdb_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
USE [testdb]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [testdb] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MonthlyCount]') AND type in (N'U'))
DROP TABLE [dbo].[MonthlyCount]
CREATE TABLE dbo.MonthlyCount
(
col1 char(3) NOT NULL,
col2 int NOT NULL
) ON [PRIMARY]
GO
COMMIT
BEGIN TRANSACTION
GO
INSERT INTO [testdb].[dbo].[MonthlyCount]([col1],[col2]) VALUES ('JAN',1000);
INSERT INTO [testdb].[dbo].[MonthlyCount]([col1],[col2]) VALUES ('FEB',1200);
INSERT INTO [testdb].[dbo].[MonthlyCount]([col1],[col2]) VALUES ('MAR',1250);
COMMIT
GO
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[qry_MonthlyGrowth]'))
DROP VIEW [dbo].[qry_MonthlyGrowth]
go
CREATE VIEW dbo.qry_MonthlyGrowth
as
SELECT M2.col1 AS Month, isnull(M2.col2 - M1.col2, 0) AS Growth
FROM
(
SELECT
(CASE [col1] WHEN 'JAN' THEN 1 WHEN 'FEB' THEN 2 WHEN 'MAR' THEN 3 END) AS Ord,
col1,
col2
FROM MonthlyCount) AS M1 RIGHT OUTER JOIN
(
SELECT
(CASE [col1] WHEN 'JAN' THEN 1 WHEN 'FEB' THEN 2 WHEN 'MAR' THEN 3 END) - 1 AS Ord,
col1,
col2
FROM MonthlyCount) AS M2 ON M1.Ord = M2.Ord
GO
SELECT * FROM dbo.qry_MonthlyGrowth
GO
-- END SCRIPT
October 23, 2008 at 9:15 am
vnguyen (10/21/2008)
Thanks for the reply but I couldn't run it in 2005 either... I tried the new one you sent and it works like a charm...Anyways to answer the last part of the OP question, here is how to get the month only to display as the result:
SELECT CASE MONTH(c.S_MONTH)
WHEN 1 then 'JAN'
WHEN 2 then 'FEB'
WHEN 3 then 'MAR'
END as S_MONTH, ISNULL(c.S_SUM - p.S_SUM, 0) as S_SUM
FROM #bogus c
LEFT OUTER JOIN #bogus p ON c.S_MONTH = DateAdd(month, 1, p.S_MONTH)
No need for a case statement to get the first three characters of the month, use the DATENAME function eg:
SELECT UPPER(LEFT(DATENAME(MONTH,GETDATE()),3))
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply