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)
LVL 4
JorgenConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Please note that the date on line 2 is the display date. It does not change the date range of the report. To change the date range you need to modify the date in the where clause, here:
WHERE
 convert(varchar,Transactions.DateStamp,112) > '20141125' AND

Open in new window

Say, if you want the dates after 12th of March 2015 (and including the date):
WHERE
 convert(varchar,Transactions.DateStamp,112) >= '20150312' AND

Open in new window

if you want to have a date range then use >= and <, like this (this is for June 2015)
WHERE
 convert(varchar,Transactions.DateStamp,112) >= '20150601' AND
convert(varchar,Transactions.DateStamp,112) < '20150701' AND

Open in new window

If you want the whole history then remove the date filter altogether:
WHERE
 Customer.HotelNumber in (0)
GROUP BY

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JorgenConsultantAuthor Commented:
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
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.