Query Problem - 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

Query Problem

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-09-09 : 12:05:05

Hi,

I have the following query which works OK..

SELECT     TOP (100) PERCENT abc.TblPD.prac_no, MAX(abc.TblCol.col_date) AS MaxColDate, 
                      MAX(abc.TblCol.audit_end + 1) AS audit_start
FROM         abc.TblCol INNER JOIN
                      abc.TblPD ON abc.TblCol.prac_no = abc.TblPD.prac_no INNER JOIN
                      dbo.QryMaxColDate ON abc.TblCol.prac_no = dbo.QryMaxColDate.prac_no AND abc.TblCol.col_date = dbo.QryMaxColDate.col_date
WHERE     (abc.TblPD.prac_status = 'Active') AND (abc.TblCol.stage = 'Processed') 
GROUP BY abc.TblPD.prac_no, abc.TblPD.prac_status
ORDER BY abc.TblPD.prac_no

Now, the update is as follows;

When abc.TblCol.col_type ='FDC3' the abc.TblCol.audit_start = '0', this is already set in the database. Now, I want the record to read the values below as explained

abc.TblPD.prac_no, MAX(abc.TblCol.col_date) AS MaxColDate, 
MAX(abc.TblCol.audit_end + 1) AS audit_start

For MAX(abc.TblCol.audit_end + 1) it should read the previous 'audit_end' (for the record when audit_start IS NOT 0) then + 1 and its corresponding abc.TblCol.col_date.

Lets look at the example below;
(abc.TblCol)

Prac no col_date audit_start audit_end col_type
2 03/06/2001 200 300 IDC
2 08/08/2002 301 500 IDC
10 12/12/2013 175 185 IDC
10 21/01/2014 186 201 IDC
10 25/03/2014 0 205 FDC3

Now, when col_type = FDC3

Read the previous audit_end in our example 201 (because it has the next max col_date), then ofcourse add 1 (202).

So my result will be

abc.TblPD.prac_no, MaxColDate, audit_start
2 08/08/2002 501
10 21/01/2014 202

Please can anyone help me to amend the query above to accomodate what i want to achieve.

Thanks

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-09 : 15:44:11

From your description and your sample data, wouldn't you achieve your goal by simply not reading the records col_type='FDC3' and audit_start=0?
Like this:

SELECT     TOP (100) PERCENT abc.TblPD.prac_no, MAX(abc.TblCol.col_date) AS MaxColDate, 
                      MAX(abc.TblCol.audit_end + 1) AS audit_start
FROM         abc.TblCol INNER JOIN
                      abc.TblPD ON abc.TblCol.prac_no = abc.TblPD.prac_no INNER JOIN
                      dbo.QryMaxColDate ON abc.TblCol.prac_no = dbo.QryMaxColDate.prac_no AND abc.TblCol.col_date = dbo.QryMaxColDate.col_date
WHERE     (abc.TblPD.prac_status = 'Active')
AND       (abc.TblCol.stage = 'Processed')
AND NOT   (abc.TblCol.col_type = 'FDC3'
AND        abc.TblCol.audit_start = '0'
          )
GROUP BY abc.TblPD.prac_no, abc.TblPD.prac_status
ORDER BY abc.TblPD.prac_no

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2014-09-10 : 05:16:36

Tried the above but when I execute SQL changes the WHERE clause changed to;


WHERE     (abc.TblPD.prac_status = 'Active')
 AND (abc.TblCol.stage  = 'Processed') AND (NOT (abc.TblCol.col_type = 'FDC3')) OR
(abc.TblPD.prac_status = 'Active') AND (abc.TblCol.stage = 'Processed') AND (NOT (abc.TblCol.audit_start = '0'))

and it doesn't show

abc.TblPD.prac_no, MaxColDate, audit_start
10 21/01/2014 202

So I changed it to;


WHERE     (abc.TblPD.prac_status = 'Active')
 AND (abc.TblCol.stage  = 'Processed') AND (abc.TblCol.col_type = 'FDC3')) OR
(abc.TblPD.prac_status = 'Active') AND (abc.TblCol.stage = 'Processed') AND (abc.TblCol.audit_start <> '0'))

Results;

abc.TblPD.prac_no, MaxColDate, audit_start
2 08/08/2002 501
10 25/03/2014 206

Instead I want to have;

abc.TblPD.prac_no, MaxColDate, audit_start
2 08/08/2002 501
10 21/01/2014 202

It managed to read the prac_no but it stills reads the MAxcoldate and audit_start of the last record, I really want it to read the one before i.e., audit_start <> 0

Thanks

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-10 : 16:57:45

Which database engine are we dealing with?
Sounds very strange that the sql you write, get changed to something, not even close to what you entered.

Also, please provide sample data from all three tables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-10 : 17:55:12

The syntax looks like the GUI is being used in Management Studio. Open a New Query window instead.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources