November 15, 2018 at 8:31 am
Hiya all
Stuck with a query (first post, quite a beginner, but a complex query (i think)
My data is as follows:
TRAID POT ATTID WC LH_MON LH_TUE LH_WED LH_THU LH_FRI TOTAL
200765 1 0001 2018-10-29 0 0 0 0 0 0
200765 1 0002 2018-11-05 7 7 0 0 0 14
200765 1 0003 2018-11-12 7 7 0 0 7 21
What I want to do, is select the TRAID, POT, ATTID, max(WC) where total > 0, What i also want to do generate then is another date off the max(WC) for example,
MAX(WC), Case when LH_FRI > 0 then MAX(WC) + 4 ELSE CASE WHEN LH_THU > 0 then MAX(WC) + 3 etc.
I don't mind if I need to get rid of the MAX(WC) selection, so long as I can get the final date (specific day) that would be great.
Any help please?!
November 15, 2018 at 9:04 am
DEANFORD - Thursday, November 15, 2018 8:31 AMHiya allStuck with a query (first post, quite a beginner, but a complex query (i think)
My data is as follows:
TRAID POT ATTID WC LH_MON LH_TUE LH_WED LH_THU LH_FRI TOTAL
200765 1 0001 2018-10-29 0 0 0 0 0 0
200765 1 0002 2018-11-05 7 7 0 0 0 14
200765 1 0003 2018-11-12 7 7 0 0 7 21What I want to do, is select the TRAID, POT, ATTID, max(WC) where total > 0, What i also want to do generate then is another date off the max(WC) for example,
MAX(WC), Case when LH_FRI > 0 then MAX(WC) + 4 ELSE CASE WHEN LH_THU > 0 then MAX(WC) + 3 etc.
I don't mind if I need to get rid of the MAX(WC) selection, so long as I can get the final date (specific day) that would be great.
Any help please?!
What are your expected result for the dataset you've provided? (p.s. I wrapped your data in a code IF tag, but it's still malformed. Ideally provide your sample data as DDL and DML statements and your expected results as well formatted text.)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 15, 2018 at 9:14 am
Thom A - Thursday, November 15, 2018 9:04 AMDEANFORD - Thursday, November 15, 2018 8:31 AMHiya allStuck with a query (first post, quite a beginner, but a complex query (i think)
My data is as follows:
TRAID POT ATTID WC LH_MON LH_TUE LH_WED LH_THU LH_FRI TOTAL
200765 1 0001 2018-10-29 0 0 0 0 0 0
200765 1 0002 2018-11-05 7 7 0 0 0 14
200765 1 0003 2018-11-12 7 7 0 0 7 21What I want to do, is select the TRAID, POT, ATTID, max(WC) where total > 0, What i also want to do generate then is another date off the max(WC) for example,
MAX(WC), Case when LH_FRI > 0 then MAX(WC) + 4 ELSE CASE WHEN LH_THU > 0 then MAX(WC) + 3 etc.
I don't mind if I need to get rid of the MAX(WC) selection, so long as I can get the final date (specific day) that would be great.
Any help please?!
What are your expected result for the dataset you've provided? (p.s. I wrapped your data in a code IF tag, but it's still malformed. Ideally provide your sample data as DDL and DML statements and your expected results as well formatted text.)
Thanks for your reply!
My expected result is as follows: TRAID POT ATTID WC DATE
200765 1 0003 2018-11-12 2018-11-16
November 17, 2018 at 1:05 pm
declare @t table (TRAID int, POT int, ATTID char(4), WC date, LH_MON int, LH_TUE int, LH_WED int, LH_THU int, LH_FRI int, TOTAL int )
insert into @t values
(200765, 1 ,'001', '2018-10-29', 0, 0, 0, 0, 0, 0),
(200765, 1, '0002','2018-11-05', 7, 7, 0, 0, 0, 14),
(200765, 1, '0003','2018-11-12', 7, 7, 0, 0, 7, 21)
select t.TRAID, t.POT, t.ATTID, v.MaxWC
,
DateAdd(day, case when LH_Fri > 0 then 4
when lh_thu > 0 then 3
when LH_WED > 0 then 2
when lh_tue > 0 then 1
else 0
end, cast(v.MaxWc as smalldatetime))
from @t t
join (
select TRAID, POT, ATTID, max(WC) MaxWC
from @t
where Total > 0
group by TRAID, POT, ATTID) v
on t.TRAID = v.TRAID
and t.POT = v.pot
and t.ATTID = v.ATTID
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 20, 2018 at 2:26 am
Mike01 - Saturday, November 17, 2018 1:05 PMdeclare @t table (TRAID int, POT int, ATTID char(4), WC date, LH_MON int, LH_TUE int, LH_WED int, LH_THU int, LH_FRI int, TOTAL int )insert into @t values
(200765, 1 ,'001', '2018-10-29', 0, 0, 0, 0, 0, 0),
(200765, 1, '0002','2018-11-05', 7, 7, 0, 0, 0, 14),
(200765, 1, '0003','2018-11-12', 7, 7, 0, 0, 7, 21)select t.TRAID, t.POT, t.ATTID, v.MaxWC
,
DateAdd(day, case when LH_Fri > 0 then 4
when lh_thu > 0 then 3
when LH_WED > 0 then 2
when lh_tue > 0 then 1
else 0
end, cast(v.MaxWc as smalldatetime))from @t t
join (
select TRAID, POT, ATTID, max(WC) MaxWC
from @t
where Total > 0
group by TRAID, POT, ATTID) v
on t.TRAID = v.TRAID
and t.POT = v.pot
and t.ATTID = v.ATTID
Thanks for your reply - I've figured a workaround.
My original data is a bit more complex compare to what I've posted, but I've simplified it and created a view with the latest 'WC' date - I'll use this view to then get the specific date.
Thanks for your help!
Dean
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply