mardi 11 août 2015

Select random values from temp table

I'm having some trouble trying to select random values from a temp table in SQL SERVER.

This is the schema and data of the temp table:

DECLARE @Colours TABLE 
(ID TINYINT PRIMARY KEY NOT NULL,
COLOR VARCHAR(30) NOT NULL)

INSERT INTO @Colours
SELECT 0 AS ID, '418CF0' as COLOUR
UNION ALL
SELECT 1, 'FCB441'
UNION ALL
SELECT 2, 'DF3A02'
UNION ALL
SELECT 3, '056492'
UNION ALL
SELECT 4, 'BFBFBF'
UNION ALL
SELECT 5, '1A3B69'
UNION ALL
SELECT 6, 'FFE382'
UNION ALL
SELECT 7, '129CDD'
UNION ALL
SELECT 8, 'CA6B4B'
UNION ALL
SELECT 9, '005CDB'
UNION ALL
SELECT 10, 'F3D288'
UNION ALL
SELECT 11, '506381'
UNION ALL
SELECT 12, 'F1B9A8'
UNION ALL
SELECT 13, 'E0830A'
UNION ALL
SELECT 14, '7893BE'

I want to select a different COLOUR value from temp table for each row of this query:

SELECT 
    PRV.NOM_PROVEE AS PROVEEDOR,
    SUM(IMPORTE_TO) AS TOTAL
FROM 
    FCA_VENTA_HEADER_HIST AS CAB
INNER JOIN
    PORTE_MAILLOT_SA.dbo.CPA01 AS PRV
        ON PRV.COD_PROVEE COLLATE Modern_Spanish_CI_AS = CAB.COD_PROVEE
WHERE
    YEAR(FECHA_EMIS) = 2015
    AND MONTH(FECHA_EMIS) = 7
    AND COD_EMPRESA = 1
GROUP BY
    PRV.NOM_PROVEE

In order to be clearer, I will give an example of what I want to achieve.

Suppose that these are the results of the main query:

+-----------------------------+----------+
|          PROVEEDOR          |  TOTAL   |
+-----------------------------+----------+
| GRANJA MARTIN S.R.L.        | 5116,34  |
| RUSSAN de Antonio Russo     | 705,59   |
| GUACCI EZEQUIEL ADRIAN      | 6908,54  |
| RUSSAN de Antonio Russo     | 3929,26  |
| ARGON PACK S.R.L            | 14716,63 |
| GRANJA MARTIN S.R.L.        | 6800,2   |
| CREMAS HELADAS BAIRES SRL   | 10564,81 |
| LUSTROL ARGENTINA SRL       | 10496,51 |
| DE SANTO S.R.L.             | 4210,8   |
| GALLETITAS BELGRANO         | 5606,7   |
| RUSSAN de Antonio Russo     | 2320,54  |
| UNIVERSAL FISHING S.A       | 1608,82  |
| SADELAR S.R.L               | 8634,41  |
| DE SANTO S.R.L.             | 4210,8   |
| RUSSAN de Antonio Russo     | 2820,07  |
| SERVIFRIO S.A.              | 1928,57  |
| CREMAS HELADAS BAIRES SRL   | 4864,7   |
| ALIMENTOS PUEYRREDON S.R.L. | 15678,67 |
+-----------------------------+----------+

I would like to get something like this:

+-----------------------------+--------+----------+
|          PROVEEDOR          | COLOUR  |  TOTAL  |
+-----------------------------+--------+----------+
| GRANJA MARTIN S.R.L.        | 418CF0 | 5116,34  |
| RUSSAN de Antonio Russo     | FCB441 | 705,59   |
| GUACCI EZEQUIEL ADRIAN      | DF3A02 | 6908,54  |
| RUSSAN de Antonio Russo     | 56492  | 3929,26  |
| ARGON PACK S.R.L            | BFBFBF | 14716,63 |
| GRANJA MARTIN S.R.L.        | 1A3B69 | 6800,2   |
| CREMAS HELADAS BAIRES SRL   | FFE382 | 10564,81 |
| LUSTROL ARGENTINA SRL       | 129CDD | 10496,51 |
| DE SANTO S.R.L.             | CA6B4B | 4210,8   |
| GALLETITAS BELGRANO         | 005CDB | 5606,7   |
| RUSSAN de Antonio Russo     | F3D288 | 2320,54  |
| UNIVERSAL FISHING S.A       | 506381 | 1608,82  |
| SADELAR S.R.L               | F1B9A8 | 8634,41  |
| DE SANTO S.R.L.             | E0830A | 4210,8   |
| RUSSAN de Antonio Russo     | 7893BE | 2820,07  |
| SERVIFRIO S.A.              | 005CDB | 1928,57  |
| CREMAS HELADAS BAIRES SRL   | 129CDD | 4864,7   |
| ALIMENTOS PUEYRREDON S.R.L. | 1A3B69 | 15678,67 |
+-----------------------------+--------+----------+

The number of rows returned by the query can be variable, so it doesn't matter if the colors are repeated in the result set. I need to get a different COLOUR (when I refer to different COLOUR I'm talking about the order in which are selected) value associated with every single row everytime I run the query.




Aucun commentaire:

Enregistrer un commentaire