JuniorDev JuniorDev - 1 month ago 7
SQL Question

LOOP and COUNT in a SELECT statement SQL SERVER

I have a view with these columns

enter image description here

and i want to extract this data

enter image description here

i am doing it in SQL Server manually in several steps and i want to automate it so that i can run a select statement directly from a macro and save it in excel file.
Here is what i am doing

select distinct
CASE
WHEN Userid ='jsolar' THEN 'Jack Solar'
WHEN Userid ='jkrcmarikova' THEN 'Jana Krcmarikova'
WHEN Userid ='lfialova' THEN 'lucia fialova'
WHEN Userid ='zsnopkova' THEN 'zuzana snopkova'
END AS [User Name]
, Region
from [SC].[vw_X86_Orders_By_UserID_GAMMA]
order by Region, [User Name]


Then i copy the result in excel file and run other queries for each user

SELECT Count ( DISTINCT [Order Number])
FROM [SC].[vw_X86_Orders_By_UserID_GAMMA]
where Userid LIKE 'jsolar'
AND Region LIKE 'CENTRAL'
and [Order Entry Date] = '2016-10-27'


i save this result in number of distinct order number. Then i run this query

SELECT Count ( DISTINCT CONCAT ([Order Number], [Line No]))
FROM [SC].[vw_X86_Orders_By_UserID_GAMMA]
where Userid LIKE 'jsolar'
AND Region LIKE 'CENTRAL'
and [Order Entry Date] = '2016-10-27'


I save this result in number of distinct order number concatenated with line no. And i repeat the same for each user

At the end it should be something like this in Excel

enter image description here

Is there a way how to do this in one select statement to loop each user and count for all users at the same time ? Thank you very much.

Answer

I really don't think you need a "LOOP". SQL operates best in set based operations returning recordsets with many rows. So we need to treat [SC].[vw_X86_Orders_By_UserID_GAMMA] as an entire set and simply update the case statement to translate all users names (assuming you have to have them, or you could do a vlookup on the userID in excel after the fact)

I think what you're really after is the count(distinct column) in combination with a group by on userID and region.

Based on comments I think you would need to amend the case statements in the select and group by to contain the translation for all the users.

I think there's too many unknowns to provide a 100% correct response but here's a shot across the bow..

SELECT CASE WHEN Userid ='jsolar' THEN 'Jack Solar'
            WHEN Userid ='jkrcmarikova' THEN 'Jana Krcmarikova'
            WHEN Userid ='******' THEN '**** *******'
            WHEN Userid ='****' THEN '***** ****' END AS [User Name]
, Region
, count(distinct [Order Number]) as cntDistinctOrders
, count(Distinct concat([order Number], [Line No]) as cntDistinctOrderLines

FROM [SC].[vw_X86_Orders_By_UserID_GAMMA]

WHERE [Order Entry Date] = '2016-10-27'
 -- and Region = 'CENTRAL'  don't think you need this the group by handles same names in different regions keeping them distinct and counts seperate.

GROUP BY CASE WHEN Userid ='jsolar' THEN 'Jack Solar'
              WHEN Userid ='jkrcmarikova' THEN 'Jana Krcmarikova'
              WHEN Userid ='******' THEN '**** *******'
              WHEN Userid ='****' THEN '***** ****'
              END
, Region

ORDER BY [User Name], Region

To put this in plain English...

You want all the usernames and regions for each user in the [SC].[vw_X86_Orders_By_UserID_GAMMA] schema.table showing the distinct count of orders and order lines for a specific date.

If you can use a Vlookup in excel for the names you could getaway without the case statements and all that extra code..

SELECT UserId [User Name] , Region , count(distinct [Order Number]) as cntDistinctOrders , count(Distinct concat([order Number], [Line No]) as cntDistinctOrderLines

FROM [SC].[vw_X86_Orders_By_UserID_GAMMA]

WHERE [Order Entry Date] = '2016-10-27'
 -- and Region = 'CENTRAL'  don't think you need this the group by handles same names in different regions keeping them distinct and counts seperate.

GROUP BY CASE UserID
, Region

ORDER BY [User Name], Region

--note because the group by executes before the select statement, we have to group by the USERID and not the alias name of [User name]

Comments