Link to home
Avatar of Jorgen
JorgenFlag for Denmark

asked on

How to change my SQL Statement in PowerQuery

Hi Experts

I have a SQL statement that I can import to a Power Query

I received it from the supplier of our hotel system, but they are not very helpful apart from giving me the SQL code.

at the moment I can change the code in line 2 and I am able to see the specific date in Power Query.

I want to be able to import all days after a specific date, and afterwards I want my datamodel to be updated with new months

I am well aware, that the information might not be of the best quality, but hopefully you can guide me anyway

SELECT
  '01.06.2015' as 'DateStamp',
isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) as 'Account Number',
 Accounts.AccountText as 'Account Text',
 SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)) as 'PriceOverWrite Without Vat',
 SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)* CASE WHEN Accounts.AccountType IN (4,5,6) THEN 0 ELSE (VatValue/100) END) as 'PriceOverWrite Vat',
 0 as 'Reserved (AccountType)',
 isnull(Accounts.ExportDepartmentNumber, 110) as 'Export Department Number',
 
                (case when isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) = 20210 then 5 else
                case when isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) = 20215 then 6 else
                case when isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) = 20222 then 7 else
                case when isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) = 20248 then 5 else
                case when isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) = 20258 then 4 else null
                end
                end
                end
                end
                end)
             as 'Account alias',
 Customer.HotelNumber as 'Hotel Number',
 CASE WHEN (Reservations.Project IS NULL) THEN IsNull(Accounts.ExportProjectCodeNumber, 0) ELSE IsNUll(Reservations.ReferenceNumber, 0) END as 'ExportProjectcodeNumber',
 Departments.Departmentnumber as 'Active Department Number',
 99999 as 'Reserved (99999)'
FROM Accounts (NOLOCK)
 FULL OUTER JOIN TurnOverGroups WITH (NOLOCK) ON Accounts.TurnOverGroupNumber = TurnOverGroups.NUMBER
 FULL OUTER JOIN AccountGroups WITH (NOLOCK) ON Accounts.AccountGroupNumber = AccountGroups.AccountGroupNumber
 FULL OUTER JOIN Transactions WITH (NOLOCK) ON Accounts.AccountNumber = Transactions.AccountNumber
 INNER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = Transactions.FolioNumber
 LEFT JOIN Reservations WITH (NOLOCK) ON (Reservations.ReferenceNumber = Folios.ReferenceNumber) AND (Reservations.GuestNumber = Folios.GuestNumber)
 LEFT OUTER JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = Transactions.FK_DepartmentNumber
 INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = Transactions.HotelCustomerNumber
 INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = Transactions.WareNumber
 INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
WHERE
 convert(varchar,Transactions.DateStamp,112) > '20141125' AND
 Customer.HotelNumber in (0)
GROUP BY
 
                CASE WHEN (Reservations.Project IS NULL) THEN IsNull(Accounts.ExportProjectCodeNumber, 0) ELSE IsNUll(Reservations.ReferenceNumber, 0) END
            ,
 
                (case when isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) = 20210 then 5 else
                case when isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) = 20215 then 6 else
                case when isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) = 20222 then 7 else
                case when isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) = 20248 then 5 else
                case when isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) = 20258 then 4 else null
                end
                end
                end
                end
                end)
            ,
 Accounts.AccountNumber,
 Accounts.AccountText,
 Accounts.ExportAccountNumber,
 Accounts.ExportDepartmentNumber,
 Customer.HotelNumber,
 Departments.Departmentnumber
HAVING
 SUM(Transactions.PriceOverwrite * (CASE Transactions.Debit WHEN 1 THEN 1 ELSE -1 END)) <> 0
ORDER BY
  isnull(Accounts.ExportAccountNumber, Accounts.AccountNumber) ,
  isnull(Customer.HotelNumber, -1)
ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Jorgen

ASKER

Hi Chaau

It all sounds as wise advice. Unfortunately I will not be with the data before thursday, where I can give it a try.

But can you explain to me, why I have to use the >= and not just the > as I did try. And the other part I would like to understand if I can just replace the datestamp with the transactions.DateStamp to get the date for all the transactions? I would expect so, but as I can read simple SQL statements, a statement of this kind is a Little to complex.

regards

Jørgen
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.