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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Condition select max(....)

Author  Topic 

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2010-04-19 : 13:56:01
I have an existing query that I need to modify and I am not sure how to do this:

Rule is: When I select a max shift from the SALESTKT table (this is the "shift_started" field), I need to make sure that this shift is closed and not open. The way to do this is to check against a SHIFTDATE table for field "shift_started". If there is a match then shift is open and I need to get the prior "shift_started" from the SALESTKT table (ie record prior to the max) as this is the last shift that was completed.

This is the current query I have and that needs to be modified.



select * from openquery(ADS_RGWP_SERVER, 'select
                                             max(shift_started) as shift,
                                             sum(case when customer_type = ''Inter-Company'' then qty_shipped_today else 0 end) as intercompany_qty,
                                             sum(case when customer_type = ''Charge'' then qty_shipped_today else 0 end) as outside_qty
                                          from salestkt')

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-04-19 : 16:46:56
Please show us some sample data to help illustrate your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2010-04-20 : 08:09:37
When I run the query against the SALESTKT table, my max value for field "shift_started" is '4/19/2010 7:07:00 PM', this is then the most recent shift. At the moment this shift could be active or be closed so I need to check the status. In order to check the status I need to query the SHIFTDATE table using the "shift_started" from the SALESTKT table. Looking at the current entry in the SHIFTDATE table I see following value '4/19/2010 7:07:00 PM' in it's "shift_started" field. This tells me that the shift is still open ("shift_started" in the SALESTKT table matches "shift_started" in the SHIFTDATE table). If there is no match then the shift is closed. In this case I am encountering and open shift. Now I need to retrieve the "shift_date" from the SALESTKT table that is next recent after the max value and in this case that will be '4/19/2010 1:58:00 PM'.

If there is a no match (between SALESTKT table and SHIFTDATE table I will use the max value and I am Ok.

Not sure if I made myself clear, bottom line is that I will have to select data from the SALESTKT table on a "shift_started" date/time where shift is closed.

Thank you.
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

264 Posts
Posted - 2010-04-20 : 09:06:06
Try something like this:

SELECT *
  FROM OPENQUERY(ADS_RGWP_SERVER, 'SELECT MAX(shift_started) as shift,
                                          SUM(CASE WHEN customer_type = ''Inter-Company''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer_type = ''Charge''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt AS SKT
                                    WHERE NOT EXIST(SELECT *
                                                      FROM SHIFTDATE AS SD
                                                     WHERE SD.shift_started = SKT.shift_started)')
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2010-04-20 : 15:42:04
Hi malpashaa,

It looks like it pulls in the correct "shift_started" record but quantities seems to be added from different shift started records.
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2010-04-20 : 16:38:21
See tkizer's post.

It's far easier to assist you if you provide sample data and desired results.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

264 Posts
Posted - 2010-04-21 : 06:37:53
I did not touch your sum part of the query, so would you clarify what you need the sum to return to be able to help you.
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2010-04-21 : 10:30:14
Hi malpashaa,

I know you did not touch the sum part of the query.

Here is what I need: I need the sum for the records for the selected "shift_date". Now it seems that it adds up all quantities from the very beginning.
Below is an extract of some data, you will see shift_date 2010-4-19 and 2010-4-21. Depending of what my max is I need to sum quantities just for records that have the selected shift_date.

QTY        Cust Type       Shift Date


182	Inter-Company  	2010-04-19 19:07:00.000
1436	Inter-Company  	2010-04-19 19:07:00.000
26.68	Inter-Company  	2010-04-19 19:07:00.000
174.38	Inter-Company  	2010-04-19 19:07:00.000
126.53	Inter-Company  	2010-04-19 19:07:00.000
351.47	Inter-Company  	2010-04-19 19:07:00.000
140.87	Inter-Company  	2010-04-19 19:07:00.000
3.1	Inter-Company  	2010-04-21 06:17:00.000
22.48	Inter-Company  	2010-04-21 06:17:00.000
1000	Inter-Company  	2010-04-21 06:17:00.000
23	Inter-Company  	2010-04-21 06:17:00.000
45.09	Inter-Company  	2010-04-21 06:17:00.000
8.12	Charge         	2010-04-21 06:17:00.000
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2010-04-21 : 10:51:39
so are you saying whatever the max value in qty is, select that record and SUM ALL MATCHING shift dates?



Selct aa.QTY
,aa.[Cust Type]
,aa.[Shift Date]
,sum(case when bb.[Shift Date] = 'INTER-COMPANY' then bb.QTY else 0 end) 
,sum(case when not  bb.[Shift Date] = 'INTER-COMPANY' then bb.QTY else 0 end)
(
Select Row_Number() over (Order by a.[Shift Date] DESC) as RowID,*
from mytable a) aa
inner join
MYtable bb
on aa.[Shift Date] = bb.[Shift Date]
Where aa.RowID = 1
group by 
aa.QTY
,aa.[Cust Type]
,aa.[Shift Date]



Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2010-04-21 : 11:16:45
whatever the max value of shift_date I need sum of all quantities matching the shift_date.

How to determine shift_date see post above:

SELECT *
  FROM OPENQUERY(ADS_RGWP_SERVER, 'SELECT MAX(shift_started) as shift,
                                          SUM(CASE WHEN customer_type = ''Inter-Company''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer_type = ''Charge''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt AS SKT
                                    WHERE NOT EXIST(SELECT *
                                                      FROM SHIFTDATE AS SD
                                                     WHERE SD.shift_started = SKT.shift_started)')
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2010-04-21 : 11:29:19
Did you try my redited query? As stated in tkizer's post, you should post your desired results with the sample data you provide.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2010-04-21 : 16:14:22
Hi Vinnie881,

I have not be able to get your solution to work so far.

I've been looking at a simpler approach (not very professional I guess but I hope it will work).

1. First I get the shift started date I need then
2. I use the shift started date to query and sum the quantities


DECLARE @shift_started as datetime
SET @shift_started = (SELECT *
FROM OPENQUERY(ADS_RGWP_SERVER, 'SELECT MAX(shift_started) as shift
                                    FROM salestkt AS STK
                                    WHERE NOT EXISTS(SELECT *
                                                      FROM SHFTDATE AS SD
                                                      WHERE SD.shift_started = STK.shift_started)'))
SELECT *
  FROM OPENQUERY(ADS_RGWP_SERVER, '  sum(case when customer_type = ''Inter-Company'' then qty_shipped_today else 0 end) as intercompany_qty,
                                     sum(case when customer_type = ''Charge'' then qty_shipped_today else 0 end) as outside_qty
                                     FROM salestkt 
                                      WHERE shift_started ='" & @shift_started & "' ''')


I get the correct shift date in the @shift_started variable but having problem with using the "@shift_started" in the where clause, guess a syntax issue:

Incorrect syntax near ' & @shift_started & '.
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

264 Posts
Posted - 2010-04-21 : 18:21:59
Try this:

SELECT *
  FROM OPENQUERY(ADS_RGWP_SERVER, 'SELECT SUM(CASE WHEN customer_type = ''Inter-Company''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer_type = ''Charge''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt
                                    WHERE shift_started = (SELECT MAX(STK.shift_started) as shift
                                                             FROM salestkt AS STK
                                                            WHERE NOT EXISTS(SELECT *
                                                                               FROM SHFTDATE AS SD
                                                                              WHERE SD.shift_started = STK.shift_started))')
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2010-04-21 : 20:33:12
Try this and let me know how it works.

Also in the future try to provide sample data and desired results. 


Select 
aa.Shift_Started as YourMaxdate
,sum(case when bb.Shift_Started = 'Inter-Company' then bb.QTY_Shipped_Today else 0 end) as intercompany_Qty
,sum(case when bb.Shift_Started = 'Charge' then bb.QTY_Shipped_Today else 0 end) AS outside_Qty
from
(
	Select Row_Number() over (Order by a.Shift_Started DESC) as RowID,*
	from ADS_RGWP_SERVER...salestkt a
) aa
inner join
	ADS_RGWP_SERVER...salestkt bb
on		aa.Shift_Started = bb.Shift_Started
Left Join
	ADS_RGWP_SERVER...SHFTDATE cc
on		aa.Shift_Started = cc.Shift_Started
Where 
	aa.RowID = 1
and cc.Shift_Started is null
group by 
aa.Shift_Started


Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881
Go to Top of Page

snufse
Constraint Violating Yak Guru

469 Posts
Posted - 2010-04-22 : 13:51:35
I've been trying to get malpashaa's code to work and with a few additions it seems to render correct results. We have about 10 different data bases and the query works for all except one. Here is the code:

SELECT *
  FROM OPENQUERY(ADS_RGGT_SERVER, 'SELECT MAX(SHIFT_STARTED) as shift_date,
                                          SUM(CASE WHEN customer_description_1 = ''Ranger Construction''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer_description_1 <> ''Ranger Construction''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt
                                     WHERE (void is null or void = false) and
                                           incoming_material = false and 
                                           shift_started = (SELECT MAX(STK.shift_started) as shift_started
                                                             FROM salestkt AS STK
                                                              WHERE NOT EXISTS(SELECT *
                                                                                FROM SHFTDATE AS SD
                                                                                 WHERE SD.shift_started = STK.shift_started))


Getting error:

Error converting data type DBTYPE_DBTIMESTAMP to datetime.

Data:

Salestkt table

13.64 2/23/2010 9:33:00 AM
27.13 2/23/2010 9:33:00 AM
92.00 2/23/2010 9:33:00 AM
9.52 2/23/2010 4:08:00 PM - this line is marked with Void=True

Shftdate table

Plant ID                Shift Started Date
Astec Plant             5/25/2007 11:25:00 AM
G                       Nothing



Based upon the logic in the query it should pick up the 2/23/2010 9:33:00 AM records in the salestkt table (since the last record is void = true)

If I run the query like this:


SELECT *
  FROM OPENQUERY(ADS_RGGT_SERVER, 'SELECT MAX(SHIFT_STARTED) as shift_date,
                                          SUM(CASE WHEN customer_description_1 = ''Ranger Construction''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer_description_1 <> ''Ranger Construction''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt
                                     WHERE (void is null or void = false) and
                                           incoming_material = false 
                                     ')


I get result:

2010-02-23 09:33:00.000   3587837.01   	3852547.62000001


Any ideas?
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site