Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Partition results from a function variable

Posted on 2010-09-20
10
Medium Priority
?
267 Views
Last Modified: 2012-05-10
Hi,

I have this function that returns data from a table. I use the main function to retrieve all the data I want then I try to break it down from a second function cause I have different needs in the reports that I use.

This is the function:

-----------------------
declare @d datetime
set @d = getdate() + {?Days}
select  * from dbo.ServiceViewBatch( @d )
WHERE serviceType = 'VMF'
----------------------

I need to partition the result for this to be the same as another function I used to use to get the same results, this was the old function. Now, I've tried adding the partition bit to the function above but it keeps saying the BY bit is wrong. Any ideas how to integrate the partition bit in the the function above? It's ONLY the partition bit I need to integrate, same BY statements as all of those are in the table I get the data from.

--------------------
  RETURN ( SELECT * FROM (
SELECT countryCode, cityCode, itemCode, storeType, vendorCode, gridCodeX, gridCodeY, facings, brand, rc, storeCode, storeName, ARABIC, ItemName, upcCode, TREF,
ROW_NUMBER() OVER ( PARTITION BY countryCode, cityCode, storeCode, gridCodeX, vendorCode ORDER BY vendorCode DESC) rnum
FROM dbo.ScheduleView
WHERE (CASE DATEPART(weekday, @date )
                      WHEN 1 THEN [SunVMF] WHEN 2 THEN [MonVMF] WHEN 3 THEN [TueVMF] WHEN 4 THEN [WedVMF] WHEN 5 THEN [ThuVMF] WHEN 6 THEN [FriVMF] WHEN 7 THEN
                       [SatVMF] END = 1)) temp
WHERE rnum = 1
   )
----------------------------
0
Comment
Question by:kenuk110
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33714524
>Now, I've tried adding the partition bit to the function above but it keeps saying the BY bit is wrong.
can you please clarify what exctly you are adding when it fails?
I mean, the code, as posted, seems fine ...
0
 

Author Comment

by:kenuk110
ID: 33714570
Sorry, yeah I see what you mean.

This is what I run:

declare @d datetime
set @d = getdate() + {?Days}
select  * from dbo.ServiceViewBatch( @d )
WHERE serviceType = 'VMF'

I'd like to have it like this

declare @d datetime
set @d = getdate() + {?Days}
select  *, ROW_NUMBER() OVER ( PARTITION BY countryCode, cityCode, storeCode, gridCodeX, vendorCode ORDER BY vendorCode DESC) from dbo.ServiceViewBatch( @d )
WHERE serviceType = 'VMF'

When I put this in it says it there is an problem with the PARTITION BY bit. It's obviously wrong but I really don't have the skill to figure out which bit is wrong!!

Any help would be really appreciated.

Regards,

Ken
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33714668
please try this:

declare @d datetime
set @d = getdate() + {?Days}
select v.*
, ROW_NUMBER() OVER ( PARTITION BY countryCode, cityCode, storeCode, gridCodeX, vendorCode ORDER BY vendorCode DESC) 
from dbo.ServiceViewBatch( @d ) v
WHERE v.serviceType = 'VMF'

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:kenuk110
ID: 33714720
Hi,

It still brings back back all the records and not just a single record, as in I should only see one record for each gridcodex for that store in that city and in that country.

Let me see if I can figure it out or give you some more info.

0
 

Author Comment

by:kenuk110
ID: 33714879
Hi again,

I'm stuck, I have tried 'partitioning' with just the gridcodex, the storecode, most of them but the result still returns all the records for a gridcodex.

This is the function that I'm using to get the main data:

ALTER function [dbo].[ServiceViewBatch](@date date)
returns table
as

RETURN WITH cte AS (
  SELECT [ServiceValue]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       , [itemCode]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , ROW_NUMBER()
            OVER (ORDER BY CountryCode, CityCode, StoreCode, RC, servicetype, gridcodeX, itemCode) rnum
  FROM [T1].[dbo].[ServiceSchedule]
  WHERE serviceday = DATENAME(WEEKDAY, @date)
)
-- final selection
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM cte a
CROSS APPLY(
  SELECT SUM(serviceValue) svSum
  FROM cte b
  WHERE b.rnum <= a.rnum
) rt
------------------------------

The original code is passes the WHERE statement correctly but when it runs the updated code it brings back all the records.

declare @d datetime
set @d = getdate() + {?Days}
select  * from dbo.ServiceViewBatch( @d )
WHERE serviceType = 'VMF'

Really stuck now as to how to select the data so it'd distinct by countryCode, cityCode, storeCode, gridCodeX.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33714995
note sure, but what about:
ALTER function [dbo].[ServiceViewBatch](@date date)
returns table
as

RETURN WITH cte AS (
  SELECT [ServiceValue]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       , [itemCode]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , ROW_NUMBER()
            OVER (ORDER BY CountryCode, CityCode, StoreCode, RC, servicetype, gridcodeX, itemCode) rnum
  FROM [T1].[dbo].[ServiceSchedule]
  WHERE serviceday = DATENAME(WEEKDAY, @date)
)
-- final selection
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM cte a
CROSS APPLY(
  SELECT SUM(serviceValue) svSum
  FROM cte b
  WHERE b.rnum <= a.rnum
) rt
WHERE a.rnum = 1

Open in new window

0
 

Author Comment

by:kenuk110
ID: 33715243
Hi again,

I have to apologize, I think I've mis comminicated the solution. The function you have sent back needs to be just how it was, it's the main function I use to get back all the data I need. From there I call the function but put in where statements like WHERE serviceType = 'VMF' etc. It just breaks down the full view to just the type of records I need.

The one I'm having trouble with is the small query that I pass to the function, this script:

declare @d datetime
set @d = getdate() + {?Days}
select v.*
, ROW_NUMBER() OVER ( PARTITION BY countryCode, cityCode, storeCode, gridCodeX, vendorCode ORDER BY vendorCode DESC)
from dbo.ServiceViewBatch( @d ) v
WHERE v.serviceType = 'VMF'

I have added this to get what I hoped was distinct per gridcodeX per store per city per country but it brings back all the records attached to that gridcodex. I only actually need one record of any that has this gridcodex assigned to it, it's just the way I need to present the data for this report.

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 33715317
hmm...
declare @d datetime
set @d = getdate() + {?Days}
;with data as ( select v.*
, ROW_NUMBER() OVER ( PARTITION BY countryCode, cityCode, storeCode, gridCodeX, vendorCode ORDER BY vendorCode DESC)rn
from dbo.ServiceViewBatch( @d ) v
WHERE v.serviceType = 'VMF'
)
select * from data
where rn = 1

Open in new window

0
 

Author Comment

by:kenuk110
ID: 33715389
PERFECT!!!

Brilliant, thank you so much for this, really.

Regards,

Ken
0
 

Author Closing Comment

by:kenuk110
ID: 33715396
Perfect solution!
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question