How to combine these sql queries in Excel

Question

I have a data sheet in excel and I want to get some information from it by using ado connection in excel vba. I have written 3 queries to accomplish this work and will write a few more. Data looks like this,

Let me explain it a little bit. Order No is the work order of a job. Resource Id is actually a unique number for a machine. Duru Kodu is the code explains the reason of the stop between Bitim Zamani(End Time) and Basl Zamani(Start Time). Qty is the production quantity.

I showed the data for only EM22 but there are more machines of course.

Stop codes are actually divided into two which are planned and unplanned. Codes for planned are D00 D10 D11. Other codes from D01 to D30 are unplanned stops.

I want total planned stops, unplanned stops and quantity for a specific Resource Id and group them by Order No.

I couldn't come up with a query that will do all the things at once but divided it to three. First one for getting unplanned stops. Second one is for Planned stops and the last one is for Quantity.

'Specified Machine Number(EM22)
makine = Sheets("Dashboard").Cells(2, 11).Value

    'Unplanned
sorgu1 = "select [Resource Id], [Order No], Sum(([Bitim Zamani]-[Basl Zamani])*1440) as Sure"
sorgu1 = sorgu1 + " from [Data$] where [Resource Id] = " + "'" + makine + "'"
sorgu1 = sorgu1 + " AND "
sorgu1 = sorgu1 + "([Duru Kodu] = 'D01' OR [Duru Kodu] = 'D02' OR [Duru Kodu] = 'D03' OR [Duru Kodu] = 'D04' OR [Duru Kodu] = 'D05' OR [Duru Kodu] = 'D06' OR [Duru Kodu] = 'D07' OR [Duru Kodu] = 'D08' OR [Duru Kodu] = 'D09' OR [Duru Kodu] = 'D12' OR [Duru Kodu] = 'D13' OR [Duru Kodu] = 'D14' OR [Duru Kodu] = 'D15' OR [Duru Kodu] = 'D16' OR [Duru Kodu] = 'D17' OR [Duru Kodu] = 'D18' OR [Duru Kodu] = 'D19' OR [Duru Kodu] = 'D20' OR [Duru Kodu] = 'D21' OR [Duru Kodu] = 'D22' OR [Duru Kodu] = 'D23' OR [Duru Kodu] = 'D24' OR [Duru Kodu] = 'D25' OR [Duru Kodu] = 'D26' OR [Duru Kodu] = 'D27' OR [Duru Kodu] = 'D28' OR [Duru Kodu] = 'D29' OR [Duru Kodu] = 'D30')"
sorgu1 = sorgu1 + "GROUP BY [Resource Id], [Order No]"

    'Planned
sorgu2 = "SELECT Sum(([Bitim Zamani]-[Basl Zamani])*1440) as Surem from [Data$] where [Resource Id] = " + "'" + makine + "'" + " AND "
sorgu2 = sorgu2 + "([Duru Kodu] = 'D00' OR [Duru Kodu] = 'D10' OR [Duru Kodu] = 'D11') "
sorgu2 = sorgu2 + "GROUP BY [Resource Id], [Order No]"

'Quantity
sorgu3 = "SELECT Sum([Qty]) as Uretim from [Data$] where [Resource Id] =" + "'" + makine + "'" + " group by [Resource Id],[Order No]"

The main question here is that if there is a way to combine those queries.
Also there is a another problem which is shown below. What it should be is like in the yellow part.

But outcome is in the grey part produced by sql queries doesnt match when a sum is equal to 0.

YowE3K · Accepted Answer · 2016-11-02 20:13:28Z

1

I think the following code should work.

'Specified Machine Number(EM22)
makine = Sheets("Dashboard").Cells(2, 11).Value

sorgu1 = "SELECT [Resource Id], " & _
                "[Order No], " & _
                "Sum(([Bitim Zamani]-[Basl Zamani])*IIF(([Duru Kodu] = 'D00' OR [Duru Kodu] = 'D10' OR [Duru Kodu] = 'D11'),0,1440)) as Sure " & _
                "Sum(([Bitim Zamani]-[Basl Zamani])*IIF(([Duru Kodu] = 'D00' OR [Duru Kodu] = 'D10' OR [Duru Kodu] = 'D11'),1440,0)) as Surem " & _
         "FROM [Data$] " & _
         "WHERE [Resource Id] = '" & makine & "' AND " & _
         "      [Duru Kodu] <> '0' "
         "GROUP BY [Resource Id], [Order No]"

Note: The test for [Duru Kodu] <> '0' may need to be changed to something like Not IsNull([Duru Kodu]) (I think that is the right syntax) if the value which shows up in your spreadsheet as 0 is actually a null.

edited Nov 2, 2016 at 20:13

answered Oct 31, 2016 at 15:36

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ali srn Over a year ago

I will try this as soon as I can find a way to work this on mac.

ali srn Over a year ago

Can you change the second sum function? You wrote conditions as equal. one of them should be D01 to D30. You can just indicate it, that would be enough. And that is working. Thanks. :)

YowE3K Over a year ago

@alisrn - The second sum function is multiplying by 1440 in the cases where the first one is multiplying by zero, and vice-versa. So, depending on the value of Duru Kodu only one of the two sums will be added to for each record.

YowE3K Over a year ago

@alisrn - Ahhh - I just noticed that there are cases where you don't want to aggregate the values into either of the two Sums, i.e. when Duru Kodu is equal to 0. I'll edit the answer and add a condition to the where clause to prevent them being selected.

Collectives™ on Stack Overflow

How to combine these sql queries in Excel

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related