• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

select query

Hi all,

Please find my attached select query, I am trying to use case statements in the select query to create new columns. Please correct my query.

Thanks in advance
parpaa  
select acno, datediff(dd,dateadd(dd,-19,[duedate]),getdate()) as deli,'over due'=case when
datediff(dd,dateadd(dd,-19,[duedate]),getdate())>19 then 'YES' else 'NO'
end ,'service line'= case when[subs]='*ISDN'then 'PRI' else '-' end,
'PRI'= case when
(case when[subs]='*ISDN'then 'PRI' else '-' end)
then 1 else 0
end
into t1 from tabel

Open in new window

0
parpaa
Asked:
parpaa
4 Solutions
 
dileepan_kCommented:
select acno, datediff(dd,dateadd(dd,-19,[duedate]),getdate()) as deli,
      'over due'=case when datediff(dd,dateadd(dd,-19,[duedate]),getdate())>19 then 'YES' else 'NO' end ,
      'service line'= case when[subs]='*ISDN' then 'PRI' else '-' end,
      'PRI'= case when (case when[subs]='*ISDN' then 'PRI' else '-' end) = 'PRI' then 1 else 0 end
into t1
from tabel
0
 
Rajkumar GsSoftware EngineerCommented:
SELECT  acno ,
        DATEDIFF(dd, DATEADD(dd, -19, [duedate]), GETDATE()) AS deli ,
        'over due' = CASE WHEN DATEDIFF(dd, DATEADD(dd, -19, [duedate]),
                                        GETDATE()) > 19 THEN 'YES'
                          ELSE 'NO'
                     END ,
        'service line' = CASE WHEN [subs] = '*ISDN' THEN 'PRI'
                              ELSE '-'
                         END ,
        CASE WHEN 'PRI' = ( CASE WHEN [subs] = '*ISDN' THEN 'PRI'
                                 ELSE '-'
                            END ) THEN 1
             ELSE 0
        END
INTO    t1
FROM    tabel

Open in new window

0
 
Anthony PerkinsCommented:
If I was to guess:
SELECT  acno,
        DATEDIFF(dd, DATEADD(dd, -19, [duedate]), GETDATE()) AS deli,
        CASE WHEN DATEDIFF(dd, DATEADD(dd, -19, [duedate]), GETDATE()) > 19 THEN 'YES'
             ELSE 'NO'
        END 'over due',
        CASE WHEN [subs] = '*ISDN' THEN 'PRI'
             ELSE '-'
        END 'service line',
        CASE WHEN [subs] = '*ISDN' THEN 1
             ELSE 0
        END 'PRI'
INTO    t1
FROM    tabel

Open in new window

0
 
LowfatspreadCommented:
this?
select acno,  deli
,'over due'=case when deli >19 then 'YES' else 'NO' end 
,'service line'= case when[subs]='*ISDN'then 'PRI' else '-' end
,'PRI'= case when[subs]='*ISDN' then 1 
   else 0 end
into t1 
from (select *
           , datediff(dd,dateadd(dd,-19,[duedate]),getdate()) as deli
      from tabel
     ) as x

Open in new window

0
 
parpaaAuthor Commented:
Thank you so much for your help. it worked
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now