Help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Help with query

Author

Topic

legollas
Starting Member

3 Posts

Posted - 2015-02-26 : 16:50:20

I want to convert this list into 2 rows 1 for each branchid with the value to Today(2015-01-01), Tomorrow and SUM of day (4,5,6,7)

Can someone tell me a way of accomplish this?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-26 : 17:09:16

You'll need to show us what it should look like after the conversion as I can't picture your explanation.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

legollas
Starting Member

3 Posts

Posted - 2015-02-26 : 17:59:11

Sorry tkizer

The list in the previous post has to be transformed into this one

Branchid TodayValue TomorrowValue NextDaysVallue
1 21 25 374
2 21 25 337

Assuming that today is 2015-01-01

legollas
Starting Member

3 Posts

Posted - 2015-02-26 : 18:02:35

Each branch has 7 values one for each day assuming that today is day 2015-01-01
1 row for each branchid with the value of today(2015-01-01) the value of tomorrow(2015-01-02) and the sum of values days (2015-01-03,2015-01-04,2015-01-05,2015-01-06,2015-01-07)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-02 : 12:23:18

Please provide test data in consumable format in future.
It may seem like a bit of an effort, but people will be more inclined to answer your question.


CREATE TABLE #t
(
	id int NOT NULL
	,value int NOT NULL
	,[datetime] datetime NOT NULL
	,branchid int NOT NULL
);
INSERT INTO #t
VALUES (1, 21, '20150101', 1)
	,(2, 25, '20150102', 1)
	,(3, 25, '20150103', 1)
	,(4, 254, '20150104', 1)
	,(5, 21, '20150105', 1)
	,(6, 20, '20150106', 1)
	,(7, 54, '20150107', 1)
	,(8, 21, '20150101', 2)
	,(9, 25, '20150102', 2)
	,(10, 215, '20150103', 2)
	,(11, 25, '20150104', 2)
	,(12, 25, '20150105', 2)
	,(13, 24, '20150106', 2)
	,(14, 48, '20150107', 2);

The following seems to work:


DECLARE @Today datetime = '20150101'; --DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0);

SELECT branchid
	,SUM(CASE WHEN [datetime] = @Today THEN value ELSE 0 END) AS TodayValue
	,SUM(CASE WHEN [datetime] = @Today + 1 THEN value ELSE 0 END) AS TomorrowValue
	,SUM(CASE WHEN [datetime] BETWEEN @Today + 2 AND @Today + 6 THEN value ELSE 0 END) AS NextDaysVallue
FROM #t
GROUP BY branchid;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-02 : 13:24:13

[code]-- SwePeso
DECLARE @Today DATETIME = '20150101';

SELECT BranchID,
SUM(CASE WHEN Offset = 0 THEN Value ELSE 0 END) AS TodayValue,
SUM(CASE WHEN Offset = 1 THEN Value ELSE 0 END) AS TomorrowValue,
SUM(CASE WHEN Offset >= 2 THEN Value ELSE 0 END) AS NextDaysValue
FROM (
SELECT BranchID,
DATEDIFF(DAY, @Today, [datetime]) AS Offset,
Value
FROM #t
) AS d
GROUP BY BranchID;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources