Creating a key and counting

[Anonymous]

I am trying to create a key based on the following fields:

I need a key based on the [Script Number] and Branch.  Where the [Script Number] and the Branch make a unique key, this should generate the same key for example [Script Number] 102356 and Branch [First] should generate the same key for the same combination.  I would then want a count of this key, something like the following:

[Distinct Script Data]:
BUFFER LOAD [Script Number],
AutoNumber([Script Number], [Branch]) as Script#,
Count Script# as Rx
Group by [Script Number];
SQL SELECT DISTINCT
    "Script Number"
FROM PharmaMarketAudit.dbo.tblDatawarehouseConsolidatedCleanStage
WHERE [Date Dispensed] >= '2012-01-01' and [Date Dispensed] <= '2015-08-31';

How can this be accomplished?

Regards

[jagan]

HI,

Try like this

[Distinct Script Data]:
BUFFER LOAD [Script Number],
AutoNumber([Script Number] & [Branch]) as Script#,
Count Script# as Rx
Group by [Script Number];
SQL SELECT DISTINCT
    "Script Number"
FROM PharmaMarketAudit.dbo.tblDatawarehouseConsolidatedCleanStage
WHERE [Date Dispensed] >= '2012-01-01' and [Date Dispensed] <= '2015-08-31';

Regards,

Jagan.

[Anonymous]

Thanks Jagan, i will try it out and advise accordingly.

Regards.