April 10, 2016 at 8:20 pm
This is a sql server question although I am using it within Visual Basic
One of the Visual Basic apps that I developed tracks number of service calls within a 60 day period by date. I create a table containing 60 rows
with a date and service call count field in each row. I run a query that counts the number of service calls by date and order by count number in descending and date in ascending order so the date with the most calls is the first set of numbers followed in order by count and date. In order to show this in one screen, i have created a single table that has 3 sets of two columns with 20 rows each, the first set of columns is the date with the most counts, count 1 through 20, then the second set of numbers show the 21st date through 40, count,
then 41st date through date 60 with count. I do this so I can show the data on one screen without scrolling as shown below.
Date Count Date Count Date Count
02/05/162002/01/1612 01/08/166
01/31/161802/22/1612 02/10/166
01/13/161701/04/1611 02/18/166
02/03/161701/12/1611 02/29/166
01/14/161602/26/1611 01/25/165
01/15/161601/16/1610 02/02/165
01/26/161601/21/1610 02/09/165
01/06/161502/04/1610 02/16/165
01/10/161502/20/1610 01/07/164
01/27/161502/24/1610 01/18/164
02/17/161501/22/169 02/08/164
02/06/161401/28/169 02/28/164
01/03/161302/23/169 01/05/163
01/09/161302/25/169 02/07/163
01/20/161302/15/168 02/27/163
02/14/161301/11/167 01/01/162
02/21/161301/29/167 01/02/162
01/19/161202/11/167 01/24/162
01/23/161202/13/167 02/12/162
01/30/161202/19/167 01/17/161
This works, and is extremely fast, but still it is a sledge hammer approach that I use to do this. I create a table with the dates and counts in the correct order. Then I create a table for each set of numbers 1-20, 21-40, and 41 -60. Then I do a join on each of the tables to put the numbers back in one table. I know this is crude and un-wieldy. How can I do this with one table. I tried doing it with unions and joins in the same table, but can't get it to work. I tried to google the solution, but don't have much luck. Is there a simple solution that I am missing? Below is the code:
<Code>
Dim Con5 As New SqlConnection
Dim Cmd5 As New SqlCommand
Sql_String(1) = "Drop Table Last_Service_Count;"
Sql_String(2) = "Drop Table Last_Service_Count_temp2;"
Sql_String(3) = "Drop Table Last_Service_Count_set1;"
Sql_String(4) = "Drop Table Last_Service_Count_set2;"
Sql_String(5) = "Drop Table Last_Service_Count_set3;"
Sql_String(6) = "Select ROW_NUMBER() OVER(ORDER BY Last_Service_Count_draw Desc,Last_Service_Count_Date) As 'Last_Daily_index', Last_Service_Count_Date,Last_Service_Count_Draw into Last_Service_Count_temp2 from Last_Service_Count_temp"
Sql_String(7) = "Select ROW_NUMBER() OVER(ORDER BY Last_Service_Count_draw Desc,Last_Service_Count_Date) As 'Last_Daily_index', Last_Service_Count_Date,Last_Service_Count_Draw into Last_Service_Count_Set1 from Last_Service_Count_Temp2 where Last_Daily_index between 1 and 20 "
Sql_String(8) = "Select ROW_NUMBER() OVER(ORDER BY Last_Service_Count_draw Desc,Last_Service_Count_Date) As 'Last_Daily_index', Last_Service_Count_Date,Last_Service_Count_Draw into Last_Service_Count_Set2 from Last_Service_Count_Temp2 where Last_Daily_index between 21 and 40 "
Sql_String(9) = "Select ROW_NUMBER() OVER(ORDER BY Last_Service_Count_draw Desc,Last_Service_Count_Date) As 'Last_Daily_index', Last_Service_Count_Date,Last_Service_Count_Draw into Last_Service_Count_Set3 from Last_Service_Count_Temp2 where Last_Daily_index between 41 and 60 "
Sql_String(10) = "Select
a.Last_Daily_index,
a.Last_Service_Count_Date As Last_Service_Count_Date1, a.Last_Service_Count_Draw as Last_Service_Count_Draw1,
b.Last_Service_Count_Date As Last_Service_Count_Date2, b.Last_Service_Count_Draw as Last_Service_Count_Draw2,
c.Last_Service_Count_Date As Last_Service_Count_Date3, c.Last_Service_Count_Draw as Last_Service_Count_Draw3,
into Last_Service_Count
From Last_Service_Count_Set1 a
Left Join Last_Service_Count_Set2 b
On a.Last_Daily_index = b.Last_Daily_index
Left Join Last_Service_Count_Set3 c
On a.Last_Daily_index = c.Last_Daily_index
For x = 1 To 110
Try
Con5.ConnectionString = "Server=Jocarlap\SQLEXPRESS;Database = Keno;Integrated Security=SSPI;"
Con5.Open()
Cmd5.Connection = Con5
Cmd5.CommandText = Sql_String(x)
Cmd5.ExecuteNonQuery()
Catch ex As Exception
Finally
Con5.Close()
End Try
Next x
<Code/>
Thanks for looking at it.
April 11, 2016 at 7:15 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2016 at 7:36 am
This code is meant to be run in SSMS, then you can transform it into VB code. I would suggest to convert it to a Stored Procedure and call the procedure from VB.
As Sean mentioned, you should post DDL and sample data in a consumable format. My code includes it only because you're new in here.
I need to create coordinates to define rows and columns for each value. After that, I just use a method called cross tabs. Read more about it in here: http://www.sqlservercentral.com/articles/T-SQL/63681/
CREATE TABLE Last_Service_Count_temp(
Last_Service_Count_Date date,
Last_Service_Count_Draw int);
INSERT INTO Last_Service_Count_temp VALUES
('02/05/16',20),
('01/31/16',18),
('01/13/16',17),
('02/03/16',17),
('01/14/16',16),
('01/15/16',16),
('01/26/16',16),
('01/06/16',15),
('01/10/16',15),
('01/27/16',15),
('02/17/16',15),
('02/06/16',14),
('01/03/16',13),
('01/09/16',13),
('01/20/16',13),
('02/14/16',13),
('02/21/16',13),
('01/19/16',12),
('01/23/16',12),
('01/30/16',12),
('02/01/16',12),
('02/22/16',12),
('01/04/16',11),
('01/12/16',11),
('02/26/16',11),
('01/16/16',10),
('01/21/16',10),
('02/04/16',10),
('02/20/16',10),
('02/24/16',10),
('01/22/16',9 ),
('01/28/16',9 ),
('02/23/16',9 ),
('02/25/16',9 ),
('02/15/16',8 ),
('01/11/16',7 ),
('01/29/16',7 ),
('02/11/16',7 ),
('02/13/16',7 ),
('02/19/16',7 ),
('01/08/16',6 ),
('02/10/16',6 ),
('02/18/16',6 ),
('02/29/16',6 ),
('01/25/16',5 ),
('02/02/16',5 ),
('02/09/16',5 ),
('02/16/16',5 ),
('01/07/16',4 ),
('01/18/16',4 ),
('02/08/16',4 ),
('02/28/16',4 ),
('01/05/16',3 ),
('02/07/16',3 ),
('02/27/16',3 ),
('01/01/16',2 ),
('01/02/16',2 ),
('01/24/16',2 ),
('02/12/16',2 ),
('01/17/16',1 );
WITH CTE1 AS( --Create groups for the columns
SELECT
NTILE(3) OVER(ORDER BY Last_Service_Count_draw Desc,Last_Service_Count_Date) As hLast_Daily_index,
Last_Service_Count_Date,
Last_Service_Count_Draw
FROM Last_Service_Count_temp
),
CTE2 AS( --Create a row number for each row partitioned by column
SELECT
ROW_NUMBER() OVER( PARTITION BY hLast_Daily_index ORDER BY Last_Service_Count_draw Desc,Last_Service_Count_Date) AS vLast_Daily_index,
hLast_Daily_index,
Last_Service_Count_Date,
Last_Service_Count_Draw
FROM CTE1
) --Use cross tabs to convert rows into columns
SELECT MAX(CASE WHEN hLast_Daily_index = 1 THEN Last_Service_Count_Date END) AS Last_Service_Count_Date1,
MAX(CASE WHEN hLast_Daily_index = 1 THEN Last_Service_Count_draw END) AS Last_Service_Count_draw1,
MAX(CASE WHEN hLast_Daily_index = 2 THEN Last_Service_Count_Date END) AS Last_Service_Count_Date2,
MAX(CASE WHEN hLast_Daily_index = 2 THEN Last_Service_Count_draw END) AS Last_Service_Count_draw2,
MAX(CASE WHEN hLast_Daily_index = 3 THEN Last_Service_Count_Date END) AS Last_Service_Count_Date3,
MAX(CASE WHEN hLast_Daily_index = 3 THEN Last_Service_Count_draw END) AS Last_Service_Count_draw3
FROM CTE2
GROUP BY vLast_Daily_index;
If you're sure that there will only be 60 rows each time, the code can be simpler.
WITH
CTE2 AS(
SELECT
(ROW_NUMBER() OVER( ORDER BY Last_Service_Count_draw Desc,Last_Service_Count_Date) + 19) % 20 AS vLast_Daily_index,
(ROW_NUMBER() OVER( ORDER BY Last_Service_Count_draw Desc,Last_Service_Count_Date) + 19) / 20 AS hLast_Daily_index,
Last_Service_Count_Date,
Last_Service_Count_Draw
FROM Last_Service_Count_temp
)
SELECT MAX(CASE WHEN hLast_Daily_index = 1 THEN Last_Service_Count_Date END) AS Last_Service_Count_Date1,
MAX(CASE WHEN hLast_Daily_index = 1 THEN Last_Service_Count_draw END) AS Last_Service_Count_draw1,
MAX(CASE WHEN hLast_Daily_index = 2 THEN Last_Service_Count_Date END) AS Last_Service_Count_Date2,
MAX(CASE WHEN hLast_Daily_index = 2 THEN Last_Service_Count_draw END) AS Last_Service_Count_draw2,
MAX(CASE WHEN hLast_Daily_index = 3 THEN Last_Service_Count_Date END) AS Last_Service_Count_Date3,
MAX(CASE WHEN hLast_Daily_index = 3 THEN Last_Service_Count_draw END) AS Last_Service_Count_draw3
FROM CTE2
GROUP BY vLast_Daily_index;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply