SQL QUERY Rows where TotalScore=@TotalScore in the range of value by using group by Date and Time
-
Dear Expert,
I am seeking your help .
here is the scenario , please see the Figure -1
I have 3 parameters .1. @StartDate, 2. @EndDate and 3. @FinalScore
Parameter @startDate and @EndDate is set and no need to change it.
but my requirement is @FinalScore. which user set as 300/12000/25000 FinalScore
if users earn 300 Score in a one day then shows data only those rows exactly date and time (starting - ending) when exactly reach 300' user may earn 300+ Score but we retrieve when users reached 300 milestone with date and time exactly.
In Figure to we saw MSISDN =8801985705555 only get 12000 final score. so if we set parameter 12000 the only those rows shows who score 12000 from starting date to when user reached 12000 score. user may score 12000+ . as you seen in figure -1.I have attached a table rows with name. please find the attached file. and paste my script.
---------- -SCRIPT - ----------
GO
DECLARE @StartDate DATE;
DECLARE @EndDate DATE;
DECLARE @FinalScore intSET @StartDate ='2017-04-26'
SET @EndDate ='2017-04-26'SELECT TOP 10
ROW_NUMBER() OVER(ORDER BY (CASE WHEN q.TOTALRightScore IS NOT NULL THEN q.TOTALRightScore ELSE 0 END*2 -
CASE WHEN v.TOTALWrongScore IS NOT NULL THEN v.TOTALWrongScore ELSE 0 END) DESC) AS Position,
t.MSISDN, CASE WHEN q.TOTALRightScore IS NULL THEN 0 WHEN q.TOTALRightScore IS NOT NULL THEN q.TOTALRightScore ELSE 0 END [Right]
, CASE WHEN q.TOTALRightScore IS NOT NULL THEN q.TOTALRightScore ELSE 0 END*2 [Right Score:2]
, CASE WHEN v.TOTALWrongScore IS NULL THEN 0 WHEN v.TOTALWrongScore IS NOT NULL THEN v.TOTALWrongScore ELSE 0 END [Wrong: -1]
,( CASE WHEN q.TOTALRightScore IS NOT NULL THEN q.TOTALRightScore ELSE 0 END*2 -
CASE WHEN v.TOTALWrongScore IS NOT NULL THEN v.TOTALWrongScore ELSE 0 END ) AS [Final Score],
LEFT(CONVERT(VARCHAR(10),b.StartTime,105),10) StartDate , SUBSTRING(CONVERT(varchar(50),b.StartTime,21),11,20) StartTime,
LEFT(CONVERT(VARCHAR(10),a.EndTime,105),10) StartDate , SUBSTRING(CONVERT(varchar(50),a.EndTime,21),11,20) StartTime
FROM tbl_Quiz_Log t
LEFT JOIN (SELECT t2.MSISDN, COUNT (t2.MSISDN)TOTALRightScore
FROM tbl_Quiz_Log t2 WHERE t2.userresult =1 AND Confirmation =1
AND CAST(t2.Lastupdate AS DATE) >= @StartDate AND CAST(t2.Lastupdate AS DATE) <= @EndDate
GROUP BY t2.MSISDN )Q ON Q.MSISDN = t.MSISDN
LEFT JOIN (SELECT MSISDN, COUNT (MSISDN)TOTALWrongScore
FROM tbl_Quiz_Log t2 WHERE t2.userresult =0 AND Confirmation =1
AND CAST(t2.Lastupdate AS DATE) >= @StartDate AND CAST(t2.Lastupdate AS DATE) <= @EndDate
GROUP BY t2.MSISDN )v ON v.MSISDN = t.MSISDN
LEFT JOIN (SELECT MSISDN, MIN(Lastupdate) StartTime FROM tbl_Quiz_Log
WHERE Confirmation =1
AND CAST(Lastupdate AS DATE) >= @StartDate AND CAST(Lastupdate AS DATE) <= @EndDate
GROUP BY MSISDN ) b ON b.MSISDN = t.MSISDN
LEFT JOIN (SELECT MSISDN, MAX(Lastupdate) EndTime FROM tbl_Quiz_Log
WHERE Confirmation =1
AND CAST(Lastupdate AS DATE) >= @StartDate AND CAST(Lastupdate AS DATE) <= @EndDate
GROUP BY MSISDN ) a ON a.MSISDN = t.MSISDN
WHERE CAST(t.Lastupdate AS DATE) >= @StartDate AND CAST(t.Lastupdate AS DATE) <= @EndDate --26-04-2017 Gakk
GROUP BY t.MSISDN ,Q.TOTALRightScore,v.TOTALWrongScore ,a.EndTime,b.StartTime
ORDER BY [Final Score] DESC, a.EndTime ASC---CREATE TABLE --------
CREATE TABLE [dbo].[tbl_Quiz_Log](
[TransactionID] [uniqueidentifier] NOT NULL,
[serviceid] [int] NOT NULL,
[MSISDN] [varchar](13) NOT NULL,
[QuestionNO] [int] NOT NULL,
[Score] [int] NOT NULL,
[Confirmation] [int] NOT NULL,
[correlator] [varchar](50) NULL,
[userresult] [bigint] NULL,
[Lastupdate] [datetime] NOT NULL,
CONSTRAINT [PK_tbl_Quiz_Log_10] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
------
my scripts work fine but need to update as per requirement. please help me.
-
Can you please post some sample data as an insert statement?
😎 -
Dear Eirikur Eiriksson
Here I attached a zipped file.
SQL script with data. -
Last 2 column of figure-1, 2
is EndDate and EndTime -
This looks like an interesting problem. Key questions:
1) if a user has 3 100 value rows and they put in 300, do you show all 3 of the 100 rows (and then nothing after if any)?
Or do you just show the last 100 that got them to the 300 total?2) If a user puts in 300 but only has 2 100 value rows, show nothing?
3) What about a scenario where they have a 100 and then a 400? Do you show that they reached 300 at the time the 400 went in - and then show a total of 300 or 500?
4) Are there any other conditions with different rules/outputs?
5) ALWAYS look to avoid hitting the same object over and over like you have. I presume that is the reason you posted here - because performance is bad? Or is it just not getting the output you seek?
BTW, that table structure is er ... suboptimal. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Dear Boles,
My query works fine and served their purpose till now. but now they need more requirements:
First Requirement:
this is a SMS text quiz program. winner select as daily basis. user who reached first 300 point(final Score) in a day then he will win a daily program contest. and we will select everyday a winner who arrive first 300 point (final score). this is our first requirement. the quiz organizer will set the parameter start day (26-04-2017) , end day (20-05-2017) and final score =300.
select the day wise 1st, 2nd, 3rd .... etc. who reached first 300 points(Score).
User may score 300+ but we want (Date/Time) when he reached exact 300 points(Score)Second requirement:
the quiz organizer will set the parameter start day (26-04-2017) , end day (20-05-2017) and final score =12000.
and select who arrived first 12000 points (Final Score). only one contestant will win this event who arrived first.
User may score 12000+ but we want (Date/Time) when he reached exact 12000 points(Score)Third requirement:
Same as requirement Second
the quiz organizer will set the parameter start day (26-04-2017) , end day (20-05-2017) and final score =25000.
and select who arrived first 25000 points (Final Score). only one contestant will win this event who arrived first.
User may score 25000+ but we want (Date/Time) when he reached exact 25000 points(Score)I know table structure is not optimal. and only one object is hitting over and over. but i don't change anything because my client have an application and don't want to change. they want only result as i mention above.
In Table :
MSISDN = Mobile no,
confirmation = 1 (charging)
confirmation = 0 (not charging)
userresult=1 ( correct answer )
userresult=0 ( wrong answer )
lastupdate = this is the main factor of this quiz. date and time. who arrived first.
if confirmation = 1 and userresult=1 then user get the point(Score) 2
if confirmation = 0 and userresult=1 then point deduct -1 (Score) point
if confirmation = 1 and userresult=0 then point deduct -1 (Score) point -
So this seems to be a pretty straight-forward running-total problem, where you simply get the earliest contestant with a running total that crosses some minimum score. Read up stuff by Itzik Ben-Gan on those (and get a copy of his 2012 windowing function book). Also Aaron Bertrand has a great blog post on something like 7 different mechanisms to calculate running totals.
My gut tells me this should be doable in a single pass on the table with the AWESOME windowing function enhancements available in SQL Server 2012+, combined with some spiffy CASE usage (which you are already familiar with it seems).
BTW, if your clustered index were on that date field I am betting the solution would be a good bit faster since you will be doing a range predicate on that and likely an order by/partition by too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Dear Boles,
Yes, windows have some AWESOME function. but my Production OS is windows 2008 and SQL server 2008R2.
I tried it but get an error.
and bound function is not allowed with Order by / partition in SQL server 2008. - imneaz - Monday, May 15, 2017 6:17 AM
Ahh, sorry. Didn't even notice we were in the 2008 forum.
Look up the "quirky update" mechanism here on SSC.com for doing running totals and see if that helps. It works on all versions I think. Note that you MUST follow the rules as presented.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
According to SUM (Transact-SQL), SUM() OVER(<partition> ORDER BY <order clause> ) is available starting in SQL-2008, which is what I was remembering.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Wednesday, May 17, 2017 11:02 AM
I keep forgetting that you can do non-windowed aggregates with OVER/PARTITIONING/ORDER BY back that far!
However, I am still not convinced the OP doesn't need a running total (i.e. 100 + 50 + 200 values would total 300+). If not they may be able to get by without the windowing necessary to do those.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service - TheSQLGuru - Saturday, May 20, 2017 8:19 AM
Sorry for taking so long to respond. I started my new job on Monday.
Adding an ORDER BY clause will create a running total (with the default frame of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). If he doesn't need a running total, then he would just leave out the ORDER BY clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply