flag rows - 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

General SQL Server Forums

New to SQL Server Programming

flag rows

Author

Topic

MichelleMabbs
Starting Member

24 Posts

Posted - 2014-12-02 : 11:14:51

I would like to recreate the following table

I have tried a windowing function but with no luck as the current value will exceed the total value as time ticks on through out the day

if someone could find a resolution to return the flag column I would be grateful

AreA Time On Plan Report Retrieved Flag LatestReport
UK 07:50:00 02/12/2014 15:00 Total 3
UK 06:33:00 02/12/2014 15:00 Current 3
UK 07:50:00 02/12/2014 16:00 Total 2
UK 06:53:00 02/12/2014 16:00 Current 2
UK 07:50:00 02/12/2014 17:00 Total 1
UK 07:59:00 02/12/2014 17:00 Current 1
Wales 06:00:00 02/12/2014 15:00 Total 3
Wales 05:50:00 02/12/2014 15:00 Current 3
Wales 06:00:00 02/12/2014 16:00 Total 2
Wales 05:52:00 02/12/2014 16:00 Current 2
Wales 06:00:00 02/12/2014 17:00 Total 1
Wales 06:55:00 02/12/2014 17:00 Current 1

Michelle

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 11:30:31

Given this data, what would the results of a correct query look like?

MichelleMabbs
Starting Member

24 Posts

Posted - 2014-12-02 : 11:37:15

Hi sorry to mislead that table is how I would like it to look like

AreA Time On Plan Report Retrieved Flag LatestReport
UK 07:50:00 02/12/2014 15:00 Total 3
UK 06:33:00 02/12/2014 15:00 Current 3

Michelle

MichelleMabbs
Starting Member

24 Posts

Posted - 2014-12-02 : 11:37:16

MichelleMabbs
Starting Member

24 Posts

Posted - 2014-12-02 : 11:40:24

sorry its not clear,

I require it to be like the one attached, I am struggling to format the table

area 'time on plan' reportretrieved flag 'latest report'
uk 07:50 01-12-2014 15:00 Total 3
uk 06:50 01-12-2014 15:00 Current 3
uk 07:50 01-12-2014 16:00 Total 2
uk 06:59 01-12-2014 16:00 Current 2
uk 07:50 01-12-2014 17:00 Total 1
uk 07:59 01-12-2014 17:00 Current 1

Michelle

MichelleMabbs
Starting Member

24 Posts

Posted - 2014-12-02 : 11:41:32

Basically a windowing rank function will not suffice as the current value will eventually exceed the total value

I am trying to flag the duplicated times on plan so I can have two tables one with the total values and one with the current values in

hope it makes sense

Michelle

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 11:42:59

OK -- though I don't understand how you compute the value for flag.

MichelleMabbs
Starting Member

24 Posts

Posted - 2014-12-02 : 11:45:40

so if the time on plan has a re occurring value such as 17:59:00 then flag it as say the total, if it is a new time then flag as Current.

Michelle

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 13:00:30

OK - since I don't have your whole query (or sample data) I can only give you a hint.


SELECT ...
     , case count(*) over(partition by AreA, Time, [On Plan], [Report Retrieved], LatestReport)
         when 1 then 'Current'
         else 'Total'
       end as Flag
FROM yourtable

MichelleMabbs
Starting Member

24 Posts

Posted - 2014-12-03 : 07:01:35

Hi thank you this has solved the problem

Michelle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources