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/
Go to Top of Page

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
Go to Top of Page

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)
Go to Top of Page

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;
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -