CODEWITHSUNDEEP
sqlexcelselectsubqueryvba
ali srn
ali srn

Reputation: 573

How to combine these sql queries in Excel

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, Data

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. what it is

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

Upvotes: 0

Views: 60

Answers (1)

YowE3K
YowE3K

Reputation: 23984

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.

Upvotes: 1

Related Questions