group by query help sql server 2005

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)

group by query help sql server 2005

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-12 : 11:49:07

Hi

I need to check if all invoices for a certain order are in status Closed,Cancelled or Removed. If yes i must update a table.
Order_id can have more than one packlist and that packlist (packlist table) can have more than one invoice (invoice table).
All invoices must have staus Closed,Cancelled or Removed in order to perform some actions against another table.
This is fine so far and i have the query below.
However, if the status is Closed =11 i must also check that the invoicestatus_date for all invoices are older than 6 months.
Only than must i perform the actions for closed.
My problem i think is with the group by. If i add invoicestatus date i will also need to group by date. While this is true for invoicestatus=11 it's not true for all other statuses.

How can i check this? Thanks

Order table
order_id order_date

Packlist table
pl_id pl_date order_id

invoice table
invoice_id invoice_date pl_id

invoicestatus table
invoicestatus_id invoice_id status_id, invoicestatus_date
if object_id('tempdb..#Orders') is not null
drop table #Orders
if object_id('tempdb..#Temp') is not null
drop table #Temp
create table #Orders
(OrderId nvarchar (100))

create table #Temp
(order_id nvarchar (100), invoicestatus_id int)
insert into #Temp
select o.order_id,invoice.invoicestatus_id
from [order] o
inner join packlist on packlist.order_id=o.order_id
inner join invoice on invoice.packlist_id=packlist.packlist_id
--inner join invoicestatushistory on invoicestatushistory.invoice_id=invoice.invoice_id
group by o.order_id, invoice.invoicestatus_id
order by o.order_id

declare @Orders table
(
OrderId nvarchar (500)
)
insert into @Orders
select O.order_id from #Temp O
left outer join
(
--get order ids where invoice is not lcp or cxl
select O.order_id from #Temp O

where invoicestatus_id not in (1,11,12)
)t on t.order_id = O.order_id
where t.order_id is null

order by t.order_id

insert into #Orders (OrderId)
select orderid from @Orders

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 11:51:15

please post some sample data from tables and explain what you want as output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-12 : 15:47:24

Order table

Order_id
O123
O34W
O890X
OI129

PL

pl_id order_id
P12x O123
P090x O123
P9uj9 O34W
P890x O890X
P9misi O890X
Pre343 OI129

invoice table

invoice_id invoicestatus_id invoicestatus_date
Pl_id
I890X 1 12/12/2009 P12x
I789X 3 12/03/10 P12x
PP0C 11 12/09/2009 P12x
PP0X 12 1/09/2008 P12x
PX4T 11 3/3/2007 P090x
P98X 11 12/03/10 P090x
P980B 1 4/4/2007 P090x

In this case all invoices for pl_id p12x aren’t in status 1,11,12 so I mustn’t do anything.
In P090x all invoices are in status 1,11,12 so according to the existing rule I move these invoices to another table. However, I must now add a check that all invoices where status_id=11 occurred over 6 months ago. Only then do I move the invoices.

I am not sure how to modify the query to add this.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 01:57:15

something like:-


INSERT INTO YourDestinationtable
SELECT i.invoice_id,
i.invoicestatus_id,
i.invoicestatus_date,
i.Pl_id
FROM invoice i
WHERE EXISTS(SELECT 1
             FROM Order o
             JOIN PL pl
             ON pl.order_id = o.order_id
             JOIN invoice i1
             ON i1.Pl_id = pl.Pl_id
             WHERE i1.Pl_id=i.Pl_id
             AND o.order_id = @Order_ID
             GROUP BY i1.Pl_id
             HAVING SUM(CASE WHEN i1.invoicestatus_id NOT IN (1,11,12) THEN 1 ELSE 0 END)=0
             AND MAX(CASE WHEN i1.invoicestatus_id=11 THEN invoicestatus_date ELSE NULL END)< DATEADD(mm,DATEDIFF(mm,0,GETDATE())-6,0))

@Order_ID is order value you pass for which you want transfer to happen

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-13 : 02:58:46

Thanks.

I tried your example and where for example pl_id =9900005948 it doesn't return any results but it should. The check for the date is only on the status 11. So in the example below i should get invoices 6498035131 and
6499015089
Where invoice status=11 i need to check that all invoices for the same packlist are older than 6 months.
invoice_id date status pl_id
6498035131 2010-01-05 15:20:00 12 9900005948
6498035558 2010-01-20 13:48:00 11 9900005948
6499015089 2009-11-18 12:26:00 12 9900005948
6499015941 2010-01-06 14:15:00 11 9900005948

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 04:02:12

how would it return them as per your reqmnt all invoices where status_id=11 occurred over 6 months ago but in above case both records with status 11 has dates in jan 2010 which is not 6 months old so it wont return anything

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-15 : 16:34:03

Thanks for the help. I used your example and it worked great. Thanks :)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 00:29:20

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources