sql query from text files

[ajm]

hi all, i have three text files on a server which form a particular report. I also have the SQL query used to create the report for another division. I have tried to create the query in access to get around the "charge for service" model our IT team has adopted, but am not particularly gifted at reading SQL.

SELECT
'/// select the following fields from 3 tables, "e", "ero", & "emr" (used twice)
      e.UNIBIS_Equipment_Number,
      e.Equipment_Number,
      e.Equipment_Description,
      ero.Operator_Code,
      ero.Operator_Name,
      emr_p.Meter_Reading_Date AS Previous_Meter_Reading_Date,
      emr_p.Meter_Reading_Value AS Previous_Meter_Reading_Value,
      emr_l.Meter_Reading_Date AS Current_Meter_Reading_Date,
      emr_l.Meter_Reading_Value AS Current_Meter_Reading_Value,
      CASE
'///identify which records
            WHEN emr_p.Meter_Reading_Date IS NULL THEN
'///if date NULL, then 0
                  0
            ELSE
                  DATEDIFF(DAY, emr_p.Meter_Reading_Date, emr_l.Meter_Reading_Date)
      END AS Number_Days,
      CASE
            WHEN emr_p.Meter_Reading_Value IS NULL THEN
                  0
            ELSE
                  (emr_l.Meter_Reading_Value - emr_p.Meter_Reading_Value)
      END AS Number_Kilometers,
      CASE
            WHEN emr_p.Meter_Reading_Date IS NULL THEN
                  0
            ELSE
                  ROUND((emr_l.Meter_Reading_Value - emr_p.Meter_Reading_Value) / DATEDIFF(DAY, emr_p.Meter_Reading_Date, emr_l.Meter_Reading_Date), 2)
      END AS Average_per_Day
      
FROM
      DW_ODS.dbo.T_UNIBIS_Equipment e
INNER JOIN
      DW_ODS.dbo.T_UNIBIS_Equipment_Role_Operator ero
      ON
                  e.UNIBIS_Equipment_Number = ero.UNIBIS_Equipment_Number
            AND ero.Entity_Code <> '0NX'
            AND ero.Role_Code = 'ADMIN'
INNER JOIN
      (SELECT
            emr.UNIBIS_Equipment_Number,
            emr.Meter_Code,
            emr.Meter_Reading_Date,
            emr.Meter_Reading_Value
      FROM  
            DW_ODS.dbo.T_UNIBIS_Equipment_Meter_Reading emr
      INNER JOIN
            (SELECT
                  UNIBIS_Equipment_Number,
                  Meter_Code,
                  MAX(Meter_Reading_Date) AS Meter_Reading_Date
            FROM
                  DW_ODS.dbo.T_UNIBIS_Equipment_Meter_Reading
            GROUP BY
                  UNIBIS_Equipment_Number,
                  Meter_Code) emr_mrd
            ON
                        emr.UNIBIS_Equipment_Number = emr_mrd.UNIBIS_Equipment_Number
                  AND emr.Meter_Reading_Date = emr_mrd.Meter_Reading_Date
                  AND emr.Meter_Code = emr_mrd.Meter_Code) emr_l
      ON
                  e.UNIBIS_Equipment_Number = emr_l.UNIBIS_Equipment_Number
            AND emr_l.Meter_Code LIKE 'ODO%'
LEFT JOIN
      (SELECT
            emr.UNIBIS_Equipment_Number,
            emr.Meter_Code,
            emr.Meter_Reading_Date,
            emr.Meter_Reading_Value
      FROM  
            DW_ODS.dbo.T_UNIBIS_Equipment_Meter_Reading emr
      INNER JOIN
            (SELECT
                  emr.UNIBIS_Equipment_Number,
                  emr.Meter_Code,
                  MAX(emr.Meter_Reading_Date) AS Meter_Reading_Date
            FROM
                  DW_ODS.dbo.T_UNIBIS_Equipment_Meter_Reading emr
            INNER JOIN
                  (SELECT
                        UNIBIS_Equipment_Number,
                        Meter_Code,
                        MAX(Meter_Reading_Date) AS Meter_Reading_Date
                  FROM
                        DW_ODS.dbo.T_UNIBIS_Equipment_Meter_Reading
                  GROUP BY
                        UNIBIS_Equipment_Number,
                        Meter_Code) emr_mrd
                  ON
                              emr.UNIBIS_Equipment_Number = emr_mrd.UNIBIS_Equipment_Number
                        AND emr.Meter_Reading_Date < emr_mrd.Meter_Reading_Date
                        AND emr.Meter_Code = emr_mrd.Meter_Code
            GROUP BY
                  emr.UNIBIS_Equipment_Number,
                  emr.Meter_Code) emr_prd
            ON
                        emr.UNIBIS_Equipment_Number = emr_prd.UNIBIS_Equipment_Number
                  AND emr.Meter_Reading_Date = emr_prd.Meter_Reading_Date
                  AND emr.Meter_Code = emr_prd.Meter_Code) emr_p
      ON
                  e.UNIBIS_Equipment_Number = emr_p.UNIBIS_Equipment_Number
            AND emr_p.Meter_Code LIKE 'ODO%'
ORDER BY
                  e.UNIBIS_Equipment_Number

as i understand this, there are three tables, prefaced above by "e", "ero", & "emr". and these correspond to the text files. I get lost after this. can someone help me to replicate this query in access. the output from it is a simple spreadsheet listing the fields across the page.

 

[dk]

Hi

e, ero, emr and so on are aliases for tables - so rather than writing this:

SELECT MYTABLE.SOMEFIELD, MYTABLE.SOMEOTHERFIELD FROM MYTABLE

you can write this:

SELECT M.SOMEFIELD, M.SOMEOTHERFIELD FROM MYTABLE M << - the "M" is the alias.

If you want to convert this to Access SQL then you need to change the CASE statements to something that Access supports e.g. the Switch statement - it's not as clean as the CASE structure but can be used to get the same result.

The rest of the SQL should work ok in Access. What I'd suggest is removing the entire CASE statement (from the initial CASE to the "END AS Average_Per_Day" and replacing it with something like:


SELECT

.....
Other code here
.....

emr_l.Meter_Reading_Date AS Current_Meter_Reading_Date,
emr_l.Meter_Reading_Value AS Current_Meter_Reading_Value,
99 AS Average_per_Day

FROM
DW_ODS.dbo.T_UNIBIS_Equipment e
INNER JOIN
DW_ODS.dbo.T_UNIBIS_Equipment_Role_Operator ero
....
...

Once you can get that part of the code working then post back if you have problems getting the Switch statement to work.

HTH
DK

 

[ajm]

dk, thanks for that. I have removed the case section entirely (just for expediency at the moment). I can't figure out how to create the data source (machine or file DSN) to point the query at the three text files. Do i do a dsn for each? alternatively, if i import the three as linked tables, how do i tie them in with the existing sql (removing case, as you instructed prev iously)? i have tried removing the " DW_ODS.dbo." from in front of the table names but that wasn't a success. cheers

 

[dk]

You should just be able to link to the text files. Once you've imported them rename them to the same as the original query but drop the DW_ODS.dbo as you can't have full stops in Access table names e.g. DW_ODS.dbo.T_UNIBIS_Equipment will be named T_UNIBIS_Equipment

As long as you rename them correctly you should then be able to do a find/replace in the SQL for the DW_ODS.dbo and replace with nothing.

HTH
DK

 

[ajm]

still nothing. should i keep the aliases:

e.UNIBIS_Equipment_Number,
e.Equipment_Number,
e.Equipment_Description,
erperator_Code,
erperator_Name,
emr_p.Meter_Reading_Date AS Previous_Meter_Reading_Date,
emr_p.Meter_Reading_Value AS Previous_Meter_Reading_Value,
emr_l.Meter_Reading_Date AS Current_Meter_Reading_Date,
emr_l.Meter_Reading_Value AS Current_Meter_Reading_Value,

or remove them:

UNIBIS_Equipment_Number,
Equipment_Number,
Equipment_Description,
Operator_Code,
Operator_Name,
Meter_Reading_Date AS Previous_Meter_Reading_Date,
Meter_Reading_Value AS Previous_Meter_Reading_Value,
Meter_Reading_Date AS Current_Meter_Reading_Date,
Meter_Reading_Value AS Current_Meter_Reading_Value,

and further on:

FROM
DW_ODS.dbo.T_UNIBIS_Equipment e
INNER JOIN
DW_ODS.dbo.T_UNIBIS_Equipment_Role_Operator ero
ON
e.UNIBIS_Equipment_Number = ero.UNIBIS_Equipment_Number
AND ero.Entity_Code <> '0NX'
AND ero.Role_Code = 'ADMIN'

then will be:

FROM
T_UNIBIS_Equipment
INNER JOIN
T_UNIBIS_Equipment_Role_Operator
ON
UNIBIS_Equipment_Number = UNIBIS_Equipment_Number
AND Entity_Code <> '0NX'
AND Role_Code = 'ADMIN'