August 29, 2008 at 1:40 am
Hi there,
I'm just a starter with using T-Sql in SQL SERVER. I need to write a query to compare sales with last year sales. Perhaps somebody can give me the directions.
Sources.....
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
Product VARCHAR(10),
DateValue DATETIME,
Value MONEY)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, DateValue, Value)
SELECT '4','Bike', 'Oct 17 2007 12:00AM',100 UNION ALL
SELECT '37','Bike','Oct 17 2007 12:00AM',200 UNION ALL
SELECT '44','Car', 'Oct 17 2007 12:00AM',300 UNION ALL
SELECT '54','Car', 'Oct 17 2007 12:00AM',400 UNION ALL
SELECT '55','Bike','Oct 17 2008 12:00AM',110 UNION ALL
SELECT '81','Bike','Oct 17 2008 12:00AM',220 UNION ALL
SELECT '86','Car', 'Oct 17 2008 12:00AM',290 UNION ALL
SELECT '96','Car', 'Oct 17 2008 12:00AM',375
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF
output should be like this....
Sales Percentage
Productthis year Previous Year
Bike33010%
Car665-5%
Thanks in advance
August 29, 2008 at 2:13 am
DECLARE@Sample TABLE
(
ID INT PRIMARY KEY CLUSTERED,
Product VARCHAR(10),
DateValue DATETIME,
Value MONEY
)
INSERT@Sample
SELECT 1, 'Yak', 'Oct 17 2007 12:00AM', 0 UNION ALL
SELECT 2, 'Yak', 'Oct 17 2008 12:00AM', 2 UNION ALL
SELECT 4, 'Bike', 'Oct 17 2007 12:00AM', 100 UNION ALL
SELECT37, 'Bike', 'Oct 17 2007 12:00AM', 200 UNION ALL
SELECT44, 'Car', 'Oct 17 2007 12:00AM', 300 UNION ALL
SELECT54, 'Car', 'Oct 17 2007 12:00AM', 400 UNION ALL
SELECT55, 'Bike', 'Oct 17 2008 12:00AM', 110 UNION ALL
SELECT81, 'Bike', 'Oct 17 2008 12:00AM', 220 UNION ALL
SELECT86, 'Car', 'Oct 17 2008 12:00AM', 290 UNION ALL
SELECT96, 'Car', 'Oct 17 2008 12:00AM', 375
SELECTProduct,
ThisYear,
COALESCE(LTRIM(STR(100.0 * (ThisYear - PreviousYear) / NULLIF(PreviousYear, 0), 20, 2)) + '%', 'no sale') AS PreviousYear
FROM(
SELECTProduct,
SUM(CASE WHEN DATEDIFF(YEAR, DateValue, GETDATE()) = 0 THEN Value ELSE 0 END) AS ThisYear,
SUM(CASE WHEN DATEDIFF(YEAR, DateValue, GETDATE()) = 1 THEN Value ELSE 0 END) AS PreviousYear
FROM@Sample
GROUP BYProduct
) AS d
ORDER BYProduct
N 56°04'39.16"
E 12°55'05.25"
August 29, 2008 at 2:27 am
Try with something like this:
Declare @ThisYear integer
SET @ThisYear = 2008
Select Product, ThisYearSales, PreviousYearSales,
-- Calculete the percentage only if PreviousYearSales <> 0
CASE
WHEN PreviousYearSales = 0 THEN 0
ELSE ((ThisYearSales-PreviousYearSales)/PreviousYearSales)*100
END as Percentage
FROM
( -- Obtain a derived table wiht the SUM of your sales by YEAR
Select Product,
SUM(CASE YEAR(DateValue) WHEN @ThisYear THEN Value else 0 END) as ThisYearSales,
SUM(CASE YEAR(DateValue) WHEN @ThisYear-1 THEN Value else 0 END) as PreviousYearSales
FROM #mytable
GROUP BY Product
) as Data
And make the corrections necessaries to your work: convert in a sp, limit the sales with a WHERE on DateValue, ...
August 29, 2008 at 2:42 am
Works great thanks a lot..:D
Eric
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply