October 16, 2017 at 12:49 am
Hi Experts
I am trying to use local variable table , the code works fine , if the NAME IN ( 'NAME1') is only one if i add more Name in the query it provides wrong results for both ' NAME1' & 'NAME2'
NAME IN ('NAME1', 'NAME2' )
Also i want to delete time to time decalred local variable i am trying DELETE FROM @st
but no work the reason of deleting since i added
NAME IN ('NAME1', 'NAME2' )
and removed the any one of the field still the values are not correct before when i used only one field the values was ok
DECLARE @st TABLE
(
[itemId] [char](32) NOT NULL ,
[Date] [datetime] NOT NULL ,
[EndDate] [datetime] NOT NULL,
[NAME] [char](32) NOT NULL ,
[Prod] [numeric](28, 12) NULL,
[RunningTotal] [numeric](28, 12) NULL
PRIMARY KEY ( [itemId] , [Date],[EndDate])
);
DECLARE @RunningTotal [numeric](28, 12) = 0;
INSERT @st([Date],[itemId],[NAME], [EndDate],[Prod], RunningTotal)
SELECT
DATE_START ,ITEM,
NAME, DATE_END FROM TABLE
NAME IN ('NAME1', 'NAME2' )
ORDER BY START_DATETIME;
UPDATE @st
SET @RunningTotal = RunningTotal = @RunningTotal + Prod
FROM @st
OPTION (FORCE ORDER);
SELECT [Date],Prod , RunningTotal
FROM @st
order BY [Date];
October 16, 2017 at 1:25 am
You say it provides "Wrong results", however, you have provided us any data to work with, or what you expect the right results to be. This'll be difficult to troubleshoot without that.
Could you please provide DDL and DLM for your table "TABLE", and your expected result set? Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 16, 2017 at 6:31 am
Why is there an ORDER BY in your INSERT?
Why have you added OPTION (FORCE ORDER) to a query which contains only one table?
If you want to do a 'quirky update' properly, please check this. Particularly the clustered index and MAXDOP requirements.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 16, 2017 at 7:17 am
Hi Experts
Here is the below Table defined and insert statements.
CREATE TABLE [dbo].[TEST](
[ITEM] [char](32) NOT NULL ,
[DATE_START] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[NAME] [char](32) NOT NULL ,
[Prod] [numeric](28, 12) NULL,
)
GO
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e6','2015-05-02 23:00', '2015-05-02 23:00', 'NAME1' ,10 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e6','2015-05-03 23:00', '2015-05-03 23:00', 'NAME1' ,20 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e6','2015-05-04 23:00', '2015-05-04 23:00', 'NAME1' ,30 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e6','2015-05-05 23:00', '2015-05-05 23:00', 'NAME1' ,40 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e6','2015-05-06 23:00', '2015-05-06 23:00', 'NAME1' ,50 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e6','2015-05-07 23:00', '2015-05-07 23:00', 'NAME1' ,60 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e6','2015-05-08 23:00', '2015-05-08 23:00', 'NAME1' ,70 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e7','2015-05-02 23:00', '2015-05-02 23:00', 'NAME2' ,100 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e7','2015-05-03 23:00', '2015-05-03 23:00', 'NAME2' ,110 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e7','2015-05-04 23:00', '2015-05-04 23:00', 'NAME2' ,120 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e7','2015-05-05 23:00', '2015-05-05 23:00', 'NAME2' ,130 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e7','2015-05-06 23:00', '2015-05-06 23:00', 'NAME2' ,140 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e7','2015-05-07 23:00', '2015-05-07 23:00', 'NAME2' ,150 );
INSERT [dbo].[TEST] ([ITEM], [DATE_START], [EndDate], [NAME], [Prod] ) VALUES('20607ddfa3a445ceb1fb03e76c8620e7','2015-05-08 23:00', '2015-05-08 23:00', 'NAME2' ,160 );
DECLARE @st TABLE
(
[itemId] [char](32) NOT NULL ,
[Date] [datetime] NOT NULL ,
[EndDate] [datetime] NOT NULL,
[NAME] [char](32) NOT NULL ,
[Prod] [numeric](28, 12) NULL,
[RunningTotal] [numeric](28, 12) NULL
PRIMARY KEY ( [itemId] , [Date],[EndDate])
);
DECLARE @RunningTotal [numeric](28, 12) = 0;
INSERT @st([Date],[itemId],[NAME], [EndDate],[Prod], RunningTotal)
SELECT
DATE_START ,ITEM,
NAME, EndDate,Prod ,RunningTotal = 0 FROM TEST
WHERE NAME IN ('NAME1','NAME2' )
ORDER BY DATE_START;
UPDATE @st
SET @RunningTotal = RunningTotal = @RunningTotal + Prod
FROM @st
OPTION (FORCE ORDER);
SELECT NAME, [Date],Prod , RunningTotal
FROM @st
order BY [NAME];
Running with one Name NAME IN ('NAME1' ) the results fine but running with ('NAME1','NAME2' ) the RunningTotal Column does not provide accurate results .
October 16, 2017 at 7:32 am
Instead of trying to use a self referencing variable (and asking for trouble) why not use the OVER clause?SELECT
DATE_START ,ITEM,
NAME, EndDate,Prod , SUM(Prod) OVER (ORDER BY Date_Start ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM TEST
WHERE NAME IN ('NAME1','NAME2' )
ORDER BY DATE_START;
Your image doesn't explain what is wrong with the data, so I don't know what is. Instead, however, I have maintained the ordering by Date_start.
If you want to do separate counts by Name, you'll need to add a PARTITION.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 16, 2017 at 8:12 am
Hi Thom A,
By using over clause the results still not getting ok , please find the results attachment.
October 16, 2017 at 8:18 am
Why is it not right..?
That's also what is that resultset. Seems correct to me.
As I've already asked twice already, what is your expected result set?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 16, 2017 at 10:21 am
If you see the column name ( NAME ) there are two different names NAME1 and NAME2 we have to calculate the totals based on NAME1 and NAME2.
Find the attachment containing expected results .
October 16, 2017 at 10:35 am
I'm not happy to download a stranger's Excel document I'm afraid (you'll find few who are). Sorry.
I covered doing separate counts, however, in my original answer:
If you want to do separate counts by Name, you'll need to add a PARTITION.
Have a look at using the OVER clause and have a go at adding the PARTITION so that you have separate Running Totals by Name. SELECT - OVER Clause (Transact-SQL)
If you get stuck post again, with what you tried. I'll be able to give you pointers on where you went wrong, or what you need to change. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 16, 2017 at 10:43 am
Using Thom's code as a start:
SELECT
DATE_START
, ITEM
, NAME
, EndDate
, Prod
, SUM(Prod) OVER (PARTITION BY NAME ORDER BY Date_Start ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM
TEST
WHERE
NAME IN ('NAME1','NAME2' )
ORDER BY
DATE_START;
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply