Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

access query to sql server

I had this query in access which gave output specifically only where an item went from no value to a value or either value changed
SELECT v.AbiCode, Min(v.ChangeYearMonth) AS minYM, Max(v.ChangeYearMonth) AS maxYM, First(v.transmissionPrev) AS Tprev, Last(v.transmissionChange) AS Tchange
FROM (SELECT DISTINCT AbiCode, ChangeYearMonth, transmissionPrev, transmissionChange FROM Tbltransmission)  AS v
GROUP BY v.AbiCode
HAVING (((Nz(First([v].[transmissionPrev]),""))<>Last([v].[transmissionChange])));

Open in new window


AbiCode               minYM          maxYM      Tprev      Tchange
81140561      2015-08          2015-08                            A
54682560      2015-08          2015-08           A                      M

the data returned is cut down by the query so that where a data change occurred in either tprev or tchange where both have data or only where a null went to a value are given. the only major difference in the sql server table is that fieldname is added in this case would contain transmission instead of the column.

in access each component this was transmission had its own table now the data is stored in one sqlserver table TblCompareEvents which contains all data changes.
PK_ID                                              ClientName      ClientCode      Change      ChangeYearMonth      Prev
48536197201702GROUP_5013              Abi               48536197         13                      201702                      NULL
      
ActualVariance      VehicleCategory      Matched      Actioned      FieldName
      NULL                           C                         NULL                    NULL      GROUP_50

not sure how to achieve a similar query as I dont know how to achieve the first and last Ive sketched out so far:

select v.clientcode, min(changeyearmonth)AS minYM, MAX(ChangeYearMonth) AS maxYM, v.FieldName, v.Prev, v.Change
FROM (SELECT DISTINCT ClientCode, ChangeYearMonth, Prev, Change, FieldName FROM [dbo].[TblCompareEvents])  AS v
GROUP BY clientCode, FieldName,Prev,Change
HAVING FieldName='Transmission'

Open in new window

Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

I think this achieves the same

select v.clientcode, min(changeyearmonth)AS minYM, MAX(ChangeYearMonth) AS maxYM, v.FieldName, v.Prev, v.Change
FROM (SELECT DISTINCT ClientCode, ChangeYearMonth, Prev, Change, FieldName FROM [dbo].[TblCompareEvents])  AS v
GROUP BY clientCode, FieldName,Prev,Change
HAVING FieldName='bhp' and ISNULL(LTRIM(RTRIM(change)),'')<>''

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you